Please help with my code?

[baron_samedi]

<?php

mysql_connect ("localhost", "root","panda") or die (mysql_error());



$create=mysql_query ("CREATE DATABASE IF NOT EXISTS sucko_shop") or die (mysql_error());
mysql_select_db("sucko_shop");

$table= "CREATE TABLE product_category (

category_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
category_name VARCHAR (50) NOT NULL DEFAULT '',
category_description VARCHAR (200) NOT NULL DEFAULT '',
category_image VARCHAR (200) NOT NULL DEFAULT '',

PRIMARY KEY (category_id),

)";

$table2="CREATE TABLE tableof_products (

product_id INTEGER UNSIGNED NOT NULL AUTO INCREMENT,


product_name VARCHAR (100) NOT NULL DEFAULT '',
product_description TEXT NOT NULL DEFAULT '',
product_price DECIMAL (7,2) NOT NULL DEFAULT 0.00,
product_quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,
producurrency_image VARCHAR (200),

primary key (product_id)

)";

$table3="CREATE TABLE shopping_cart (

cart_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
product_id INTEGER UNSIGNED NOT NULL,
cart_quantity INTEGER UNSIGNED NOT NULL DEFAULT 1,
cart_session_id CHAR (30) NOT NULL DEFAULT  '',
cart_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (cart_id)

)";

$table4="CREATE TABLE customer_order (

order_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
order_date DATETIME NOT NULL DEFAULT '0000-00-00 '00:00:00',
order_status ('Awaiting payment', 'Sent', 'Cancelled'),
order_shipping_first_name VARCHAR (20) NOT NULL DEFAULT  '',
order_shipping_last_name VARCHAR (30) NOT NULL DEFAULT  '', 
order_shipping_address1 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_address2 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_city VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_post_code VARCHAR (15) NOT NULL DEFAULT  '',
order_shipping_cost DECIMAL (5,2) DEFAULT 0.00 DEFAULT  '',

order_payment_first_name VARCHAR (50) NOT NULL,
order_payment_last_name VARCHAR (50) NOT NULL DEFAULT '',
order_payment_address1 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_address2 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_city VARCHAR (100) NOT NULL DEFAULT '',
order_payment_postal_code VARCHAR (10) NOT NULL DEFAULT '',

PRIMARY KEY (order_id)
)";

$table5="CREATE TABLE tableorder_item (

order_id INTEGER UNSIGNED NOT NULL,
product_id INTEGER UNSIGNED NOT NULL,
order_quantity INTEGER UNSIGNED NOT NULL,

PRIMARY KEY (order_id, product_id)

)";

$table6="CREATE TABLE table_user (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL DEFAULT '',
  user_password VARCHAR(32) NOT NULL DEFAULT '',
  user_registration_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  user_last_login DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (user_id),
  UNIQUE KEY user_name (user_name)
)";

$insert="INSERT INTO table_user VALUES(1, 'admin', PASSWORD('admin'), NOW(), NOW())";
$results=mysql_query($insert) or die (mysql_error());

$table7="CREATE TABLE table_shop_config (
    shopconfig_name VARCHAR(50) NOT NULL DEFAULT '',
    shopconfig_address VARCHAR(100) NOT NULL DEFAULT '',
    shopconfig_phone VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_email VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_shipping_cost DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    shopconfig_currency INTEGER unsigned NOT NULL DEFAULT 1
)";

$insert2="INSERT INTO table_shop_config VALUES ('', '', '', '', 0.00, 1)";
$results2=mysql_query($insert2) or die (mysql_error());

$table8="CREATE TABLE table_currency (
    currencurrency_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    currencurrency_code CHAR(3) NOT NULL,
    currencurrency_symbol VARCHAR(8) NOT NULL,
    PRIMARY KEY (currency_id)
)";

$insert3="INSERT INTO table_currency (currencurrency_id, currency_code, currency_symbol) VALUES (1, 'EUR', '€')";
$results3=mysql_query($insert3) or die (mysql_error());

$insert4="INSERT INTO table_currency (currencurrency_id, currency_code, currency_symbol) VALUES (2, 'GBP', '&pound;')";
$results4=mysql_query($insert4) or die (mysql_error());


