store multiple row result from mySQL left join query as one variable

Question

I am new to php and mySQL. I have a left join query that I am using to find all the products a customer has registered. My query works in phpmyadmin, and I can echo it in php with the following code:

        $custProds =  "SELECT t.prodType, s.sizes, c.color FROM registeredProducts p LEFT JOIN prodTypes t ON t.id = p.prodType LEFT JOIN prodSizes s ON  s.id = p.prodSize LEFT JOIN prodColors c ON c.id = p.prodColor WHERE p.customerID = '".$thisCust."'"; 
        $allCustProds = mysql_query($custProds);    
        while($prodRow = mysql_fetch_array($allCustProds)){
            echo $prodRow['prodType']. " - ". $prodRow['sizes']. " - ". $prodRow['color'];
            echo "<br />";
        }

However, I don't want to echo it for the user to see, I want to store the result in a variable for later use. I have tried doing this instead:

        while($prodRow = mysql_fetch_array($allCustProds)){
            $allProds = $prodRow['prodType']. " - ". $prodRow['sizes']. " - ". $prodRow['color'];

        }

but that only gives me the most recent row inserted instead of all results. Same with using my_fetch_assoc as seen here. I have also tried this:

        while($prodRow = mysql_fetch_array($allCustProds)){
            $allProds = array($prodRow['prodType']. " - ". $prodRow['sizes']. " - ". $prodRow['color']);
        }

which echos 'Array'. I followed this post and used print_r($allProds); which once again only gave me the last inserted row. I have spent hours researching this and would be so grateful for any help. Thank you.

The while loop will go through all of the rows, sequentially, but you're setting $allProds to each row you get. Meaning it just gets overwritten. What you might want to do, is make $allProds an array before doing the while loop, and then push each item onto it. — crimson_penguin, Jun 23 '14 at 19:46
@crimson_penguin i know how to make $allProds an array but how do I push the results from the loop into it without overwriting it? — surfbird0713, Jun 23 '14 at 19:51
@bloodyKnuckles thank you but that didn't work, still says "Array" — surfbird0713, Jun 23 '14 at 19:51

score 1 · Answer 1 · answered Jun 23 '14 at 20:24

I figured it out and wanted to post in case any one else has this issue. Needed to declare $allProds as an array, and then use array_push to store each instance. Then used implode to store into one variable for later use.

        $custProds =  "SELECT t.prodType, s.sizes, c.color FROM registeredProducts p LEFT JOIN prodTypes t ON t.id = p.prodType LEFT JOIN prodSizes s ON  s.id = p.prodSize LEFT JOIN prodColors c ON c.id = p.prodColor WHERE p.customerID = '".$thisCust."'"; 
        $allCustProds = mysql_query($custProds);
        $allProds = array();    
        while($prodRow = mysql_fetch_assoc($allCustProds)){
            array_push($allProds, $prodRow['prodType']. " - ". $prodRow['sizes']. " - ". $prodRow['color']);
        }
        $custProds = implode('<br>', $allProds);

score 0 · Answer 2 · answered Jun 23 '14 at 20:25

As you figured out, you needed to push the rows into an array variable. This is another form of that, using a unique ID, if you have one, in case you want to pull a specific row out later.

while($prodRow = mysql_fetch_array($allCustProds)){
    $allProds[$prodRow['prodID']] = array($prodRow['prodType']. " - ". $prodRow['sizes']. " - ". $prodRow['color']);
}

foreach ( $allProds as $prodID => $prodRow ) {
  echo "$prodID: $prodRow<br>";
}

// can also be accessed by:
echo $allProds[$getProdID];

store multiple row result from mySQL left join query as one variable

2 Answers2