Knowledge Crafts Knowledge Crafts - 3 years ago 121
MySQL Question

Coloums repeats (total number of ID) time within multiple while loop in php

I create a table in which i create just one and 11 . The Values of first 9 come from one while loop and last 2 values of come from second while loop. Both While Loop are executing in . Now, it give me correct values, but it repeat the as the total number of records e.g I have total 5 records, it gives me 25 records and the first record executes 5 time continuously and rest like that.

My Question is to execute the record once, not to repeat the duplicate records.

This is my code

<table id="myTable" class="table table-bordered table-hover">
<thead>
<tr>

<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Status</th>
<th>Reg on</th>
<th>Upload</th>
<th>Total Upload</th>
<th>Sale</th>
<th>Total Sale</th>
<th>Purchase</th>
<th>Total Purchase</th>

</tr>
</thead>

<tbody>

<?php

include("connection.php");

$query ="SELECT s.*, s.student_id, s.student_email, u.student_email, b.payer_email,

COUNT(u.student_email) AS 'uploadCount',
SUM(u.price) AS 'uploadTotal',

COUNT(b.payer_email) AS 'buyCount',
SUM(b.payment_amount) AS 'buyTotal'

FROM students s

LEFT JOIN academic_work u ON u.student_email = s.student_email
LEFT JOIN orders b ON b.payer_email = s.student_email

GROUP BY s.student_id
ORDER BY s.student_id DESC
";

$run = mysqli_query($con, $query) or die(mysqli_error($con));

?>
<tr>

<?php


while ($row=mysqli_fetch_array($run)) {
$student_id=$row['0'];
$student_first_name=$row[1];
$student_last_name=$row[2];
$student_email=$row[3];
$student_password=$row[4];
$student_status=$row[5];
$student_time=$row[6];
$uploadCount = $row['uploadCount'];
$buyCount = $row['buyCount'];
$uploadTotal = $row['uploadTotal'];
$buyTotal = $row['buyTotal'];

$query1 ="SELECT ss.*, ss.student_id, ss.student_email, p.email,

COUNT(p.email) AS 'purchaseCount',
SUM(p.payment_amount) AS 'purchaseTotal'

FROM students ss

LEFT JOIN orders p ON p.email = ss.student_email

GROUP BY ss.student_id
";

$run1 = mysqli_query($con, $query1) or die(mysqli_error($con));

while ($row1=mysqli_fetch_array($run1)) {
$purchaseCount = $row1['purchaseCount'];
$purchaseTotal = $row1['purchaseTotal'];

?>

<td><?php echo $student_first_name; ?></td>
<td><?php echo $student_last_name; ?></td>
<td><a href="academic_work_add.php?add_academic_work=<?php echo $student_id; ?>" style="text-decoration:none;"><?php echo $student_email; ?></a></td>
<td><?php echo $student_status; ?></td>
<td><?php echo $student_time; ?></td>
<td><?php if($uploadCount == 0) { echo 0; } else { echo $uploadCount; }?></td>
<td><?php if($uploadTotal == 0) { echo 0; } else { echo $uploadTotal; }?></td>
<td><?php if($buyCount == 0) { echo 0; } else { echo $buyCount; }?></td>
<td><?php if($buyTotal == 0) { echo 0; } else { echo $buyTotal; }?></td>

<td><?php if($purchaseCount == 0) { echo 0; } else { echo $purchaseCount; }?></td>
<td><?php if($purchaseTotal == 0) { echo 0; } else { echo $purchaseTotal; }?></td>

</tr>
<?php } } ?>
</tbody>
</table>


Thanks in Advance !!!

Answer Source

Your $query1 loops through all rows of the table on each iteration. Use a where clause and then it will return the 1 row you want. Use the id of $row to target the correct row. You should parameterize this so you aren't open to a SQL injection (https://security.stackexchange.com/questions/146595/second-order-sql-injection-protection).

This also could probably be done in 1 query, looping a query is usually an incorrect implementation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download