mysql_insert_array()
Inserts $data into $table using the associative array keys as field names and the values as values (requires an existing open database connection).
Parameters
| Argument | Type | Explanation |
|---|---|---|
| $table | String | The name of the database table to insert into |
| $data | Array | The associative array containing fieldnames as keys and values |
| $exclude | String/Array | Optional string or array of field names to exclude from the insertion. Useful for excluding certain elements when using this on $_POST |
Return Values
The function returns an associative array with the following elements:
| Key | Description |
|---|---|
| mysql_error | FALSE if the query was successful, detailed MySQL error otherwise |
| mysql_insert_id | The most recent ID generated from the query (only for tables with an AUTO_INCREMENT) |
| mysql_affected_rows | The number of rows affected by the query |
| mysql_info | MySQL information about the query |
Code
<?php
function mysql_insert_array($table, $data, $exclude = array()) {
$fields = $values = array();
if( !is_array($exclude) ) $exclude = array($exclude);
foreach( array_keys($data) as $key ) {
if( !in_array($key, $exclude) ) {
$fields[] = "`$key`";
$values[] = "'" . mysql_real_escape_string($data[$key]) . "'";
}
}
$fields = implode(",", $fields);
$values = implode(",", $values);
if( mysql_query("INSERT INTO `$table` ($fields) VALUES ($values)") ) {
return array( "mysql_error" => false,
"mysql_insert_id" => mysql_insert_id(),
"mysql_affected_rows" => mysql_affected_rows(),
"mysql_info" => mysql_info()
);
} else {
return array( "mysql_error" => mysql_error() );
}
}
?>
Example
<?php
// Open database here
// Let's pretend these values were passed by a form
$_POST['name'] = "Bob Marley";
$_POST['country'] = "Jamaica";
$_POST['music'] = "Reggae";
$_POST['submit'] = "Submit";
// Insert all the values of $_POST into the database table `artists`, except
// for $_POST['submit']. Remember, field names are determined by array keys!
$result = mysql_insert_array("artists", $_POST, "submit");
// Results
if( $result['mysql_error'] ) {
echo "Query Failed: " . $result['mysql_error'];
} else {
echo "Query Succeeded! <br />";
echo "<pre>";
print_r($result);
echo "</pre>";
}
// Close database
?>
Since every field value is sanitized through mysql_real_escape_string(), the potential for SQL injection is reduced significantly.
In a public environment, or anywhere that users can modify the array keys, you should validate and sanitize the keys in the $data array to prevent SQL errors and injections. For example, if someone forges a POST to your script with additional fields, MySQL will most likely throw an error.
To combat this, simply make sure that the keys in the array are what you expect them to be, and disallow anything foreign.
Me Array is overfull, therefor i cant run it without errors, is there away arround without loosing some of my inputs?
Einar, if by “errors” you mean timing out, then I fear you are limited by what your web server can handle in one page load. Since the function makes one query per execution, I don’t believe this is a good solution for your particular application.
You may want to consider modifying the function to use the MySQLi extension, which would allow you to take advantage of the mysqli_multi_query() function (http://us2.php.net/manual/en/function.mysqli-multi-query.php)
For a large number of queries, it may even be practical to write them to a file first, then import them into the database using LOAD DATA (http://dev.mysql.com/doc/refman/5.0/en/load-data.html).
No, the Array is to full..
http://pastie.caboo.se/115422
As you se, the last entry is ‘tag’ wich was supposed to be ‘tags’. If I add a entry before tags, then tags dissapear and the same thing happend.
So the Array cant hold more information..
(i use xampp on my Mac)
#Sorry my bad English!
Fixed…
This is a great little script. Thanks. But have you (or anyone) thought about using it along with something like the md5 function?
nevermind! i figured it out. simply wrap the post variable in the md5 function in the php before runnign the insert. works like a champ.
This is a very helpful code. thnx
I do thank you very much for the helping hand. It kept my from reinventing the wheel once again!
Ta ra
This is an awesome script, nothing compares to it’s simplicity and usefulness currently available on the web. I have been looking for books and snippets to integrate ajax, php and mysql for weeks, but until I stumbled upon your solution, I was loosing sleep trying to use foreach, for and while loops to insert dynamic arrays for textbox from within innerHTML elements. Thanks and much obliged my fellow coder.
This is very helpful, Thankyou very much
Worked perfectly on the first try … thank you so much!
I’m using this function to transfer data between MySQL databases and it’s making the process MUCH easier. I specify a table in a form, the php script accepts that form name and grabs all the records from the table in the old DB to the same table in the new DB.
This Function rocks – so does the author!
wow bob marley i love him so much!
Thanks dear for the nice script.
Cheers,
Arya