Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MYSQL Selection

Status
Not open for further replies.

TauZu

IS-IT--Management
Jul 10, 2006
8
US
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!
 
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.
Code:
  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>)13 * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]
 
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?
 
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>)13 * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]
 
thanks. I really appreciate it.

so when I do the PHP, do I take the PHP item from table one or table two?
 
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>)13 * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]
 
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?
 
#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
 
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?
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top