Why doesn't the count match the value for the SQL query?

Question

I cannot really explain my problem that well, but here is the code that should:

<?php
   ...
   class Keys {
            public $ids = array(1, 2, 3, 4, 5, 6);
            public $fnames = array("Joan", "Max", "Lori", "William", "Emily", "James");
            public $lnames = array("Williams", "Helder", "Doe", "Must", "Deen", "Harthwell");
            public $ages = array(32, 15, 19, 25, 17, 8);
   }
   $keys = new Keys; // instiantate an object as an instance of Keys

   $sqlQueries = array(); // empty array to make SQL queries

   for($i = 0; $i < 6; $i++) {
        foreach($keys as $field => $value) {
            for($j = 0; $j < count($value); $j++) {
                array_push($sqlQueries, "INSERT INTO students VALUES($value[$j])");
            }
        }
   } // adds queries to array

   for($i = 0; $i < 6; $i++) {
        if(mysqli_query($connection, $sqlQueries[$i])) {
            echo "Data added successfully!";
        } else {
            die("Couldn't add data to table: " . mysqli_error($connection));
        }
    } // validates queries
    ...
?>

I have created a class called Keys with the values I wanted for the SQL queries. I define an object to be an instance of this new class. Then, I define an empty array of the queries I am going to implement. Inside the first loop, I attempt to create the queries with the values from my object. Every completed query is "pushed" to the $sqlQueries array. In the second loop, I validate the queries. But, I get the following error at my output:

Couldn't add data to table: Column count doesn't match value count at row 1

I know this means that the amount of data is not even with the table. But I cannot figure out why I have this problem. I have looked at a lot of sources, but none of them helped.

Does anyone have an idea?

EDIT:

I attempted the methods of all 3 answers, and out of all of them, @lps' answer is currently the closest to my solution. Now, my error is:

Couldn't add data to table: Unknown column 'age' in 'field list'

EDIT 2:

A new answer came in, realizing my error in my code, and that's done. But, now I have an error saying this:

Couldn't add data to table: Unknown column 'Joan' in 'field list'

EDIT 3:

A comment under @lps' answer came in, which removed the error about the unknown 'Joan' column. Now, I'm getting an error saying:

Couldn't add data to table: Duplicate entry '1' for key 'PRIMARY'

FINAL UPDATE:

My problem is solved! Here is my final code:

<?php
   ...
   class Keys {
            public $fnames = array("Joan", "Max", "Lori", "William", "Emily", "James");
            public $lnames = array("Williams", "Helder", "Doe", "Must", "Deen", "Harthwell");
            public $ages = array(32, 15, 19, 25, 17, 8);
   }
   $keys = new Keys; // instiantate an object as an instance of Keys

   $sqlQueries = array(); // empty array to make SQL queries

   for($i = 0; $i < 6; $i++) {
        $query = "INSERT INTO students (fname, lname, avg_grade) VALUES "; // base query
        $values = array(); // empty array of values
        foreach($keys as $field => $value) {
            array_push($values, $value[$i]); // pushes each value to $values array
        }
        $query .= "(" . implode(',', $values) . ")"; // adds each value of array to the query
        array_push($sqlQueries, $query); // pushes complete query to $sqlQueries array
   } // adds queries to the $sqlQueries array

   var_dump($sqlQueries); // Just wanted to see my queries

   $query = "SELECT * FROM students";
   $result = mysqli_query($connection, $query);
   if(mysqli_num_rows($result) > 0) {
        mysqli_query($connection, "DELETE FROM students");
   } // cleans out the table if there are any duplicate queries

   for($i = 0; $i < 6; $i++) {
        if(mysqli_query($connection, $sqlQueries[$i])) {
            $recordID = mysqli_insert_id($connection);
            echo "Data added successfully! The last inserted ID was " . $recordID; // outputs success message and gets last inserted ID
        } else {
            die("Couldn't add data to table: " . mysqli_error($connection)); // Outputs an error if there was a failure attempting to implement data
        }
    } // validates queries
    ...
?>

Answer 1

The resulting SQL queries are not formatted correctly. The fields and values counts in the insert query must match. If you don't specify any fields, then the values must match the structure of the table.

