delete record

[gBase]

Hi, I am implementing a form in my database to allow the user to remove records by ID # (which is the primary key for the table.)  The form calls to a php file that has this code:

[code]
$sql="DELETE FROM table (ID, Name, Organization, Title, Street, City, State, Zip)
VALUES
('$_POST[ID]','$_POST[Name]','$_POST[Organization]','$_POST[Title]','$_POST[Street]','$_POST[City]','$_POST[State]','$_POST[Zip]')
WHERE ID='$ID'";
[/code]

Will this work as intended?  Just want to get another pair of eyes to check this over before I try it.  I am working with a live database and while I'm trying to remove a non-used record, I don't want to risk any of the other data.

[JayBachatero]

This should work [code=php:0]$sql = "DELETE FROM table WHERE ID = $ID";[/code].  No need to specify any other fields since ID is as PRIMARY key.

[gBase]

RIGHT...I was about to try something similar...$sql = "DELETE FROM table WHERE ID = '$ID'";

But yours worked great and I think I may have gotten a syntax error with the 's.  Thanks!  :)

[JayBachatero]

No you wouldn't get a syntax error.  It's just that id ID is an int there is no need to use ' around the var as that would cast as string.

[gBase]

Ohhh ok.  Thanks for clearing that up.  I was going to post a new thread but I didn't want to junk up the board...I have another question based on the fact that I'm using ID as my primary key.  Currently, I have a form on my app that inserts new records into my database...only problem is that currently you have to enter in the new ID manually.  Is there a way to clean this up so it updates the ID automagically?  ;)

Here's my code:

[code]
$sql="INSERT INTO table01 (ID, Name, Organization, Title, Street, City, State, Zip)
VALUES
('$_POST[ID]','$_POST[Name]','$_POST[Organization]','$_POST[Title]','$_POST[Street]','$_POST[City]','$_POST[State]','$_POST[Zip]')";

$result = mysql_query("SELECT ID, Name, Organization, Title, Street, City, State, Zip FROM table");

if
(!mysql_query($sql,$connection))
  {
  die ('Error: ' . mysql_error());
  }
echo "1 record added";
[/code]

[JayBachatero]

Did you make the column auto_increment?  Is so remove ID from the query and it should auto increment.

[gBase]

Hmm...yes the column is auto-increment, but I tried removing ID from the query and went to try my form and it added a new record with a blank ID.  Do you have a sample query I could try?  Thanks, I have to run to class...

[JayBachatero]

Something like this.

[code]
$sql = "
INSERT INTO table01
(Name, Organization, Title, Street, City, State, Zip)
VALUES
('$_POST[Name]','$_POST[Organization]','$_POST[Title]','$_POST[Street]','$_POST[City]','$_POST[State]','$_POST[Zip]')";

if (!mysql_query($sql,$connection))
die ('Error: ' . mysql_error());
else
echo "1 record added";
[/code]

[gBase]

Thanks, that's similar to what I tried before, but that inserted a new record with a blank ID as well... ???

Thanks for the help!  :)

[JayBachatero]

Umm that should work.  Make sure that the auto increment field is not null.

[gBase]

My ID field is set to Null...should I change it to 'not Null?'

[JayBachatero]

Yes change it to NOT NULL.

[gBase]

Ok thanks...one more question.  Is it possible to write a query in my php insert file that will show an error if the user leaves any of the fields blank when they try to insert a new record?

[JayBachatero]

You can use something like

[code=php:0]if (trim($_POST['name']) == '')
        echo 'You must fill in the field...';[/code]

[gBase]

Great...thanks for the helpful tips.  :)