Update MySQL query by foreign column

Question

I have two tables named members and orders.

Members table Members columns

Orders table Orders columns In my php codes, I wanted to update the columns in Orders table based on members_id which is a foreign key for members' id table.

This is how I put it:

$query = "UPDATE orders 
          SET package='$package', comments='$comments', refno='$refno', status='no'
          WHERE members_id.username='$username'";

I'm stuck at the WHERE query part.

Did you try the MySQL docs: [Update](http://dev.mysql.com/doc/refman/5.0/en/update.html) and [Join](http://dev.mysql.com/doc/refman/5.0/en/join.html) — Marc B, Jun 20 '12 at 14:57
I certainly hope you are sanitizing those variables before applying them to your SQL, Bobby Tables... — ty812, Jun 20 '12 at 14:59

score 5 · Answer 1 · answered Jun 20 '12 at 14:58

5

If you need to update the order based on a username, you need to JOIN the members table:

UPDATE orders o
  INNER JOIN members m ON m.id = o.member_id
  SET o.package='$package', o.comments='$comments', o.refno='$refno', o.status='no'
  WHERE m.username='$username'

OR alternatively, first fetch the appropriate member id - SELECT id FROM members WHERE username=... and then use it in update query.

PS: Do not forget to escape all the values you place inside the query.

answered Jun 20 '12 at 14:58

poncha

7,726
2
34
38

1

I may not be the brightest, but how to escape the values? And why? Thanks anyway for the queries. Much appreciated. – emen Jun 20 '12 at 15:03
2

use `mysql_real_escape_string($_POST['something'])` to prevent mysql injection – Tepken Vannkorn Jun 20 '12 at 15:07
1

[SQL Injection](http://en.wikipedia.org/wiki/SQL_Injection) is an attack technique used to inject arbitrary database queries into a victim database server via a website connected to it... – poncha Jun 20 '12 at 15:10

score 1 · Answer 2 · edited May 23 '17 at 12:19

Try this:

UPDATE orders o
INNER JOIN members m
ON o.member_id = m.id
SET package='$package', 
    comments='$comments', 
    refno='$refno', status='no' 
WHERE members_id.username='$username'

You should protect your query from sql injection using PDO or any other way to sanitize your parameters, see this excellent post here in SO for more details about sql injection and how to prevent it: Best way to prevent SQL Injection in PHP.

score 1 · Answer 3 · answered Jun 20 '12 at 15:01

$query = "UPDATE orders SET package='$package', comments='$comments', refno='$refno', status='no' WHERE members_id=(select member_id from members where username='$username')";

Normally you should use primary key (member_id) not username in your php/sql code to identify record. This will make risk of SQL injections lower, help to avoid problems when username is not unique, etc.

score 1 · Answer 4 · answered Jun 20 '12 at 15:03

The correct query will be:

$query = "";

$query .= "UPDATE orders";
$query .= "SET  package='$package'";
$query .= ",comments='$comments'";
$query .= ",refno='$refno'";
$query .= ",status='no'";
$query .= "WHERE members_id=(";
$query .= "    SELECT id FROM members WHERE username='$username'";
$query .= ")";

Update MySQL query by foreign column

4 Answers4