CODEWITHSUNDEEP
phpmysqlarrays
surfbird0713
surfbird0713

Reputation: 1219

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

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.

Upvotes: 0

Views: 634

Answers (2)

bloodyKnuckles
bloodyKnuckles

Reputation: 12089

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];

Upvotes: 0

surfbird0713
surfbird0713

Reputation: 1219

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);

Upvotes: 1

Related Questions