Right now your code is generating queries that look like this:

array(144) {
  [0]=>
  string(30) "INSERT INTO students VALUES(1)"
  [1]=>
  string(30) "INSERT INTO students VALUES(2)"
  [2]=>
  string(30) "INSERT INTO students VALUES(3)"
  [3]=>
  string(30) "INSERT INTO students VALUES(4)"
  [4]=>
  string(30) "INSERT INTO students VALUES(5)"
  [5]=>
  string(30) "INSERT INTO students VALUES(6)"
  [6]=>
  string(33) "INSERT INTO students VALUES(Joan)"
  [7]=>
  string(32) "INSERT INTO students VALUES(Max)"
  [8]=>
  string(33) "INSERT INTO students VALUES(Lori)"
  [9]=>
  string(36) "INSERT INTO students VALUES(William)"
  [10]=>
  string(34) "INSERT INTO students VALUES(Emily)"
  [11]=>
  string(34) "INSERT INTO students VALUES(James)"
  [12]=>
  string(37) "INSERT INTO students VALUES(Williams)"
  [13]=>
  string(35) "INSERT INTO students VALUES(Helder)"
  [14]=>
  string(32) "INSERT INTO students VALUES(Doe)"
  [15]=>
  string(33) "INSERT INTO students VALUES(Must)"
  [16]=>
  string(33) "INSERT INTO students VALUES(Deen)"
  [17]=>
  string(38) "INSERT INTO students VALUES(Harthwell)"
  ...

So, change

   for($i = 0; $i < 6; $i++) {
        foreach($keys as $field => $value) {
            for($j = 0; $j < count($value); $j++) {
                array_push($sqlQueries, "INSERT INTO students VALUES($value[$j])");
            }
        }
   } // adds queries to array

To:

   $string_fields = ['fnames', 'lnames'];
   for($i = 0; $i < 6; $i++) {
       $query = "INSERT INTO students (id, fname, lname, age) VALUES ";
       $values = array();
        foreach($keys as $field => $value) {
            $value = $value[$i];

            if(in_array($field, $string_fields)) {
                $value = "'" . $value . "'";
            }

            array_push($values, $value);
        }
       $query .= "(" . implode(',', $values) . ")";
       array_push($sqlQueries, $query);
   } // adds queries to array

This will produce the following queries:

array(6) {
  [0]=>
  string(76) "INSERT INTO students (id, fname, lname, age) VALUES (1,'Joan','Williams',32)"
  [1]=>
  string(73) "INSERT INTO students (id, fname, lname, age) VALUES (2,'Max','Helder',15)"
  [2]=>
  string(71) "INSERT INTO students (id, fname, lname, age) VALUES (3,'Lori','Doe',19)"
  [3]=>
  string(75) "INSERT INTO students (id, fname, lname, age) VALUES (4,'William','Must',25)"
  [4]=>
  string(73) "INSERT INTO students (id, fname, lname, age) VALUES (5,'Emily','Deen',17)"
  [5]=>
  string(77) "INSERT INTO students (id, fname, lname, age) VALUES (6,'James','Harthwell',8)"
}

Change the code so it matches whatever the actual students table schema.

Answer 2

Use the method explained by LPS. The message you are getting now means that need to create a field named age in your database structure. Check your data base, I am pretty sure the field age do not exist.

EDIT:

I am glad to see my previous answer solved your error, the new error you are getting now (Couldn't add data to table: Duplicate entry '1' for key 'PRIMARY') means that the field id in your database is a primary field and this type of fields do not allow repeated (duplicated) values. So, I guess the code ran well once and inserted the values in your DB and now it can't do it again because it find the value 1 already exist in the field id and the error handler in your code stop the script.

To test if I am right, try this:

Go to your database, the records should be already inserted, delete them and run the script, it should run with no problems and if you check your DB the records should be there again. Let the records there and try to run the script, if you receive the same error again this means I am right.

I can think in two simple options to solve the issue (depending on the final behavior you want):

1) To insert a new record every time (probably the best option):

Make sure the field id has the attribute autoincrement and don't try to insert and ID, let MySQL to create the ID for you. Thats all.

This way the first time you run the script Joan is going to have the id number 1 and the 2nd time there is going to be 2 records with the name Joan: the ID number 1 (created the first time you ran the script) and ID number 7 (created with this number because it was created after James who was number 6).

The change would look like this in your code:

 class Keys {
        //  public $ids = array(1, 2, 3, 4, 5, 6);
            public $fnames = array("Joan", "Max", "Lori", "William", "Emily", "James");
            public $lnames = array("Williams", "Helder", "Doe", "Must", "Deen", "Harthwell");
            public $ages = array(32, 15, 19, 25, 17, 8);
   }
   $keys = new Keys;

• Note that I just commented the ID part but you could also delete it.

2) If you want to manage the ID manually (Not recomended)

