Mysql SELECT , find number of rows , then INSERT in a single query statement

Question

Let say we have inbox table and only maximum 50 messages can be recieved at a time, I use the following code for this purpose:

$maximum_messages_in_inbox = 50;

$query1 = mysql_query("SELECT * from inbox");
if(mysql_num_rows($query1) > $maximum_messages_in_inbox)
{
   echo "Message memmory if full please try again later";
}
else
{
   mysql_query("insert into inbox set text = '$text' , sender = '$sender' ");
}

This code work very well, but each time two queries will be executed.
My question is can we adjust above purpose in a single query like:

mysql_query("select * from inbox if num > 50 insert else error");
if(error)
{
   echo "Message memmory if full please try again later";
}

Looking forward to your co-operation. Thanks

[Don't use mysql_* anymore](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Shafizadeh, May 18 '15 at 16:02
Just as an FYI, you are using deprecated `mysql_` functions, which can cause the risk of sql injection. You might want to look into `mysqli_` or `PDO` for safety purposes — nomistic, May 18 '15 at 16:03
Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 18 '15 at 16:03
dear i m using `mysqli` instead of `mysql` but just for asking purpose i use mysql, anyhow thanks for suggestion . i will use `mysqli` for asking next time — Aftab Ahmad, May 18 '15 at 16:04
@Sajad go look at the link in my comment. It really simplifies PDO. — Jay Blanchard, May 18 '15 at 16:06
@JayBlanchard i have a question, do you know what is `foo` ? Is it dummy name of what ? tnx — Shafizadeh, May 18 '15 at 16:22
[`'foo'`](https://www.ietf.org/rfc/rfc3092.txt) is a dummy variable @Sajad — Jay Blanchard, May 18 '15 at 16:24

vard · Accepted Answer · 2015-05-18T16:12:05.557

4

You can do conditional INSERT in SQL with this trick :

INSERT INTO inbox(text, sender)
SELECT 'text value', 'sender value'
FROM dual
WHERE (SELECT COUNT(*) FROM inbox) < 50;

Thay way the INSERT will be processed only if the WHERE clause is satisfied.

edited May 18 '15 at 16:12

answered May 18 '15 at 16:09

vard

3,888
2
22
42

thanks, i'm trying it but please tell me you used `dual` word in `mysql_query`, it is `table_name` or `keyword` @vard – Aftab Ahmad May 18 '15 at 16:13
1

`dual` is a dummy table name. See https://dev.mysql.com/doc/refman/5.0/en/select.html So you don't need to create a table named `dual` – vard May 18 '15 at 16:15
I always like to know what is `foo`? is it dummy name of what ? – Shafizadeh May 18 '15 at 16:16
thank u so much @vard , i tried it and it work very well, at start i use same table name instead of `dual` then i face a new problem but then i tried `dual` and it work very well, my problem solved thank u so much – Aftab Ahmad May 18 '15 at 16:31
@Sajad Look at http://stackoverflow.com/questions/4868904/what-is-the-origin-of-foo-and-bar ;) – vard May 18 '15 at 16:41
@vard Thanks buddy ...! – Shafizadeh May 18 '15 at 18:27

Mysql SELECT , find number of rows , then INSERT in a single query statement

1 Answers1