Need to update only one tuple, but this is updating the whole column in table

Question

I check if the the url is present first and see if he is up voting first time, if so then i will add the users repute to the upvoted column, but i want to add only to that url not to all urls, this code is adding to all all tuples in "upvotes" column, i want it to add only to a particular tuple.

<!Doctype html>
<html>
<?php
$c=$_GET['a'];// users name
$d=$_GET['b'];// usesrs id
$e=$_GET['c'];//  users repute
$ur=$_POST['url'];

// Create connection
$con=mysqli_connect("localhost","root","","repute system");
    if(mysqli_connect_errno()){
        echo "ERROR ".mysqli_connect_error();
} 

$sql = mysqli_query($con,"SELECT * FROM sites");
if (mysqli_num_rows($sql) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($sql)) 
    {

        if($ur == $row['URL'] && $d != $row['id'])
        {
        $ne = $row['upvotes'] + $e;   
        $sol = mysqli_query($con, "UPDATE sites SET upvotes = $ne ");
        $bew = mysqli_query($con,"INSERT INTO v_sites(teacher_id,URL,vote) VALUES ('$d','$ur','$e')");
        echo "Upvoted the site   ";
        echo $ur;       
       }
    }
} else {
    echo "Sorry before upvoting you have to block it first or you are trying to upvote your own report, in which you cant";
}
?>
</html>

Which particular tuple is it supposed to update? How is it supposed to know which it is? — Barmar, Apr 28 '15 at 18:09
`UPDATE sites SET upvotes = $ne` yeah, that'd do it. As stated.... where's the where? Read up on update https://dev.mysql.com/doc/refman/5.0/en/update.html — Funk Forty Niner, Apr 28 '15 at 18:12
The tuple which has same url name as the one i will input in the html page(i am getting that by _post as u can see), so only if both match i want to update that row of url with added repute score to its upvotes @Barmar — razor, Apr 28 '15 at 18:13
My guess; `UPDATE sites SET upvotes = $ne WHERE id='$d'` if that works, let me know. I'll post an answer along with a few other tidbits. @razor — Funk Forty Niner, Apr 28 '15 at 18:16
My table's name in sites it has 5 columns, which are URL,status,upvotes,downvotes, id and all are varchar with 30 length — razor, Apr 28 '15 at 18:17

score 1 · Answer 1 · answered Apr 28 '15 at 18:23

You need a WHERE clause that matches the URL:

$stmt = mysqli_prepare($con, "UPDATE sites 
                              SET upvotes = upvotes + 1
                              WHERE url = ? AND id = ?";
mysqli_stmt_bind_param($stmt, "ss", $ur, $d);
mysqli_stmt_execute($stmt);

You don't need the SELECT or while loop, since MySQL can find the matching rows and update them all by itself.

You also shouldn't have the INSERT query inside the loop, since it's inserting the same row each time.

And you should switch to prepared statements, as shown above, instead of inserting strings into your queries, since your code is subject to SQL injection.

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

All your rows are being updated because you are not using a where clause.

UPDATE sites SET upvotes = $ne

should be changed to:

UPDATE sites SET upvotes = $ne WHERE id='$d'

However, if $ne is also a string, $ne should also be quoted:

UPDATE sites SET upvotes = '$ne' WHERE id='$d'

Read up on UPDATE:

https://dev.mysql.com/doc/refman/5.0/en/update.html

"My table's name in sites it has 5 columns, which are URL,status,upvotes,downvotes, id and all are varchar with 30 length"

This tells me that id is VARCHAR also; not a good idea but that's up to you. It's best to use int for queries like this should all your id's be numerically-based.

Which is why using quotes WHERE id='$d' around the $d variable will be required.

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 28 '15 at 18:24

Funk Forty Niner

74,450
15
68
141

the line `UPDATE sites SET upvotes = $ne WHERE id='$d'` updates the upvotes for the id of the user trying to upvote the block, thats not what i intended, i want the update for upvotes which has same url in db and same url input – razor Apr 28 '15 at 18:28
@razor `WHERE id='$d' AND URL='$ur'` or `WHERE URL='$ur'` – Funk Forty Niner Apr 28 '15 at 18:31
@razor or `WHERE URL='$ur' AND id='$d'` - TBH, am having a bit of trouble wrapping my head around it. But try a few combinations as per the variables you're using. – Funk Forty Niner Apr 28 '15 at 18:35
@razor where are we at; still giving you trouble or has this been solved? – Funk Forty Niner Apr 28 '15 at 18:44
@razor Errr. ok. So you chose the other answer. Whatever. – Funk Forty Niner Apr 28 '15 at 18:55
Haha , you were more helpful – razor Apr 28 '15 at 18:56

Need to update only one tuple, but this is updating the whole column in table

2 Answers2