deleting database entries problems!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nineseveninteractive
Forum Newbie
Posts: 7
Joined: Mon Jun 25, 2007 6:13 pm

deleting database entries problems!

Post 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
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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());
?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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;
}
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: deleting database entries problems!

Post 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??
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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!
nineseveninteractive
Forum Newbie
Posts: 7
Joined: Mon Jun 25, 2007 6:13 pm

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

It means that either $id is empty, or sqlInjectionProtect() is returning NULL.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

nineseveninteractive wrote:$deletevehicle = deleteVehicle($id);
That's line 211?
What is $id supposed to be there? What is the "source" of $id?
nineseveninteractive
Forum Newbie
Posts: 7
Joined: Mon Jun 25, 2007 6:13 pm

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

$id must be defined in your PHP code.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
nineseveninteractive
Forum Newbie
Posts: 7
Joined: Mon Jun 25, 2007 6:13 pm

Post by nineseveninteractive »

i've got this fixed now, so thanks for all the help. :lol:
Post Reply