user1915308 user1915308 - 5 months ago 12
PHP Question

Getting All the information from two tables

I'm having some issues trying to get all the information from two tables with my

query


here is how my tables look

products

http://imgur.com/uI7Z8XG

products_photos

http://imgur.com/l5JSxWx

and here is my
SQL query


$id = json_decode(file_get_contents('php://input'), true)["id"];

$result = mysqli_query($con, "SELECT * FROM products INNER JOIN products_photos ON products.ProductId = products_photos.ProductId");
$row = mysqli_fetch_object($result);

$data = '';

if (!is_null($row)) {
$data = $row;
}

print json_encode($data);


when I do a
var_dump($row)
this is the result I get

object(stdClass)[3]
public 'ProductId' => string 'pzhVrLsYXG' (length=10)
public 'ProductName' => string 'Dell xps' (length=8)
public 'ProductStatus' => string 'instock' (length=7)
public 'ProductOverview' => string '' (length=0)
public 'CategoryId' => string '5' (length=1)
public 'CharacteristicId' => null
public 'ReviewId' => null
public 'ProducerId' => string '1' (length=1)
public 'PromotionId' => null
public 'ProductQty' => string '35' (length=2)
public 'ProductOldPrice' => string '' (length=0)
public 'ProductPrice' => string '2000' (length=4)
public 'ProductLink' => string 'dell-xps' (length=8)
public 'ProductPhoto' => string 'http://ecommerce/uploads/NDgxNzYwLWRlbGwteHBzLTg5MDAuanBn.jpg' (length=61)
public 'PhotoId' => string '11' (length=2)
public 'PhotoSrc' => string 'http://ecommerce/uploads/NDM5MDUzNDUzNDUuanBn.jpg' (length=49)


How can I make it so
PhotoSrc
returns an
array
of all my photos rather than a
string


*Note: I'm not sure if
PhotoId
in the
products
table is needed or not

Answer

It looks like you're selecting the data with a full join. No. Think about it, there might be a case where you have a product without any photo, we're dealing with multiple photos possibly linked to a single product and SQL does not permit selecting array to column value so we'll have to iterate through your query result for all products and declare Photos manually

$result = mysqli_query($con, "SELECT * FROM products");
$products = array();
if ($result) //sql query was executed successfully
{
    while (($product = mysqli_fetch_object(result)) != null) //iterate through all rows
    {
        $pid = (int)$product->ProductId;
        $photoresult = mysqli_query($con, "SELECT * FROM products_photos where ProductId = $pid");
        $product->Photos = array();
        if ($photoresult) //sql query was executed successfully
        {
            while (($photo = mysqli_fetch_object($photoresult)) != null) //iterate through all rows
            {
                //push the photo into the products photo array
                array_push($product->Photos, $photo);
            }
        }
        //push the product into products array
        array_push($products, $product);
    }
}

var_dump($products);//for debugging purpose (todo: remove)