PHP: MYSQL select inside another MYSQL select?

Question

I'm trying to place one mysql select inside another one and combine the results to be displayed.

this is my code:

$allattrs = "";
$sql69 = "SELECT * FROM product_details";
$query69 = mysqli_query($db_conx, $sql69);
$login_check69 = mysqli_num_rows($query69);
if($login_check69 > 0){ 
    while($row69 = mysqli_fetch_array($query69, MYSQLI_ASSOC)){ 


$FID = $row69["id"];

$sql2s = "SELECT * FROM ATTRIBUTES WHERE id='$FID'";
$query2s = mysqli_query($db_conx, $sql2s);
$login_check2s = mysqli_num_rows($query2s);
if($login_check2s > 0){ 
    while($row2s = mysqli_fetch_array($query2s, MYSQLI_ASSOC)){ 
        // Get member ID into a session variable

             $Sid = $row2s["id"];
             $attr = $row2s["attr"];

             $allattrs .= ''.$attr.', ';
    }

}



    $product_list .= '<tr>
        <td>'.$allattrs.'</td>
    </tr>';

    } 
}

The problem i'm having is that the $allattrs returns the values but it will put everthing together.

for example:

if one attr column in mysql database has apples, and another one has oranges, when i see the results of $allattrs on my PHP page i see this:

id 1 - apples
id 2 - apples, oranges
id 3 - apples, oranges, apples, oranges 

etc etc

this is in fact wrong as each attribute value needs to stay true to their own id and product_details id field!

I'm not sure what I am doing wrong to cause this.

could someone please advise on this issue?

any help would be appreciated.

Can you please share your tables' structure, some sample data, and the result you're trying to achieve? — Mureinik
– Mureinik, Commented Oct 4, 2015 at 16:09

Gordon Linoff · Accepted Answer · 2015-10-04 16:15:51Z

3

The right way to write your query is using JOIN, EXISTS, or IN. I think you would find this most natural:

SELECT a.id, GROUP_CONCAT(a.attr) as attrs
FROM ATTRIBUTES a
WHERE a.id IN (SELECT id FROM product_details)
GROUP BY a.id;

This replaces a bunch of your code.

answered Oct 4, 2015 at 16:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

user2420647 · Accepted Answer · 2015-10-04 16:14:47Z

2

Looks like you are only interested in the the attributes then try this out instead of the first sql:

SELECT * FROM ATTRIBUTES where id IN (SELECT id FROM product_details)

answered Oct 4, 2015 at 16:14

user2420647

18311 bronze badges

Comments

C3roe · Accepted Answer · 2015-10-04 16:10:16Z

1

You need to set $allattrs to an empty string inside your first while loop, instead of only once before.

Apart from that, you should look into the following two topics: Normalization and JOINs.

answered Oct 4, 2015 at 16:10

C3roe

97.7k15 gold badges100 silver badges172 bronze badges

Collectives™ on Stack Overflow

PHP: MYSQL select inside another MYSQL select?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related