insert data from array to DB

Question

I know that this theme is very common, but i'm stuck and can't find an error.

I created an array in PHP:

$dataarray=array("FECHAS" => date("Y-m-d"),"HORAS" => date("H:i:s"),
             "RGD" => 0,"RGA" => 0,"FLU" => 0,"DD2" => 0,
             "H2O" => 0,"PRES:U" => 0,"U" => 0,"V" => 0,"TS" => 0,
             "T1" => 0,"T2" => 0,"H1" => 0,"H2" => 0, "HS" => 0,
             "VV1" => 0,"VV2" => 0);

and i've got a table in MYSQL with the same names, but when i try to put data into it, it does nothing.

for($j=0;$j<$variable_para_base;$j++)
{
    $keys;
    $vars;
foreach($dataarray[$j] as $k=>$v)
{
    $keys.= $k.',';
    $vars.= $v.",";
}
echo $keys."<br>";
echo $vars."<br>";
mysqli_query($mysqli,'INSERT INTO ff ( .$keys.) VALUES ( .vars. ) ') or die(mysql_error());

unset($keys);
unset($vars);
}

if i do it with die option it does for only once another way my key starts to have strange values in the end of it. Any ideas, and again sorry for maybe a repeted question. I get access to DB because it doesn't give me any error, though noow i'm doubting :(.

Answer 1

You have syntax promlems in your query.

INSERT INTO ff ( .$keys.) VALUES ( .vars. ) ' change it to

INSERT INTO ff ( '.$keys.') VALUES ( '.$vars.') '

Also you need to add ' to the varialbles inserted as VALUES.

like that: $vars.= "'".$v."',";

In addition your last variable is also ending with , and it shouldn't be.

Answer 2

You can not insert a array directly to mysql as mysql doesn't understand php data types. Mysql only understands SQL. So to insert this array into a mysql database you have to convert it to an sql statement. This can be done manually or by a library. The output should be an INSERT statement.

Here is a standard mysql insert statement.

INSERT INTO TABLE1(COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2..)

If you have a table with name fbdata with the columns which are presented in the keys of your array you can insert with this small snippet. Here is how your array is converted to this statement.

$columns = implode(", ",array_keys($insData));
$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode(", ", $escaped_values);
$sql = "INSERT INTO `fbdata`($columns) VALUES ($values)";

Answer 3

you have error in query try this,

mysqli_query($mysqli,'INSERT INTO ff (' .$keys. ') VALUES (' .$vars. ') ') or die(mysql_error());

Answer 4

So your end result might look something like this:

<?
for($j=0;$j<$variable_para_base;$j++)
{
    $keys = array();
    $vars = array();
    foreach($dataarray[$j] as $k=>$v)
    {
        $keys[] = $k;
        $vars[] = $v;
    }

    $placeholders = array_fill(0, count($keys), '?'); //used to fill a number of '?' needed to fill later

    //here we use the '?' array to be placeholders for the values
    $query = "INSERT INTO ff (".implode(', ', $keys).") VALUES (".implode(', ', $placeholders).")"; //implode the arrays and separate by comma

    $statement = $mysqli->prepare($query); 

    $types  = array(str_repeat('s', count($vars))); //get the number of parameters and put the 's' to it (used for string values)
    $values = array_merge($types, $vars); //merge the arrays (gets you {'s', $value})

    call_user_func_array(array($statement, 'bind_param'), $values); //bind the values to the statement 

    $result = $statement->execute(); //execute.

    if($result) {
        print "Array inserted, worked like a charm.";
    }
    else {
        print "I failed, sorry...". $mysqli->error();
    }

    unset($keys);
    unset($vars);
}
$statement->close();
?>

This is however untested so test it good.

References you can use:

Stackoverflow question: PHP - MySQL prepared statement to INSERT an array

Stackoverflow question: Best way to INSERT many values in mysqli

Stackoverflow question: Mysqli insert command