Insert multi-dimensional Array with both strings and numbers into MySQL

Question

I've seen many examples looping through array using foreach and implode function to build sql string for MySQL Insert query. However, all those examples assume that all values in the Array and all columns in the MySQL table are either string or a number. Is there a way to do something similar, but with an Array that has both strings and numbers and is to be inserted into MySQL table that has string and number columns?

$insertArr[] = array(
        'order_id' => $orderID, 
        'user_id' => $userID,         
        'price' => $price, 
        'quantity' => $quantity);

$colNames = "( " . implode(", ",array_keys($insertArr[0])) . " )";
foreach ( $insertArr as $row )
{        
    $colValuesArr[] = "( '" . implode("', '", $row) . "' )";
}
$colValues = implode(", ", $colValuesArr);
$strsql = "INSERT INTO tbl_orders $colNames 
            VALUES $colValues";

The code above is not working right, because order_id and user_id are strings and price and quantity are numbers. Imploding it with quotes and commas assumes that all values are strings, imploding it without quotes assumes that all values are numbers. Is there a way, without having to loop through every single element in the array, to do this?

The variable $strsql has invalid SQL syntax. That could explain why the code is not working. — Gordon Linoff
– Gordon Linoff, Commented Jan 5, 2014 at 21:29
MySQL is fine with numbers being quoted. If you want to keep it simple, just quote everything. (But remember to escape your data!) Or, look into prepared statements; in the long run, they will make things much easier for you. — cHao
– cHao, Commented Jan 5, 2014 at 21:29
Gordon Linoff, what is wrong with the $strsql SQL syntax? It seems good to me. I printed it, don't see anything wrong with it. Can you be more specific? — user2395238
– user2395238, Commented Jan 5, 2014 at 21:33

Community · Accepted Answer · 2023-11-17 20:33:26Z

1

Assuming this is the way you want to handle things, & you do not want to loop through arrays or use prepared statements, I think this should work. I reformatted your code a bit but the overall logic is still intact. The logic I added is to basically create two separate arrays. One for numerical values. The other for text values:

// Test data.
$orderID = 'testorderid';
$userID = 'testuserid';
$price = 7.99;
$quantity = 2;

// Create the text values array.
$insertArrText[] = array('order_id' => $orderID, 'user_id' => $userID);

// Create the numerical values array.
$insertArrNumerical[] = array('price' => $price, 'quantity' => $quantity);

// Get the array keys & merge them into one combined array.
$array_keys = array_merge(array_keys($insertArrText[0]), array_keys($insertArrNumerical[0]));

// Set the column names.
$colNames = "( " . implode(", ", $array_keys) . " )";

// Loop through the '$insertArrText'
foreach ($insertArrText as $key => $row ) {

  // Set the numerical values.
  $numerical_values = implode(", ", $insertArrNumerical[$key]);

  // Set the text values.
  $text_values = "'" . implode("', '", $row) . "'";

  // Set the column values array.
  $colValuesArr[] = "(" . $text_values . "," . $numerical_values . ")";
}

// Set the column values string.
$colValues = implode(", ", $colValuesArr);

// Create the MySQL query.
$strsql = "INSERT"
        . " INTO tbl_orders " . $colNames
        . " VALUES " . $colValues
        ;

// Echo the output for testing.
echo $strsql;

The output of that script is:

INSERT INTO tbl_orders ( order_id, user_id, price, quantity ) VALUES ('testorderid', 'testuserid',7.99, 2)

EDIT Here is a reworking using gettype to detect string type. More flexible & robust. This is using test data of course, but should be easily adaptable to real world scenarios.

// Test data.
$orderID = 'testorderid';
$userID = 'testuserid';
$price = 7.99;
$quantity = 2;

// Create an array map based on strings & MySQL DB field values.
$array_map = array();
$array_map['orderID'] = 'order_id';
$array_map['userID'] = 'user_id';
$array_map['price'] = 'price';
$array_map['quantity'] = 'quantity';

// Create the text arrays.
$insertArrText = array();
$insertArrNumerical = array();

// Set arrays for text and numberical types.
$text_types = array('string');
$numerical_types = array('double','integer');

// Lopop through the array map & assign values based on type.
foreach ($array_map as $array_map_key => $array_map_value) {
  if (in_array(gettype($$array_map_key), $text_types)) {
    $insertArrText[0][$array_map_value] = $$array_map_key;
  }
  else if (in_array(gettype($$array_map_key), $numerical_types)) {
    $insertArrNumerical[0][$array_map_value] = $$array_map_key;
  }
}

// Get the array keys & merge them into one combined array.
$array_keys = array_merge(array_keys($insertArrText[0]), array_keys($insertArrNumerical[0]));

// Set the column names.
$colNames = "( " . implode(", ", $array_keys) . " )";

// Loop through the '$insertArrText'
foreach ($insertArrText as $key => $row ) {

  // Set the numerical values.
  $numerical_values = implode(", ", $insertArrNumerical[$key]);

  // Set the text values.
  $text_values = "'" . implode("', '", $row) . "'";

  // Set the column values array.
  $colValuesArr[] = "(" . $text_values . "," . $numerical_values . ")";
}

// Set the column values string.
$colValues = implode(", ", $colValuesArr);

// Create the MySQL query.
$strsql = "INSERT"
        . " INTO tbl_orders " . $colNames
        . " VALUES " . $colValues
        ;

// Echo the output for testing.
echo $strsql;

And the output of this code is:

INSERT INTO tbl_orders ( order_id, user_id, price, quantity ) VALUES ('testorderid', 'testuserid',7.99, 2)

edited Nov 17, 2023 at 20:33

CommunityBot

11 silver badge

answered Jan 5, 2014 at 21:33

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2395238 Over a year ago

this might work if it's just for a specific purpose. However, I am trying to build a function that I could use anytime, and every time the array might be different, different value types.

user2395238 Over a year ago

how would you use prepared statements?

user2395238 Over a year ago

gettype is an option, because I can just do it on the first row, as it is assumed that all of the next rows will have the same types. that way i don't have to loop through every single element in the array, just the first row. i'll try that.

user2395238 Over a year ago

JakeGould, do you know if the performance of the query is worse if I use PDO prepared statements rather then the method above?

Bram · Accepted Answer · 2014-01-05 21:31:00Z

0

Use array_map:

$row = array_map(function ($a) {
  if (is_numeric($a)) return $a;
  else return "'".$a."'";
},$row);

Assuming your data is safe, otherwise please use something like mysql_real_escape or use PDO prepared statements.

answered Jan 5, 2014 at 21:31

Bram

4,55225 silver badges25 bronze badges

Collectives™ on Stack Overflow

Insert multi-dimensional Array with both strings and numbers into MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related