No index used in query/prepared statement when updating columns

Question

I have this error:

No index used in query/prepared statement UPDATE members SET fname = ?, mname = ?, lname = ? WHERE username = ?

Bear in mind that this is script for logged in users only, so the session will always be started.

This script is to change/update current Name (1st name, middle name and last name). Columns in the database are fname, mname and lname, all set to varchar(36) (because it's very unlikely that user's first, middle or last name is longer than 36 letters)

Here is my form :

<form method="POST" action="">

<input type="text" name="fname" class="input" placeholder="First Name"><br>
<input type="text" name="mname" class="input" placeholder="Middle Name"><br>
<input type="text" name="lname" class="input" placeholder="Last Name"><p>
<input type="submit" name="sbmtnm" class="buttondiv" value="Submit"><p>
</form>

Here is my php code for updating name :

    <?php 
mysqli_report(MYSQLI_REPORT_ALL);

    if(isset($_POST['sbmtnm'])){
        if(empty($_POST['fname']) && ($_POST['lname'])) {
            echo "<font color='#DB4D4D'>First Name or Last name field is blank!</font>";
        }
        else { 

            $_POST['fname'] = $fname;
            $_POST['mname'] = $mname;
            $_POST['lname'] = $lname;
            $_SESSION['username'] = $username;

            $sqlnm = "UPDATE members SET fname = ?, mname = ?, lname = ? WHERE username = ?";

            // I also tried this :
            $sqlnm = "UPDATE members SET (fname, mname, lname) VALUES (?, ?, ?) WHERE username = ?";
            //↑ This code shows another error message : You have an error in your SQL syntax; check the             
            //manual that corresponds to your MySQL server version for the right syntax to use near '(fname, 
            //mname, lname) VALUES (?, ?, ?) WHERE username = ?


            $stmt = $mysqli->prepare($sqlnm);
            if(false===$stmt){
                die('prepare() failed:' . $mysqli->errno. htmlspecialchars($mysqli->error));
            }
            $check = $stmt->bind_param('ssss', $fname, $mname, $lname, $username);
            if(false===$check){
                die('bind_param() failed:' . htmlspecialchars($stmt->error));
            }
            $ok = $stmt->execute();
            if(false===$ok){
                die('execute() failed:' . htmlspecialchars($stmt->error));
            }
            $stmt->close();

            if($ok == true){
                echo "<p><font color='#00CC00'>Your name has been successfuly submitted.</font>";
            }
        }
    }


?>

I really don't know where is problem...

See also [this question and comment threads](http://stackoverflow.com/questions/25526307/mysqli-fatal-error-no-index-used-in-query-prepared-statement) and also [this question](http://stackoverflow.com/questions/5580039/fatal-error-uncaught-exception-mysqli-sql-exception-with-message-no-index-us). You may create an index on the column `username`, so the `WHERE` clause makes use of an index. — Michael Berkowski, Apr 04 '15 at 20:53
I already seen this questions but they are not telling me any solution :|ň — Steven Tomko, Apr 05 '15 at 09:42
They do tell you the solution to disable `MYSQLI_REPORT_ALL`. That's the answer you accepted. — Michael Berkowski, Apr 05 '15 at 12:04
@Michael Berkowski - I didn't know that this is the error so I didn't know that it's the solution — Steven Tomko, Apr 05 '15 at 14:09

MorganF · Accepted Answer · 2015-04-04T21:22:15.157

0

Try commenting out mysqli_report(MYSQLI_REPORT_ALL); as there are instances of it reporting too many errors, and it may be causing your code to not execute fully.

edited Apr 04 '15 at 21:22

answered Apr 04 '15 at 21:16

MorganF

73
6

How to comment it out? – Steven Tomko Apr 05 '15 at 09:47
Simply use `//mysqli_report(MYSQLI_REPORT_ALL);` – MorganF Apr 05 '15 at 09:48
did you notice the very first line in my code? – Steven Tomko Apr 05 '15 at 09:50
Using two fowardslashes tells php to ignore the line – MorganF Apr 05 '15 at 09:52
Now it tell me that stmt->execute() is TRUE , but it does not write the information in the database... – Steven Tomko Apr 05 '15 at 10:06
@MoragnF - Ah I got it... I left middle name blank so all names stays blank in db but.. why? – Steven Tomko Apr 05 '15 at 10:14
Suddenly everything is working now :o Thanks BTW! – Steven Tomko Apr 05 '15 at 10:16

No index used in query/prepared statement when updating columns

1 Answers1