$insert5="INSERT INTO table_currency (currency_id, currency_code, currency_symbol) VALUES (3, 'JPY', '&yen;')";
$results5=mysql_error($insert5) or die (mysql_error());

$insert6="INSERT INTO table_currency (currency_id, currency_code, currency_symbol) VALUES (4, 'USD', '$')"; 
$results6=mysql_error($insert6) or die (mysql_error());


?>

Hi guys, for some reason the above code will not work 🙁

Whenever I test it in localhost, using the XAMP/Apache server...the following message appears:

Table 'sucko_shop.table_user' doesn't exist

I am a total loss as to what I have done wrong....can anyone please help?

I thought that perhaps the database had not been created....but then it clearly stated that the specific table had not been created. Also, there a number of tables before the relevant table, again confusing me somewhat :queasy:

[dagon]

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

7 and 8 to

[igorlopez]

What dagon means is that you have not created any tables, just the query strings for how they should be created, and you need to create them before using them.

[baron_samedi]

<?php

mysql_connect ("localhost", "root","panda") or die (mysql_error());



$create=mysql_query ("CREATE DATABASE IF NOT EXISTS sucko_shop") or die (mysql_error());
mysql_select_db("sucko_shop");

$table= "CREATE TABLE product_category (

category_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
category_name VARCHAR (50) NOT NULL DEFAULT '',
category_description VARCHAR (200) NOT NULL DEFAULT '',
category_image VARCHAR (200) NOT NULL DEFAULT '',

PRIMARY KEY (category_id),

)";

$query=mysql_query($table);

$table2="CREATE TABLE tableof_products (

product_id INTEGER UNSIGNED NOT NULL AUTO INCREMENT,


product_name VARCHAR (100) NOT NULL DEFAULT '',
product_description TEXT NOT NULL DEFAULT '',
product_price DECIMAL (7,2) NOT NULL DEFAULT 0.00,
product_quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,
producurrency_image VARCHAR (200),

primary key (product_id)

)";

$query2=mysql_query($table2);


$table3="CREATE TABLE shopping_cart (

cart_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
product_id INTEGER UNSIGNED NOT NULL,
cart_quantity INTEGER UNSIGNED NOT NULL DEFAULT 1,
cart_session_id CHAR (30) NOT NULL DEFAULT  '',
cart_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (cart_id)

)";

$query3=mysql_query($table3);

$table4="CREATE TABLE customer_order (

order_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
order_date DATETIME NOT NULL DEFAULT '0000-00-00 '00:00:00',
order_status ('Awaiting payment', 'Sent', 'Cancelled'),
order_shipping_first_name VARCHAR (20) NOT NULL DEFAULT  '',
order_shipping_last_name VARCHAR (30) NOT NULL DEFAULT  '', 
order_shipping_address1 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_address2 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_city VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_post_code VARCHAR (15) NOT NULL DEFAULT  '',
order_shipping_cost DECIMAL (5,2) DEFAULT 0.00 DEFAULT  '',

order_payment_first_name VARCHAR (50) NOT NULL,
order_payment_last_name VARCHAR (50) NOT NULL DEFAULT '',
order_payment_address1 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_address2 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_city VARCHAR (100) NOT NULL DEFAULT '',
order_payment_postal_code VARCHAR (10) NOT NULL DEFAULT '',

PRIMARY KEY (order_id)
)";

$query4=mysql_query($table4);

$table5="CREATE TABLE tableorder_item (

order_id INTEGER UNSIGNED NOT NULL,
product_id INTEGER UNSIGNED NOT NULL,
order_quantity INTEGER UNSIGNED NOT NULL,

PRIMARY KEY (order_id, product_id)

)";

$query5=mysql_query($table5);

$table6="CREATE TABLE table_user (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL DEFAULT '',
  user_password VARCHAR(32) NOT NULL DEFAULT '',
  user_registration_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  user_last_login DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (user_id),
  UNIQUE KEY user_name (user_name)
)";

$query6=mysql_query($table6);

$insert="INSERT INTO table_user VALUES(1, 'admin', PASSWORD('admin'), NOW(), NOW())";
$results=mysql_query($insert) or die (mysql_error());

