querying multiple tables with multiple GET functions using 1 query

Question

I am trying to query 6 separate tables in my mysql database, the structures are as follows;

item

actor

actoractorid | name | actorthumb | bio |

brand

brandbrandid | brandname | description | image |

movie

user

request

By clicking a link to a page called style.php using the get commands I can view the request and the info within it by pulling it from the joined tables. For example where the requestid=1 I can see the movie in the request, the actor in the movie, the item of clothing they were wearing and its brand. By using the following 4 querys;

Movie
$requestid = $_GET['requestid'];
$query = "SELECT * FROM movie, request WHERE movie.movieid = 
request.movieid and requestid = ".$requestid;

Actor
$requestid = $_GET['requestid'];
$query = "SELECT * FROM actor, request WHERE actor.actorid = 
request.actorid and requestid = ".$requestid;

Item
$requestid = $_GET['requestid'];
$query = "SELECT * FROM item, request WHERE item.itemid = 
request.itemid and requestid = ".$requestid;

Brand
$requestid = $_GET['requestid'];
$query = "SELECT * FROM brand, request WHERE brand.brandid = 
request.brandid and requestid = ".$requestid;

Therefore I would like to combine the 4 querys above into 1 single query that would allow me to show all the info, including querying the user and request tables to show who logged the request, please advise?

Why why why why why why no SQL injection prevention? Who is teaching this stuff?! — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Aug 15, 2011 at 20:43

Brad · Accepted Answer · 2011-08-15 20:12:01Z

1

SELECT * FROM request r
INNER JOIN  movie m ON m.movieid = r.requestid
INNER JOIN actor a ON a.actorid = r.requestid
INNER JOIN item i ON i.itemid = r.requestid
INNER JOIN brand b ON b.brand_id = r.requestid
WHERE r.requestid = {your request id}

Of course, if there is a possibility one of the joins will fail (e.g., a movie without a brand) then use a left join instead.

Also, isnt a bit strange that all of your primary keys are equal? You could get away with never selecting from the request table if this is truly the case:

SELECT * FROM movie m
INNER JOIN actor a ON a.actorid = m.movieid
INNER JOIN item i ON i.itemid = m.movieid
INNER JOIN brand b ON b.brand_id = m.movieid
WHERE m.movieid = {your request id}

answered Aug 15, 2011 at 20:12

Brad

5,4681 gold badge36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Dye Over a year ago

Both of these queries fail to run; Unable to perform query select r.requestid, m.*, a.*, i.*, b.* FROM request r INNER JOIN movie m ON m.movieid = r.movieid INNER JOIN actor a ON a.actorid = r.actorid INNER JOIN item i ON i.itemid = r.itemid INNER JOIN brand b ON b.brandid = r.brandidWHERE r.requestid = 57

Brad Over a year ago

did you replace the {your request id} with an actual value? I cant really help if you dont give me the error. Also, I dont know your schema so I am just going off the column names you provided but it will at least get you started.

Martin Dye Over a year ago

Not the actual value the variable $requestid which gets the request ID using the GET function. All tables are linked the to request table using the individual table's PK.

score 1 · Accepted Answer · 2011-08-15 20:41:25Z

1

First: if you are not using mysql_real_escape_string() on your database inputs, you are asking for a LOT of trouble.

Second: SELECT * should not be used this way... you can get duplicate column names which will make your life very difficult very quickly. It takes a bit more up-front time, but specify the columns you want to return.

Third: you can join all of these, if you want to get a lot of columns back, and a lot of rows with duplicate data:

$requestid = mysql_real_escape_string($_GET['requestid']);
$query = "select r.requestid, m.*, a.*, i.*, b.* FROM request r INNER JOIN movie m ON m.movieid = r.movieid INNER JOIN actor a ON a.actorid = r.actorid INNER JOIN item i ON i.itemid = r.itemid INNER JOIN brand b ON b.brandid = r.brandid WHERE r.requestid = $requestid";

you also might want to use LEFT OUTER JOIN if some of these rows won't join up.

edited Aug 15, 2011 at 20:41

answered Aug 15, 2011 at 20:14

user610217

4 Comments

Martin Dye Over a year ago

Hello Thanks for your response however this query fails to run; Unable to perform query select r.requestid, m.*, a.*, i.*, b.* FROM request r INNER JOIN movie m ON m.movieid = r.movieid INNER JOIN actor a ON a.actorid = r.actorid INNER JOIN item i ON i.itemid = r.itemid INNER JOIN brand b ON b.brandid = r.brandidWHERE r.requestid = 57

user610217 Over a year ago

looks like the line break before the WHERE clause was removed. Add a space in there, see if it helps.

Martin Dye Over a year ago

Hi Jeremy I have corrected this, however still the same error SELECT * FROM request rINNER JOIN movie m ON m.movieid = r.requestidINNER JOIN actor a ON a.actorid = r.requestidINNER JOIN item i ON i.itemid = r.requestidINNER JOIN brand b ON b.brand_id = r.requestid WHERE r.requestid = 61

user610217 Over a year ago

I still see a lot of phrases mushed up together. I will change my answer to make it one line, that should help.

Collectives™ on Stack Overflow

querying multiple tables with multiple GET functions using 1 query

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related