frank2k16 frank2k16 - 1 month ago 8
PHP Question

Automatic Count Row and update results to another table

I have two tables
one is

tbl_candidates
and the other one is
tbl_votes


tbl_candidates
contains the information of candidates and their total votes.
sid
is the
userid
of the candidate.

id | partyid | positionid | sid | votecount
-------------------------------------------
1 | 1 | 1 | 444 | 0
2 | 2 | 1 | 555 | 0
3 | 1 | 2 | 666 | 0
4 | 2 | 2 | 777 | 0


tbl_votes
contains vote counts.

id | partyid | positionid | sid | voterid
-------------------------------------------
1 | 1 | 1 | 444 | 1111
2 | 1 | 2 | 666 | 1111
3 | 1 | 1 | 444 | 2222
4 | 1 | 2 | 666 | 2222


So would it possible to count the number of rows for each candidate(sid) in the
tbl_votes
and update the
votecount
on
tbl_candidates
ON PAGE LOAD?

The result would hopefully be like

id | partyid | positionid | sid | votecount
-------------------------------------------
1 | 1 | 1 | 444 | 2
2 | 2 | 1 | 555 | 0
3 | 1 | 2 | 666 | 2
4 | 2 | 2 | 777 | 0


I already tried many variations of queries but they were silly as I failed.

IN ADDITION

I tried to udpate the vote counts in tbl_votes upon registration of votes. Failed so im trying to udpate vote counts on page load. Here is my code.

<div class="content">
<?php
$sql = "SELECT count(id) as id1 FROM tbl_positions";
$result = $DBcon->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$id1=$row["id1"];
}

} else {

}

?>
<?php
$sql = "SELECT count(id) as id2 FROM tbl_votes where userid='$usersid'";
$result = $DBcon->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$id2=$row["id2"];
}

} else {

}
?>

<div class="container-fluid">
<div class="card">
<div class="card-header" data-background-color="purple">
<h4 class="title">Ballot</h4>
<p class="category">Select one each position.</p>
</div>
<div class="card-content">
<form method="post" action="vote.php" enctype="multipart/form-data">



<?php
$sql = "SELECT * FROM tbl_positions";
$result = $DBcon->query($sql);
if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {
$pospos=$row["position"];
$posid=$row["id"];


?>

<div class="col-lg-4 col-md-12">
<div class="card">
<div class="card-header" data-background-color=" <?php
if ($posid % 3 == 0) {
echo "red";
} elseif ($posid % 2 == 0) {
echo "green";
}else{
echo "orange";
} ?>">
<h4 class="title"><?php echo $pospos;?></h4>
<p class="category">New employees on 15th September, 2016</p>
</div>
<div class="card-content table-responsive">
<table class="table table-hover">
<thead class="text-warning">
<th></th>
<th></th>
<th>Photo</th>
<th>Name</th>
<th>Partylist</th>

</thead>
<tbody>


<?php
$sql2 = "SELECT u.*,p.*,d.*,c.*
FROM tbl_users u
JOIN tbl_candidates c on c.sid = u.sid
JOIN tbl_party p on c.partyid = p.id
JOIN tbl_positions d on c.positionid = d.id
WHERE c.status='1' AND d.id='$posid'
";
$result2 = $DBcon->query($sql2);
if ($result2->num_rows > 0) {

while($row2 = $result2->fetch_assoc()) {
$csid=$row2["sid"];
$fn=$row2["firstname"];
$ln=$row2["lastname"];
$cpar=$row2["partyname"];
$cimage=$row2["image"];
$vc1=$row2["votecount"];
$vc2=$vc1+1;

?>

<tr>
<td><input type="number" name="voted" value="<?php echo $vc2; ?>"/></td>
<td><input type="radio" value="<?php echo $csid; ?>" name="<?php echo $posid; ?>" required></td>
<td>
<ul class="enlarge">
<li>
<img style="width:30px;height:30px;border-radius:100%;" src="candidateimages/<?php echo $cimage; ?>">
<span> <!--span contains the popup image-->
<img style="width:200px;height:200px;border-radius:100%;" src="candidateimages/<?php echo $cimage; ?>" /> <!--popup image-->

</span>
</li>
</ul>
</td><td><?php echo $fn; ?> <?php echo $ln; ?></td>
<td><?php echo $cpar; ?></td></tr>
<?php }?>
<?php
if (isset($_POST['submitvote'])){
$please=$_POST[$posid];
$please2=$_POST["voted"];

if ($id1>$id2){


$sql = "INSERT INTO tbl_votes (sid,posid,userid,date,time)
VALUES ('$please','$posid','$usersid','$date','$time')";

if ($DBcon->query($sql) === TRUE) {

$sql = "UPDATE tbl_candidates SET votecount='$please2' WHERE sid='$csid' AND positionid='$posid'";
if ($DBcon->query($sql) === TRUE) {

$sql = "UPDATE tbl_users SET votestatus='1' WHERE sid='$usersid'";
if ($DBcon->query($sql) === TRUE) {
echo'<script>window.location="vote.php?success";</script>';
} else {
}
} else {
}





} else {

}

}


}
?>

<?php

} else {
echo "<td></td><td>No candidates</td>";
}

?>



</tbody>
</table>
</div>
</div>
</div>


<?php
}

} else {
echo "No Positions";
} ?>



<button type="submit" name="submitvote" class="btn btn-info pull-right">Submit</button>




</form>
</div>
</div>
</div>
</div>


Screenshot

The problem with my code is the wrong values are being updated into the tbl_votes... Would it also be possible using this method?

Answer

Use this query,

UPDATE tbl_candidates 
   SET votecount = (
        SELECT COUNT(sid) 
        FROM tbl_votes 
        WHERE tbl_votes.sid = tbl_candidates.sid 
        GROUP BY sid
       );