SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    2
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    If row exists update, if not insert new row

    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.

    Code:
    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\n", $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.
    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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/...duplicate.html

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

  3. #3
    SitePoint Member
    Join Date
    Jun 2014
    Posts
    2
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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/...duplicate.html

    No need anymore for three queries (select, update, insert)
    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. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    @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).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •