473,287 Members | 1,643 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

what do you need to create tables in a MySql database?

I have a webserver through Rackspace. I create a domain. I create an
FTP user. I upload some files. I create a database called
testOfSetupScript and then I create a database user named setup.

I write some PHP code which should, I think, be able to to auto create
the tables.

The SQL looks like this:


$str4 = <<<EOD
CREATE TABLE whatBelongsToWhat (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
belongsToWhichPage int(11) NOT NULL default '0',
type varchar(255) NOT NULL default '',
belongsToWhichWebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
EOD;

global $db;
$formInputs = $GLOBALS["formInputs"];

if (is_object($db)) {
if (is_array($formInputs)) {
extract($formInputs);

$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);

// 11-23-04 - this code runs every time the pages loads. Obviously
we don't want to
// run this code if the tables already exist. So we check for tables
first.
if ($num == 0) {
$db->query($str1);
$db->query($str2);
$db->query($str3);
$db->query($str4);

$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);

if ($num == 4) {
echo "<p>The 4 parts of the database have been successfully
created</p>";
} else {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Awful sorry, there is a problem. The database should
have 4 tables but only has '$num'. $errmsg ";
}
} else {
if ($num < 4) {
echo "<p>The 4 parts of the database have been successfully
created</p>";
} else {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Awful sorry, there is a problem. The database should
have 4 tables but only has $num. $errmsg</p>";
}
}
}
} else {
echo "<p>Awful sorry, but the software expects a database object that
will allow us to connect to the database, but we don't seem able to
find or create the database object. Please make sure that the file
McDatastoreConnectorMySql.php was uploaded and is in the 'neededFiles'
folder.</p>";
}
}

I'm only showing one of the SQL statements for simplicity.


But I get these warnings:

Warning: Supplied argument is not a valid MySQL-Link resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/McDatastoreConnectorMySql.php
on line 349

Here is the method that is being flagged:

/*
error function
==============
terminates script execution with a message

*/
function error() {
die('('.mysql_errno($this->pp_linkid).')
'.mysql_error($this->pp_linkid));
}


Also I get this:

Warning: Supplied argument is not a valid MySQL result resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/createTheDatabaseTables.php
on line 175
which points to the last line here:
$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);


I'm using this method to connect to the database. How do I trouble
shoot what the problem is?
/**
*
* 11-23-04 - this class gets called at the top of setup.php and it
becomes a
* global variable for the setup script. However, there is no
constructor so
* nothing is initialized. It's simply held in global space, ready to
be used.
*
*
*
*
*
*/
function connectToDs() {
// $db = $dbName;
// $server = "localhost";
// $user = "usr226042127";
// $password = "38F1yumgErcUY";
// $port = $db_port;

$formInputs = $GLOBALS["formInputs"];

if (is_array($formInputs)) {
extract($formInputs);

if ($dbUsername && $dbPassword && $dbName) {

$this->pp_linkid = mysql_connect($server.':'.$port, $user,
$password);

if ($this->pp_linkid) {
return true;
} else {
echo "<p>Awful sorry, but when we tried to reach the database,
using the username and password you provided, we failed to make a
connection. The most likely reason is that you mis-entered the database
username and password. Remember that the database username and password
are different from the username and password you'll use to reach the
cms.</p>";
}

$this->selectdb($dbName);
} else {
echo "<p>Awful sorry, but we're being asked to contact the
database, yet we don't yet have a database username or a database
password, nor the name of the database. Please fill those in and try
again. Use Plesk or talk to your admin to get a database username and
password. Please note that the database username and password are going
to be different from the username and password that you'll use to login
to the cms.</p>";
}
}
}


/*
selects the active database
===========================
*/
function selectdb($db) {
if (FALSE === @mysql_select_db($db, $this->pp_linkid)) {
$this->error();
}
}

Jul 17 '05 #1
5 3005
On 20 Dec 2004 12:09:12 -0800, lk******@geocities.com wrote:
I have a webserver through Rackspace. I create a domain. I create an
FTP user. I upload some files. I create a database called
testOfSetupScript and then I create a database user named setup.

I write some PHP code which should, I think, be able to to auto create
the tables.

The SQL looks like this:

