Error: INSERT INTO / VALUES ('Array'); Column count doesn't match value count at row 1

Question

I spent all Sunday trying to figure this out but, I am not sure how to word it right. It checks every hour via Cron job for new sales. Then takes the product number and receipt number. It uses the product number to check an information database and gather all that info from each row for the newly sold products. Then for each sold product, I need to add the receipt number on the end of the array and insert all that information into a 3rd database that saves all the sales.

My main problem is I merge them and it won't insert into the database. I had it working the first time but only grabbed the first row.

<?php
//find out current time and 1 hour ago
$current_time = strtotime("now");
$tenmin_ago = strtotime('-10 min');
$hour_ago = strtotime('-1 hour');

//////////////////////////////////////////////////////////////////
/////////////////// Connect to Sales Database  ///////////////////
//////////////////////////////////////////////////////////////////

// connect to EMAP sales MySQL server
$server='localhost';
$user='user';
$password='pass';
$database='sales_data';

$con = mysqli_connect($server,$user,$password,$database);

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: SALES " . mysqli_connect_error();
  }

//////////////////////////////////////////////////////////////////
///////////////////// Connect to EM Database  ////////////////////
//////////////////////////////////////////////////////////////////

//EM connect to DLGuard-EM MySQL server
$em_server='localhost';
$em_user='user';
$em_password='pass';
$em_database='databaseEM';

$em_con = mysqli_connect($em_server,$em_user,$em_password,$em_database);

//EM Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: EM " . mysqli_connect_error();
  }

//EM grab store name
$em_dlg_store = "EM";

$em_rows = array();   
$em_request = "SELECT * FROM customers WHERE date BETWEEN $hour_ago AND {$current_time}";
$em_result = mysqli_query($em_con, $em_request) or die("ERROR NO SALES EM");
 while ($em_row = mysqli_fetch_array($em_result)) {
    $em_prod_num = $em_row["prod_num"];
    $em_rows[] = $em_row["receipt"];
    }

//////////////////////////////////////////////////////////////////
///////////////////// Grab info for EM Sales  ////////////////////
//////////////////////////////////////////////////////////////////

$emap_rows = array();
$emap_request = "SELECT * FROM all_products WHERE dlgprod_num='{$em_prod_num}' AND dlg_store='{$em_dlg_store}'";
$emap_result = mysqli_query($con, $emap_request) or die("ERROR dlg prod num EM");
 while ($emap_row = mysqli_fetch_array($emap_result)) $emap_rows[] = $emap_row;

$em_emap_rows = array_merge($em_rows, $emap_rows);
 /*VALUES ('$emap_sku', '$emap_dlgprod_num', '$emap_book_title', '$emap_dlgprod_price', '$emap_author_name', '$emap_author_email', '$emap_publisher', '$emap_dlg_store', '$em_receipt');";*/
// 1
$em_add_sql  = "INSERT INTO all_author_sales (sku, dlgprod_num, dlgprod_nam, dlgprod_price, author_name, author_email, publisher, dlg_store, dlgcustomer_receipt)
VALUES ('$em_emap_rows');";         
if ($con->multi_query($em_add_sql) === TRUE) {
} else {
    echo "Error: " . $em_add_sql . "<br>" . $con->error;
}

?>

xxxxxxxxxxxxxxxxxxxxxxxxx Update 2/27/17 xxxxxxxxxxxxxxxxxxxxxx

Here is an updated version and it grabs the information but it loses the array for receipt somewhere in the merge or string section. Also, it puts the order wrong. instead of UPC, product number, title, price, etc. then start the next line for the second sale it mixes them together like upc, UPC, product number, product number, title, title, price, price when there are two sales. Here is the new error. I am so close to figuring this out thank you. I am going to make 4 scripts and have them alternating checking all 4 stores for sales every hour.

Warning: array_map(): Argument #2 should be an array in  /home1/lotscav1/public_html/Sales/scripts/sales-notif.php on line 53
Warning: implode(): Invalid arguments passed in  /home1/lotscav1/public_html/Sales/scripts/sales-notif.php on line 53
Error: INSERT INTO all_author_sales (sku, dlgprod_num, dlgprod_nam,  dlgprod_price, author_name, author_email, publisher, dlg_store,  dlgcustomer_receipt) VALUES ('('EM2200002','EM2200002','1','1','Island  Girl','Island Girl','4.95','4.95','Marshall Gibson','Marshall  Gibson','jasminerice1993@gmail.com','jasminerice1993@gmail.com','Dan  Cuneo','Dan Cuneo','EM','EM'),(),');
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   'EM2200002','EM2200002','1','1','Island Girl','Island   Girl','4.95','4.95','Marsha' at line 2
Array

