SQL joining join creates a nice array
Hi I want to make a good query to have a nice array. Now, for example, I have this request:
SELECT DISTINCT * FROM products
LEFT OUTER JOIN product_aliases
ON product_aliases.product_id = products.id
AND product_aliases.alias = '$alias'
LEFT OUTER JOIN (product_images
LEFT OUTER JOIN product_image_votes
ON product_image_votes.product_image_id = product_images.id)
ON product_images.product_id = products.id
WHERE products.id = $id
This results in two arrays:
array(
(int) 0 => array(
'products' => array(
'id' => '1',
'user_id' => '1',
),
'product_aliases' => array(
'id' => '1',
'product_id' => '1',
'language' => 'it',
),
'product_images' => array(
'id' => '1',
'product_id' => '1',
),
'product_image_votes' => array(
'id' => '2',
'product_image_id' => '1',
'vote' => '1',
)
),
(int) 1 => array(
'products' => array(
'id' => '1',
'user_id' => '1',
),
'product_aliases' => array(
'id' => '1',
'product_id' => '1',
'language' => 'it',
),
'product_images' => array(
'id' => '1',
'product_id' => '1',
),
'product_image_votes' => array(
'id' => '2',
'product_image_id' => '1',
'vote' => '1',
)
)
The problem is: I only want a unique array containing the product and for example product_images, which are contained in the product_images_votes array. First problem:
- have a unique array, not two arrays
- create an array inside an array at the base of my left unlink
Array example:
array(
(int) 0 => array(
'products' => array(
'id' => '1',
'user_id' => '1',
),
'product_aliases' => array(
'id' => '1',
'product_id' => '1',
'language' => 'it',
),
'product_images' => array(
'id' => '1',
'product_id' => '1',
array('product_image_votes' => array(
'id' => '2',
'product_image_id' => '1',
'vote' => '1',
))
)
Can I do it? I am working with php
source to share
Your query is fine as is, but you need to build nesting in PHP. You really can't create nested structures in SQL, so you have to work with a flattened structure like you do.
This can be done with some creative loop. Create an output array that is indexable products['id']
. At each iteration, create a new record if it doesn't already exist. If it exists, add it to product_images
, the array is indexed as well product_images['id']
.
// To hold the final array
$output = array();
foreach ($original_array as $row) {
// Storing these id values to make them easier to refer to...
$id = $row['products']['id'];
$pid = $row['product_images']['id'];
$paid = $row['product_aliases']['id'];
$pivid = $row['product_image_votes']['id'];
// Create the product entry if it does not exist
// and initialize arrays for product_images and product_aliases
if (!isset($output[$id])) {
$output[$id] = array(
'products' => $row['products'],
// Initialize these to sub-arrays containing the first ones from this row
// using the id as the array key
'product_aliases' => array($paid => $row['product_aliases']),
'product_images' => array($pid => $row['product_images'])
);
// Then add the first vote
$output[$id]['product_images'][$pid]['product_image_votes'] = array();
$output[$id]['product_images'][$pid]['product_image_votes'][$pivid] = $row['product_image_votes'];
}
// If it does exist already, append the alias if that does not exist, the image, the vote etc.
else {
// First add the alias if not already set
if (!isset($output[$id]['product_aliases'][$paid])) {
$output[$id]['product_aliases'][$paid] = $row['product_aliases'];
}
// Then add the image if not already set
if (!isset($output[$id]['product_images'][$pid])) {
$output[$id]['product_images'][$pid] = $row['product_images'];
}
// And nest in the image_votes
if (!isset($output[$id]['product_images'][$pid]['product_image_votes'][$pivid])) {
$output[$id]['product_images'][$pid]['product_image_votes'][$pivid] = $row['product_image_votes'];
}
}
}
There are many here, and most likely I have syntax errors or are missing ]
somewhere. Good luck to you.
source to share