Let your database structure the way it is and just ignore the error message if it is related to a duplicated entry.

In order to do so look for this block:

   for($i = 0; $i < 6; $i++) {
        if(mysqli_query($connection, $sqlQueries[$i])) {
            echo "Data added successfully!";
        } else {
            die("Couldn't add data to table: " . mysqli_error($connection));
        }
    } // validates queries

And replace it for:

   for($i = 0; $i < 6; $i++) {
        if(mysqli_query($connection, $sqlQueries[$i])) {
            echo "Data added successfully!";
        } else {
            $mysqliError = mysqli_error($connection);
            if(strpos($mysqliError, 'Couldn\'t add data to table: Duplicate entry \'') !== false && strpos($mysqliError, '\' for key \'PRIMARY\'') !== false){
                die("Couldn't add data to table: " . $mysqliError);
            }
        }
    } // validates queries

Answer 3

    <?php
class Keys {
            public $ids = array(1, 2, 3, 4, 5, 6);
            public $fnames = array("Joan", "Max", "Lori", "William", "Emily", "James");
            public $lnames = array("Williams", "Helder", "Doe", "Must", "Deen", "Harthwell");
            public $ages = array(32, 15, 19, 25, 17, 8);
   }
   $keys = new Keys;
   $str='(';
    for ($i=0; $i <6 ; $i++) { 
        foreach ($keys as $key => $value) {
                $str.=$value[$i].',';
        }
        $str=rtrim($str,',');
        $str.='),(';
    }
    $str=rtrim($str,',(');
    $query="INSERT INTO students (id, fname, lname, age) VALUES ".$str;
    var_dump($query);
?>

you can run this query in it will insert into database.

Answer 4

You should use an ORM or ActiveRecord style data access layer. You're walking down a well trodden path without realizing it.

Answer 5

Your queries as you have it would be as follows:

string(30) "INSERT INTO students VALUES(1)"
[1] => string(30) "INSERT INTO students VALUES(2)"
[2] => string(30) "INSERT INTO students VALUES(3)"
[3] => string(30) "INSERT INTO students VALUES(4)"
[4] => string(30) "INSERT INTO students VALUES(5)"
[5] => string(30) "INSERT INTO students VALUES(6)"
[6] => string(33) "INSERT INTO students VALUES(Joan)"

There are several issues here: You're not specifying which field it needs to go into, you're only passing 1 field at a time, and you're not quoting the strings. A better way to go about it would be this:

$queries = [];
foreach($keys->ids as $k => $v) {
    $queries[] = "INSERT INTO students (id,first_name,last_name,age) VALUES ({$keys->ids[$k]},'{$keys->fnames[$k]}','{$keys->lnames[$k]}',{$keys->ages[$k]})";
}

Even better would be to use prepared statements, and insert them on this loop instead of looping through the statements again.

Answer 6

You're going to end up with each field as a query

INSERT INTO students VALUES(1) INSERT INTO students VALUES(2)

etc in sqlQueries because you're adding each field one at a time.

Assuming you want INSERT INTO students VALUES(1, "Joan", "Williams", 32) you'll need to build the query differently.

Prepared statements are by far the easiest way to do this.

$statement = $db->prepare("INSERT INTO students VALUES(?,?,?,?)"); 
foreach($keys->ids as $k => $v)
{
  $statement->bind_param("issi", $keys->ids[$k], $keys->fnames[$k], $keys->lnames[$k], $keys->ages[$k]);
  $statement->execute();
}