php push 2d array into mysql

Question

Hay All, I cant seem to get my head around this dispite the number to examples i read. Basically I have a 2d array and want to insert it into MySQL. The array contains a few strings.

I cant get the following to work...

$value = addslashes(serialize($temp3));//temp3 is my 2d array, do i need to use keys? (i am not at the moment)
$query = "INSERT INTO table sip (id,keyword,data,flags) VALUES(\"$value\")"; 
mysql_query($query) or die("Failed Query");

Thanks Guys,

Answer 1

Not sure it's be a full answer to your question, but here at least a couple of possible problems :

• You should not use addslashes ; instead, use mysql_real_escape_string
  • It knows about the things that are specific to your database engine.
• In your SQL query, you should not use double-quotes (") arround string-values, but single-quotes (')
• In your SQL query, you should have as many fields in the values() section as you have in the list of fields :
  • Here, you have 4 fields : id,keyword,data,flags
  • but only one value : VALUES(\"$value\")
• You should use mysql_error() to know what was the precise error you've gotten while executing the SQL query
  • This will help you find out the problems in your queries ;-)

Answer 2

<?php

// let's assume we have a 2D array like this:
$temp3 = array(
    array(
        'some keywords',
        'sme data',
        'some flags',
    ),
    array(
        'some keywords',
        'sme data',
        'some flags',
    ),
    array(
        //...
    ),
);

// let's generate an appropriate string for insertion query
$aValues = array();
foreach ($temp3 as $aRow) {
    $aValues[] = "'" . implode("','", $aRow) . "'";
}
$sValues = "(" . implode("), (", $aValues) . ")";

// Now the $sValues should be something like this
$sValues = "('some keywords','some data', 'someflags'), ('some keywords','some data', 'someflags'), (...)";

// Now let's INSERT it.
$sQuery = "insert into `my_table` (`keywords`, `data`, `flags`) values $sValues";
mysql_query($sQuery);

Answer 3

As an addition to the useful answers already given, if you have a big table that you need to insert it might not fit in one SQL statement. However, making a separate transaction for each row is also slow. In that case, we can tell MySQL to process multiple statements in one transaction, which will speed up the insertion greatly for big tables (>1000 rows).

An example:

<?php
function dologin() {
    $db_username    =   'root';
    $db_password    =   'root';
    $db_hostname    =   'localhost';
    $db_database    =   'logex_test';

    mysql_connect($db_hostname, $db_username, $db_password);
    mysql_select_db($db_database);  
}

function doquery($query) {
    if (!mysql_query($query)) {
        echo $query.'<br><br>';
        die(mysql_error());
    }
}

function docreate() {
    doquery("drop table if exists mytable");
    doquery("create table mytable(column1 integer, column2 integer, column3 integer)");
}

function main() {
    $temp3 = array(
        array('1','2','3',),
        array('4','5','6',),
        array('7','8','9',),
    );

    dologin();
    docreate();
    doquery("start transaction");
    foreach($temp3 as $row)
        doquery("insert into mytable values('" . implode("','", $row) . "')");
    doquery("commit") or die(mysql_error());
}

main();
?>