CODEWITHSUNDEEP
phpmysqlsql
Richard Harper
Richard Harper

Reputation: 53

mysql_query results in a while loop X 2

I am completely stumped here, Thanks in advance for any solutions or pointers, I really do appreciate any help you can offer.

I am trying to make a memo system. Showing memo's is no problem but I am trying to only show the unread memo's. I have 2 tables as you can see, one that has the memo's and the other that lists the read memo's. when a user has read the memo's he/she ticks the box and the memo id and the user id is added to the table 'memo_read'.

So basically I am trying to retrieve the memo's that have been read from the 'memo_read' table and then display the results from the 'memo' table that haven't been read where the user_id and the company are correct. As you can see I have 2 variables from a function on another page and these are working fine '$user_id , $company_id',

I am trying to display the unread memo's in a while loop displaying the desired variables. I have been working on this for ages and no joy, I have tried every approach i can think of but i'm sure you can tell i'm not the most experienced of guys and i'm sure am have missed some thing or have taken the completely wrong approach! 

<?php
$user_id = $user_data['user_id'] ;
$company_id = $user_data['company'];

$result1 = mysql_query ("SELECT `user`, `memo_id` FROM`memo_read` WHERE `user`= '$user_id '") or die(mysql_error());
while($row = mysql_fetch_array($result1)){
$memo_id   = $row['memo_id']; 


$result = mysql_query ("SELECT `id`, `link`, `author`, `time`, `title`, `company` FROM `memo` WHERE `worker`= 1 AND `company`= '$company_id' AND `id`!= '$memo_id'") or die(mysql_error());
while($row = mysql_fetch_array($result))    {

    $id           = $row['id']; 
    $link         = $row['link'];
    $author     = $row['author'];
    $time        = $row['time'];
    $title         = $row['title'];
    $company  = $row['company'];

?>

<ul>
    <li><?php  echo $title; ?></li>
    <li><?php echo $author; ?></li>
    <li><?php echo $company;?></li>
    <li><?php echo $time;?></li>
    <a href="<?php  echo $link; ?>"> Read memo</a> 
</ul><br>
<?php
}   }
?>

Upvotes: 1

Views: 218

Answers (2)

Adam
Adam

Reputation: 1371

Try This: 

<?php
$user_id = $user_data['user_id'] ;
$company_id = $user_data['company'];

$result1 = mysql_query ("SELECT `user`, `memo_id` FROM`memo_read` WHERE `user`= '$user_id '") or die(mysql_error());
$memo_id_query = '';
while($row = mysql_fetch_array($result1)){
$memo_id_query .= " AND `id`!= '".$row['memo_id']."'";
}


$result = mysql_query ("SELECT `id`, `link`, `author`, `time`, `title`, `company` FROM `memo` WHERE `worker`= 1 AND `company`= '$company_id' ".$memo_id_query) or die(mysql_error());
while($row = mysql_fetch_array($result))    {

    $id           = $row['id']; 
    $link         = $row['link'];
    $author     = $row['author'];
    $time        = $row['time'];
    $title         = $row['title'];
    $company  = $row['company'];

?>

<ul>
    <li><?php  echo $title; ?></li>
    <li><?php echo $author; ?></li>
    <li><?php echo $company;?></li>
    <li><?php echo $time;?></li>
    <a href="<?php  echo $link; ?>"> Read memo</a> 
</ul><br>
<?php
}
?>

Upvotes: 1

Oscar P&#233;rez
Oscar P&#233;rez

Reputation: 4397

Why don't you use a query that returns unread memos? Something like:

 SELECT `id`, 
        `link`, 
        `author`, 
        `time`, 
        `title`, 
        `company` 
   FROM `memo` 
  WHERE `worker`= 1 
    AND `company`= '$company_id' 
    AND `id` NOT IN (SELECT `memo_id` 
                       FROM `memo_read` 
                      WHERE `user`= '$user_id '
                    )

Upvotes: 2

Related Questions