getting errors when combining these 2 sql queries

Question

So I have this

$sql_totalbooknumber =  "SELECT SUM(items_counter) FROM probid_categories WHERE items_counter>0 AND `category_id <>355";
$sql_updatebooknumber = "UPDATE `probid_categories` SET `items_counter` = ".$sql_totalbooknumber." WHERE  `category_id` =  '355'";


            if (!mysql_query($sql_totalbooknumber))
  {
  die('Error: ' . mysql_error());
  };
            if (!mysql_query($sql_updatebooknumber))
  {
  die('Error: ' . mysql_error());
  };

somehow they work fine individually (if i comment out one or the other) but when I combine them I get this error

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 'SELECT SUM(items_counter) FROM probid_categories WHERE items_counter>0 AND `cate' at line 1

AND `category_id remove the `

wesside
– wesside

2012-10-04 18:01:54 +00:00
Commented Oct 4, 2012 at 18:01 — wesside
– wesside, Commented Oct 4, 2012 at 18:01

Adam Plocher · Accepted Answer · 2012-10-04 18:01:07Z

2

$sql_totalbooknumber is a string with your query in it. Not the results of that query.

You're expecting an integer to be passed in to $sql_updatebooknumber, but you're passing the string that contains your first query into it.

Try this:

$sql_totalbooknumber =  "SELECT SUM(items_counter) FROM probid_categories WHERE items_counter>0 AND `category_id` <>355";
$result = mysql_query($sql_totalbooknumber);
list($id) = mysql_fetch_array($result);

$sql_updatebooknumber = "UPDATE `probid_categories` SET `items_counter` = ".$id." WHERE  `category_id` =  '355'";
mysql_query($sql_updatebooknumber);

answered Oct 4, 2012 at 18:01

Adam Plocher

14.3k6 gold badges51 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tadman Over a year ago

@andrewsi has a single-pass solution to this that is also less...SQL-injectionny.

Adam Plocher Over a year ago

He's not passing any user input in, so there's no risk of SQL Injection. Unless the 355 is user input.

Adam Plocher Over a year ago

Oh gotcha, I didn't realize he was trying to do a subquery.

tadman Over a year ago

Any data should be escaped. You start to assume and you eventually make serious mistakes.

andrewsi · Accepted Answer · 2012-10-04 18:08:51Z

2

You're doing a subquery; you really need to wrap it in brackets:

$sql_totalbooknumber =  "SELECT SUM(items_counter) FROM probid_categories WHERE items_counter>0 AND `category_id` <>355";
$sql_updatebooknumber = "UPDATE `probid_categories` SET `items_counter` = (".$sql_totalbooknumber.") WHERE  `category_id` =  '355'";

Otherwise, the database can't parse it properly.

edited Oct 4, 2012 at 18:08

answered Oct 4, 2012 at 18:02

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

6 Comments

Adam Plocher Over a year ago

You're missing a backtick by category_id

Supra Man Over a year ago

i get You can't specify target table 'probid_categories' for update in FROM clause

andrewsi Over a year ago

@AdamPlocher - Well-spotted. Thank you! (Though I did take that from the question - I wonder if that might also not be helping with the code not working?)

Supra Man Over a year ago

i added the back-tick already, i think this applies xaprb.com/blog/2006/06/23/… for this error because i had this yesterday

andrewsi Over a year ago

I'm at the limit of my knowledge of subqueries - have you tried the answer from @AdamPlocher ?

|

Collectives™ on Stack Overflow

getting errors when combining these 2 sql queries

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related