Using multiple where clauses to check two tables

Question

So I have a sql statement in a php file that is related to a form that gets input from a user. When they enter in a genre though it updates all the records in that column and not the specific one they chose.

The original statement I had that works is this first code but it edits the whole column. So I have been trying out other clauses so it only changes the one with the matching album_id but have not had any luck. Sample Table data

$sql = "UPDATE album 
    SET genre_id = (SELECT genre_id
    FROM genre
    WHERE genreName='" . $_POST['lstGenre'] . "'
    )";

Above is what I started with and keep going back to. Result from code above changes genre in entire column

$sql = "UPDATE album 
    SET genre_id = (SELECT genre_id
    FROM genre
    WHERE genreName='" . $_POST['lstGenre'] . "'
    )
        FROM album
        WHERE album_id='" . $_POST['album_id'] . "'";

That is one version I tried but then it does not update anything. This is what I am looking for: Different genre for each case

This works I solved it:

$sql = "UPDATE album 
        SET genre_id = (SELECT genre_id
        FROM genre
        WHERE genreName='" . $_POST['lstGenre'] . "'
        )
        FROM album
        WHERE album_id='" . $thisAlbum['album_id'] . "'";

Can you at least explain what is missing instead of just posting that. I have explained the problem. Shown the code that is causing the problem and one of the attempts I have tried to fix the problem. — Zatoichi, Apr 07 '19 at 22:05
Sample table data and the expected result would be a great improvement. — jarlh, Apr 07 '19 at 22:09
You should read about [SQL injection](https://www.php.net/manual/en/security.database.sql-injection.php). — sticky bit, Apr 07 '19 at 23:17

JStephen · Answer 1 · 2019-04-08T22:43:05.890

0

Almost, but Update ... From ... Where ... is not valid, you just have to get rid of the From section in the second part. It should be something like

$sql = "UPDATE album 
SET genre_id = (SELECT genre_id
    FROM genre
    WHERE genreName='" . $_POST['lstGenre'] . "'
)
WHERE album_id='" . $_POST['album_id'] . "'";

EDIT: Also, sub-queries like that need to only pull one row. Make sure there are not multiple rows in the genre table that match the provided genreName. Though you should be getting a sql error if that were the case.

edited Apr 08 '19 at 22:43

answered Apr 07 '19 at 23:27

JStephen

1,059
3
13
28

are you getting a sql error? have you tried printing the $sql string to make sure the sql statement actually looks how you expect it to? – JStephen Apr 08 '19 at 22:27
No, I am not getting an error. I have printed it and it shows it is grabbing all the right values but it is not actually updating the database. – Zatoichi Apr 08 '19 at 23:08

score 0 · Answer 2 · answered Apr 07 '19 at 23:36

0

Your query should look like this:

UPDATE album a       
    SET genre_id = (SELECT g.genre_id
                    FROM genre g
                    WHERE g.genreName = ?
                   )
    WHERE a.album_id = ?;

The ? is a placeholder for a parameter. You should be using parameters to pass values in to queries, not munging the query string with values.

answered Apr 07 '19 at 23:36

Gordon Linoff

1,242,037
58
646
786

So I have learned about prepared statements this week so from now on that is what I will use. I am a student so was not aware of this when you posted the comment. – Zatoichi Apr 14 '19 at 20:01

Using multiple where clauses to check two tables

2 Answers2