Sign in to follow this  
Followers 0
darbok

connecting to msqli

2 posts in this topic

Looking at how form items are put into a mysql table, the part that seems daunting is if ones form has like 50 items... having to list all those IDs with their variables makes me wonder if there is a better way or if its just " it up buttercup.".

Share this post


Link to post
Share on other sites

You can generalize a query. I've used the following algorithm in some of my projects, but you have to guarantee that the data coming in corresponds to fields in the table otherwise it will give an SQL error.

Here's the MySQLi version ( I don't use MySQLi, so the piece that binds parameters could probably be improved)


$query_data = array(
  'first_name' => 'John',
  'last_name' => 'Smith',
  'age' => 40,
  // ... 50 other fields
  'favorite_color' => 'Blue'
);

// Break into fields and values
$fields = array_keys($query_data);
$values = array_values($query_data);

// Generate placeholders
$placeholders = array_fill(0, count($fields), '?');

// Sanitize field names
foreach($fields as &$field) {
  $field = str_replace('`', '', $field);
}

// Construct query

$sql  = "INSERT INTO my_table (";
$sql .= '`' . implode('`,`', $fields) . '`';
$sql .= ') VALUES (';
$sql .= implode(',', $placeholders);
$sql .= ')';
$stmt  = $mysqli->prepare($sql);

// Bind parameters
// In PDO this wouldn't be necessary since you can pass an array when executing a query
// MySQLi required parameters to be bound with bind_param
// I strongly recommend PDO over MySQLi
$types = '';
foreach($values as $index => $value) {
  // The first character of integer, double and string matches what MySQLi expects
  $type = gettype($value);
  $types .= $type[0];
}
array_unshift($values, $types);
call_user_func_array(array($stmt, 'bind_param'), $values);

// Execute the statement
$stmt->execute();

I work in PDO, which is safer for this type of operation because you don't need to specify the type of the parameters. Here's the PDO version:

$query_data = array(
  'first_name' => 'John',
  'last_name' => 'Smith',
  'age' => 40,
  // ... 50 other fields
  'favorite_color' => 'Blue'
);

// Break into fields and values
$fields = array_keys($query_data);
$values = array_values($query_data);

// Generate placeholders
$placeholders = array_fill(0, count($fields), '?');

// Sanitize field names
foreach($fields as &$field) {
  $field = str_replace('`', '', $field);
}

// Construct query

$sql  = "INSERT INTO my_table (";
$sql .= '`' . implode('`,`', $fields) . '`';
$sql .= ') VALUES (';
$sql .= implode(',', $placeholders);
$sql .= ')';
$query  = $pdo->prepare($sql);

// Execute the query
$query->execute();

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0