Difficulty Entering Array Data Into a Database Table

Question

Difficulty Entering Array Data Into a Database Table

I wonder if anyone could point out what is wrong with the following script. I am having a difficulty entering Array data into a database table. The full code is stated below with the results of the final array that should be entered into the database table printed out in print_r() format.

Everything appears to function properly as far as gathering the content for the 6 data arrays and creating the MySQL database table. The difficulty is the last section of the code where the data is to be entered into the database table.

The bottom of the script contains 5 different version of MySQL commands that were offered up by other contributors and site users that should have entered the data into the database table. I tried each version separately and all 5 versions failed to work. The comments next to the version labels state the mode of failure.

Any help would be greatly appreciated.

<?php

// Data Arrays (6)
     $current_topic_array =       array();
     $post_date_array =           array();
     $phone_array =               array();
     $item_title_original_array = array();
     $item_title_array =          array();
     $item_link_original_array =  array();

// Enter Data into the Data Arrays.
// This can be done either by local loop as shown here, or gathered from an earlier part of the routine

   for($c = 0; $c < 3; $c++) {              // eventually the maximum value shown here, 3, will be a $variable
     $current_topic_array[] =       'a'.$c; // text string - Topic Section
     $post_date_array[] =           'b'.$c; // text string - Article Post Date as text string i.e. 03-30-2012
     $phone_array[] =               'c'.$c; // text string - Author's Contact Phone number as text string i.e. 888-555-1212
     $item_title_original_array[] = 'd'.$c; // text string - Original Article Title
     $item_title_array[] =          'e'.$c; // text string - Modified Article Title (shortened)
     $item_link_original_array[] =  'f'.$c; // text string - Author's website URL i.e. http://www...
   }

// MySQL Database Connection -  Authentication: user, password
     $TableName = 'User_Data';
     $dbh = mysql_connect ("localhost", "user", "password")
     or die ('Cannot connect to the database because: ' . mysql_error());
     mysql_select_db ("database");

// Creates a Database Table only if the Table does not already exist
     if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$TableName."'")) != 1) {
        mysql_query("CREATE TABLE $TableName(
          id INT NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (id),
          Topic                varchar(300)  NOT NULL default '',
          Post_Date            varchar(100)  NOT NULL default '',
          Phone_Number         varchar(100)  NOT NULL default '',
          Original_Item_Title  varchar(2000) NOT NULL default '',
          Modified_Item_Title  varchar(2000) NOT NULL default '',
          Original_Item_Link   varchar(2000) NOT NULL default '') ") or die(mysql_error()
        );
     }

// Combine all 6 Data Array into one array
     $queries = array(); 
     for($i = 0; $i < count($current_topic_array); $i++) {
        $queries[] = '(
           '.$current_topic_array[$i].',
           '.$post_date_array[$i].',
           '.$phone_array[$i].',
           '.$item_title_original_array[$i].',
           '.$item_title_array[$i].',
           '.$item_link_original_array[$i].'
        )'; 

        // Check - $queries array item &i
           echo '<span style="color: #008000;">'.$queries[$i].'</span>';
     }

// Check - Final $queries array content
     echo '<hr>Final $queries array content =';
     echo '<pre>';
     print_r($queries);
     echo '</pre>';
     echo '<hr>';

// Enter the $queries Data into the MySQL Database Table 

// Version 1 - Failed: Column count doesn't match value count at row 1
    // mysql_query("insert into $TableName (Topic, Post_Date, Phone_Number, Original_Item_Title, Modified_Item_Title, Original_Item_Link)
    // values (". implode(',', $queries) . ")") or die(mysql_error());

// Version 2 - Failed: Column count doesn't match value count at row 1
    // mysql_query("insert into $TableName (Topic, Post_Date, Phone_Number, Original_Item_Title, Modified_Item_Title, Original_Item_Link)
    // values ('". implode("','", $queries) . "')") or die(mysql_error());

// Version 3 - Failed - No Error message and No Data imported into Table
    // $query = mysql_query("insert into $TableName (Topic, Post_Date, Phone_Number, Original_Item_Title, Modified_Item_Title, Original_Item_Link) 
    // values ('. implode(',', $queries)') or die(mysql_error()");

// Version 4 - Failed - No Error message and No Data imported into Table
    // $query = "insert into User_Data(Topic, Post_Date, Phone_Number, Original_Item_Title, Modified_Item_Title, Original_Item_Link)
    // values " . implode(", ", $queries);

// Version 5 - Failed - No Error message and No Data imported into Table
     $query = "insert into (Topic, Post_Date, Phone_Number, Original_Item_Title, Modified_Item_Title, Original_Item_Link)
     values " . implode(", ", $queries);

?>

$queries[$i] items:

( a0, b0, c0, d0, e0, f0 )( a1, b1, c1, d1, e1, f1 )( a2, b2, c2, d2, e2, f2 )

Final $queries array content:

Array
(
    [0] => (
           a0,
           b0,
           c0,
           d0,
           e0,
           f0
        )
    [1] => (
           a1,
           b1,
           c1,
           d1,
           e1,
           f1
        )
    [2] => (
           a2,
           b2,
           c2,
           d2,
           e2,
           f2
        )
)

Answer 1

As per MySql reference:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

That's the way to go inserting many rows at once. Please notice the ',' between values. Though it would be much easier and safer inserting data row by row using for-loop and taking i-th row from the queries array.

Also, I'd like to point out that currently the code is prone to SQL injection attacks since it manipulates strings and has no sql-checking.