Rating correct record PHP SQL

Question

I have no idea how to make "plus / minus" rating to the correct record, I tried to do this in while loop, which shows all the records, but it's rating only the first record. How to refer to correct record? I'm newbie in PHP. Here's my code:

if (isset($_GET['najstarsze']))
{   
    $sql = "SELECT * FROM wpisy ORDER BY id";       
}
else
{
    $sql = "SELECT * FROM wpisy ORDER BY id DESC";
}

$stmt = $db->query($sql);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);

if($stmt->rowCount() > 0){
    while($row = $stmt->fetch()){
        echo "
        <div class='data'>
        ".$row['data']."
        </div><br>
        <div class='daneautora'>
        <b>Ocena: </b>".$row['ocena']."<br>
        <b>Nr: </b>".$row['id']."<br>
        <b>Nick: </b>".$row['nick']."<br>
        <b>E-mail: </b>".$row['email']."<br>
        <b>Wpis: </b><br></div>
        <div class='suchar'>
        <p>
        ".$row['tresc']."
        </p>
        </div>  
        <div class='ocena'>
        <p><a href='index.php?plus=true'>+</a> &nbsp; <a href='index.php?minus=true'>-</a></p>
        </div>
        <hr>                        
        ";
        if (isset($_GET['plus']))
        {
            $sql = "UPDATE wpisy SET ocena = ocena + 1 WHERE id = ".$row['id']."";  
            $stmt = $db->query($sql);
            $stmt->execute();
        }
        else
        {
            if (isset($_GET['minus']))
            {
                $sql = "UPDATE wpisy SET ocena = ocena - 1 WHERE id = ".$row['id']."";  
                $stmt = $db->query($sql);
                $stmt->execute();
            }
        }
    }       
}

Show source
| pdo   | php   | mysql   2017-01-07 19:01 2 Answers

Answers to Rating correct record PHP SQL ( 2 )

  1. 2017-01-07 19:01
    1. You need to move your block where you're setting rating (if (isset($_GET['plus']))...) outside while block
    2. Use parameter binding instead of passing variable right in SQL query string to avoid SQL injection.

    There is nice PDO tutorial

  2. 2017-01-07 20:01

    You need to update the link so it has a reference to the record you want to update. Try:

    index.php?plus=true&id=' . $row['id']
    

    You also probably want to update the isset to include this new parameter as well.

    if (isset($_GET['plus'], $_GET['id']))
    

    Then you need to use prepared statements with parameterized queries so you aren't susceptible to SQL injections. Here's an example:

    $sql = "UPDATE wpisy SET ocena = ocena + 1 WHERE id = ?";  
    $stmt = $db->prepare($sql);
    $stmt->execute(array($_GET['id']));
    

    Also when using query() you don't need execute(), that executes as well. The execute is to execute a prepared statement.

    PDOStatement::execute — Executes a prepared statement

    -http://php.net/manual/en/pdostatement.execute.php

Leave a reply to - Rating correct record PHP SQL

◀ Go back