nom nom nom nom -4 years ago 44
MySQL Question

update mysql record to add count when button on loop is clicked

I have a form that displays candidates with their details using a mysql while loop and below each candidate is a "vote" button which is also inside the loop. I need to add 1count to a record when their button is clicked. My problem is the buttons inside the loop has the same names so they are all affected with the update even if only one is clicked.

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT ename FROM election_title ORDER BY `sdate` ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$ename= $row['ename'];
?>
<p><font size= "6px" align = "center" color = "#efbf77"> <?php echo $row['ename']. "<br>";?></p>
<?php


$sql = "SELECT * FROM candidate_list T1 INNER JOIN election_title T2 ON T1.ename = T2.ename WHERE T1.ename LIKE '%$row[ename]%';";
$res = $conn->query($sql);
if ($res->num_rows > 0) {
while($rowval = $res->fetch_assoc()) {
$id= $rowval['id'];
$image_content= $rowval['image_content'];
$ename= $rowval['ename'];
$pos= $rowval['pos'];
$fname= $rowval['fname'];
$mname= $rowval['mname'];
$lname= $rowval['lname'];

?>

<div class = "cand">
<?php echo '<img src="data:image/jpeg;base64,' . base64_encode( $rowval['image_content'] ) . '" width = "100%" height = "auto" />';?><?php echo "<p class = 'bold'>" .$rowval['fname']. " " .$rowval['mname']. " " .$rowval['lname'] . "</p>" .$rowval['pos']. "<br/>" .$rowval['pname'];?>
<form action="castvote.php" method="post">
<INPUT TYPE=submit NAME="<?php echo $fname; ?>" VALUE="<?php echo 'Vote ' .$fname; ?>">
</div>
<?php
}
} else {
echo "No candidate(s) listed.";
}

?>
<?php
}
} else {
echo "0 results";
}
?>


And this is my query.

<html>
<head>
<title>NSDCI Voting System</title>
<link rel="stylesheet" href="css/style.css">
</head>
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$db = 'voting_system';

$fname = $_POST['fname'];
$con = mysqli_connect($host, $user, $pass, $db);
if($con)
{

$sql = "UPDATE candidate_list SET votes = votes +1 WHERE fname = $fname";
$query = mysqli_query($con, $sql);

if($query)
echo 'data inserted succesfully';
}

echo 'connected succesfully to the db!';

?>


How can i query a WHERE clause that matches my button name. Thanks in advance

Answer Source

One way would be to assign, to each button, a dataset attribute - such as data-id=$rowval['id'] and use javascript to read that dataset value and either send an ajax request or submit the form with that value. You would not need a form for every candidate - one form should suffice and change the value of a hidden field.

Presumably fname means forename or firstname - if so then that is not a good item to use in your update statement, especially if the candidate's firstname is John for example where there a likely to be many. As each candidate in the db has his/her own ID it would make sense to use the ID for the update because that is guaranteed(?) to be unique.

<html>
    <head>
        <title>vote</title>
        <script>
            document.addEventListener('DOMContentLoaded',function(e){
                var form=document.forms['vote'];
                var bttns=document.querySelectorAll('input.candidate');
                for( var n in bttns )if( bttns[ n ].nodeType==1 )bttns[ n ].addEventListener('click',function(e){
                    form['id'].value=this.dataset.id;
                    form.submit();
                }.bind(bttns[n]),false);
            },false);
        </script>
    </head>
    <body>
        <form id='vote' action="castvote.php" method="post">
            <input type='hidden' name='id' />
        </form>

        <?php

            if ( $conn->connect_error )exit('unable to connect to database');
            /*
                not sure about the query but there should be no need to use nested queries in a loop
                when a join or a selection as below should suffice.
            */
            $sql="select * from `candidate_list` c 
                inner join `election_title` e on c.`ename` = e.`ename` 
                where c.`ename` in ( select distinct `ename` from `election_title` );";


            $res = $conn->query( $sql );
            if( $res->num_rows > 0 ) {

                while( $rs = $res->fetch_object() ){
                    $id=$rs->id;
                    $pos=$rs->pos;
                    $image=$rs->image_content;
                    $ename=$rs->ename;
                    $fname=$rs->fname;
                    $mname=$rs->mname;
                    $lname=$rs->lname;
                    $pname=$rs->pname;

                    echo "
                        <div class='cand'>
                            <img src='data:image/jpeg;base64," . base64_encode( $image ) . "' />
                            <p class='bold'>
                                {$fname}{$mname}{$lname}
                            </p>{$pos}
                            <br/>
                            {$pname}
                            <input type='button' data-id='{$id}' class='candidate' value='Vote for {$fname}' />
                        </div>";
                }
            }

        ?>

    </body>
</html>

Using the above methodology would mean that the PHP code that updates the db needs to be changed to use the ID ( ie: $_POST['id'] )

$sql = "UPDATE candidate_list SET votes = votes+1 WHERE id='{$_POST['id']';";

I realise the code is vulnerable to sql injection - prepared statements is the way forward.

One thing I noticed after posting my answer was the base64_encode( $image ) line - if this is coming from the db, as it is, then I guess that would be the path to the image rather than RAW data? If that is the case the line ought to read base64_encode( file_get_contents( $image ) )

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