how to use 2 while loops together from fetching same table but different conditions

Question

I am trying to create online chat between admin and user by using PHP and MySQL.for that I created a common table for saving messages from admin and users. and I used two while loops with different conditions like printing their messages.by using while loops together, one loop only working..can you please help me to work 2 while loops work simultaneously...

here is my PHP code:-

<?php
$userid=$_SESSION['registerid'];
$sql1="select * from registers where registerid='$userid'";
$query1=mysql_query($sql1,$con);
$result1=mysql_fetch_array($query1);
$username=$result1['name'];
$sql18="select * from usertoadmin_chat where userid='$userid' and sender='$username'";
$query18=mysql_query($sql18,$con);
$sql19="select * from usertoadmin_chat where sender='Admin' and userid='$userid'";
$query19=mysql_query($sql19,$con);
 ?>

here my displaying code:-

 <form method="post" action="#">
                    <div class="chat-content chat-scrollbar">
                      <?php while($row19=mysql_fetch_array($query19)) { ?>
                      <?php while($row18=mysql_fetch_array($query18)) { ?>
                        <div class="author-chat">
                            <h3><?php echo $row18['sender'];?><span class="chat-date"><?php echo $row18['time'];?></span></h3>
                            <p><?php echo $row18['msg'];?></p>
                        </div>
                        <div class="client-chat">
                            <h3><?php echo $row19['sender'];?><span class="chat-date"><?php echo $row19['time'];?></span></h3>
                            <p><?php echo $row19['msg'];?></p>
                        </div>
<?php } ?>
<?php } ?>
                    </div>
                    <div class="chat-send">
                        <input type="text" name="msg" placeholder="send msg..." />
                        <span><button type="submit" name="submit">Send</button></span>
                    </div>
                  </form>

You may want to check [select * vs select column](https://stackoverflow.com/questions/3180375/select-vs-select-column) and [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Next, what is wrong with a SQL `or` statement? For example: `...where (userid='$userid' and sender='$username') or (sender='Admin' and userid='$userid')` — Tigger, Mar 05 '18 at 10:16
thanks for the reply...but i have a doubt..how to write a single query without using 2 while loops... — rose, Mar 06 '18 at 04:40
table field contain id,user_id,user_email,sender,receiver,msg,date,time. — rose, Mar 06 '18 at 04:42

score 0 · Answer 1 · answered Mar 06 '18 at 10:06

Without really understanding why you have chosen PHP instead of WebSocket and Javascript for this project AND while you have selected this layout, here is an option.

PHP / SQL: Change to a single query and remove SQL injection

function sqlSafe($dbConn,$str) {
    if (! $dbConn) {
         echo 'no DB connection';
         exit;
    }
    return mysqli_real_escape_string($dbConn,$str);
}

function getChat($dbConn,$username) {
    if (! $dbConn) {
         echo 'no DB connection';
         exit;
    }
    // only request the columns needed
    $query = 'SELECT sender,time,msg FROM usertoadmin_chat WHERE
        (userid=\''.sqlSafe($dbConn,$userid).'\' AND 
            sender=\''.sqlSafe($dbConn,$username).'\') OR 
        (sender=\'Admin\' AND 
            userid=\''.sqlSafe($dbConn,$userid).'\')
        ORDER BY id';

     $dbResult = mysqli_query($dbConn,$query);
     // Check for no results.
     if (! $dbResult) {
         return null;
     }
     // collect the db data in to an array
     while($row = mysqli_fetch_assoc($dbResult)) {
          $data[] = $row;
     }
     // free up and return data
     mysqli_free_result($dbResult);
     return $data;
}

HTML: Lets change this around a lot and assume short tag support :]

<?php
    /*
        other stuff above to establish db connection and
        get $username and whatever
    */

    // Get the data for $username
    $data = getChat($con,$username);

    // Get the comments from the Admin and user in to different
    // HTML chunks in a single loop.
    if (! $data) {
        $htmlAdmin = 'no chat yet';
        $htmlUser = '';
    } else {
        foreach($data as $k => $v) {
            if ($v['sender'] == 'Admin') {
                $htmlAdmin .= '<div class="author-chat">
                    <h3>Admin 
                    <span class="chat-date">'.$v['time'].'</span></h3>
                    <p>'.htmlspecialchars($v['msg']).'</p>
                    </div>';
            } else {
                $htmlUser .= '<div class="client-chat">
                    <h3>'.htmlspecialchars($v['sender']).'
                    <span class="chat-date">'.$v['time'].'</span></h3>
                    <p>'.htmlspecialchars($v['msg']).'</p>
                    </div>';
            }
        }
    }
?>
<form method="post" action="#">
<div class="chat-content chat-scrollbar">
    <?=$htmlAdmin.$htmlUser?>
</div>
<div class="chat-send">
    <input type="text" name="msg" placeholder="send msg..." />
    <span><button type="submit" name="submit">Send</button></span>
</div>
</form>

This will create a block that has all the admin chat in a group and all the user chat in another block. This MAY NOT be what you want, but it is what I understood from your request.

If you wanted to thread the text, change to something like so:

    if (! $data) {
        $html = 'no chat yet';
    } else {
        foreach($data as $k => $v) {
            if ($v['sender'] == 'Admin') {
                $html .= '<div class="author-chat">
                    <h3>Admin 
                    <span class="chat-date">'.$v['time'].'</span></h3>
                    <p>'.htmlspecialchars($v['msg']).'</p>
                    </div>';
            } else {
                $html .= '<div class="client-chat">
                    <h3>'.htmlspecialchars($v['sender']).'
                    <span class="chat-date">'.$v['time'].'</span></h3>
                    <p>'.htmlspecialchars($v['msg']).'</p>
                    </div>';
            }
        }
    }

And then just:

    // Change <?=$htmlAdmin.$htmlUser?> to
    <?=html?>

how to use 2 while loops together from fetching same table but different conditions

1 Answers1