MySQL Output different values by the same id

Question

I have a database containing two tables:

products_attributes ( id , image )
options_values ( id , text )

The id value is the same in both tables. Now I need to output the text and the image based on the same id from both tables.

Then possibly base this on the loop. I have created something like this, but it doesn't work correctly.

$sqlquery = "SELECT * FROM products_attributes JOIN options_values WHERE BY
 id=id ASC LIMIT 0,40";
$sqlresult = @mysql_query($sqlquery);
while ($content = mysql_fetch_array($sqlresult, MYSQL_NUM)) {
    echo "<img src='$content[1]'/> $content[2]";
}

@EDIT

So the query ended up like this, however I can not get the attributes_image (table column name) to display. It does however work using the numerical system.

$sqlquery = "SELECT * FROM zen_products_attributes pa JOIN zen_products_options_values ov ON pa.options_values_id = ov.products_options_values_id LIMIT 0,40;";
$sqlresult = @mysql_query ($sqlquery);
while ($content = mysql_fetch_array($sqlresult, MYSQL_NUM)){

    echo $content['attributes_image'];

;}

Cris · Accepted Answer · 2014-01-02 12:51:26Z

2

Use

   $sqlquery = "SELECT * FROM products_attributes JOIN options_values 
               ON products_attributes.id=options_values.id ASC LIMIT 0,40";

answered Jan 2, 2014 at 12:51

Cris

13.4k5 gold badges37 silver badges51 bronze badges

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

3 Comments

R R Over a year ago

just curious what does 'WHERE BY ' does?

Cris Over a year ago

@Mrcoder oops my bad!!!i have copied the query from OP's post,didnt recognized it,thanks for correcting

pasujemito Over a year ago

How do I go about displaying the results in a PHP $content form, using your example?

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

1:You need to use alias for tables when you are joining on same column name to avoid ambiguity.i.e 'id=id' is wrong.

2.Syntax error.there is no 'WHERE BY' in mysql.

$sqlquery = "SELECT * FROM 
 products_attributes pa 
JOIN options_values ov 
on pa.id=ov.id ASC LIMIT 0,40";

Edit answer:

while ($content = mysql_fetch_array($sqlresult, MYSQL_NUM)){

    echo $content['attributes_image'];

;}

--^(extra semi colon,remove this)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 2, 2014 at 12:51

R R

2,9562 gold badges27 silver badges43 bronze badges

14 Comments

pasujemito Over a year ago

How do I go about outputting the data from those tables. As I'd like it to only display data that has the exact the same id. ie. Table 1 - id:1 Green, id:2 Red, id:3 Yellow Table 2 - id:1 green.jpg, id:2 red.jpg, id:3 yellow.jpg The PHP quarry will display the text and the image based on the same ID.

R R Over a year ago

@DamianJanik please elaborate or if possible update your question.

R R Over a year ago

@DamianJanik the query i have written will do the same as we are joining on id value.

pasujemito Over a year ago

What I mean is how would I go about using PHP in this case. As previously I was able to retrieve the data using $content[1], now I have two tables and not sure how to go about getting it from both tables. As my tables contain more columns and I need to specify the data I'd like to retrieve.

R R Over a year ago

just give the index as name like this $content['id'],$content['image']

|

rray · Accepted Answer · 2014-01-02 12:51:55Z

0

if you have fields with same name give them alias.

SELECT p.`desc` as description, v.`desc` FROM products_attributes as p
JOIN options_values as v ON  p.id=v.id
ASC LIMIT 0,40

answered Jan 2, 2014 at 12:51

rray

2,5461 gold badge30 silver badges38 bronze badges

Comments

Peon · Accepted Answer · 2014-01-02 12:52:12Z

0

SELECT
    *
FROM
    products_attributes pa
JOIN
    options_values ov
ON
    pa.id = ov.id
LIMIT
    0,40;

answered Jan 2, 2014 at 12:52

Peon

8,0417 gold badges63 silver badges104 bronze badges

Comments

sas · Accepted Answer · 2014-01-02 12:52:29Z

0

SELECT * FROM products_attributes AS pa JOIN options_values AS ov ON pa.id=ov.id ORDER BY ov.id ASC LIMIT 0,40

answered Jan 2, 2014 at 12:52

sas

2,5971 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL Output different values by the same id

5 Answers 5

3 Comments

14 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

14 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related