$table7="CREATE TABLE table_shop_config (
    shopconfig_name VARCHAR(50) NOT NULL DEFAULT '',
    shopconfig_address VARCHAR(100) NOT NULL DEFAULT '',
    shopconfig_phone VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_email VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_shipping_cost DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    shopconfig_currency INTEGER unsigned NOT NULL DEFAULT 1
)";

$query7=mysql_query($table7);

$insert2="INSERT INTO table_shop_config VALUES ('', '', '', '', 0.00, 1)";
$results2=mysql_query($insert2) or die (mysql_error());

$table8="CREATE TABLE table_currency (
    currencurrency_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    currencurrency_code CHAR(3) NOT NULL,
    currencurrency_symbol VARCHAR(8) NOT NULL,
    PRIMARY KEY (currency_id)
)";
$query8=mysql_query($table8);

$insert3="INSERT INTO table_currency (currency_id, currency_code, currency_symbol) VALUES (1, 'EUR', '€')";
$results3=mysql_query($insert3) or die (mysql_error());

$insert4="INSERT INTO table_currency (currencurrency_id, currency_code, currency_symbol) VALUES (2, 'GBP', '&pound;')";
$results4=mysql_query($insert4) or die (mysql_error());


$insert5="INSERT INTO table_currency (currency_id, currency_code, currency_symbol) VALUES (3, 'JPY', '&yen;')";
$results5=mysql_error($insert5) or die (mysql_error());

$insert6="INSERT INTO table_currency (currency_id, currency_code, currency_symbol) VALUES (4, 'USD', '$')"; 
$results6=mysql_error($insert6) or die (mysql_error());


?>

Hi guys,

I have amended my code to include queries to actually create the tables. I have a GUI client for SQL, Mysql adminstrator, and when I checked the results of my code being executed, it seems that ALL the tables have been created save for ONE.

Whenever I execute my code I get the following message:

Table 'sucko_shop.table_currency' doesn't exist.

I checked the MySQL Adminstrator and lo and behold, there is a number of tables, with that table omitted.

I am not quite sure what I have done wrong (yet again, some silly error I imagine)...really appreciate your help again guys :o

[igorlopez]

I think your query string is not what you intented,

$table8="CREATE TABLE table_currency (
    currencurrency_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    currencurrency_code CHAR(3) NOT NULL,
    currencurrency_symbol VARCHAR(8) NOT NULL,
    PRIMARY KEY (currency_id)
)";
$query8=mysql_query($table8);

You should probably remove the first part of each field name, curren, otherwise you have to change your PRIMARY KEY to currencurrency_id

[baron_samedi]

[COLOR="Red"]<?php

mysql_connect ("localhost", "root","panda") or die (mysql_error());

[COLOR="Red"]

$create=mysql_query ("CREATE DATABASE IF NOT EXISTS sucko_shop") or die (mysql_error());
mysql_select_db("sucko_shop");

$table= "CREATE TABLE IF NOT EXISTS product_category (

category_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
category_name VARCHAR (50) NOT NULL DEFAULT,
category_description VARCHAR (200) NOT NULL DEFAULT,
category_image VARCHAR (200) NOT NULL DEFAULT,

PRIMARY KEY (category_id)
)";
[/COLOR]
$query=mysql_query($table);

[COLOR="Red"]$table2="CREATE TABLE IF NOT EXISTS products (

product_id INTEGER UNSIGNED NOT NULL AUTO INCREMENT,


product_name VARCHAR (100) NOT NULL DEFAULT ,
product_description TEXT NOT NULL DEFAULT, 
product_price DECIMAL (7,2) NOT NULL DEFAULT 0.00,
product_quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,
producurrency_image VARCHAR (200),

primary key (product_id)
)";[/COLOR]
[/COLOR]
$query2=mysql_query($table2);


$table3="CREATE TABLE IF NOT EXISTS shopping_cart (

cart_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
product_id INTEGER UNSIGNED NOT NULL,
cart_quantity INTEGER UNSIGNED NOT NULL DEFAULT 1,
cart_session_id CHAR (30) NOT NULL DEFAULT  '',
cart_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (cart_id)
)";

$query3=mysql_query($table3);

[COLOR="Red"]$table4="CREATE TABLE IF NOT EXISTS customer_order (