Here is the updated code

    <?php
//find out current time and 1 hour ago
$current_time = strtotime("now");
$hour_ago = strtotime('-24 hour');

//////////////////////////////////////////////////////////////////
/////////////////// Connect to Sales Database  ///////////////////
//////////////////////////////////////////////////////////////////

// connect to EMAP sales MySQL server
$mysqli = new mysqli("localhost", "username", "password",     "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") "  . $mysqli->connect_error;
}

//////////////////////////////////////////////////////////////////
///////////////////// Connect to EM Database  ////////////////////
//////////////////////////////////////////////////////////////////
$mysqli_em = new mysqli("localhost", "username", "password",    "database");
if ($mysqli_em->connect_errno) {
echo "Failed to connect to MySQL_EM: (" . $mysqli_em->connect_errno .     ") " . $mysqli_em->connect_error;
}

//EM grab store name
$em_dlg_store = "EM";


$em_rows = array();   
$em_request = "SELECT * FROM customers WHERE date BETWEEN '$hour_ago'    AND '$current_time'";
$em_result = mysqli_query($mysqli_em, $em_request) or die("Error No      Sales EM");
while ($em_row = mysqli_fetch_array($em_result)) {
$em_prod_num = $em_row["prod_num"];
$em_rows[] = $em_row["receipt"];
}
//////////////////////////////////////////////////////////////////
///////////////////// Grab info for EM Sales  ////////////////////
//////////////////////////////////////////////////////////////////
$emap_rows = array();
$emap_request = "SELECT * FROM all_products WHERE       dlgprod_num='$em_prod_num' AND dlg_store='$em_dlg_store'";
$emap_result = mysqli_query($mysqli, $emap_request) or die("Error dlg  prod num EM");
while ($emap_row = mysqli_fetch_array($emap_result)) $emap_rows[] =     $emap_row;

$em_emap_rows = array_merge($emap_rows, $em_rows);
/*VALUES ('$emap_sku', '$emap_dlgprod_num', '$emap_book_title',    '$emap_dlgprod_price', '$emap_author_name', '$emap_author_email',     '$emap_publisher', '$emap_dlg_store', '$em_receipt');";*/
// 1
$string = "";
foreach ($em_emap_rows as $key => $innerArr) {
$result = implode( array_map('quoteItems', $innerArr), ",");
$string .= "(" . $result . ")";

if( $key != count($array) - 1 ){
    $string .= ",";
}
}
function quoteItems($item){
return "'" . $item . "'";
}
//$em_rowss = implode(",", $em_rows); this turns the receipt array     into a string
$em_add_sql  = "INSERT INTO all_author_sales (sku, dlgprod_num,      dlgprod_nam, dlgprod_price, author_name, author_email, publisher,       dlg_store, dlgcustomer_receipt)
VALUES ('$string');";           
if ($mysqli->multi_query($em_add_sql) === TRUE) {
} else {
echo "Error: " . $em_add_sql . "<br>" . $mysqli->error . "<br>" .           $em_rows;
}

?>

Thank you very much by the way. I know it's something easy. I just can't seem to get it and after 13 hours of trying everything I could find I am at a loss. — Mike Ocampo, Feb 20 '17 at 18:59
Do you get any errors? And why do you outcomment $current_time and not $hour_ago? it's better to be consistent with your choises — Mart, Feb 20 '17 at 19:00
You're attempting to insert and array as a string. ```VALUES ('$em_emap_rows');"; ``` -- $em_emap_rows is an array not a string. Unless I'm missing something. — Jacob S, Feb 20 '17 at 19:01
Possible duplicate of [Error Column count doesn't match value count at row 1?](http://stackoverflow.com/questions/34433030/error-column-count-doesnt-match-value-count-at-row-1) — Koen Hollander, Feb 20 '17 at 19:02
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Feb 20 '17 at 19:12
@ Mart Haarman - The current error is that of the title. VALUES ('Array'); Column count doesn't match value count at row. As far as the timers I will have a cron job that checks every hour for new sales. Therefore I have it to look for anything with a timestamp between current and an hour ago. The 10min by the way was just for testing and will be omitted. — Mike Ocampo, Feb 20 '17 at 23:58
@JacobS I am looking to have a the insert into rows filled with a list of the information in 8 rows and the 9th row being the receipt number. I feel dumb but this problem is draining my brain. — Mike Ocampo, Feb 20 '17 at 23:59
Thank you Bobby I will look into that once I get this script working — Mike Ocampo, Feb 21 '17 at 00:02
all in all my end goal is to connect to 4 different databases and check if any sales in the last hour using the timestamp row in the customer tables of each 4 databases. --- From there it will grab the receipt number and product number. --- From there the product number is being used to grab 8 rows of data for each book sale from the product info database --- add a receipt number for each sale on the end of that array --- then take all that info and insert per sale into the final sales database --- then send an email with that information to the authors and one to myself. The email works :) — Mike Ocampo, Feb 21 '17 at 00:10

