PHP implode adding extra field for no reason

Question

For some reason, in PHP, the implode function is returning an extra field from $tmpTblRow at the end of it's returned string and causes a MySQL error.

MySQL statement is generated from:

$sqll = sprintf(
    "INSERT INTO $sqlToTbl (%s) VALUES ('%s')", 
    $sqlToCols, 
    implode("','", array_values($tmpTblRow))
);

$tmpTblRow is the assocative array:

[lineItem] => null
[partID] => 1
[partNumber] => tr2-mod2-0001
[serialNumber] => 
[partDescription] => mob176wertyu
[quantity] => 10
[price] => 500
[warranty] => 
[dateRequired] => 2055-11-11
[note] => 
[discount] => 
[isProcessed] => 1
[parameter] => 
[isPrivate] => 
[processedByUserID] => 1
[processedDate] => 2013-02-04
[extPrice] => 0
[parentID] => 36
[isToAct] => 1
[userID] => 0
[type] => 
[dateGenerated] => 2013-02-04 10:53:12
[unitType] =>

$sqll VALUE RETURNS:

INSERT INTO tblOrdersItems 
    ( lineItem, partID , partNumber , serialNumber, partDescription , quantity, 
      price , warranty  , dateRequired , note , discount, isProcessed, parameter,
      isPrivate, processedByUserID, processedDate, extPrice, parentID, isToAct, 
      userID, type, dateGenerated, unitType) 
VALUES 
    ( 'null', '1', 'tr2-mod2-0001', '', 'mob176wertyu', '10', '500', '', 
      '2055-11-11', '', '', '1', '', '', '1', '2013-02-04', '0', '36', '1', '0', 
      '', '2013-02-04 10:53:12', '', '1' )

The return error is: "Column count doesn't match value count at row 1"

Notice the extra 1 at the end of the string $sqll. Can anyone explain why and how to fix this?

edit... var_dump for $tmpTblRow returns:

array(23) {
  ["lineItem"]=>
  string(4) "null"
  ["partID"]=>
  string(1) "1"
  ["partNumber"]=>
  string(13) "tr2-mod2-0001"
  ["serialNumber"]=>
  string(0) ""
  ["partDescription"]=>
  string(12) "mob176wertyu"
  ["quantity"]=>
  string(2) "10"
  ["price"]=>
  string(3) "500"
  ["warranty"]=>
  NULL
  ["dateRequired"]=>
  string(10) "2055-11-11"
  ["note"]=>
  NULL
  ["discount"]=>
  NULL
  ["isProcessed"]=>
  string(1) "1"
  ["parameter"]=>
  NULL
  ["isPrivate"]=>
  NULL
  ["processedByUserID"]=>
  string(1) "1"
  ["processedDate"]=>
  string(10) "2013-02-04"
  ["extPrice"]=>
  string(1) "0"
  ["parentID"]=>
  string(2) "36"
  ["isToAct"]=>
  string(1) "1"
  ["userID"]=>
  string(1) "0"
  ["type"]=>
  string(0) ""
  ["dateGenerated"]=>
  string(19) "2013-02-04 10:53:12"
  ["unitType"]=>
  string(0) ""
}

... and the full code is this:

$q = "SELECT $sqlFromCols FROM $sqlFromTbl $sqlFromWhere";
$result1 = $conn->query(stripslashes($q)) ;
    if (!$result1) die($conn->error.">>none<< 1");

    echo $q."||";


echo "<br/>start>";
while($tmpTblRow = $result1->fetch_array(MYSQL_ASSOC) )
{


     var_dump ($tmpTblRow);
     echo "<br/>end|";
     echo "<br/>";
    $tmpTblRow[$sqlFromIDcol] = $newID;

    $sqll = sprintf("INSERT INTO $sqlToTbl (%s) VALUES ('%s')", $sqlFromCols, implode("','",array_values($tmpTblRow)));


    echo $sqll."||";

     $result = $conn->query(stripslashes($sqll)) ;
    if (!$result) die($conn->error.">>none<< while");


    $q = "UPDATE $sqlToTbl SET $change WHERE $sqlToIDcol = $newID";
        $q = stripslashes($q);

    $result = $conn->query(stripslashes($q)) ;
    if (!$result) die($conn->error.">>none<< update");

        //mysql_query($q);
        //echo $q."||<br>";



    $newID = $newID+1;


}

