Nu heb ik een probleem met mijn query en bijhorende output. Zie hieronder.
Tabellen in db:
Tabel users:
id firstname lastname company
1 user1_firstname user1_lastname company_user1
2 user2_firstname user2_lastname company_user2
Tabel messages
id userid motivation article_id date_created
1 1 Motivation text from buyer 1 1 date_created1
2 1 Motivation text from buyer 1 1 date_created2
3 2 Motivation text from buyer 2 1 date_created3
Tabel articles
id userid title explaination date_created
1 3 Title for article 1 explaination for article 1 date_created1
2 3 Title for article 2 explaination for article 2 date_created2
Tabel messages_sellers
id userid_buyers message_id reaction article_id date_created
1 1 1 Reaction from seller to buyer 1 1 date_created1
2 2 2 Reaction from seller to buyer 2 2
Totale code voor de output:
<?php
$article_id = $_GET['article_id'];
$sql = "
SELECT
u.firstname,
u.lastname,
u.company,
m.motivation,
ms.reaction,
m.userid as message_id_buyer,
a.id as article_idx,
a.explaination,
a.date_created as datecreated_seller,
m.id as message_id,
group_concat(CONCAT(m.motivation, '&&', m.date_created) SEPARATOR '----') as motivations,
group_concat(CONCAT(ms.reaction, '&&', ms.date_created) SEPARATOR '----') as reactions
FROM
messages m
LEFT JOIN
articles a
ON
a.id = m.article_id
LEFT JOIN
users u
ON
a.userid = u.id
LEFT JOIN
messages_sellers ms
ON
ms.message_id = m.id
WHERE
m.article_id = '$article_id'
GROUP BY
a.id
ORDER BY
m.date_created desc
";
$res = mysql_query($sql) or die (mysql_error());
$num_rows = mysql_num_rows($res);
if($num_rows > 0) {
while($row = mysql_fetch_assoc($res)) {
?>
<?php //Explaination from seller for article ?>
<article class="review-box clearfix">
<div class="rev-content" style="margin-left: -90px !important;">
<h3 style="text-transform: none !important;"><?php echo $row['firstname']; ?> <?php echo $row['lastname']; ?> (<?php echo $row['company']; ?>) | <?php echo date('d-m-Y H:i', strtotime($row['datecreated_seller'])); ?></h3>
<div class="rev-text">
<p><?php echo $row['explaination']; ?></p>
<a href="?article_id=<?php echo $row['article_idx']; ?>&userid_buyer=<?php echo $row['message_id_buyer']; ?>#add-review" class="theme-button marT10">Post your reaction</a>
</div>
</div>
</article>
<?php
$motivations = $row['motivations'];
$motivations_array = explode('----',$motivations);
if(is_array($motivations_array) && count($motivations_array) > 0 ){
foreach($motivations_array as $motivation_name){
$arr = explode('&&',$motivation_name);
if(!empty($arr[1])){
?>
<!-- posting motivations -->
<article class="review-box clearfix" style="margin-bottom: -5px !important;">
<div class="rev-content">
<h3 style="text-transform: none !important;">Uw antwoord verstuurd op <?php echo date('d-m-Y H:i', strtotime($arr[1])); ?></h3>
<div class="rev-text">
<p><?php echo $arr[0]; ?></p>
</div>
</div>
</article>
<?php } } } ?>
<?php
$reactions = $row['reactions'];
$reactions_array = explode('----',$reactions);
if(is_array($reactions_array) && count($reactions_array) > 0 ){
foreach($reactions_array as $reaction_name){
$arr2 = explode('&&',$reaction_name);
if(!empty($arr2[1])){
?>
<!-- Posting reactions on motivations -->
<article class="review-box clearfix" style="margin-left: -90px !important;">
<div class="rev-content">
<h3 style="text-transform: none !important;">Uw reactie verstuurd op <?php echo date('d-m-Y H:i', strtotime($arr2[1])); ?></h3>
<div class="rev-text">
<p><?php echo $arr2[0]; ?></p>
</div>
</div>
</article>
<?php } } } ?>
Het resultaat hiervan is:
Explaination for article seller 1
motivation message buyer 1 for article seller 1
motivation message buyer 1 for article seller 1
reaction back from buyer 1
reaction seller 1
Maar wat ik uiteindelijk wil bereiken is:
Explaination for article seller 1
motivation message buyer 1 for article seller 1
motivation message buyer 1 for article seller 1
Reaction seller 1
reaction back from buyer 1
Iemand die mij hiermee kan helpen?
