PHP mysql query against two tables

Question

I have a sql query that is working well but I need a way to extend this query to also get information from another table called LineUps.

Original query:

$stmt = $conn->prepare("SELECT channel, description, Tier
FROM Channel_LineUps WHERE Market_ID = 1 ORDER BY Tier ASC");

I need to also now get data from a different table called.

Columns are called DIG and HD in the LineUps table.

I tried the following but does not work:

$stmt = $conn->prepare("SELECT Channel_LineUps.channel,   Channel_LineUps.description, Channel_LineUps.Tier, LineUps.HD, LineUps.DIG FROM Channel_LineUps, LineUps WHERE Channel_LineUps.Market_ID = 1 ORDER BY Channel_LineUps.Tier ASC");

i want to be able to determine where a channel is marked as yes for DIG or HD and so thinking I'll need a single query for this.

Any ideas?

Please provide table structure and sample data. At first sight, it looks like you have no join condition so you get a Cartesian join which probably takes too long to render. — A.D.
– A.D., Commented Feb 9, 2015 at 20:40
what key do the lineups and channel_lineups tables have in common? — oks
– oks, Commented Feb 9, 2015 at 20:43

Pawan · Accepted Answer · 2015-02-09 20:55:44Z

1

You need to create a join between two tables that is you need have a common column from first table (Channel_lineUps) to LineUps table and then you can have a inner join on these two table like channel_LineUps.id=LineUps.related_column

you can in your where clause just add like where Channel_lineUps.market_id=LineUps.market_id and it should return the related data.

edited Feb 9, 2015 at 20:55

answered Feb 9, 2015 at 20:44

Pawan

3,84417 gold badges53 silver badges88 bronze badges

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

5 Comments

user3137032 Over a year ago

Ok thanks. Market_ID is common to both. How would I put this in the php code?

user3137032 Over a year ago

Tried the following but receive an error: $stmt = $conn->prepare("SELECT Channel_LineUps.channel, Channel_LineUps.description, Channel_LineUps.Tier, LineUps.HD, LineUps.DIG FROM Channel_LineUps, LineUps WHERE Channel_LineUps.Market_ID=LineUps.Market_ID AND Market_ID = 1 ORDER BY Channel_LineUps.Tier ASC");

A.D. Over a year ago

Again, it would be easier if you provide table structure and sample data

user3137032 Over a year ago

Not sure if i know that. Not a sql guy. I think, my issue is somehow related to doing a where on Market_ID =1 - I need to limit to that Market_ID but if I insert the where clause to join "WHERE Channel_LineUps.Market_ID=LineUps.Market_ID" then it seems i do not have a way to limit to Market_ID = 1 The query seems to work but as i say, is not limiting the market by Market_ID

user3137032 Over a year ago

i ended up using this query which seems to solve the issue. I don;t think I provided you all with enough info to solve this. $stmt = $conn->prepare("SELECT distinct Channel_LineUps.channel, Channel_LineUps.description, Channel_LineUps.Tier, LineUps.HD, LineUps.DIG FROM Channel_LineUps, LineUps WHERE (LineUps.Market_ID = Channel_LineUps.Market_ID AND Channel_LineUps.Market_ID = 1) ORDER BY Channel_LineUps.Tier ASC");

Collectives™ on Stack Overflow

PHP mysql query against two tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related