473,573 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
testOfSetupScri pt 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 whatBelongsToWh at (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
belongsToWhichP age int(11) NOT NULL default '0',
type varchar(255) NOT NULL default '',
belongsToWhichW ebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
EOD;

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

if (is_object($db) ) {
if (is_array($form Inputs)) {
extract($formIn puts);

$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
McDatastoreConn ectorMySql.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/publicdomainsof tware.org/httpdocs/setup/neededFiles/McDatastoreConn ectorMySql.php
on line 349

Here is the method that is being flagged:

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

*/
function error() {
die('('.mysql_e rrno($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/publicdomainsof tware.org/httpdocs/setup/neededFiles/createTheDataba seTables.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 = "usr2260421 27";
// $password = "38F1yumgErcUY" ;
// $port = $db_port;

$formInputs = $GLOBALS["formInputs "];

if (is_array($form Inputs)) {
extract($formIn puts);

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

$this->pp_linkid = mysql_connect($ server.':'.$por t, $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($dbNa me);
} 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_d b($db, $this->pp_linkid)) {
$this->error();
}
}

Jul 17 '05 #1
5 3030
On 20 Dec 2004 12:09:12 -0800, lk******@geocit ies.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
testOfSetupScr ipt 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 whatBelongsToWh at (
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.
belongsToWhich Page int(11) NOT NULL default '0',
Ditto.
type varchar(255) NOT NULL default '',
belongsToWhich Website 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/publicdomainsof tware.org/httpdocs/setup/neededFiles/McDatastoreConn ectorMySql.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/publicdomainsof tware.org/httpdocs/setup/neededFiles/createTheDataba seTables.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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #2
----------------------------------
$str4 = <<<EOD
CREATE TABLE whatBelongsToWh at (
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 '',
belongsToWhich Website 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($form Inputs)) {
echo "<p>The database variables are:";
print_r($formIn puts);
echo "</p>";
extract($formIn puts);

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

$this->pp_linkid = mysql_connect($ server.':'.$por t, $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($dbNa me);
} 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_d b($db, $this->pp_linkid)) {
$errmsg = 'Query error: ' . mysql_error();
echo "<p>Trouble in selectdb() in McDatastoreConn ectorMySql.
$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******@geoci ties.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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
testOfSetupScri pt 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 whatBelongsToWh at (
id int(11) NOT NULL auto_increment,
what int(11) NOT NULL default '0',
belongsToWhichP age int(11) NOT NULL default '0',
type varchar(255) NOT NULL default '',
belongsToWhichW ebsite varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
EOD;

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

if (is_object($db) ) {
if (is_array($form Inputs)) {
extract($formIn puts);

$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
McDatastoreConn ectorMySql.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/publicdomainsof tware.org/httpdocs/setup/neededFiles/McDatastoreConn ectorMySql.php
on line 349

Here is the method that is being flagged:

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

*/
function error() {
die('('.mysql_e rrno($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/publicdomainsof tware.org/httpdocs/setup/neededFiles/createTheDataba seTables.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 = "usr2260421 27";
// $password = "38F1yumgErcUY" ;
// $port = $db_port;

$formInputs = $GLOBALS["formInputs "];

if (is_array($form Inputs)) {
extract($formIn puts);

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

$this->pp_linkid = mysql_connect($ server.':'.$por t, $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($dbNa me);
} 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_d b($db, $this->pp_linkid)) {
$this->error();
}
}

Jul 17 '05 #5
lk******@geocit ies.com wrote:
----------------------------------
$str4 = <<<EOD
CREATE TABLE whatBelongsToWh at (
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
2241
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 value="1">Main</option> <option value="2">Main > Computers</option> <option value="4">Main > Computers > Hardware </option>
125
14607
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 software giant such as Microsoft SQL Server, Oracle, and Sybase? Is PostgreSQL reliable enough to be used for high-end commercial application? Thanks
48
3828
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 query of all students in both tables with no duplicates. No clue whatsoever.
2
1501
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 can't show them in asp pages. the browser gives me following error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Access...
7
2107
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 database that you want, and send it as a GET request. This would be an example: ...
39
3203
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
2666
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 the tutorials I can find seem to bypass the issue by ignoring it? Am I missunderstanding something?
12
4533
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 database with all tables. Can you help please ?
1
8345
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 mysql datasbases that are functioning perfectly. I am trying to add a database which will be stored on a different drive, therefore the entry in...
0
7760
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7996
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7760
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8049
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6392
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3723
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1289
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1029
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.