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
– Álvaro González, Commented Jul 25, 2014 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
– Álvaro González, Commented Jul 25, 2014 at 15:50
$result2=mysql_query($query2) or die(mysql_error()); try changing to that and see if it triggers an error — Youness
– Youness, Commented Jul 25, 2014 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 <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); see if it yields anything. Along with mysql_error() as already stated. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 25, 2014 at 15:52
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
– Brad, Commented Jul 25, 2014 at 15:55

Pang · Accepted Answer · 2017-10-14 01:16:40Z

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)

sjagr · Accepted Answer · 2014-07-25 15:59:52Z

1

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)

answered Jul 25, 2014 at 15:59

sjagr

16.6k5 gold badges43 silver badges69 bronze badges

Collectives™ on Stack Overflow

How to execute two queries in the same PHP code?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related