Deleting a mysql row - proper syntax?

[jessnoonyes]

Hi guys! I'm toying around with PHP and mySql, trying to learn as I go and I'm doing ok. I'm stuck on a problem though and it's probably fairly simple but I can't see where I'm going wrong. I have a mySQL query that's pulling up a list from a table, and I'm using some fun javascript effects demoed here: http://zero.dengel.me/queue-list.php

All is well except when I try to delete the item. It deletes from my list, but doesn't actually get deleted. When I refresh the page the item shows back up again. I'm guessing the problem is probably with my delete.php file? Here's the code for that:

<?php
if(isset($_GET['delete'])
{
include("config/config.php"); // where my connection details are
$query = 'DELETE FROM clientList WHERE id = '.(int)$_GET['delete'];
$result = mysql_query($query);
});
?>

And on my page where the list is generated:

<?php
$query = "SELECT * FROM clientList ORDER BY id ASC";
//take the results of 'query' and store then in 'result'
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{

echo '<li class="record sort" id="record-',$row['id'],'">
<a href="?delete=',$row['id'],'" class="delete">Delete</a>
<strong>',$row['accountName'],'</strong>
';
}
?>


Anyone know why the record isn't actually being deleted? I'd appreciate any advice! Thanks.

[Fumigator]

Check the delete query's return value to see if the query worked or not. The call to mysql_query() will return "true" or "false", and if it returns "false", then the query error can be retrieved using mysql_error(). Example:


$query = 'DELETE FROM clientList WHERE id = '.(int)$_GET['delete'];
$result = mysql_query($query);
if (!$result) {
die("Query failed. Query text: $query<br>Error:".mysql_error());
}


You should write your code to always check the queries to make sure they worked, and if they didn't worked, take the appropriate action.

[jessnoonyes]

Good point, thanks. That pointed out an error that I fixed. Now it submits to that page, no error, but it doesn't do anything. Odd.

[_Aerospace_Eng_]

Then you need to post your code that does the submission. Are you using any ajax of any kind?

[jessnoonyes]

Well I'm trying, but even if I skip the ajax I can't get it to delete. I guess I'm just not understanding it. Here's what I have for the delete function:

<?php
if(isset($_GET['delete']))
{
$query = 'DELETE FROM clientList WHERE item_id = '.(int)$_GET['delete'];
$result = mysql_query($query);
if (!$result) {
die("Query failed. Query text: $query<br>Error:".mysql_error());
}
}?>

And the code that pulls up the table rows with the delete link:

<?php
$query = "SELECT * FROM clientList ORDER BY id ASC";

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo '<div class="record" id="record-',$row['id'],'">
<a href="?delete=',$row['id'],'" class="delete">Delete</a>
<strong>',$row['accountName'],'</strong></div>
';
}
?>

And the "delete" activates the javascript, which creates a nifty effect and I think submits the change to the current page, which I thought would activate that first bit of PHP for deleting.

var request = new Request({
url: 'mypage.php',

It deletes it from the page, but not from the database. If I refresh the page it shows up again. If I skip the ajax and just submit straight to the first bit of PHP on it's own file, like delete.php, it doesn't do anything. Just shows me a blank page.

[jessnoonyes]

Aha! I got it. WHERE item_id should have been WHERE id

See. I'm learning. :) Thank you for your help!

[_Aerospace_Eng_]

Well you have this
var request = new Request({
url: '#',
link: 'chain',
data: {
'delete': el.getParent('li').get('id').replace('record-',''),
ajax: 1,
method: 'get'
}
You see the url part? This needs to be a php file on its own that has this inside of it.
<?php
if(isset($_GET['delete']))
{
$id = intval($_GET['delete']);
$query = "DELETE FROM clientList WHERE item_id = $id";
$result = mysql_query($query) or die("Query failed. Query text: $query<br>Error:".mysql_error());
}?>
As a simple test go to the file that has the above DELETE query in directly and make sure you have delete=# on it. For example
myfile.php?delete=1

And see if it actually deletes anything. Once you get that working everything else should work as long as you change your ajax to call the file with the delete stuff in it. Does queue-list.php have any of this inside of it?
<?php
if(isset($_GET['delete']))
{
$query = 'DELETE FROM clientList WHERE item_id = '.(int)$_GET['delete'];
$result = mysql_query($query);
if (!$result) {
die("Query failed. Query text: $query<br>Error:".mysql_error());
}
}?>
If it doesn't then ?delete=1 or ?delete=2 will never work. FYI the above is not a function.

[Fumigator]

Aha! I got it. WHERE item_id should have been WHERE id

See. I'm learning. :) Thank you for your help!

Good catch! :thumbsup:

[jessnoonyes]

Ok here's another one. This one is supposed to edit and update the table row. It edits on the screen, but again it's not editing the database and I'm not sure why.

Here's the update.php file:

<?php
if(is_numeric($_POST['id']) && isset($_POST['content']))
{
$query = "UPDATE clientList SET content = '".mysql_real_escape_string(stripslashes($_POST['content']))."' WHERE id = ".(int)$_POST['id'];
$result = mysql_query($query);
}

?>

And the javascript which supposedly passes the changes to update.php, but perhaps it's not

<script type="text/javascript">
//once the dom is ready
window.addEvent('domready', function() {
//find the editable areas
$$('.editable').each(function(el) {
//add double-click and blur events
el.addEvent('dblclick',function() {
//store "before" message
var before = el.get('html').trim();
//erase current
el.set('html','');
//replace current text/content with input or textarea element
if(el.hasClass('textarea'))
{
var input = new Element('textarea', { 'class':'box', 'text':before });
}
else
{
var input = new Element('input', { 'class':'box', 'value':before });
//blur input when they press "Enter"
input.addEvent('keydown', function(e) { if(e.key == 'enter') { this.fireEvent('blur'); } });
}
input.inject(el).select();
//add blur event to input
input.addEvent('blur', function() {
//get value, place it in original element
val = input.get('value').trim();
el.set('text',val).addClass(val != '' ? '' : 'editable-empty');

//save respective record
var url = 'update.php?id=' + el.get('id') + '&content=' + el.get('text');
var request = new Request({
url:url,
method:'post',
onRequest: function() {
//alert('making ajax call :: ' + url);
alert('Updated!');
}
}).send();
});
});
});
});</script>


And the code:

<?php
$query = "SELECT * FROM clientList ORDER BY id ASC";

//take the results of 'query' and store then in 'result'
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "<span class='editable' id='";
echo $row['id'];
echo "'>";
echo $row['Address'];
echo "</span><br />";

.... etc

[Fumigator]

Again, check the query for errors.