Page 1 of 1
deleting database entries problems!
Posted: Mon Jun 25, 2007 6:18 pm
by nineseveninteractive
Hi
I am having problems deleting entries from a database using the following code
Code: Select all
case "deletevehicle":
// Delete the vehicle from the database.
$deletevehicle = deleteVehicle($id);
// Return to the vehicle list.
$vehiclelist = selectSiteCars(0);
$message = "Vacancy Deleted!";
$template = "vehiclelist.php";
break;
the deleteVehicle function which is invoked is:
Code: Select all
function deleteVehicle($id) {
global $conn;
$id = sqlInjectionProtect($id);
$sql = mysql_query("DELETE FROM
site_cars
WHERE site_cars.car_id = '$id'") or die(mysql_error());
return($sql);
}
which i thought would delete the entry assigned to the variable id. unfortunatley this is not the case and all i get is the deletion message but the entry is not deleted.
I have tried searching for a solution to this on google etc but can't solve the problem, so if anyone has any ideas or could point me in the right direction then i would be very grateful.
many thanks in advance
Posted: Mon Jun 25, 2007 6:26 pm
by ReverendDexter
What happens when you change
Code: Select all
$sql = mysql_query("DELETE FROM
site_cars
WHERE site_cars.car_id = '$id'") or die(mysql_error());
to
Code: Select all
$sql = mysql_query("DELETE FROM
site_cars
WHERE car_id = '$id'") or die(mysql_error());
?
Posted: Mon Jun 25, 2007 6:34 pm
by volka
please try
Code: Select all
error_reporting(E_ALL);
ini_set('display_errors', true);
function deleteVehicle($id) {
global $conn;
$id = sqlInjectionProtect($id);
$query = "DELETE FROM
site_cars
WHERE
site_cars.car_id = '$id'";
echo '<div>Debug: ', htmlentities($query), "</div>\n";
$sql = mysql_query($query, $conn) or die(mysql_error($conn));
echo '<div>Debug: ', mysql_affected_rows($conn), " affected row(s)</div>\n";
return $sql;
}
Re: deleting database entries problems!
Posted: Mon Jun 25, 2007 6:43 pm
by califdon
Code: Select all
$sql = mysql_query("DELETE FROM site_cars WHERE site_cars.car_id = '$id'") or die(mysql_error());
Is your car_id field a CHAR field??
Posted: Mon Jun 25, 2007 9:22 pm
by bdlang
Do we even know that $id exists at that point in the script?
What does sqlInjectectionProtect() return? ( I can assume it returns an escaped value, but do you know it works at this point in the script? )
I'm with califdon; what column type is `car_id`? INTEGER? Don't surround the value with quotes.
Posted: Mon Jun 25, 2007 9:30 pm
by Benjamin
MySQL, unlike some other databases, won't complain if you quote an integer.
The best solution is to just echo the query. If the query is correct, check the connection. Global variables are BAD!
Posted: Tue Jun 26, 2007 2:50 pm
by nineseveninteractive
Thanks for the replies. Its very much appreciated.
I put the code in above and got the error:
Notice: Undefined variable: id in /home/sites/nidd-recruitment.com/public_html/admin/index.php on line 211
Debug: DELETE FROM site_cars WHERE site_cars.car_id = ''
Debug: 0 affected row(s)
so does this mean that i need to define the variable $id in the script earlier????
the field car_id is an integer. its the primary key and auto incremates each time an entry is added. i tried removing the quotes but just got mysql errors so put them back !!
thanks
Posted: Tue Jun 26, 2007 3:21 pm
by superdezign
It means that either $id is empty, or sqlInjectionProtect() is returning NULL.
Posted: Tue Jun 26, 2007 3:24 pm
by volka
nineseveninteractive wrote:$deletevehicle = deleteVehicle($id);
That's line 211?
What is $id supposed to be there? What is the "source" of $id?
Posted: Tue Jun 26, 2007 4:10 pm
by nineseveninteractive
the $id would relate to the car_id field in the database which in the case of a test entry i am using is 187.
would the source of $id not be provided from the database connection or will i have to pull this thorugh with the the deleteVehicle function?
i am able to update the fields for $id but i just cant delete from the db
sorry if i'm being stupid but i'm fairly new to php so i have a lot to learn!
the delete button code looks like this.
Code: Select all
index.php?sub=deletevehicle&id=<?php echo mysql_result($vehiclelist, $i, "site_cars.car_id"); ?>"
the string it returns is
Code: Select all
index.php?sub=deletevehicle&id=187
so i think its pulling through teh variable $id from the database
its just not deleting the line in the db relating to &id
thanks
Posted: Tue Jun 26, 2007 4:11 pm
by superdezign
$id must be defined in your PHP code.
Posted: Tue Jun 26, 2007 5:03 pm
by volka
nineseveninteractive wrote:the string it returns is
Code: Select all
index.php?sub=deletevehicle&id=187
so i think its pulling through teh variable $id from the database
That would be $_GET['id'] then instead of $id.
Posted: Thu Jun 28, 2007 5:10 am
by nineseveninteractive
i've got this fixed now, so thanks for all the help.
