Enter mixed data into MySQL database from array using PHP

Question

I have an array containing values I want to enter into a database. My database is structured like so:
1 string value, 7 int values, 5 float values, 1 string value.
The data I want to pass to the database is in an array, with proper typing, $outputArr[]

I have tried using the following snippets with no success:

$escaped_values = array_map('mysqli_real_escape_string', array_values($outputArr));
$values  = implode(", ", $escaped_values);
$query = "INSERT INTO table(columns) VALUES ($values)

(type)var_export($outputArr[$i]) for each element of $outputArr and
$query = INSERT INTO table(columns) VALUES ('$Opos0', $Opos1, $Opos2, ... , '$Opos13')

How can I pass my data into the database while maintaining proper data types?

Entire function as is now written:

$outputArr = array();
foreach($numArr as $k1 => $val){
    foreach($val as $k2 => $val2){
        $outputArr[$k2] = $numArr[$k1][$k2];
    }

    $Opos0 = serialize($connect->real_escape_string(var_export($outputArr[0])));
    $Opos1 = serialize((int)var_export($outputArr[1]));
    $Opos2 = serialize((int)var_export($outputArr[2]));
    $Opos3 = serialize((int)var_export($outputArr[3]));
    $Opos4 = serialize((int)var_export($outputArr[4]));
    $Opos5 = serialize((int)var_export($outputArr[5]));
    $Opos6 = serialize((int)var_export($outputArr[6]));
    $Opos7 = serialize((int)var_export($outputArr[7]));
    $Opos8 = serialize((float)var_export($outputArr[8]));
    $Opos9 = serialize((float)var_export($outputArr[9]));
    $Opos10 = serialize((float)var_export($outputArr[10]));
    $Opos11 = serialize((float)var_export($outputArr[11]));
    $Opos12 = serialize((float)var_export($outputArr[12]));
    $Opos13 = serialize($connect->real_escape_string(var_export($outputArr[13])));

    $query = "INSERT INTO `num_data`(`CalledNum`, `NumCalls`, `uniqueCalls`, `dur1sec`, `dur30sec`, `dur60sec`, `dur90sec`, `dur120sec`, 
    `grossIncome`, `cogs`, `split`, `netIncome`, `nipuc`, `CallDate`) VALUES ($Opos0, $Opos1, $Opos2, $Opos3, $Opos4, $Opos5, $Opos6, 
    $Opos7, $Opos8, $Opos9, $Opos10, $Opos11, $Opos12, $Opos13)";
    $result = $connect->query($query);
    if($result == false){
        echo "<p>Error entering data into num_data!</p>";
        echo "<pre>", print_r( $query ), "</pre>";
        die(mysqli_error($connect));
    }
    $k2 = 0;
}   //Parse numArr info into INSERT command.`

Don't use mysql_ functions. Use PDO or MySQLi and use prepared statements. — AbraCadaver
– AbraCadaver, Commented Jun 16, 2015 at 20:13
Using MySQLi in the program. Thanks for pointing out the copy/paste typo — Surilan
– Surilan, Commented Jun 16, 2015 at 20:17
@visevo Not sure which errors are being thrown, as I'm checking with all I know how to: if(!($result = mysqli->query($query))). My best guess would be incompatible data types between the PHP and database. — Surilan
– Surilan, Commented Jun 16, 2015 at 20:48
Look at the query that you're trying to execute. Right before mysqli->query($query))) do this: echo "<pre>", print_r( $query ), "</pre>"; die(); and look at the results. There's probably missing data. — visevo
– visevo, Commented Jun 16, 2015 at 20:50

MKoosej · Accepted Answer · 2015-06-16 21:33:16Z

1

I would use serialize function and store the result as a BLOB field. You can unserialize to get the data back while fetching.

answered Jun 16, 2015 at 21:33

MKoosej

3,7063 gold badges24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2226755 · Accepted Answer · 2015-06-16 22:48:41Z

0

Ok, finally able to pinpoint errors and figure out an answer to my question/problem.

$Opos0 = $connect->real_escape_string($outputArr[0]);
$Opos1 = (int)($outputArr[1]);
$Opos2 = (int)($outputArr[2]);
$Opos3 = (int)($outputArr[3]);
$Opos4 = (int)($outputArr[4]);
$Opos5 = (int)($outputArr[5]);
$Opos6 = (int)($outputArr[6]);
$Opos7 = (int)($outputArr[7]);
$Opos8 = (float)($outputArr[8]);
$Opos9 = (float)($outputArr[9]);
$Opos10 = (float)($outputArr[10]);
$Opos11 = (float)($outputArr[11]);
$Opos12 = (float)($outputArr[12]);
$Opos13 = $connect->real_escape_string(($outputArr[13]));

$query = "INSERT INTO `num_data`(`CalledNum`, `NumCalls`, `uniqueCalls`, `dur1sec`, `dur30sec`, `dur60sec`, `dur90sec`, `dur120sec`, 
    `grossIncome`, `cogs`, `split`, `netIncome`, `nipuc`, `CallDate`) VALUES ('$Opos0', $Opos1, $Opos2, $Opos3, $Opos4, $Opos5, $Opos6, 
    $Opos7, $Opos8, $Opos9, $Opos10, $Opos11, $Opos12, '$Opos13')";
$result = $connect->query($query);
if($result == false){
    echo "<p>Error entering data into num_data!</p>";
    echo "<pre>", print_r( $query ), "</pre>";
    die(mysqli_error($connect));
}

Doesn't feel very pretty, but it works.

edited Jun 16, 2015 at 22:48

user2226755

13.3k6 gold badges55 silver badges77 bronze badges

answered Jun 16, 2015 at 22:14

Surilan

931 silver badge11 bronze badges

2 Comments

MKoosej Over a year ago

If you're data is not really heavy why not mix all of them in one array and save it in one field?? you have to save it in binary anyway

gview Over a year ago

If you use the prepare, bind, execute methodology which utilizes parameters and binds the variables to them, you won't have to concern yourself with escaping values.

Collectives™ on Stack Overflow

Enter mixed data into MySQL database from array using PHP

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related