sql table creation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
paulytrick
Forum Newbie
Posts: 8
Joined: Mon May 11, 2009 1:40 pm
Location: Sheffield, UK

sql table creation

Post 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.
Last edited by Benjamin on Mon May 11, 2009 2:05 pm, edited 1 time in total.
Reason: Fixed [code=php] tags.
ldougherty
Forum Contributor
Posts: 103
Joined: Sun May 03, 2009 11:39 am

Re: sql table creation

Post 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.
Last edited by Benjamin on Mon May 11, 2009 2:06 pm, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
paulytrick
Forum Newbie
Posts: 8
Joined: Mon May 11, 2009 1:40 pm
Location: Sheffield, UK

Re: sql table creation

Post by paulytrick »

Hi, thanks for the reply..This creates a table called category but not the one called shops.

Regards,

Paul
ldougherty
Forum Contributor
Posts: 103
Joined: Sun May 03, 2009 11:39 am

Re: sql table creation

Post 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));  ");
 
 
Last edited by Benjamin on Mon May 11, 2009 3:14 pm, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
paulytrick
Forum Newbie
Posts: 8
Joined: Mon May 11, 2009 1:40 pm
Location: Sheffield, UK

Re: sql table creation

Post 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.
ldougherty
Forum Contributor
Posts: 103
Joined: Sun May 03, 2009 11:39 am

Re: sql table creation

Post 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
User avatar
paulytrick
Forum Newbie
Posts: 8
Joined: Mon May 11, 2009 1:40 pm
Location: Sheffield, UK

Re: sql table creation

Post by paulytrick »

Thanks Larry, Thats great.
Post Reply