Select and Update in same query

Question

Can you select and update in the same query? I'm trying to count views to create a top blog posts by views for my sidebar.

$sql = mysql_query("SELECT * FROM articles WHERE id='$id' LIMIT 1
                    UPDATE pages SET views=views+1 WHERE ID=$id");

This is not about PHP - what you're suggesting is outside MySQL Syntax — , Jul 11 '13 at 01:46
Use PDO instead of the deprecated `mysql_` extensions. Not only are they safer, but they let you do stuff like this. — Ry-, Jul 11 '13 at 01:48
Please remove all of the PHP from your question, as it has nothing to do with the question being asked (it's just clutter). Your question boils down to "Can I do this query?" and the first code block you posted; everything else is unnecessary. — Ken White, Jul 11 '13 at 01:53
@MikeW: I disagree; this question is very much tied to the client. Adding back [tag:php] tag. — eggyal, Jul 11 '13 at 02:34

score 3 · Answer 1 · answered Jul 11 '13 at 01:50

3

Basically, NO, you cannot SELECT and UPDATE with the same query. Your only option would be to write a STORED PROCEDURE which is a more advanced technique of database interaction.

answered Jul 11 '13 at 01:50

DevlshOne

8,357
1
29
37

However, many drivers do enable one to submit batched queries (i.e. send multiple separate/distinct queries to the server as one string, which is basically what the OP appears to be after). – eggyal Jul 11 '13 at 01:57
Right @eggyal . I'm confused as to how to do this though as I've never done both on the same page. – Hire Logo Jul 11 '13 at 01:58

score 3 · Accepted Answer · answered Jul 11 '13 at 01:57

3

No, you cannot do that, but there is nothing wrong with doing two queries.

       mysql_query("UPDATE pages SET views=views+1 WHERE ID=$id");
$sql = mysql_query("SELECT * FROM articles WHERE id=$id");

Also, if id is the primary key you don't have to do LIMIT 1 here, id is unique, therefore it will always have only one result matching your condition.

answered Jul 11 '13 at 01:57

Havenard

27,022
5
36
62

I see. @Havenard the LIMIT 1 is designated to the page that displays a selected blog post from an article. Is this still a conflict. I'm only interested in the blog posts in particular. for example blog lists the posts then blog-view.php displays. – Hire Logo Jul 11 '13 at 02:15
That was easy enough. I guess combingin them is really no issue when you put it like that. Thanks so much. One last thing if you all don't mind. If I was to create the table with the views column should I create that as an int within the structure? – Hire Logo Jul 11 '13 at 02:18
Sure, sinse its not containing fractional numbers `int` should do fine. – Havenard Jul 11 '13 at 02:25

score 2 · Answer 3 · edited May 23 '17 at 11:45

As others have observed, you can send multiple commands to the database with separate invocations of the relevant function call. It is rarely (if ever) necessary to submit multiple commands to the server within a single function call.

However, it can be done (and it is to answer that question in the generality that I write this post—in your particular case, it should be avoided). Note that enabling multi-statements increases the risk of SQL injection attack and should therefore rarely ever be done (and, at that, only when sufficient alternative precautions have been taken to avoid or to mitigate against such attacks).

The ancient ext/mysql extension (which you are using, but which has been deprecated in PHP v5.5 and whose use in new code has been strongly discouraged for years) has no official support for multi-statements. Indeed, the documentation clearly states:

Description
resource mysql_query ( string $query [, resource $link_identifier = NULL ] )
mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

However, this restriction is only enforced at the driver level and can be overridden (in an undocumented/unsupported) manner by setting the driver's CLIENT_MULTI_STATEMENTS connection flag as shown below (or else by recompiling PHP to enable such by default):

mysql_connect($host, $user, $pass, false, 65536);
mysql_select_db($db);
mysql_query("
  SELECT * FROM articles WHERE id='$id' LIMIT 1;
  UPDATE pages SET views=views+1 WHERE ID=$id;
");

A serious down-side to this approach is that one can only access the result of the first command. Should subsequent commands return results (or even fail), such information will be unavailable to your application.

MySQLi, which is the modern replacement for ext/mysql, natively supports multi-statements without such hackery. PDO, a more generic database abstraction library, can also support multi-statements in some circumstances.

Select and Update in same query

3 Answers3

Description

Linked