how to insert a converted array to mysql? (Php)

Question

I have converted a json data to array but now i wanted to insert it into my database i keep getting Array

here is the code

    for ($i=0; $i<=$checking; $i++) {

    $catid = $ids[$i];
    $catname = $names[$i];
    $catapps = $apps[$i];
    $caturl = $iconurls[$i];

$query = "INSERT INTO cat VALUES ('".$catid."','".mysql_real_escape_string($catname)."','".$catapps."','".mysql_real_escape_string($caturl)."')";
mysql_query($query);



};

Answer 1

which column contains 'Array'? try to print_r or var_dump $catid, $catname, $catapps and $caturl.

if you have 'Array' it's probably because at least one of them is an array. when php try to cast an Array into a String (eg when concaneting it with a string), it's transformed to the string 'Array'.

Answer 2

Compose a single query by concatenating the values. vis:
(with a bit of reorg for clarity)

 $query = "INSERT INTO cat VALUES ";

 for ($i=0; $i<=$checking; $i++) 
 {
    if ($i > 0)
        $query .= ',';
    $catid = intval($ids[$i]);
    $catname = mysql_real_escape_string($names[$i]);
    $catapps = mysql_real_escape_string($apps[$i]);
    $caturl = mysql_real_escape_string($iconurls[$i]);
    $query .= "($catid, '$catname', '$catapps', '$caturl')";
 }
 mysql_query($query);

Answer 3

I want to suggest you to use AdoDB to do a better insert/update data into MySQL. AdoDB do an automatic escaping when you insert/update using AutoExecute. Here is an usage example:

$catid = $ids[$i];
$catname = $names[$i];
$catapps = $apps[$i];
$caturl = $iconurls[$i];
//set table name
$table_name = 'cat';
//set field values
$data = array (
  'catid' => $catid,
  'catname' => $catname,
  'catapps' => $catapps,
  'caturl' => $caturl
);
//do insert
$result = $adodb->AutoExecute($table_name, $data, 'INSERT');

Please note that the key used in $data array is the column name in the cat table in MySQL, so adjust it according to the column name in your database.

If you want to insert an array into a mysql column, then I suggest you to use serialize when inserting, then when fetch the data, use unserialize before displaying or processing it.

Answer 4

Doing it with PDO and prepared statements:

try {

   // connect to the DB
   $dbh = new PDO('mysql:host=localhost;dbname=my_db','user','password');

   // prepare the query : it will be faster and safer
   $query = $dbh->prepare("INSERT INTO cat VALUES (id, name, app, url) VALUES (:id, :name, :app, :url)");


   // tell which vars will be used to fill the query
   $query->bindParam(':id', $catid);
   $query->bindParam(':name', $catname);
   $query->bindParam(':app', $catapps);
   $query->bindParam(':url', $caturl);

   // execute your stuff
   for ($i = 0; $i <= $checking; $i++) {

        $catid = $ids[$i];
        $catname = $names[$i];
        $catapps = $apps[$i];
        $caturl = $iconurls[$i];

        $stmt->execute();

   };

} catch(PDOException $e) { // handle connection errors }

$dbh = NULL;