MySQL select count and update in one query?

PHP
1

Greetings,

I was wondering if there was a way to speed up this process:

  $id = '99';
  $sqlcount = "SELECT COUNT(*) as num FROM items WHERE category = '$id'";
  $resultcount = mysql_fetch_array(mysql_query($sqlcount));
  $count = $resultcount['num'];
  mysql_query("UPDATE categories SET count = '$count' WHERE id = '$id'") or die(mysql_error());

Basically what this does is selects category ids and then counts up the number of items within that category, pulls out the count so I can display it and then updates the count back into the database for other purposes.

My question is what is an efficient way to count the number of items within a category, store it’s value in a variable and update the count in the database in one query.

Thanks

2

can’t be done in one query, not if you insist on updating a variable

3

Thanks for the response. I guess this is just the best way to do it then unless you know of another?

Kind regards

4

there is a way to do the update without pulling out the count first

and you can do it for a given id, or for all of them at once

but no variable will be initialized

so if you still need to display the count, stick to two queries

5

You can use stored procedures to execute multiple queries in one go.