Jump to content


Photo

delete record


  • Please log in to reply
14 replies to this topic

#1 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 03 October 2006 - 07:35 PM

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:

$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'";

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.

#2 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 October 2006 - 07:41 PM

This should work
$sql = "DELETE FROM table WHERE ID = $ID";
.  No need to specify any other fields since ID is as PRIMARY key.
JayBachatero
SMF Developer && Converter Specialist

#3 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 03 October 2006 - 07:44 PM

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!  :)

#4 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 October 2006 - 07:48 PM

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.
JayBachatero
SMF Developer && Converter Specialist

#5 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 03 October 2006 - 07:59 PM

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:

$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";


#6 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 October 2006 - 08:01 PM

Did you make the column auto_increment?  Is so remove ID from the query and it should auto increment.
JayBachatero
SMF Developer && Converter Specialist

#7 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 03 October 2006 - 08:43 PM

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...

#8 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 October 2006 - 08:45 PM

Something like this.

$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";

JayBachatero
SMF Developer && Converter Specialist

#9 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 03 October 2006 - 09:06 PM

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!  :)

#10 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 03 October 2006 - 09:54 PM

Umm that should work.  Make sure that the auto increment field is not null.
JayBachatero
SMF Developer && Converter Specialist

#11 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 04 October 2006 - 12:28 AM

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

#12 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 04 October 2006 - 01:18 AM

Yes change it to NOT NULL.
JayBachatero
SMF Developer && Converter Specialist

#13 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 04 October 2006 - 01:17 PM

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?

#14 JayBachatero

JayBachatero
  • Members
  • PipPipPip
  • Advanced Member
  • 296 posts
  • LocationQueens NY

Posted 04 October 2006 - 03:10 PM

You can use something like

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

JayBachatero
SMF Developer && Converter Specialist

#15 gBase

gBase
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 04 October 2006 - 06:23 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users