Question

2

1

For some reason when I changed my php code from mysql to mysqli everything got messed up.

For example, when a user enters a rating my mysql table updates twice by entering one new row and updating the correct row. I was wondering how do I correct this problem so it only updates the new row and checks to see if there is no row it enters one?

PHP code

// function to insert rating
function rate(){
    $dbc = mysqli_connect ("localhost", "root", "", "sitename");
    $text = strip_tags($_GET['rating']);
    $update = "update vote set counter = counter + 1, value = value + ".$_GET['rating']."";

    $result = mysqli_query($dbc,$update); 
    if(mysqli_affected_rows() == 0){
        $insert = "insert into vote (counter,value) values ('1','".$_GET['rating']."')";
        $result = mysqli_query($dbc,$insert); 
    }
}

old php code

// function to insert rating
function rate(){
    $text = strip_tags($_GET['rating']);
    $update = "update vote set counter = counter + 1, value = value + ".$_GET['rating']."";

    $result = mysql_query($update); 
    if(mysql_affected_rows() == 0){
        $insert = "insert into vote (counter,value) values ('1','".$_GET['rating']."')";
        $result = mysql_query($insert); 
    }
}

edited 2 days ago

asked 2 days ago

php
23●3

1

Aren't you missing a where clause on your update? – klausbyskov 2 days ago

the code worked before until I changed mysql to mysqli. – php 2 days ago

1

You may be better off using INSERT ... ON DUPLICATE KEY UPDATE (see dev.mysql.com/doc/refman/…), provided you've got a column with a unique index on it (which ought to be in your missing WHERE clause, as in klausbyskov's comment - what you've got now will increment every row's counter value, and set the value value to whatever's in $_GET['rating']). – Dominic Rodger 2 days ago

1ch1g0 · Answer 1 · 2010-01-15 13:25:14Z

0

how about trying this

if (mysqli_affected_rows($dbc) ==0){

answered 2 days ago

1ch1g0
293●5

This should be done automatically by the function (so says the manual). Source: php.net/manual/en/… – AntonioCS 2 days ago

cballou · Answer 2 · 2010-01-15 13:30:37Z

You should instead combine the two statements. This would require you to have a PRIMARY KEY, perhaps named id.

// function to insert rating
function rate() {
    $dbc = mysqli_connect ("localhost", "root", "", "sitename");
    $text = strip_tags($_GET['rating']);
    $update = sprintf('INSERT INTO vote SET
                       id = 1,
                       counter = 1,
                       value = %1$d
                       ON DUPLICATE KEY UPDATE
                       counter = counter + 1,
                       value = value + %1$d',
                       $text);
    mysqli_query($dbc,$update); 
}

PHP & Mysql database updating problem

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql or ask your own question.

Hello World!

Related