PHP Create Database problem

sweyhrich · April 20th, 2005, 11:02 PM

I am trying to learn about manipulating a MySQL database from PHP, using the examples in the "Beginning PHP4" book. I've modified the "create_db.php" file from pg. 413 (of the 2000 edition), and am getting one of those frustrating PHP parse errors.

Here is the php file:

Code:

<?php
// create_db.php

include "../../common_db.inc";

$dbname = "godogs2_gklg1";

$banner_tablename = 'banner2';

$banner_table_def =  "num INT(11) NOT NULL AUTO_INCREMENT, ";
$banner_table_def .= "fontname TINYTEXT NOT NULL, ";
$banner_table_def .= "text_color TINYTEXT NOT NULL, ";
$banner_table_def .= "bkgd_color TINYTEXT NOT NULL, ";
$banner_table_def .= "height TINYINT(4) NOT NULL DEFAULT '0', ";
$banner_table_def .= "direction TINYTEXT NOT NULL, ";
$banner_table_def .= "behavior TINYTEXT NOT NULL, ";
$banner_table_def .= "scroll_amount TINYINT(4) NOT NULL DEFAULT '0', ";
$banner_table_def .= "scroll_delay TINYINT(4) NOT NULL DEFAULT '0', ";
$banner_table_def .= "banner TEXT NOT NULL, ";
$banner_table_def .= "PRIMARY KEY ('num')";

$banner_type = "TYPE=MyISAM AUTO_INCREMENT=1";

$link_id = db_connect($dbname);

if(!$link_id) die(sql_error());

echo "Connected to database '$dbname'...<br><br>";

if(!mysql_select_db($dbname, $link_id))
    die(sql_error());

echo "Selected database '$dbname'...<br><br>";


if (!mysql_query ("CREATE TABLE $banner_tablename ($banner_table_def)") )
    die(sql_error());

echo "Successfully created the $banner_tablename table.";

?>

Here is what appears on the web page when I try to load this page into my browser:

Code:

Connected to database 'godogs2_gklg1'...

Selected database 'godogs2_gklg1'...

1064: You have an error in your SQL syntax. Check the manual that corresponds 
to your MySQL server version for the right syntax to use near ''num'))' at line 1

Obviously, the error is not at line 1, but I am having trouble finding exactly WHERE it IS. Can anyone help me find this problem?

Steven Weyhrich

jmukesh · April 27th, 2005, 05:06 AM

Hi,

Well if you create your table from the mysql command terminal you need to
give the following command:

CREATE TABLE tblcustomimages (
  image_id int(11) NOT NULL auto_increment,
  custom_id int(11) NOT NULL default '0',
  custom_imagename varchar(100) default NULL,
  image_path varchar(100) default NULL,
  content_id int(11) NOT NULL default '0',
  PRIMARY KEY (image_id)
) TYPE=MyISAM;

Now , what i would suggest is just give an echo statement after you prepare the
sql query.

echo $banner_table_def ;

and post it back here, so that i would be in a position to make any comment.

Thanx

sweyhrich · April 27th, 2005, 01:40 PM

Okay, here we go... :-)

I used phpMyAdmin to get the MySQL code, and made this statement:

Code:

