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

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;
}

?>

Solution

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.