Problem with MySQL 1

[PhoenixDown]

I'm using this in my install.php file:

DROP TABLE IF EXISTS guestbook_admins;
CREATE TABLE guestbook_admins (
  adminid smallint(5) NOT NULL auto_increment,
  username varchar(150) NOT NULL default '',
  password varchar(150) NOT NULL default '',
  PRIMARY KEY  (adminid)
) TYPE=MyISAM;[code]

It creates the table correctly. Now, this is what I have in my admin.php file:

[code]include 'data/config.php';

$dbh = mysql_connect($server,$dbusername,$dbpassword) or die ('Cannot connect to database.');
mysql_select_db($dbase,$dbh) or die ('Cannot select database. Make sure data/config.php exists.');
$err = mysql_error();
if ($err){
echo CPMessage("Error in database: $err");

}

$query="INSERT INTO guestbook_admins VALUES 
(1,'$Username','$Password')";
mysql_query($query);

If there is no admins, it adds one, however, if I would like to add more, it doesn't add them. The table should be something like:

1 test tset
2 asdf fdsa
3. tset test

Can someone please show me what's wrong? Thank you.

 

[sonnysavage]

You are specifying the "adminid" to be "1" when you do the insert command, you are just overwriting the existing record when you execute the code.

 

[PhoenixDown]

Hmm, thank you. Would you please tell me how to fix it? I have no clue.

 

[sonnysavage]

Because your column "adminid" is "NOT NULL" and "auto_increment", you can do the insert query like this, and it will automatically use the next number for the new record:

$query = "INSERT INTO guestbook_admins VALUES('','$Username','$Password');";
$result = mysql_query($query);

Sorry, I should have stated that in my last message.

You are also going to want to give the ability to specify the "adminid" when you want to update a record. You'll want to do it like this:

$query = "UPDATE guestbook_admins SET username='".$Username."', password='".$Password."' WHERE adminid='".$Adminid."';";
$result = mysql_query($query);

You may or may not want to give the ability to change the username, in which case you can just remove "

username='".$Username."',

" from the query.

 

[PhoenixDown]

Thanks alot! It worked!

 

[sonnysavage]

I thought you might like this little code trick I use. It helps me re-use code for updating AND creating new records.
Example:

if($submit) {
	$query = "table_name SET column1='".$data1."', column2='".data2."', column3='".$data3."'";
	if($record_id) { $query = "UPDATE ".$query." WHERE id=".$record_id; }
	else { $query = "INSERT INTO ".$query; }
	$query .= ";";
	echo $indent.&quot;<p><b>Save &quot;;
	if(mysql_query($query)) { echo &quot;was successful.&quot;; }
	else { echo &quot;FAILED.&quot;; }
	echo &quot;</b></p>\n&quot;;
}

Enjoy!