donpedroper donpedroper - 1 year ago 86
MySQL Question

LEFT/OUTER JOIN with result as array instead of nested mysql queries

Is there a way to put the result of a LEFT/OUTER JOIN table into an array instead of using nested queries? As far as I remember MySQL doesn't support array datatypes, but I figured that there has to be a workaround. I read somewhere about concatenating the result to a comma seperated list, but is this really the way to go?

The code beneath works with a "nested query" for images[], but the performance and response time is bad.

$qur = "
SELECT, events.title, events.type,
types.text AS typeText
FROM events
ON events.type = types.type
" or die("Error in the consult.." . mysqli_error($link));

$result = $link->query($qur);
$events = array();

while ($row = mysqli_fetch_assoc($result)) {

// Fetch images & extend in event object
$qurImg = "SELECT * FROM images WHERE event = '".$row["id"]."'" or die("Error in the consult.." . mysqli_error($link));
$resultImg = $link->query($qurImg);

$images = array();
while ($rowImg = mysqli_fetch_assoc($resultImg)) {
$images[] = $rowImg;

$row['images'] = $images;
$events[] = $row;

$json = array("events" => $events);

echo json_encode($json);

This gives me a result like this, which I would like to achieve with LEFT/OUTER JOIN instead:

enter image description here

Answer Source

Closing the question again without a resolution.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download