$str4 = <<<EOD
CREATE TABLE whatBelongsToWhat (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
Why a quoted zero when it's a numeric column? Won't cause an error, though.
belongsToWhichPage int(11) NOT NULL default '0',
Ditto.
type varchar(255) NOT NULL default '',
belongsToWhichWebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Queries should not end in a semi-colon.
EOD;

But I get these warnings:

Warning: Supplied argument is not a valid MySQL-Link resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/McDatastoreConnectorMySql.php
on line 349

Here is the method that is being flagged:

/*
error function
==============
terminates script execution with a message*/
function error() {
die('('.mysql_errno($this->pp_linkid).')
'.mysql_error($this->pp_linkid));
}

Also I get this:

Warning: Supplied argument is not a valid MySQL result resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/createTheDatabaseTables.php
on line 175
which points to the last line here:

$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);


Of what type is $db? Does it return MySQL result set resources? It appears you
haven't checked for errors.
I'm using this method to connect to the database. How do I trouble
shoot what the problem is?


Check each and every mysql_* call for returning false, and if it does, output
mysql_error() along with some information to track the line causing the error.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #2
----------------------------------
$str4 = <<<EOD
CREATE TABLE whatBelongsToWhat (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
Why a quoted zero when it's a numeric column? Won't cause an error,
though.
-----------------------------------

I don't know why it is like that. I asked phpMyAdmin to dump the
structure for me, and this is what it created. I guess for whatever
reason the programmers working on phpMyAdmin thought this was a good
idea.


-----------------------------------type varchar(255) NOT NULL default '',
belongsToWhichWebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;


Queries should not end in a semi-colon.
-----------------------------------

Again, it was what phpMyAdmin was putting out. Thanks for catching this
as it was causing me great trouble.

$db is an object and it does get tested with is_object after it is
created, and an error is thrown if it is not an object.

I'm adding in more error checking. Still haven't resolved all my
problems. Right now I'm getting this:

Query error: No Database Selected(1046) No Database Selected

Yet my selectDb() method, which you can see below at bottom, doesn't
seem to be throwing an error.
/**
*
* 11-23-04 - this class gets called at the top of setup.php and it
becomes a
* global variable for the setup script. However, there is no
constructor so
* nothing is initialized. It's simply held in global space, ready to
be used.
*
*
*
*
*
*/
function connectToDs() {
$formInputs = $GLOBALS["formInputs"];

if (is_array($formInputs)) {
echo "<p>The database variables are:";
print_r($formInputs);
echo "</p>";
extract($formInputs);

if ($dbUserName && $dbPassword && $dbName) {
$db = $dbName;
$server = "localhost";
$user = $dbUserName;
$password = $dbPassword;

$this->pp_linkid = mysql_connect($server.':'.$port, $user,
$password);

if ($this->pp_linkid) {
return true;
} else {
$errmsg = 'Query error: ' . mysql_error();
echo $errmsg;
echo "<p>Awful sorry, but when we tried to reach the database,
using the username and password you provided, we failed to make a
connection. The most likely reason is that you mis-entered the database
username and password. Remember that the database username and password
are different from the username and password you'll use to reach the
cms.</p>";
}

$this->selectdb($dbName);
} else {
echo "<p>Awful sorry, but we're being asked to contact the
database, yet we don't yet have a database username or a database
password, nor the name of the database. Please fill those in and try
again. Use Plesk or talk to your admin to get a database username and
password. Please note that the database username and password are going
to be different from the username and password that you'll use to login
to the cms.</p>";
}
}
}


/*
selects the active database
===========================
*/
function selectdb($db) {
if (FALSE === @mysql_select_db($db, $this->pp_linkid)) {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Trouble in selectdb() in McDatastoreConnectorMySql.
$errmsg </p>";
$this->error();
}
}

Jul 17 '05 #3
Okay, sorry, I am an idiot. I was putting "return true" a head of the
part where I connect to the database.

Jul 17 '05 #4
Are you sure you've successfully connected to the database? I'm assuming
this happens either within $db's constructor or some other method, but not
in the code you've shown. You should follow Andy's advice on checking for
errors.

Regarding the quotes in the create statement, this was probably generated by
a "SHOW CREATE TABLE" statement. For some reason MySQL quotes INT's values
in CREATE statements.

- Kevin
<lk******@geocities.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have a webserver through Rackspace. I create a domain. I create an
FTP user. I upload some files. I create a database called
testOfSetupScript and then I create a database user named setup.

I write some PHP code which should, I think, be able to to auto create
the tables.

The SQL looks like this:


$str4 = <<<EOD
CREATE TABLE whatBelongsToWhat (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
belongsToWhichPage int(11) NOT NULL default '0',
type varchar(255) NOT NULL default '',
belongsToWhichWebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
EOD;

global $db;
$formInputs = $GLOBALS["formInputs"];

if (is_object($db)) {
if (is_array($formInputs)) {
extract($formInputs);

$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);

// 11-23-04 - this code runs every time the pages loads. Obviously
we don't want to
// run this code if the tables already exist. So we check for tables
first.
if ($num == 0) {
$db->query($str1);
$db->query($str2);
$db->query($str3);
$db->query($str4);

$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);

if ($num == 4) {
echo "<p>The 4 parts of the database have been successfully
created</p>";
} else {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Awful sorry, there is a problem. The database should
have 4 tables but only has '$num'. $errmsg ";
}
} else {
if ($num < 4) {
echo "<p>The 4 parts of the database have been successfully
created</p>";
} else {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Awful sorry, there is a problem. The database should
have 4 tables but only has $num. $errmsg</p>";
}
}
}
} else {
echo "<p>Awful sorry, but the software expects a database object that
will allow us to connect to the database, but we don't seem able to
find or create the database object. Please make sure that the file
McDatastoreConnectorMySql.php was uploaded and is in the 'neededFiles'
folder.</p>";
}
}

