CODEWITHSUNDEEP
phpmysql
user1725794
user1725794

Reputation: 267

Searching multiple mysql tables

I currently have this code.

            switch($search_topic)
            {
                case 'Title':
                    $stmt = $dbh->prepare("SELECT game_id, title, platform_name, author_name, price
                                            FROM games 
                                            WHERE title 
                                            LIKE :search_term");
                break;

                case 'Platform':
                    $stmt = $dbh->prepare("SELECT game_id, title, platform_name, author_name, price
                                            FROM games 
                                            INNER JOIN platforms ON games.platform_id = author.platform_id
                                            WHERE platform_name LIKE :search_term");
                break;
            }

the search_topic variable is coming from a listbox on the previous webpage, the Title search works correctly as its simply just search one MySQL table. The Platform search is being abit difficult as I'm attempting to search multiple tables, If that makes sense.

The games table has a column called "platform_id" this this links to the platform table where the key field links to what platform it is under. So how would I go about getting the "platform_id" from the games table and then link it to the "platforms" so I retieve the "platform_title" and then use that to search against.

Schema: http://sqlfiddle.com/#!2/3d3e3/6

Upvotes: 2

Views: 229

Answers (2)

hafichuk
hafichuk

Reputation: 10771

Based off of your fiddle, here's the update for you:

SELECT g.game_id, g.title, p.platform_name, a.author_name, g.price
FROM games g
INNER JOIN platforms p ON p.platform_id = g.platform_id
INNER JOIN author a ON a.author_id = g.author_id
WHERE g.title LIKE 'Skyrim';

You would obviously change WHERE g.title LIKE 'Skyrim'; to whichever field you would like to filter on. So the platform query would be:

SELECT g.game_id, g.title, p.platform_name, a.author_name, g.price
FROM games g
INNER JOIN platforms p ON p.platform_id = g.platform_id
INNER JOIN author a ON a.author_id = g.author_id
WHERE p.platform_name LIKE 'Xbox';

I would also suggest that you change your CHARSET to utf8 instead of latin1, assuming this reflects your production database setup.

Upvotes: 3

0x_Anakin
0x_Anakin

Reputation: 3269

SELECT games.game_id, 
games.title, 
games.platform_name, 
games.author_name, 
games.price, 
games.platform_id, 
platforms.platform_id,
platforms.platform_name
FROM games, platforms 
WHERE
games.platform_id = platforms.platform_id
AND
platform.platform_name LIKE %:search_term%

Upvotes: 2

Related Questions