current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 1 down vote favorite

I have a PHP array of the column names in my SQL table. I also have an array of the values I want to assign to these columns. How do I put this in an SQL query. At present im writing out each column title like so: 

$query = "INSERT INTO `first_page_data`(`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`) 
VALUES ('$1','$2','$3','$4','$5','$6','$7','$8')";

But there must be a way of just using the arrays?

As an extra, is there a way of defining key/value pairs to keep the two pairs of data together, and then using these to insert into the database? how is this formatted in the SQL query?

share|improve this question
    
With the code provided and the reasons you give there is not much to do. The `INSERT' cannot be written in any other way and you need to specify the columns and parameters. What are you trying to do? –  pid Feb 6 '14 at 20:12

4 Answers 4

up vote 4 down vote accepted

Here's another similar solution.

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 usage:

<?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

?>

Source: Inserting An Array into a MySQL Database Table

share|improve this answer
    
Would the output of this look similar to what ive done, except its created in a much more an automated way? –  Giovanni Feb 6 '14 at 20:51
    
Yes, the final query will look exactly the same as yours. It just eliminates the hassle of manually specifying table columns if your submitted data follows their naming scheme. It also returns some useful info if in case you need it. If you want a shorter solution, @M.Eskandari's code should work just as well. –  Slavko Feb 6 '14 at 21:42
    
I like extended code over shorter code, i can follow it easier! Thanks! –  Giovanni Feb 6 '14 at 21:43
up vote 3 down vote 
//Insert ( var , Array )
    function insert($table, $inserts) {
        $values = array_map('mysql_real_escape_string', array_values($inserts));
        $keys = array_keys($inserts);   
        return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
    }
/*  Samples
 insert('first_page_data', array(
    'a' => 'Just Persian Gulf',
    'b' => 'DB9',
    'c' => '2009'
));
*/

it's good And Rapid!

share|improve this answer
    
Edited!!!!!!!!! –  M.Eskandari Feb 6 '14 at 20:14
    
This is similar to how Zend Framework does it, only you probably want to use ? for each value and bind your values in, using PDO. –  Marcus Adams Feb 6 '14 at 20:48
up vote 0 down vote

Using PHP:

Getting your values into an array as $key => $value depends on the situation, but manually it would happen like so:

$array = array(`a` => '$1',`b` => '$2', ...and so on); //I am assuming that $ is not a variable indicator since it is inside single quotes.

There are a variety of array functions that can help you if you have existing arrays that you would rather manipulate to create the final array.

Once you have it, however:

$query = 'INSTERT INTO `first_page_data` (';
foreach ($array as $key => $value) {
    $query .= '`' . $key . '`';
}
$query .= ') VALUES (';
foreach ($array as $value) {
    $query .= '`' . $value . '`';
}
$query .= ')';

The code runs a foreach on the array twice, once to get the key and append it to the appropriate part of the string, and the other to add the corresponding values.

share|improve this answer
up vote 0 down vote

Try serialize() before the INSERT and unserialize() to get the array after a SELECT.

You need only one field to insert all the data.

http://ca1.php.net/manual/fr/function.serialize.php
http://ca1.php.net/manual/fr/function.unserialize.php

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.