Obinna Nwakwue Obinna Nwakwue - 4 months ago 8
PHP Question

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

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

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