Page 1 of 1

sql table creation

Posted: Mon May 11, 2009 1:56 pm
by paulytrick
Hello all. I'm new to sql and fairly new to php.

I would be very grateful if someone could help me with a problem i have encountered.

I am trying to make an sql database. i wrote the code to create the db and that went fine but when i try to create a table and insert information, it does nothing.

here is my code:

Code: Select all

<body>
<?echo "Connecting to database<br>";
$con = mysql_connect("localhost","root","****");
if (!$con)
{
die('coulkd not connest: ' . mysql_error());
}
echo "seems we connected just fine<br>";
$ip = $_SERVER['REMOTE_ADDR'];
echo " your IP address is" . $ip . "<br>";
mysql_select_db("tackle_shops", $con);
$sql = "Create table shops
(
shopID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(shopID),
location int,
name varchar(20),
town varchar(20),
desc varchar(500),
address varchar(60),
phonenumber int,
photoaddress varchar(100)
)";
mysql_query($sql,$con);
 
mysql_close($con);
echo "created table and colums. :D";
 
 
 
?>
 
</body>
</html>

I get the message that the table and colums were created but when i look in phpmyadmin the database is there but no table.

I get no errors either in the html page which is displayed.

I've double checked everything I can think of and don't know what to do next.

Thanks to anyone who can give me any help / advice.

Regards,

Paul.

p.s,I understand this is not the best way to connect to a database but It's going to be a use once only script and I'm really doing this to try to learn.

I have also substitued long hand for short hand tags (well, the ones i know how to) apologies if posting code like this is in breach of your posting rules.

Re: sql table creation

Posted: Mon May 11, 2009 2:03 pm
by ldougherty
The best way to get the proper code is to create the table the way you'd like in phpmyadmin then copy the code it generates into your script.

Code: Select all

 
 
mysql_query("CREATE TABLE IF NOT EXISTS `shops` (
  `shopID` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(`shopID`),
  `location` int,
  `name` varchar(20),
  `town` varchar(20),
  `desc` varchar(500),
  `address` varchar(60),
  `phonenumber` int,
  `photoaddress` varchar(100); ");
 
 
I believe this should work, try placing this in your code to create the table.

Re: sql table creation

Posted: Mon May 11, 2009 2:10 pm
by paulytrick
Hi, thanks for the reply..This creates a table called category but not the one called shops.

Regards,

Paul

Re: sql table creation

Posted: Mon May 11, 2009 2:38 pm
by ldougherty
sorry, missed a parentheses.

Code: Select all

 
 
mysql_query("CREATE TABLE IF NOT EXISTS `shops` (
  `shopID` int NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(`shopID`),
   `location` int,
   `name` varchar(20),
   `town` varchar(20),
   `desc` varchar(500),
   `address` varchar(60),
   `phonenumber` int,
   `photoaddress` varchar(100));  ");
 
 

Re: sql table creation

Posted: Mon May 11, 2009 2:43 pm
by paulytrick
Thanks Larry,
That works great and I can see the mistakes I made in my origional code.

I intend to add data to the database through an online form. does it matter if i add rows to the colums in any order i specify or is there a strict order for this kind of thing?

Regards,

Paul.

Re: sql table creation

Posted: Mon May 11, 2009 3:07 pm
by ldougherty
Paul,

You have to specify the columns and data in a specific order. Read up at http://www.w3schools.com/PHP/php_mysql_insert.asp

Re: sql table creation

Posted: Mon May 11, 2009 3:30 pm
by paulytrick
Thanks Larry, Thats great.