This code below is working but the output is not what i really want. We have different tables in database:
Table users:
id firstname lastname company
1 user1_firstname user1_lastname company_user1
2 user2_firstname user2_lastname company_user2
Table 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
Table 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
Table 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
And the code:
$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)) {
?>
<!-- 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 } } } ?>
The output is as follows:
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
But what i want is the following:
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
Anyone who can help me out here?
This is for a messages system where buyers have to post a motivation message to the sellers, so the seller can decide on who he will sell his product. It is like a webshop but a little bit different in communication. Buyers can give a reaction back on the message of the seller.
I think the query is correct but not the output.