MySQL Database Create

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

MySQL Database Create

Post by Pazuzu156 »

I'm writing a php page that will create a database with tables so when the user installs my guestbook into their web server, it will automatically create the database and insert the tables. Yet the code does not create this for me. Here is the code I'm using:

Code: Select all

<?php

$connect = mysql_connect("localhost","root","");
$dbconn = mysql_select_db("jae_guestbook");
$queryget = mysql_query("SELECT * FROM userposts ORDER BY id ASC");

$checkdb = $dbconn;

if($checkdb){
	echo "<meta http-equiv='refresh' content='1;../index.php'>";
} else {
	$install = mysql_query("CREATE DATABASE  `jae_guestbook` ;

CREATE TABLE  `jae_guestbook`.`userposts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 64 ) NOT NULL ,
`email` VARCHAR( 64 ) NOT NULL ,
`message` TEXT NOT NULL ,
`date` DATE NOT NULL ,
`time` TIME NOT NULL
) ENGINE = MYISAM ;
");
}

?>
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL Database Create

Post by mikosiko »

PHP MYSQL API doesn't allow multi-queries... you are trying to execute a multi-query
s992
Forum Contributor
Posts: 124
Joined: Wed Oct 27, 2010 3:06 pm

Re: MySQL Database Create

Post by s992 »

I believe you need to run two separate queries - one to create the DB, and one to create the table.

From PHP: mysql_query:
mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
and
The query string should not end with a semicolon. Data inside the query should be properly escaped.
EDIT: mikosiko beat me to it :(
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: MySQL Database Create

Post by McInfo »

1. (Argh! What mikosiko and s992 said.)

2. The $checkdb variable is redundant and unnecessary.

3. It is illogical to execute a query before testing the result of mysql_select_db().

4. See mysql_create_db().

5. There are other (maybe better) ways to connect to databases, such as PDO and MySQLi.
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: MySQL Database Create

Post by Pazuzu156 »

It checks for the database before creating to check if it already exists. I'll work on doing it in two different queries
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
s992
Forum Contributor
Posts: 124
Joined: Wed Oct 27, 2010 3:06 pm

Re: MySQL Database Create

Post by s992 »

Pazuzu156 wrote:It checks for the database before creating to check if it already exists. I'll work on doing it in two different queries
I think he was referencing this query:

Code: Select all

$queryget = mysql_query("SELECT * FROM userposts ORDER BY id ASC");
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: MySQL Database Create

Post by Pazuzu156 »

s992 wrote:
Pazuzu156 wrote:It checks for the database before creating to check if it already exists. I'll work on doing it in two different queries
I think he was referencing this query:

Code: Select all

$queryget = mysql_query("SELECT * FROM userposts ORDER BY id ASC");
I understand, also, I don't think since this page is only running once to post the queries, the $queryget = mysql_query("SELECT * FROM userposts ORDER BY id ASC"); probably doesn't need to be in there as it is unnecessary.
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: MySQL Database Create

Post by Pazuzu156 »

s992 wrote:I believe you need to run two separate queries - one to create the DB, and one to create the table.

From PHP: mysql_query:
mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
and
The query string should not end with a semicolon. Data inside the query should be properly escaped.
EDIT: mikosiko beat me to it :(
Thanks, the semicolons were hindering me from this working, but it works now, thanks.
mikosiko wrote:PHP MYSQL API doesn't allow multi-queries... you are trying to execute a multi-query
I'm using phpMyAdmin. From messing with querying here, it allows multiple queries.
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL Database Create

Post by mikosiko »

Pazuzu156 wrote:
mikosiko wrote:PHP MYSQL API doesn't allow multi-queries... you are trying to execute a multi-query
I'm using phpMyAdmin. From messing with querying here, it allows multiple queries.
you seems to be confused.... the PHP code that you posted... specially this part:

Code: Select all

        
$install = mysql_query("CREATE DATABASE  `jae_guestbook` ;
CREATE TABLE  `jae_guestbook`.`userposts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 64 ) NOT NULL ,
`email` VARCHAR( 64 ) NOT NULL ,
`message` TEXT NOT NULL ,
`date` DATE NOT NULL ,
`time` TIME NOT NULL
) ENGINE = MYISAM ;
");
IS a multi-query intent using the PHP MYSQL API (mysql_query) and THAT is not allowed... phpMyAdmin is a different animal... don't confound a SQL script execution using any MYSQL client GUI (as PhpMyAdmin, MySQLQUERY, MYSQLWorkwench etc..etc) with trying to execute the same using the PHP MYSQl API ... these are 2 totally different things. hope this help to clarify you.
User avatar
Pazuzu156
Forum Contributor
Posts: 241
Joined: Sat Nov 20, 2010 9:00 pm
Location: GA, USA
Contact:

Re: MySQL Database Create

Post by Pazuzu156 »

I see. And I have separated them into two different queries as well.
- Kaleb Klein
------------------------------------
Web Developer | Software Developer
https://kalebklein.com
PGP Key: https://keybase.io/pazuzu156
Post Reply