How to execute two queries in the same PHP code?

Question

I'm new to PHP programming. I want to execute 2 queries at the same PHP code. The first query gave me expected results. But the second query seems to be not working. But it does not show me any error message. Can someone help me?

 <?php
                  mysql_connect('localhost','root','');
                  mysql_select_db('health');
                  $query1="SELECT * FROM messages,doctor WHERE doctor.regNo=messages.regNo AND messages.ID='".$id."';"; 
                  $result1=mysql_query($query1) or die(mysql_error());
                  $row=mysql_fetch_array($result1);
                  echo '<table cellspacing="10">';
                  echo '<tr><td><b>From: </b></td><td>Dr. '.$row['firstName'].' '.$row['lastName'].'</td></tr>';
                  echo '<tr><td><b>Category: </b></td><td>'.$row['category'].'</td></tr>';
                  echo '</table>';
                  echo '<p style="padding-left:13px;">'.$row['reply'].'</p>';
                  echo '</br><b style="padding-left:13px;">Your question</b></br>';
                  echo '<p style="padding-left:13px;">'.$row['question'].'</p>';

                  $query2="UPDATE health.messages SET read='Y' WHERE messages.ID='".$id."';"; 
                  $result2=mysql_query($query2);
                  mysql_close();
             ?>

If you're new, just don't waste your time with the legacy mysql extension. It'll trigger errors in your code and will be removed some day. — Álvaro González, Jul 25 '14 at 15:48
Anyway, the part of the code that's in charge of printing errors is `or die(mysql_error())`. You don't use it on the second query. — Álvaro González, Jul 25 '14 at 15:50
$result2=mysql_query($query2) or die(mysql_error()); try changing to that and see if it triggers an error — Youness, Jul 25 '14 at 15:50
*"But it does not show me any error message."* - That's because you're not checking for errors. Add error reporting to the top of your file(s) right after your opening ` — Funk Forty Niner, Jul 25 '14 at 15:52
Yes, it gave me an error. The error is "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 'read='Y' WHERE messages.ID='7'' at line 1" — user3194672, Jul 25 '14 at 15:55
There are a couple serious issues with your code. The first is that you should be using prepared queries with PDO or similar. Otherwise, you are open to SQL injection attacks and broken code since your data is used in a query with no escaping. Also, you are just echoing text data directly into HTML with no escaping. You must use htmlspecialchars()` or you risk creating invalid HTML and opening yourself up to XSS attacks. — Brad, Jul 25 '14 at 15:55
*"near 'read='Y'"* - Guess what... `read` is a MySQL reserved word http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html wrap it in backticks or choose another name for it. `SET read='Y'` is a/the problem. — Funk Forty Niner, Jul 25 '14 at 15:56

score 2 · Answer 1 · edited Oct 14 '17 at 01:16

Yes, it gave me an error. The error is "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 'read='Y' WHERE messages.ID='7'' at line 1"

read is a MySQL reserved word

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Use backticks around the column name.

For example:

SET `read`='Y'

or use another word for it.

But it does not show me any error message.

That's because you're not checking for errors. Add error reporting to the top of your file(s) right after your opening <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); including or die(mysql_error()) for the second query, which you are not doing as you did for your first query.

Plus, your code is highly prone to SQL injection with the present (and deprecated) MySQL API you are using.

Use mysqli_* with prepared statements, or PDO with prepared statements.

Comments have been left under your question regarding other security-related issues with your code.

Quoting Brad's comment:

you are just echoing text data directly into HTML with no escaping. You must use htmlspecialchars() or you risk creating invalid HTML and opening yourself up to XSS attacks.

Here are a few links you can read up on the subject of SQL injection:

Cross-site scripting (XSS)

score 1 · Accepted Answer · answered Jul 25 '14 at 15:59

You are using reserved MySQL words (such as READ) as column names.

To fix this, change the line

$query2="UPDATE health.messages SET read='Y' WHERE messages.ID='".$id."';";

to

$query2="UPDATE health.messages SET `read` = 'Y' WHERE messages.ID='".$id."';";

I would also suggest that you heed the warnings from everyone else about using depreceated MySQL functions and SQL injection issues.

You should also try to be more unique with naming columns that don't collide with reserved MySQL keywords. Try using a consistent prefix (e.g. messageRead or isRead)

How to execute two queries in the same PHP code?

2 Answers2