I'm only showing one of the SQL statements for simplicity.


But I get these warnings:

Warning: Supplied argument is not a valid MySQL-Link resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/McDatastoreConnectorMySql.php
on line 349

Here is the method that is being flagged:

/*
error function
==============
terminates script execution with a message

*/
function error() {
die('('.mysql_errno($this->pp_linkid).')
'.mysql_error($this->pp_linkid));
}


Also I get this:

Warning: Supplied argument is not a valid MySQL result resource in
/home/httpd/vhosts/publicdomainsoftware.org/httpdocs/setup/neededFiles/createTheDatabaseTables.php
on line 175
which points to the last line here:
$query = "SHOW TABLES FROM $dbName";
$result = $db->query($query);
$num = mysql_num_rows($result);


I'm using this method to connect to the database. How do I trouble
shoot what the problem is?
/**
*
* 11-23-04 - this class gets called at the top of setup.php and it
becomes a
* global variable for the setup script. However, there is no
constructor so
* nothing is initialized. It's simply held in global space, ready to
be used.
*
*
*
*
*
*/
function connectToDs() {
// $db = $dbName;
// $server = "localhost";
// $user = "usr226042127";
// $password = "38F1yumgErcUY";
// $port = $db_port;

$formInputs = $GLOBALS["formInputs"];

if (is_array($formInputs)) {
extract($formInputs);

if ($dbUsername && $dbPassword && $dbName) {

$this->pp_linkid = mysql_connect($server.':'.$port, $user,
$password);

if ($this->pp_linkid) {
return true;
} else {
echo "<p>Awful sorry, but when we tried to reach the database,
using the username and password you provided, we failed to make a
connection. The most likely reason is that you mis-entered the database
username and password. Remember that the database username and password
are different from the username and password you'll use to reach the
cms.</p>";
}

$this->selectdb($dbName);
} else {
echo "<p>Awful sorry, but we're being asked to contact the
database, yet we don't yet have a database username or a database
password, nor the name of the database. Please fill those in and try
again. Use Plesk or talk to your admin to get a database username and
password. Please note that the database username and password are going
to be different from the username and password that you'll use to login
to the cms.</p>";
}
}
}


/*
selects the active database
===========================
*/
function selectdb($db) {
if (FALSE === @mysql_select_db($db, $this->pp_linkid)) {
$this->error();
}
}

Jul 17 '05 #5
lk******@geocities.com wrote:
----------------------------------
$str4 = <<<EOD
CREATE TABLE whatBelongsToWhat (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',

Why a quoted zero when it's a numeric column? Won't cause an error,
though.
-----------------------------------

I don't know why it is like that. I asked phpMyAdmin to dump the
structure for me, and this is what it created. I guess for whatever
reason the programmers working on phpMyAdmin thought this was a good
idea.


MySQL itself quotes numerical defaults (at least zeros) if you issue a
SHOW CREATE TABLE tablename statement.

JP

--
Sorry, <de*****@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.
Jul 17 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Brian Murphy | last post by:
<?php /* I need your help. I'd be very thankfull if write me this script.I need a script that displays a list of categories and subcategories like this: <select name="category"> <option...
125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
2
by: bhv | last post by:
hello everybody. i am new to MySQL and want to build some asp pages providing search facility where search result displays from database. i have successfully created database in MySQL 5.0, but i...
7
by: lawrence k | last post by:
Okay, I just backed up my database, just in case. The whole schema for the database is here: http://www.accumulist.com/index.php?whatPage=db.php You can run any SELECT query against this...
39
by: windandwaves | last post by:
Hi Folk I have to store up to eight boolean bits of information about an item in my database. e.g. with restaurant drive-through facility yellow windows
15
by: harvey | last post by:
How do I make PHP create a database for mysql please? I can see how to make tables and I have read all the documents I can find but I don't understand how to make the database itself. All...
12
by: Robbo | last post by:
Hi, My database contains 45 tables the structure of which is evolving as I develop. I need a utility that will "read" the structure of this database and it's tables and then create an identical...
1
by: amygrant1701 | last post by:
Hi, I've done this before so I don't see what I could doing wrong here. I'm running mysql 5x on freebsd. I'm using the default data directory of "/var/db/mysql" In there I have several dozen...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.