Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > Beginning PHP
| Search | Today's Posts | Mark Forums Read
Beginning PHP Beginning-level PHP discussions. More advanced coders should post to the Pro PHP forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning PHP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 20th, 2005, 11:02 PM
Registered User
 
Join Date: Apr 2005
Location: Omaha, NE, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default PHP Create Database problem

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
  #2 (permalink)  
Old April 27th, 2005, 05:06 AM
Authorized User
 
Join Date: Apr 2005
Location: Pune, MH, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jmukesh Send a message via Yahoo to jmukesh
Default

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


  #3 (permalink)  
Old April 27th, 2005, 01:40 PM
Registered User
 
Join Date: Apr 2005
Location: Omaha, NE, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old April 28th, 2005, 06:59 AM
Authorized User
 
Join Date: Apr 2005
Location: Pune, MH, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jmukesh Send a message via Yahoo to jmukesh
Default

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
  #5 (permalink)  
Old April 28th, 2005, 09:59 AM
Registered User
 
Join Date: Apr 2005
Location: Omaha, NE, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old April 28th, 2005, 02:04 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: , , .
Posts: 117
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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);

  #7 (permalink)  
Old April 28th, 2005, 10:27 PM
Registered User
 
Join Date: Apr 2005
Location: Omaha, NE, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #8 (permalink)  
Old April 29th, 2005, 12:20 AM
Authorized User
 
Join Date: Apr 2005
Location: Pune, MH, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jmukesh Send a message via Yahoo to jmukesh
Default

Hi,

Well thats great,

All is well that ends well!

Regards
Mukesh

  #9 (permalink)  
Old January 25th, 2007, 01:07 AM
Registered User
 
Join Date: Jan 2007
Location: madurai, tn, India.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hai

  #10 (permalink)  
Old January 29th, 2007, 06:39 AM
Authorized User
 
Join Date: Jan 2007
Location: Lisbon, , Portugal.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create SiteMapPath in PHP? firblazer PHP How-To 0 July 14th, 2008 09:33 PM
how can i create file using php seco PHP How-To 1 April 11th, 2007 05:58 PM
create database in mysql through php coding vivekananth PHP Databases 2 January 31st, 2007 01:48 AM
PHP Database Connectivity Problem Vulcan999 Beginning PHP 3 July 18th, 2006 07:51 AM
Create +/- hiearchy using PHP? anshul Pro PHP 2 June 20th, 2005 04:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.