Jump to content
Sign in to follow this  
darbok

connecting to msqli

Recommended Posts

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...