order_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
order_date DATETIME NOT NULL DEFAULT '0000-00-00 '00:00:00',
order_status ('Awaiting payment', 'Sent', 'Cancelled'),
order_shipping_first_name VARCHAR (20) NOT NULL DEFAULT  '',
order_shipping_last_name VARCHAR (30) NOT NULL DEFAULT  '', 
order_shipping_address1 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_address2 VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_city VARCHAR (100) NOT NULL DEFAULT  '',
order_shipping_post_code VARCHAR (15) NOT NULL DEFAULT  '',
order_shipping_cost DECIMAL (5,2) DEFAULT 0.00 DEFAULT  '',

order_payment_first_name VARCHAR (50) NOT NULL,
order_payment_last_name VARCHAR (50) NOT NULL DEFAULT '',
order_payment_address1 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_address2 VARCHAR (100) NOT NULL DEFAULT '',
order_payment_city VARCHAR (100) NOT NULL DEFAULT '',
order_payment_postal_code VARCHAR (10) NOT NULL DEFAULT '',

PRIMARY KEY (order_id)
)";
[/COLOR]
$query4=mysql_query($table4);

$table5="CREATE TABLE IF NOT EXISTS order_item (

order_id INTEGER UNSIGNED NOT NULL,
product_id INTEGER UNSIGNED NOT NULL,
order_quantity INTEGER UNSIGNED NOT NULL,

PRIMARY KEY (order_id, product_id)
)";

$query5=mysql_query($table5);

$table6="CREATE TABLE IF NOT EXISTS user (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(20) NOT NULL DEFAULT '',
  user_password VARCHAR(32) NOT NULL DEFAULT '',
  user_registration_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  user_last_login DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (user_id),
  UNIQUE KEY user_name (user_name)
)";

$query6=mysql_query($table6);

$insert="INSERT INTO user VALUES(1, 'admin', PASSWORD('admin'), NOW(), NOW())";
$results=mysql_query($insert) or die (mysql_error());

$table7="CREATE TABLE shop_config (
    shopconfig_name VARCHAR(50) NOT NULL DEFAULT '',
    shopconfig_address VARCHAR(100) NOT NULL DEFAULT '',
    shopconfig_phone VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_email VARCHAR(30) NOT NULL DEFAULT '',
    shopconfig_shipping_cost DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    shopconfig_currency INTEGER unsigned NOT NULL DEFAULT 1
)";

$query7=mysql_query($table7);

$insert2="INSERT INTO shop_config VALUES ('', '', '', '', 0.00, 1)";
$results2=mysql_query($insert2) or die (mysql_error());

$table8="CREATE TABLE currency (
    currency_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    currency_code CHAR(3) NOT NULL,
    currency_symbol VARCHAR(8) NOT NULL,
    PRIMARY KEY (currency_id)
)";
$query8=mysql_query($table8);

$insert3="INSERT INTO currency (currency_id, currency_code, currency_symbol) VALUES (1, 'EUR', '&#8364;')";
$results3=mysql_query($insert3) or die (mysql_error());

$insert4="INSERT INTO currency (currency_id, currency_code, currency_symbol) VALUES (2, 'GBP', '&pound;')";
$results4=mysql_query($insert4) or die (mysql_error());


$insert5="INSERT INTO currency (currency_id, currency_code, currency_symbol) VALUES (3, 'JPY', '&yen;')";
$results5=mysql_query($insert5) or die (mysql_error());

$insert6="INSERT INTO currency (currency_id, currency_code, currency_symbol) VALUES (4, 'USD', '$')"; 
$results6=mysql_query($insert6) or die (mysql_error());


?>

Guys, I am getting a REALLY weird problem...the above code executes, and creates the database but only creates some of the tables. There are 8 tables in total, only 5 are being created.

The tables above which are in red are the ones that are NOT being created. I am getting no errors...and I am not sure as to why some are being made, and others are not.

[dagon]

$table4

'0000-00-00 '00:00:00',

should be '0000-00-00 00:00:00',

add some basic error checking and mysql will tell you what the problem it

[baron_samedi]

yeah thanks Dagon...

seems that I had some errors, like no value for the NOT NULL DEFAULT columns. Works a treat now 😃

thanks guys.