By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
----------------------------------
$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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.