score 0 · Answer 1 · answered Feb 20 '17 at 19:25

Looks like this could all be done in a single query:

INSERT INTO all_author_sales (sku, dlgprod_num, dlgprod_nam, 
       dlgprod_price, author_name, author_email, publisher, dlg_store, dlgcustomer_receipt)

    SELECT ap.sku, ap.dlgprod_num, ap.dlgprod_nam, ap.dlgprod_price, 
           ap.author_name, ap.author_email, ap.publisher, ap.dlg_store, c.receipt 
        FROM customers c
        INNER JOIN all_products ap 
                ON  c.prod_num = ap.dlgprod_num
                AND c.date BETWEEN '{$hour_ago}' AND '{$current_time}'
                AND dlg_store='{$em_dlg_store}'

A few pointers to note:

It can be seen from the query in question that receipt is a column in customers table which goes into the all_author_sales as dlgcustomer_receipt. However, for all ap.* columns in the SELECT part of the query above, please use the appropriate column names from all_products table as applicable if they aren't the same as written in the query.
Since you're using MySQLi, please consider changing your code to include Prepared Queries for the sake of simplicity and security.

Abdulkareem Mohammed · Answer 2 · 2017-02-20T19:59:48.143

You just insert array as string VALUES('$em_emap_rows'). That is the error.

Solution

To loop for the array and quote every element in every array and then implode arrays in the main array.

$string = "";
foreach ($em_emap_rows as $key => $innerArr) {
    $result = implode( array_map('quoteItems', $innerArr), ",");
    $string .= "(" . $result . ")";

    if( $key != count($array) - 1 ){
        $string .= ",";
    }
}
function quoteItems($item){
    return "'" . $item . "'";
}

Then use Values($string)

score 0 · Accepted Answer · answered Feb 20 '17 at 20:33

I'll assume that top part of the code works. But from here there are several things to check:

//////////////////////////////////////////////////////////////////
///////////////////// Grab info for EM Sales  ////////////////////
//////////////////////////////////////////////////////////////////

$emap_rows = array();
$emap_request = "SELECT * FROM all_products WHERE dlgprod_num='{$em_prod_num}' AND dlg_store='{$em_dlg_store}'";
$emap_result = mysqli_query($con, $emap_request) or die("ERROR dlg prod num EM");

 while ($emap_row = mysqli_fetch_array($emap_result)) $emap_rows[] = $emap_row;

Basically you put all rows you got from query to $emap_rows array, so it look more like:

$emap_rows = array(
    0 => array(
        'sku' => 'value',
        'dlgprod_num' => 'value',
        'dlgprod_nam' => 'value',
        ...
    ),
    1 => array(
        'sku' => 'value2',
        'dlgprod_num' => 'value2',
        'dlgprod_nam' => 'value2',
        ...
    ),
)

Are you expecting only 1 result from a previous query? I'll presume that you're expecting only 1 row for receipt, otherwise it will make no sense to me. I'll presume that you want to add $em_rows value (receipt) to each $emap_rows

// $em_emap_rows = array_merge($em_rows, $emap_rows);

Maybe you can try:

foreach( $emap_rows as $emap_row ) {
    $v = array_values( $emap_row ); 
    $v[] = $em_rows[0]; // because I expect only 1 result, added to array

    $em_add_sql  = "INSERT INTO all_author_sales ";
    $em_add_sql .= " (sku, dlgprod_num, dlgprod_nam, dlgprod_price, author_name, author_email, publisher, dlg_store, dlgcustomer_receipt) ";
    $em_add_sql .= " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; 

    $em_con->prepare( $em_add_sql );
    $em_con->bind_param( 'sssssssss', $v[0], $v[1], $v[2], $v[3], $v[4], $v[5], $v[6], $v[7], $v[8] );
    $em_con->execute();
}

All in all I recommend creating entire script from scratch, making it less vulnerable to injects etc. and more precise in terms of which data is used. Do not rely on getting only 1 result if you do not specify that in your query (using LIMIT, for example). Do not rely on data order, but on its key. I used in the example above $v[0] but it would be much better to use $emap_row['sku'].

