Mysql error

[abdo1212]

Dear all
I'm teaching mysql and php , i 'm creating simple database for bookstore whatever i created file booksite.php(script) , script is :
<?php
//connect to MySQL; note we've used our own parameters- you should use
//your own for hostname, user, and password
$connect = mysql_connect("localhost", "root", "root") or
die ("Hey loser, check your server connection.");

//create the main database if it doesn't already exist
$create = mysql_query("CREATE DATABASE IF NOT EXISTS books")
or die(mysql_error());

//make sure our recently created database is the active one
mysql_select_db("books");

//create "title" table
$title = "DROP TABLE IF EXISTS title;CREATE TABLE title (
title_id int(20) NOT NULL auto_increment,
title_name varchar(255)NOT NULL,
title_serial tinyint(2)NOT NULL default 0,
title_edition int(4) NOT NULL default 0,
PRIMARY KEY (title_id),
KEY title_serial (title_serial,title_edition)
))";

$results = mysql_query($title)
or die (mysql_error());

//create "serialname" table
$serialname = "CREATE TABLE serialname (
serialname_id int(11) NOT NULL auto_increment,
serialname_subtitle varchar(100) NOT NULL,
PRIMARY KEY (serialname_id)
))";

$results = mysql_query($serialname)
or die(mysql_error());

//create "instructors" table
$instructors = "CREATE TABLE people (
instructors_id int(11) NOT NULL auto_increment,
instructors_fullname varchar(255) NOT NULL,
instructors_serial tinyint(1) NOT NULL default 0,
PRIMARY KEY (instractors_id)
))";

$results = mysql_query($instructors)
or die(mysql_error());

echo "book Database successfully created!";

?>
but when i run the site :

http://localhost/booksite.php

the message error 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 title( title_id int(20) NOT NULL auto_increment, title_name var' at line 1

Pls anyone y can help me

 

[GuardianOfTheFlame]

it's simple: there's one extra ")" in your $title string

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[r937]

it's simple: php cannot handle more than one SQL statement, so it dies right after [blue]DROP TABLE IF EXISTS title;[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

oh right: you cannot run multiquery statements using mysql_query. To do that, you must use mysqli:

http://php.net/manual/en/mysqli.multi-query.php

however, the CREATE TABLE statements have an extra ")" and raise errors if you run them directly in MySQL

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[abdo1212]

Thank for all for interested to me but i changed this script but sill the problem and i'm not undderstand about mysqli :

http://php.net/manual/en/mysqli.multi-query.php

i'm sorry... this my script :
<?php
//connect to MySQL; note we've used our own parameters- you should use
//your own for hostname, user, and password
$connect = mysql_connect("localhost", "root", "root") or
die ("Hey loser, check your server connection.");

//create the main database if it doesn't already exist
$create = mysql_query("CREATE DATABASE IF NOT EXISTS books")
or die(mysql_error());

//make sure our recently created database is the active one
mysql_select_db("books");

//create "title" table
$title = "DROP TABLE IF EXISTS title;CREATE TABLE title (
title_id int(11) NOT NULL auto_increment,
title_name varchar(255)NOT NULL ,
title_serial tinyint(2)NOT NULL default 0,
title_edition int(4) NOT NULL default 0,
PRIMARY KEY (title_id),
KEY title_serial (title_serial,title_edition)
)";

$results = mysql_query($title)
or die (mysql_error());

//create "serialname" table
$serialname = "CREATE TABLE serialname(
serialname_id int(11) NOT NULL auto_increment,
serialname_subtitle varchar(100) NOT NULL,
PRIMARY KEY (serialname_id)
)";

$results = mysql_query($serialname)
or die(mysql_error());

//create "instructors" table
$instructors = "CREATE TABLE people(
instructors_id int(11) NOT NULL auto_increment,
instructors_fullname varchar(100) NOT NULL,
instructors_serial tinyint(11) NOT NULL default 0,
PRIMARY KEY (instractors_id)
)";

$results = mysql_query($instructors)
or die(mysql_error());

echo "book Database successfully created!";

?>

 

[r937]

you're still getting a mysql error?

try running your queries outside of php, i.e. right in mysql, that way you can pinpoint the exact error

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[GuardianOfTheFlame]

if you don't use mysqli, you can run only one statement at a time.
In your code, the string $title contains 2 statements (DROP and CREATE); you should separate them:

//drop "title" table if exists
$drop_title = "DROP TABLE IF EXISTS title;"
$results = mysql_query($drop_title)
  or die (mysql_error());

//create "title" table
$create_title = "CREATE TABLE title (
  title_id int(11) NOT NULL auto_increment,
  title_name varchar(255)NOT NULL ,
  title_serial tinyint(2)NOT NULL default 0,
  title_edition int(4) NOT NULL default 0,
  PRIMARY KEY (title_id),
  KEY title_serial (title_serial,title_edition)
)";

$results = mysql_query($create_title)
  or die (mysql_error());

---

The surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us - Calvin (and Hobbes) ;-)

 

[r937]

there are other problems, too

you really should test these outside of php

CREATE TABLE people
( instructors_id int(11) NOT NULL auto_increment
, instructors_fullname varchar(100) NOT NULL
, instructors_serial tinyint(11) NOT NULL default 0
, PRIMARY KEY ([red]instractors_id[/red])
)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon