CODEWITHSUNDEEP
phpmysql
H.HISTORY
H.HISTORY

Reputation: 518

PHP: MYSQL select inside another MYSQL select?

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.

Upvotes: 1

Views: 111

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

user2420647
user2420647

Reputation: 183

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)

Upvotes: 2

C3roe
C3roe

Reputation: 96226

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.

Upvotes: 1

Related Questions