PHP execute does not return any errors but no rows added in database

Question

I have the following PHP script that should be executed when the register button is clicked and if the address checkbox is checked.

if(isset($_POST['register'])){
   if($_POST['addressCheckBox']  == 'Yes'){
      if(isset($_POST['streetNumber']) && isset($_POST['route']) && isset($_POST['sublocality_level_1'])){
         $unitDetails = trim($_POST['unitDetails']);
         $streetNumber = mysqli_real_escape_string($db, trim($_POST['streetNumber']));
         $route = mysqli_real_escape_string($db, trim($_POST['route']));
         $suburb = mysqli_real_escape_string($db, trim($_POST['sublocality_level_1']));
         $city = mysqli_real_escape_string($db, trim($_POST['city']));
         $zipCode = mysqli_real_escape_string($db, trim($_POST['zipCode']));
         $province = mysqli_real_escape_string($db, trim($_POST['province']));
         $country = mysqli_real_escape_string($db, trim($_POST['country']));
         $email = mysqli_real_escape_string($db, trim($_POST['email']));

         if(!empty($streetNumber)  && !empty($route) && !empty($city) && !empty($province) && !empty($zipCode) && !empty($country)){
            $queryInsertMemberAddress = $db->prepare("INSERT INTO address (memberEmail, unitDetails, houseNumber, streetName, suburb, city, postalCode, province, country) VALUES (?,?,?,?,?,?,?,?,?)");

            $queryInsertMemberAddress->bind_param('ssisssiss', $email, $unitDetails, $streetNumber, $route, $suburb, $city, $zipCode, $province, $country);

            $queryInsertMemberAddress->execute() or die($queryInsertMemberAddress->error);
         }
      }
   }
}

When the script runs I don't get any errors but no rows are INSERTED in the database. I tried to find errors by doing the following:

execute a var_dump on the $queryInsertMemberAddress after ->execute() and it returns

object(mysqli_stmt)[19]
   public 'affected_rows' => null
   public 'insert_id' => null
   public 'num_rows' => null
   public 'param_count' => null
   public 'field_count' => null
   public 'errno' => null
   public 'error' => null
   public 'error_list' => null
   public 'sqlstate' => null
   public 'id' => null

execute a print_r on the $queryInsertMemberAddress variable after ->execute() and it returns

mysqli_stmt Object ( [affected_rows] => 1 [insert_id] => 28 [num_rows] => 0 [param_count] => 9 [field_count] => 0 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 4

I can't understand why the print_r shows that one row was affected but no records show in the database. Does anyone see where I went wrong?

@ÁlvaroGonzález Yes it does. I've tested it by using echo to make sure that It actually enters all four conditional statements — Gloire, Feb 21 '17 at 10:56
you don't need all those `mysqli_real_escape_string`s with parameterized queries — Martin, Feb 21 '17 at 11:01
@NirjharLo the db connection works. I have another script that isn't related to this one in the same file that runs perfectly. — Gloire, Feb 21 '17 at 11:01
_“[insert_id] => 28”_ – is there a record with that id in the database? If not - are you looking into the _correct_ database? — CBroe, Feb 21 '17 at 11:02
@Martin I thought so as well but I'm new to parameterized queries so I wasn't really sure if I had to use them or not. I'll remove them afterwards. — Gloire, Feb 21 '17 at 11:03
@Martin I forgot to add the $email variable when I was typing on stackoverflow. I've just added it. But it is present in my current script — Gloire, Feb 21 '17 at 11:12
@CBroe There are records with insert_id => 28. For now I only have 20, 22, 25 which I added manually as part of my testing. — Gloire, Feb 21 '17 at 11:14

score 0 · Answer 1 · edited May 23 '17 at 12:00

Broad Concerns

Every time you call a method of the database class, you should wrap it in an if statement to check if it executed correctly, and to then feedback any errors found.

MySQL error reporting setup

 if(!$queryInsertMemberAddress = $db->prepare("INSERT INTO address (memberEmail, ... , country) VALUES (?,...,?)")){
     die("failed prepare: ".$db->error);
 }
 if(!$queryInsertMemberAddress->bind_param('ssisssiss', $email, ..., $country)){
     die("failed binding: ".$queryInsertMemberAddress->error);
 }
 if(!$queryInsertMemberAddress->execute()){
       die("failed execution: ".$queryInsertMemberAddress->error);
 }

Remove all your mysqli_real_escape_string calls, they're redundant.
Is your MySQL user allowed to insert data into that table? Does your user specified in your PHP connection have permission to INSERT?

I may be completely wrong here, but I think what may be going on is your bind_param statement is sabotaging your SQL.

Bind Param requires the given elements to be of the type specified in the first string, you have ssisssiss which is all strings with a few integers mixed in. That's fine, but your data is all derived from $_POST elements which are always strings.

So if you var_dump the data you are inserting then you will var_dump a lot of (string) type values. And no (int) type values.

Example

 $_POST['value'] = "1"; //this is a string
 $value = mysqli_real_escape_string($db,trim($_POST['value'])); //optional, from your code...
 var_dump($value); /// (string)"1";

But you can't turn Bind Param into all strings (sssssssss) because this will then mismatch with the data types of the MySQL columns you're trying to insert into.

What I believe is happening is that due to these type mismatches the bind_param is missing values and/or aborting the whole SQL and for some reason not giving you apporpriate error feedback.

How to check

if(!$queryInsertMemberAddress->bind_param('ssisssiss', $email, $unitDetails, ...) ){
    die("bind failed"); //bind_param returns true or false 
}

Solution Suggestion:

$streetNumber = (int)$streetNumber;
$zipCode = (int)$zipCode;

Please let me know how this goes and what results this presents to you.

PHP execute does not return any errors but no rows added in database

1 Answers1

Broad Concerns

Solution Suggestion: