delete MySQL row using PHP

[vmarvaso]

dudes, can you help me figure out how to delete a MySQL record in PHP???

i know the output should read:
delete.php?id=9

i thought this would nail it but no luck:


<a href="delete.php?id=<? echo "$id"; ?>">delete</a>


and heres the delete.php:


<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query=" DELETE * FROM contacts WHERE id='$id'";

mysql_close();
?>
deleted!

[lorren.biffin]

I'm assuming that your first file(with the link) pulls the id from the DB, so it knows where to pull from...

The way you have it now will only work if you've set PHP to automatically register global variables...which I wouldn't recommend. And even then, you've forgotten to actually send your query to the DB. Try this to get us started:



<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "DELETE * FROM contacts WHERE id='$_GET['id']';";
$result = mysql_query($query) or die('Could not query DB!');

if(!result) {

echo 'Could not find result.';

} else {

echo 'Deleted!';

}

mysql_close();
?>

[icio]

Whenever you're having problems with a MySQL query the first thing that should come to mind is `[d-php]mysql_error[/d-php](),` which is a function that returns the problem with your SQL query.

I think in this case the problem is coming from the "DELETE * FROM" section of your SQL statement.

A quick look at the MySQL DELETE Documentation (http://dev.mysql.com/doc/refman/5.0/en/delete.html) will show that your query should actually be in the form


DELETE FROM `contacts` WHERE `id`='$id' LIMIT 1;

Hope that helps :thumb:

[vmarvaso]

ok cool... so now my output is:
Could not query DB!

so somethings wrong with how im querying mysql...hmmmm


is the syntax on this right????


<a href="delete.php?id=<? echo "$id"; ?>">delete</a>

[icio]

Have you tried my suggestion ?

[vmarvaso]

excellent icio, i just saw your suggestion and applied it.

now my output is:
Deleted!

however, the record remains... the db has not been effected.



sooooo close! any further suggestions???

[icio]

Sorry, I failed to mention that you should either replace `$id` with `{$_GET['id']}` or put

$id = mysql_real_escape_string($_GET['id']);
before you try to execute the query.

Did you do that?

[lorren.biffin]

Ah, good one icio, i didn't even catch the *.

But yea, unless you've got registered globals on(don't, lol), then you'll need to grab the $id fromt he query string in the URI with $_GET['id'].

Also, I could be wrong, and I can't double check right now, but I think your code suggestion above (icio) will return another "supplied argument is not a valid mysql resource" or whatever the exact error is. If so(which I'm thinking it will) you'll need to run the mysql_real_escape_string() either inside your query, or use the sprintf() function to help place it.

[vmarvaso]

oh HELL YEAH! woooooooooo! thanks icio!

can i use the real_escape_string for updating as well as deleting?

i currently have the following in my update.php but no luck:


$id = mysql_real_escape_string($_GET['id']);
$query="SELECT * FROM `contacts` WHERE `id`='$id' LIMIT 1";


it outputs correctly, but again, the db is unaffected?!?!?!?!


FOR REFERENCE
update.php:


<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$id = mysql_real_escape_string($_GET['id']);
$query="SELECT * FROM `contacts` WHERE `id`='$id' LIMIT 1";
$result = mysql_query($query) or die('Could not query DB!');
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
?>

<form action="updated.php" method="post">
<input type="hidden" name="ud_id" value="<? echo $id; ?>">
First Name: <input type="text" name="ud_first" value="<? echo $first; ?>"><br>
Last Name: <input type="text" name="ud_last" value="<? echo $last; ?>"><br>
<input type="Submit" value="Update">
</form>
<?
++$i;
}
?>


which references updated.php:


<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);

$query="UPDATE contacts SET first='$ud_first', last='$ud_last' WHERE id='$ud_id'";
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "updated...";
mysql_close();
?>
<BR><BR>
<a href=index.php><< go back</a>

[icio]

`mysql_real_escape_string` is to make sure that nothing goes wrong with the SQL statement when you're adding in variables and protects against SQL injections. I use it for every variable before I put it into a query.

[vmarvaso]

good to know, i'll use that for sure.

any idea why my update.php isn't doing its job???

[icio]

A general hint for programming: When something isn't working as you'd expect, output the variables so you can see that they are as you'd expect.

Here, I think the problem is that you haven't defined your `$ud_first`, `$ud_last `and `$ud_id` variables. You'll need to put that code before your query:

$ud_first = $_POST['first'];
$ud_last = $_POST['last'];
$ud_id = $_POST['id'];
But remember to change the name of the variable you're taking from `$_POST` to match your form input.

Hope that helps :thumb:

Edit: And remember to wrap them in `mysql_escape_real_string` like before.

[vmarvaso]

awesome, thank you! youve been very helpful. cheers

[icio]

Glad I could help :thumb: