php mysql select multiple values from one table

Question

I'm trying to access a MySQL DB using PHP. I need to retrieve 6 values from my actor_id table displaying them in a html table. My SELECT statement is:

SELECT actor_id.actor_name, FROM actor_id WHERE movie.star1 = actor_id.actor_id;

Now how can I add another movie.star2, movie.star3 etc.??

DB Structure

NAME - fields movie - movie_id title star1 star2 actor_id - actor_id actor_name

Please show your database structure. Do you really have a table called actor_id? And since you do not join anything how do you can use movie.star1? This should throw a syntax error. — Tobias Golbs, Aug 30 '13 at 11:01
Commas, commas, commas, and do you really have a table called something_id? i think it's time to go back to [My]SQL 101 — Strawberry, Aug 30 '13 at 11:05
More info needed! Check comments below Fluffeh's answer (and perhaps check both his and my answer) because with your question as it now stands, there's about 1904338473294 possible answers... — Elias Van Ootegem, Aug 30 '13 at 11:23

score 2 · Answer 1 · edited May 23 '17 at 10:31

2

By the looks of this, you are actually using two tables here. Your query however only references one of them.

I think that you need something like this:

SELECT 
    actor_id.actor_name, 
FROM 
    actor_id 
        join movie
            on movie.star1 = actor_id.actor_id
            or movie.star2 = actor_id.actor_id
            or movie.star3 = actor_id.actor_id
            -- etc etc

On that note though, you would probably do really well to read this lengthy Q&A that I wrote to explain these things in a lot more detail.

edited May 23 '17 at 10:31

Community

1
1

answered Aug 30 '13 at 11:07

Fluffeh

33,228
16
67
80

Wouldn't it make more sense to join actor_id on `movie`, since the `actor_id` row selected depends on which `movie.star1` values are being used? – Elias Van Ootegem Aug 30 '13 at 11:17
@EliasVanOotegem Quite possibly, I am guessing at the table structure though and trying to use the inputs in the question :) – Fluffeh Aug 30 '13 at 11:19
Of course, there's not enough info to give a definitive answer. I've read the question a second time... I've added a sub-query example to my answer, because (based on info provided by OP) that might be what he's looking for, too... – Elias Van Ootegem Aug 30 '13 at 11:21
@EliasVanOotegem +1 from me :) – Fluffeh Aug 30 '13 at 11:22
+1 right back... my answer is getting rather tltr, and probably unintelligable for the OP, or besides the point – Elias Van Ootegem Aug 30 '13 at 11:27
1

@EliasVanOotegem Hehehe, you think that's long? Take a gander at the link in my answer. I had to post under multiple answers just to get it in there... – Fluffeh Aug 30 '13 at 11:28

score 1 · Answer 2 · edited May 23 '17 at 11:47

As I said in my comment, it's quite easy (if you read the manual):

SELECT field1, field2, field3 AS altName, lastFieldNoCommaHere
    FROM table
WHERE someField = 'value'
  AND id IN (1,2,3,4,5,6,7,8,9)
  AND (dateField > NOW()
       OR dateField IS NULL);

RTM and don't use mysql_* extension, it's deprecated. Use PDO or mysqli_*, not just because of the deprecation issue, But for various reasons.

Seing as you're using movie.star1 in your where clause, you should look into the JOIN syntax, too:

SELECT actor_id.name 
FROM actor_id
    LEFT JOIN movie
       ON movie.star1 = actor_id.actor_id
WHERE actor_id.actor_id IN (1,2,4,5,6,7,8,123);

That's, probably, what you're after, or perhaps:

SELECT actor_id.name
FROM actor_id
WHERE actor_id.actor_id
          IN ( SELECT movie.star1
                  FROM movie
                  WHERE movie.release_date >= '2012-01-01');

This is an example of a sub-query. Be careful with this, because it's often quite slow...
And as Fluffeh pointed out to me, you can't select multiple values, and have to resort to something like this:

SELECT actor_id.name
FROM actor_id
WHERE actor_id.actor_id
          IN ( SELECT IFNULL(movie.star1, IFNULL(movie.star2, movie.star3))
                  FROM movie
                  WHERE movie.release_date >= '2012-01-01');

Or:

SELECT actor_id.name
FROM actor_id
WHERE actor_id.actor_id
          IN ( SELECT IF (movie.star1 <= 12,
                          IF (movie.star2 <= 15,
                              movie.star3,
                              movie.star2),
                          movie.star1)
                  FROM movie
                  WHERE movie.release_date >= '2012-01-01');

You can't use multiple columns in a `in (select...)` subquery though. — Fluffeh, Aug 30 '13 at 11:23

to4dy · Answer 3 · 2013-08-30T11:02:34.920

0

Use the or statement. I don't know your db structure but should looks like this with the or:

SELECT actor_id.actor_name,
FROM actor_id 
WHERE (movie.star1 = actor_id.actor_id or movie.star2 = actor_id.actor_id);

edited Aug 30 '13 at 11:02

answered Aug 30 '13 at 11:01

to4dy

128
1
1
10

score 0 · Answer 4 · answered Aug 30 '13 at 11:07

0

SELECT actor_id.actor_name
FROM actor_id WHERE actor_id.actor_id in [specify the values like movie.star2, movie.star3... etc];

But can not use the comma before FROM ..

answered Aug 30 '13 at 11:07

Gautam Tak

129
1
7

php mysql select multiple values from one table

4 Answers4