Trouble creating tables

[milams]

Hi,

I'm trying to write a PHP script to create a MySQL table. When I try to run the script, it won't create the table. I noticed that it will create it if I don't put "auto_increment" or "Primary key" in the script. If I take those two things it works great, but when I try to put that in, it fails. Can someone please shed some light on why I can't get this to work?

<?php

$createlog="CREATE TABLE login (cusid int(11) NOT NULL DEFAULT '123000' auto_increment, cid VARCHAR(25), cpswd VARCHAR(15), fname VARCHAR(25), lname VARCHAR(25), email VARCHAR(50), primary key('cusid'))";

?>

 

[r937]

primary key('cusid') is wrong because it is trying to make a primary key out of a string

try primary key(cusid) instead

r937.com | rudy.ca

 

[milams]

Oh ok. Thanks I tried it and it worked. Thanks for your help.

 

[guelphdad]

you also can't put a default in for an auto increment field, for two reasons. 1) if you leave it as null it uses the next auto increment value. 2)if you were able to somehow use the default, you could only use it once. after that you would get a duplicate key error because a primary key can't have a duplicate row in it.

if you mean you want your auto increment field to start at a specific number then you can do that.

CREATE TABLE 
login 
(cusid int(11) NOT NULL primary key, 
cid VARCHAR(25), 
cpswd VARCHAR(15), 
fname VARCHAR(25), 
lname VARCHAR(25), 
email VARCHAR(50)
);
ALTER table login auto_increment=2000

 

[milams]

Thanks guelphdad,

That makes sense, but I can't get the ALTER Table command to work. I don't think I have the right syntax.

<?php
$alterlog = "ALTER table login SET cusid auto_increment=2000";
?>

I've tried CHANGE, MODIFY, ADD, just about every thing I can think of and I still can't get it to put in AUTO_INCREMENT with a value of 2000 in there.

 

[r937]

did you try the ALTER syntax at the end of guelphdad's post?

r937.com | rudy.ca

 

[milams]

I tried it, but then I don't think I have the right syntax.

<?php
$createlog = "CREATE TABLE 
		login
		(cusid int(11) NOT NULL primary key, 
		sportid VARCHAR(25) NOT NULL, 
		cpswd VARCHAR(15) NOT NULL,
		fname VARCHAR(25) NOT NULL,
		lname VARCHAR(25) NOT NULL,
		email VARCHAR(50) NOT NULL)
	ALTER table login SET cusid auto_increment=2000";
?>

is this syntax right? Or do I need to put the ALTER TABLE in a different query?

 

[r937]

the ALTER TABLE is a statement in its own right, and must be set off with a semi-colon if you're going to run it right after the CREATE TABLE statement -- but i don't see why you would need to (re)create your table, since it already exists, right? i mean, you did successfully create it already, right? in that earlier post? in which case you need not create it again

besides, guelphdad's CREATE TABLE example does not correctly create the table with an auto_increment, because the actual auto_increment property wasn't declared for the primary key

but his ALTER statement for resetting the auto_increment number is correct

r937.com | rudy.ca