CODEWITHSUNDEEP
phpsql
user1915308
user1915308

Reputation: 364

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

https://i.sstatic.net/uRHfN.jpg

products_photos

https://i.sstatic.net/F1afj.jpg

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

Upvotes: 0

Views: 51

Answers (3)

Efe Omoregie Elijah
Efe Omoregie Elijah

Reputation: 488

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)

Upvotes: 1

Christian
Christian

Reputation: 423

First of all, in case you'd expect products which do not have photos, you should probably use a left join (use all rows from the first table and join rows from second table if there are any):

SELECT * FROM products LEFT JOIN products_photos ON products.ProductId = products_photos.ProductId

The statement will give you the product along with every photo, in case there is one (be careful, the product will hence be the same in 2 rows, only the latter part with the image will change). The reason you don't get this from the inner join (which would also work in the case every product has at least one photo) is that mysqli_fetch_object returns only the current row (see http://www.w3schools.com/php/func_mysqli_fetch_object.asp for a solution to loop all rows).

The photoId in the products table is not needed for this kind of relationship.

Upvotes: 0

Cathmor
Cathmor

Reputation: 59

I think you need to first split images and products apart...

$images = array_map(function($element){; return [$element['ProductId'] => $element['PhotoSrc']];}, $products);

And then loop through the array and add push the value into the photosrc array.

Upvotes: 0

Related Questions