MYSQL Selection

[TauZu]

Hi,

I have a question on how I do a multiple selection from a database. Basically I have one table that is this:
SELECT * FROM show_episodes ORDER BY show_episodes.release_date DESC LIMIT 6

but within this table is a (Show ID) number that represents the name of a show on another table within the same database.

How do I write up a php script that basically says if the show id=2, then show name?
Do you know what I mean?

Thanks,
I'm a newbie!

 

[cLFlaVA]

you don't need to do this with PHP - you can do it with MySQL...

this will give you the records you're already receiving in your code above, along with one additional column containing the show name.

  select e.*
       , o.episode_name
    from show_episodes e join other_table o
      on e.show_id = o.show_id
order by e.release_date desc
   limit 6

*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TauZu]

Thanks cLFlaVA!

So basically I can join the two tables and take the ID# from table one and have it give me the show name it represents from table two.

Is that how it would be written out?

 

[cLFlaVA]

more or less. joins are a very integral, important and necessary part of almost all database applications. i suggest reading up on them and playing with them to see what works best for you.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TauZu]

thanks. I really appreciate it.

so when I do the PHP, do I take the PHP item from table one or table two?

 

[cLFlaVA]

there are several ways to retrieve information from results sets in PHP. if you run the query I provided (you'll need to change the second table name and possibly the column names), you will see the result set will be something like:

[tt]
table1col1 table1col2 ... table1coln table2col1
-------------------------------------------------------
x x x show name
y y y another show name
[/tt]

so you'd just use your standard PHP to display a column from a result set. for more detailed information, i suggest visiting the PHP forum (forum434) if you're not sure where to go from there.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TauZu]

Hi cLFlaVA,

This is what I did, but its not working:

select e.* , o.name from show_episodes e join shows o on e.show_id = o.show_id
order by e.release_date desc
limit 6;

is there something I'm missing here? I tried a query and it didn't work?

 

[cLFlaVA]

but it's not working". what's the error message?



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TauZu]

#1064 - You have an error in your SQL syntax near 'on e.show_id = o.id
order by e.release_date desc limit 6' at line 1

 

[TauZu]

I'm not sure what type of error this is, but this is what I understand from what you showed me.

1) when I do select e.* - this means select from the database using the "e" as the first table in my list?
2) o.name - on the second line here - does this state the category I want to eventually get to?

 

[cLFlaVA]

maybe o is a reserved word (although i doubt it).

try changing o to something else.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[TauZu]

I solved it.

SELECT * FROM show_episodes, shows WHERE show_episodes.show_id=shows.id order by show_episodes.release_date desc limit 6

basically I just join the two tables and I just state what the bridge is between the two tables then go about my biz.

thanks CLFLAVA, I wouldn't have gotten this far without your assistance. It is much appreciated.

 

[r937]

if this works --

SELECT * FROM show_episodes, shows WHERE show_episodes.show_id=shows.id order by show_episodes.release_date desc limit 6

then this will too, and it's the recommended syntax --

SELECT show_episodes.*
, shows.name
FROM show_episodes
inner
join shows
on shows.id = show_episodes.show_id
order
by show_episodes.release_date desc limit 6

r937.com | rudy.ca