donpedroper donpedroper - 18 days ago 6
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.id, events.title, events.type,
types.text AS typeText
FROM events
INNER JOIN types
ON events.type = types.type
ORDER BY events.id DESC
" 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

Closing the question again without a resolution.