[SOLVED] mysql DELETE

[bigdspbandj]

Running in to this error when trying to do a delete query:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

The query is executed, but the error prevents the code from being read any further. The id is set as an int.

 

		$delExceptionConn = dbConnect();
	$exceptionPgs = $_POST['exceptionPages'];
	$check = false;
	$i = 0;
	while ($i <= count($exceptionPgs)) {
		$exceptionID = $exceptionPgs[$i];
		$delExceptionSql = "DELETE FROM ct_print_setup WHERE id = $exceptionID";
		mysql_query($delExceptionSql) or die(mysql_error());
		++$i;
		$check = true;
	}
	if (!$check) {
		echo 'Delete was unsuccesful';
	}	else {
			header('location: '.$config_basedir.'work-order.php?id='.$job_id.'&exceptionDeleted=1');
		}

[nikefido]

i'm not sure - my first instinct is to wrap your $exceptionID in curly braces:

 

$delExceptionSql = "DELETE FROM ct_print_setup WHERE id = {$exceptionID}";
//or
$delExceptionSql = "DELETE FROM ct_print_setup WHERE id = " . $exceptionID . ";

 

This shouldn't be the problem, however.

[pocobueno1388]

You need to put quotes around your variable, it would also help to print the query out.

 

<?php

$delExceptionSql = "DELETE FROM ct_print_setup WHERE id = '$exceptionID'";
mysql_query($delExceptionSql) or die(mysql_error()."<p>With Query:<br>$delExceptionSql");

?>

[cooldude832]

your half way there to figuring out your problem

when you run a query for debugging do it like this for example

<?php
$q = "Delete from `this` where this=that";
$r = mysql_query($r) or die(mysql_error()."<br /><br /.".$q);
?>

use that or die method and then you can clear see the outputted query and hopefully see you have an issue in it. 

 

Also just a note you can run all your delete without that loop

just say

 

<?php
foreach($_POST['exceptionPages'] as $value);
$delete_id[] = "id = '".$value."'";
}
$delete_ids = implode(" || ",$delete_id);
$q = "Delete from `ct_print_setup` where ".$delete_ids;
$r = mysql_query($q) or die(mysql_error()."<Br /><br />".$q);
?>

runs 1 qurey vs a million queries.

 

Also your checking idea isn't very good because it only is valid on the last item

if something midway is "false" well actualy you don't have an if(mysql_upodated_row($r) >0) part so its always true

[bigdspbandj]

Hey, Cooldude

 

That worked like a charm. Thanks!

 

Would the implode method work on a select query as well?

[cooldude832]

kinda, for a very basic select it does, the idea is to minimize your query count and maximize your usefulness on them.  That means don't user * to select stuff  and don't do stuff like you did, 1 query is sufficient the query's length is pretty much negligible, but having 50 queries vs having a single query selecting 50 rows  50 queries is longer

 

Show me a select you are looking to do.