mysql_fetch_array from two different rows

Question

I am having difficulty to explain this problem, but I will try anyway.

I have two different database rows

  SOMETHING
*---------------------------------------------------------*
|    id    |    category_id    |    body                  |
*---------------------------------------------------------*

  CATEGORY
*---------------------------------------------------------*
|    id    |    title    |    description                 |
*---------------------------------------------------------*

Now, I want to extract data from both rows at the samt time, how do i do this?

I need to extract values from SOMETHING and display the info from CATEGORY from the category_id in SOMETHING - how is this done the best way ?

I use to extract values like this:

$query = mysql_query("select * from SOMETHING WHERE id='$id' LIMIT 1");

while ($row = mysql_fetch_array($query)) {
   extract($row);

}

Mark Byers · Accepted Answer · 2011-11-22 20:04:07Z

2

Use a join

SELECT
    S.id,
    S.category_id,
    S.body,
    C.title,
    C.description
FROM SOMETHING AS S
JOIN CATEGORY AS C
ON S.category_id = C.id
WHERE id = '42'
LIMIT 1

If there might not be a matching row in the CATEGORY table you could use a LEFT JOIN instead.

answered Nov 22, 2011 at 20:04

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

5 Comments

2by Over a year ago

Thanks, can you give me an example on how to print the description from the category then? What if both rows has "description" ?

Mark Byers Over a year ago

@2by: You can give each column a unique alias. SELECT S.description AS something_description, C.description AS column_description, ...etc...

2by Over a year ago

Ah okay, thanks, i will try that. And yes, i need to use LEFT JOIN i can see.

2by Over a year ago

Can i select all results from SOMETHING? Like S.* ?

Mark Byers Over a year ago

@2by: Yes, but there are good reasons why that is not recommended, and they have been discussed before at great length on this site so I won't repeat it all here.

Chris Forrence · Accepted Answer · 2011-11-22 20:05:17Z

0

It sounds like you should join the two tables together:

SELECT * FROM SOMETHING, CATEGORY WHERE SOMETHING.category_id=CATEGORY.id AND 
SOMETHING.id='$id' LIMIT 1

Make sure you sanitize $id coming into the database!

answered Nov 22, 2011 at 20:05

Chris Forrence

10.1k11 gold badges50 silver badges66 bronze badges

Comments

stealthyninja · Accepted Answer · 2011-11-22 20:06:20Z

0

$query = mysql_query("SELECT * FROM SOMETHING INNER JOIN CATEGORY ON CATEGORY.id = SOMETHING.category_id WHERE id='$id' LIMIT 1");

This is susceptible to SQL injection though.

answered Nov 22, 2011 at 20:06

stealthyninja

10.4k11 gold badges56 silver badges61 bronze badges

1 Comment

paulsm4 Over a year ago

@2by - by "susceptible to SQL injection", stealthyninja is warning you against EVER passing ANY user input directly into a SQL query. GlaciesofPacis is also warning you of the same thing.

paulsm4 · Accepted Answer · 2011-11-22 20:08:35Z

0

You definitely want a "join". Here's another example:

select a.id ID, b.title TITLE, b.description DESCRIPTION, a.body TEXT
from something a 
inner join category b on a.id = b.id;

answered Nov 22, 2011 at 20:08

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Collectives™ on Stack Overflow

mysql_fetch_array from two different rows

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related