scan the array first to make sure all the required values are there with acceptable values — Aaron W., Feb 04 '13 at 19:41
[lineItem] => null [partID] => 1 [partNumber] => tr2-mod2-0001 [serialNumber] => [partDescription] => mob176wertyu [quantity] => 10 [price] => 500 [warranty] => [dateRequired] => 2055-11-11 [note] => [discount] => [isProcessed] => 1 [parameter] => [isPrivate] => [processedByUserID] => 1 [processedDate] => 2013-02-04 [extPrice] => 0 [parentID] => 36 [isToAct] => 1 [userID] => 0 [type] => [dateGenerated] => 2013-02-04 10:53:12 [unitType] => ... yehp, all there, -1 — Shawn Dotey, Feb 04 '13 at 19:43
@user2040763 why did you re-write all the same values from above in a comment? Was there a purpose to that madness? — War10ck, Feb 04 '13 at 19:50
yes, because i already showed that to be case.. can anyone actually answer the question? — Shawn Dotey, Feb 04 '13 at 20:02

score 1 · Accepted Answer · answered Feb 04 '13 at 20:42

1

If I let this code run:

$tmpTblRow = array(
    'lineItem' => null,
    'partID' => 1,
    'partNumber' => 'tr2-mod2-0001',
    'serialNumber' => '',
    'partDescription' => 'mob176wertyu',
    'quantity' => 10,
    'price' => 500,
    'warranty' => '',
    'dateRequired' => '2055-11-11',
    'note' => '',
    'discount' => '',
    'isProcessed' => 1,
    'parameter' => '',
    'isPrivate' => '',
    'processedByUserID' => 1,
    'processedDate' => '2013-02-04',
    'extPrice' => 0,
    'parentID' => 36,
    'isToAct' => 1,
    'userID' => 0,
    'type' => '',
    'dateGenerated' => '2013-02-04 10:53:12',
    'unitType' => '',
);

$sqlToCols = "col, col, col";

$sqll = sprintf(
    "INSERT INTO $sqlToTbl (%s) VALUES ('%s')",
    $sqlToCols,
    implode("','", array_values($tmpTblRow))
);

var_dump($sqll);

I get the result:

string(183) "INSERT INTO  (col, col, col) VALUES ('','1','tr2-mod2-0001','','mob176wertyu','10','500','','2055-11-11','','','1','','','1','2013-02-04','0','36','1','0','','2013-02-04 10:53:12','')"

As you can see, there is no 1 at the end. This can only mean one thing: You did not dump the right array when debugging.

answered Feb 04 '13 at 20:42

Sven

69,403
10
107
109

thank you Sven and others for helping with my formatting of question. I hope someone can figure this out though, it really has me stumped – Shawn Dotey Feb 04 '13 at 20:51
$tmpTblRow = $result1->fetch_array(MYSQL_ASSOC) is the dump....$sqll = sprintf("INSERT INTO $sqlToTbl (%s) VALUES ('%s')", $sqlToCols, implode("','",array_values($tmpTblRow))); is the string. Its IS the same $tmpTblRow – Shawn Dotey Feb 04 '13 at 20:53
This cannot be. `var_dump($tmpTblRow)` is a dump. What is it's output? – Sven Feb 04 '13 at 20:56
print_r ($tmpTblRow); ... was the debug statement used, not litterally var_dump – Shawn Dotey Feb 04 '13 at 20:58
And the result from another run? The code is OK, your data is wrong, e.g. it does contain more values than you expect. You are the only one that can check this, nobody on Stackoverflow can help you with this step. – Sven Feb 04 '13 at 21:03
ok, now i just updated my question with the vardump values btw. I have checked many times. all im giving is a litteral cut and paste of my code and the results – Shawn Dotey Feb 04 '13 at 21:07
its something with that implode statement, its the only thing not giving me the right reponse. so weird – Shawn Dotey Feb 04 '13 at 21:09
No, the implode works correctly, as does the array_values(). It must be somewhere else in your code. Can you try and copy/paste my code example into a single file on your machine and run it? Try to add things that are in your code from there. – Sven Feb 04 '13 at 21:11
ok I added the full code btw – Shawn Dotey Feb 04 '13 at 21:15
1

Ok, here we are: `$tmpTblRow[$sqlFromIDcol] = $newID;` You are adding an array field after your dump! Q.E.D. – Sven Feb 04 '13 at 21:26
By the way: If you want to copy data inside the database, there is a `INSERT ... SELECT` statement that does just this, and it is even faster. And while we are at it: Why the hell do you `stripslashes()` your SQL strings? I suspect you are attackable by SQL injection, because this simply makes no sense. – Sven Feb 04 '13 at 21:30
stripslashes() was copied and pasted from other code I made and I did not care to mine it out... Noted about the INSERT ... SELECT, Thank you again – Shawn Dotey Feb 04 '13 at 21:32

PHP implode adding extra field for no reason

1 Answers1