Page 1 of 1

MySQL Database Create

Posted: Wed Dec 01, 2010 11:51 am
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 ;
");
}

?>

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:06 pm
by mikosiko
PHP MYSQL API doesn't allow multi-queries... you are trying to execute a multi-query

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:07 pm
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 :(

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:10 pm
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.

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:20 pm
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

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:21 pm
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");

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:29 pm
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.

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 1:33 pm
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.

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 3:33 pm
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.

Re: MySQL Database Create

Posted: Wed Dec 01, 2010 3:48 pm
by Pazuzu156
I see. And I have separated them into two different queries as well.