[RESOLVED] Array values to SQL statement

mrbaseball34

If I have an array like this:

Array ( [0] => "81" [1] => [2] => "NS" [3] => "60" [4] => "LAMP" [5] => [6] => )

How can I make this code:

$sql = "INSERT INTO linv_inventory (user_id,inventory_part_number,inventory_alt_part_number,".
                                             "inventory_condition_code,inventory_quantity,inventory_description,".
                                             "last_update) ".
                                     "VALUES ($user_id, ".implode(',', $fields_order).", NOW())";

produce this SQL:

INSERT INTO linv_inventory (user_id,inventory_part_number,inventory_alt_part_number,
                            inventory_condition_code,inventory_quantity,
                            inventory_description,last_update)
VALUES (425, "81", "","NS","60","LAMP", NOW())

I can't seem to make element 1 = "" at all. I'm removing 5 and 6 using array_slice.

ahundiak

If the number of fields are always fixed then something like:

$values = sprintf('VALUES (%d, "%s","%s","%s","%s", NOW())',
  $user_id,$fields[0],$fields[1],$fields[2],$fields[3]);

A more generic approach would be something like:

$tmp = null;
foreach($fields as $field)
{
  if (!$tmp) $tmp .= ',';
  $tmp .= '"' . $field . '"';
}

mrbaseball34

ahundiak;10965145 wrote:
If the number of fields are always fixed then something like:
$values = sprintf('VALUES (%d, "%s","%s","%s","%s", NOW())',
  $user_id,$fields[0],$fields[1],$fields[2],$fields[3]);

But if the array element already has the double quotes, the result will be something like this:

INSERT INTO linv_inventory (user_id,inventory_part_number,inventory_alt_part_number,
                            inventory_condition_code,inventory_quantity,
                            inventory_description,last_update)
VALUES (425, ""81"", "",""NS"",""60"",""LAMP"", NOW())

ahundiak;10965145 wrote:
A more generic approach would be something like:
$tmp = null;
foreach($fields as $field)
{
  if (!$tmp) $tmp .= ',';
  $tmp .= '"' . $field . '"';
}

I don't EVEN understand what you are doing here.

ahundiak

But if the array element already has the double quotes, the result will be something like this:

Have you tried it? The double quotes that display when you use print_r merely indicate that you have strings. You could actually store the values that way in which case your original implode would work just fine. But you don't see that very often. Again, try the sprintf statement and see what happens. Just echo the results out.

mrbaseball34

This seems to work fine. I have a function called stripIllegalChars to take out the double quotes.

$field_str = sprintf('"%s",  "%s",  "%s",  %d,  "%s"',  

                     trim($this->stripIllegalChars($fields_order[0])),
                     trim($this->stripIllegalChars($fields_order[1])),
                     strtoupper($fields_order[2]),
                     $fields_order[3],
                     addslashes($fields_order[4]));

The 4th element may have double quotes in the string, not enclosed in them so I need
addslashes...