If row exists update, if not insert new row

PHP
1

I’m working on a survey script and have everything working with one exception. The logic is to store the question_id’s, answer’s and username in a database. Then compare one user’s answer’s with another user. Then showing % same. Where I’m stuck is I’d like to allow a user to retake the quiz , if desired. Upon doing so then the old answers will be updated. What I have below just creates more rows instead of updating. I’ve been working on this a few days and did alot of searching, and by the way where I live is dial-up only, so limited searching capabilities.


ini_set('display_errors',1);
error_reporting(E_ALL);
function ExtendedAddslash(&$params)
{
    foreach ($params as &$var) {
      is_array($var) ? ExtendedAddslash($var) : $var=addslashes($var);
         unset($var);
        }
}
    ExtendedAddslash($_POST);

// Retrieve variables
$username = $_POST['username'];
$member_id = $_POST['member_id'];
$survey = $_POST['survey'];
$array = $_POST['question'];
foreach ($array as $key=>$val ) {

// make connection
$mysqli = new mysqli("localhost", "root", "", "test");
if ($mysqli->connect_errno) {
    printf("Connection failed: %s\
", $mysqli->connect_error);
  exit();
}
// Check if entry exists
$result = $mysqli->query("SELECT  *  FROM `survey_members` WHERE `username` = ".$username." ");
$row_cnt = $result->num_rows;       // <--Line 28
if ($row_cnt > 0) {

// If entry exists update with new answer
$mysqli->query("UPDATE `survey_members` SET `answer` =  '".$val."' WHERE `username` = ".$username."  AND `question_id` = ".$key."  ");

  echo 'Updated';
}
else {

// If no entries exist insert new rows
$mysqli->query("INSERT INTO `survey_members`(`username`, `member_id`, `question_id`, `answer`) VALUES ('".$username."', '".$member_id."', '".$key."', '".$val."')
  ");
  echo 'Inserted';

  }
}

I’m also getting a Notice at line 28.


Notice: Trying to get property of non-object in C:\\EasyPHP5.3.0\\www\\submit1.php on line 28

Which is no doubt where my problem is, even though it’s just a notice. Any ideas?
By the way, I’m aware there are most likely security issues with the coding but at the moment it’s on localhost.

2

You forgot to put quotes around the username value in the select.

By the way, if username is unique in that table, you could put a unique index on it (if you haven’t already) and use this:
http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

No need anymore for three queries (select, update, insert) :slight_smile:

3

Thanks Guido for your help. But I had allready spent several hours trying to do the Update on Duplicate. The problem with that is since username has duplicate entries I can’t make that unique.
Today I figure’d out a “dirty” way of doing this that actually works. I created a functions.php file and made two functions, insert() and update(). I call the update from the above script if num_rows returns results. If not, I call the insert(). No warnings or notices. Allthough since the num_rows changes after insert it then updates with the same answers.

4

@JP714; be aware that your script is wide-open to a potential SQL Injection attack. You should use prepared statements. The golden rule is to never let any user submitted data anywhere near the database without sanitizing it and escaping it (or more preferably using prepared statements).