CODEWITHSUNDEEP
phpmysqlsql
emen
emen

Reputation: 6318

Update MySQL query by foreign column

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.

Upvotes: 0

Views: 94

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

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.

Upvotes: 1

SHAKIR SHABBIR
SHAKIR SHABBIR

Reputation: 1295

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 .= ")";

Upvotes: 1

Andy
Andy

Reputation: 1618

$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.

Upvotes: 1

poncha
poncha

Reputation: 7866

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.

Upvotes: 5

Related Questions