Note: The code is not tested, it's just my attempt to understand the script and make some help.

score 0 · Answer 4 · answered Apr 01 '17 at 04:10

Thank you for all the help. I couldn't make work what you gave me for my unique situation. However, it inspired me towards my working solution along with a little help from an old friend. Here is the code that now works.

    <?php
//find out current time and 1 hour ago
date_default_timezone_set('America/New_York');
$current_time = strtotime("now");
$hour_ago = strtotime('-1 hour');

//////////////////////////////////////////////////////////////////
/////////////////// Connect to Sales Database  ///////////////////
//////////////////////////////////////////////////////////////////

$mysqli_s = new mysqli("localhost", "user", "password", 
"server_sales_data");
if ($mysqli_s->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli_s->connect_errno . ") 
" . $mysqli_s->connect_error;
}

//////////////////////////////////////////////////////////////////
///////////////////// Connect to EM Database  ////////////////////
//////////////////////////////////////////////////////////////////

$mysqli_em = new mysqli("localhost", "user", "password", 
"server_dlgEM");
if ($mysqli_em->connect_errno) {
echo "Failed to connect to MySQL_EM: (" . $mysqli_em->connect_errno . 
") " . $mysqli_em->connect_error;
}

//Grab store name
$dlg_store = "EM";

$em_request = "SELECT * FROM customers WHERE date BETWEEN '$hour_ago' 
AND '$current_time'";
$em_result = mysqli_query($mysqli_em, $em_request) or die("Error No 
Sales EM");
while ($em_row = mysqli_fetch_array($em_result)) {
$em_prod_num = $em_row["prod_num"];
$em_receipt = $em_row["receipt"];


//////////////////////////////////////////////////////////////////
///////////////////// Grab info for EM Sales  ////////////////////
//////////////////////////////////////////////////////////////////

$request_s = "SELECT * FROM all_products WHERE 
dlgprod_num='$em_prod_num' AND dlg_store='$dlg_store'";
$result_s = mysqli_query($mysqli_s, $request_s) or die("Error dlg 
prod num EM");
while ($row_s = mysqli_fetch_array($result_s)) {
        $sku_s = $row_s["sku"];
        $dlgprod_num_s = $row_s["dlgprod_num"];
        $book_title_s = addslashes($row_s["book_title"]);
        $dlgprod_price_s = $row_s["dlgprod_price"];
        $author_name_s = addslashes($row_s["author_name"]);
        $author_email_s = $row_s["author_email"];
        $publisher_s = $row_s["publisher"];
        $dlg_store_s = $row_s["dlg_store"];

            $add_sql_s  = "INSERT INTO all_author_sales SET
            `sku`='$sku_s', 
            `dlgprod_num`='$dlgprod_num_s',
            `dlgprod_nam`='$book_title_s',
            `dlgprod_price`='$dlgprod_price_s',
            `author_name`='$author_name_s',
            `author_email`='$author_email_s',
            `publisher`='$publisher_s',
            `dlg_store`='$dlg_store_s',
            `dlgcustomer_receipt`='$em_receipt' ";  

//create signature
$sig = "The Admin Team at www.website.com";
//to
$admin_email = "admin@website.com";
$to = array($author_email_s, $admin_email);

//setup email headers
$headers='From: ' . $admin_email . "\r\n" .
'Reply-To: ' . $admin_email . "\r\n" .
'X-Mailer: PHP/' . phpversion();
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
$headers .= $emailbody."\n\n";

//email subject and body
$subject = "Your book stats";
$message = "
Hi $author_name_s,<br />
I just wanted to send you a message and let you know that the book or 
books below have just been purchased.<br /><br />

Store: $dlg_store_s<br />
Receipt: $em_receipt<br />
Sku Number: $sku_s<br /><br />

Book Title: $book_title_s<br />
Publisher: $publisher_s<br />
Product Number: $dlgprod_num_s<br />
Price: $dlgprod_price_s<br /><br />

Sincerely,<br />
$sig<br /><br />

To remove yourself from this notification, please send an email to 
$admin_email with Unsubscribe in the subject line.
";

            if ($mysqli_s->multi_query($add_sql_s) === TRUE) {
            mail (implode(',', $to), $subject, $message, $headers); 
            } else {
                echo "Error: " . $add_sql_s . "<br>" . $mysqli_s-
>error . "<br>" . $string;
            }


    }



}


?>

Error: INSERT INTO / VALUES ('Array'); Column count doesn't match value count at row 1

4 Answers4

Solution