$sql = "DROP TABLE IF EXISTS `banner`;
CREATE TABLE `banner` (
  `id` int(11) NOT NULL auto_increment,
  `banner_name` tinytext NOT NULL,
  `fontname` tinytext NOT NULL,
  `text_color` tinytext NOT NULL,
  `bkgd_color` tinytext NOT NULL,
  `height` tinyint(4) NOT NULL default '0',
  `direction` tinytext NOT NULL,
  `behavior` tinytext NOT NULL,
  `scroll_amount` tinyint(4) NOT NULL default '0',
  `scroll_delay` tinyint(4) NOT NULL default '0',
  `banner` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;";

Then, I echoed what I got after creating that, and got this below:

Code:

$sql = 'DROP TABLE IF EXISTS `banner`; CREATE TABLE `banner` 
( `id` int(11) NOT NULL auto_increment, `banner_name` tinytext NOT NULL, 
`fontname` tinytext NOT NULL, `text_color` tinytext NOT NULL, `bkgd_color` 
tinytext NOT NULL, `height` tinyint(4) NOT NULL default '0', `direction` tinytext 
NOT NULL, `behavior` tinytext NOT NULL, `scroll_amount` tinyint(4) NOT NULL 
default '0', `scroll_delay` tinyint(4) NOT NULL default '0', `banner` text NOT NULL,
PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ;'

Running this through the following code:

Code:

if (!mysql_query ($sql)) die(sql_error());

Resulted again in this output:

Code:

1064: You have an error in your SQL syntax. Check the manual that 
corresponds to your MySQL server version for the right syntax to use near '; CREATE 
TABLE `banner` ( `id` int(11) NOT NULL auto_incremen

Is that what you were looking for?

Steven Weyhrich

jmukesh · April 28th, 2005, 06:59 AM

Hi Steven ,

There is a single quote which is misplaced and causing the problems.

$sql = "DROP TABLE IF EXISTS `banner`; CREATE TABLE `banner`
( `id` int(11) NOT NULL auto_increment, `banner_name` tinytext NOT NULL,
`fontname` tinytext NOT NULL, `text_color` tinytext NOT NULL, `bkgd_color`
tinytext NOT NULL, `height` tinyint(4) NOT NULL default '0', `direction` tinytext
NOT NULL, `behavior` tinytext NOT NULL, `scroll_amount` tinyint(4) NOT NULL
default '0', `scroll_delay` tinyint(4) NOT NULL default '0', `banner` text NOT NULL,
PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ;";

I have encapsulated the entire string in double quotes ie ".
Well now this will work, :D

Regards
Jmukesh

sweyhrich · April 28th, 2005, 09:59 AM

Quote:

quote:There is a single quote which is misplaced and causing the problems.

Sorry; my posting did not QUITE match the code in my source php file. There IS a double quote at the end of the $sql string, as you stated in your reply. Any other thoughts?

Steven Weyhrich

mani_he · April 28th, 2005, 02:04 PM

Hi sweyhrich

$sql = "DROP TABLE IF EXISTS `banner`; CREATE TABLE `banner`
( `id` int(11) NOT NULL auto_increment, `banner_name` tinytext NOT NULL,
`fontname` tinytext NOT NULL, `text_color` tinytext NOT NULL, `bkgd_color`
tinytext NOT NULL, `height` tinyint(4) NOT NULL default '0', `direction` tinytext
NOT NULL, `behavior` tinytext NOT NULL, `scroll_amount` tinyint(4) NOT NULL
default '0', `scroll_delay` tinyint(4) NOT NULL default '0', `banner` text NOT NULL,
PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ;";

See the last line, you used 2 ; so delete the 1st one and see if it works.
so the code should be like:

//mysql Connect
//Select Database

$sql = "CREATE TABLE `banner`
( `id` int(11) NOT NULL auto_increment, `banner_name` tinytext NOT NULL,
`fontname` tinytext NOT NULL, `text_color` tinytext NOT NULL, `bkgd_color`
tinytext NOT NULL, `height` tinyint(4) NOT NULL default '0', `direction` tinytext
NOT NULL, `behavior` tinytext NOT NULL, `scroll_amount` tinyint(4) NOT NULL
default '0', `scroll_delay` tinyint(4) NOT NULL default '0', `banner` text NOT NULL,
PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=1";

$query = mysql_query($sql);

sweyhrich · April 28th, 2005, 10:27 PM

Hi again, I found the problem. Part of it was apparently having the "DROP TABLE IF EXISTS" as part of the $sql. When I took it out (as your example did) it worked for me. Thanks!

Steven Weyhrich

jmukesh · April 29th, 2005, 12:20 AM

Hi,

Well thats great,

All is well that ends well!

Regards
Mukesh

vivekananth · January 25th, 2007, 01:07 AM

hai

davidc2p · January 29th, 2007, 06:39 AM

I always avoid to put the type of Database ("TYPE=MyISAM AUTO_INCREMENT=1";) because it always says there's a yntax error in the query.

Visit my page: http://www.webrickco.com