Difficulty Entering Array Data Into a Database Table

Go To StackoverFlow.com

0

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
        )
)
2012-04-04 05:46
by Sammy


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.

2012-04-04 05:55
by Dmitry Reznik
Thanks Dmitriy, If this is the case how would the final MySQL command look to perform the data import into the table, referencing Versions 1 thru 5 in the above code? This way I can immediately try it to see if it works - Sammy 2012-04-04 06:08
Try using first version with $queries[0]. That way only one row would should be inserted - Dmitry Reznik 2012-04-04 06:11
As suggested with Version 1 and $queries[0] I received the "Column count doesn't match value count at row 1" erro - Sammy 2012-04-04 06:16
That means there's too much data. Please, print out the resulting query and you'll see the error immediately. Including the query itself ("INSERT INTO (.....) values (....)" - Dmitry Reznik 2012-04-04 06:17
Final $queries array content = Array ( [0] => ( a0, b0, c0, d0, e0, f0 ) ) is this what you want? Otherwise, I do not know how to do it - Sammy 2012-04-04 06:20
No, the final query is the thing i'd like to see. The one which gets passed to mysql_query function - Dmitry Reznik 2012-04-04 06:21
I do not know how to do it. What would the command be to print it out - Sammy 2012-04-04 06:22
printr ("insert into $TableName (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItem_Link) values (". implode(',', $queries[0]) . ")") - Dmitry Reznik 2012-04-04 06:23
here it is: Warning: implode() [function.implode]: Invalid arguments passed in /home/testsite/publichtml/32012/insert.php on line 88 insert into UserData (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItemLink) values ( - Sammy 2012-04-04 06:26
Invalid values are being used (basically, implode returns empty string). Try using it without implode - Dmitry Reznik 2012-04-04 06:38
how so without implode? Sorry, but I am not a seasoned MySQL programmer - Sammy 2012-04-04 06:39
I tried with and $queries and $queries[0] in the command mysqlquery("insert into $TableName (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItemLink) values (". $queries . ")") or die(mysqlerror()); but got the Column count doesn't match value count at row 1 error and printr yields: insert into UserData (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItemLink) values (( a0, b0, c0, d0, e0, f0 ) - Sammy 2012-04-04 06:45
Was that $queries or $queries[0] ? Try removing unnecessary "(" and ")" also. From the query text - Dmitry Reznik 2012-04-04 06:47
I tried with both $queries and $queries[0] and got Column count doesn't match value count at row - Sammy 2012-04-04 06:50
Okay, here's the code:

$query = "INSERT INTO UserData (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItemLink) VALUES ("; foreach ($queries[0] as $value) $query = $query.$value.", " $query = substr($query, 0, strlen($query)-2); $query = $query.")"; printr ($query - Dmitry Reznik 2012-04-04 06:54

$query = "INSERT INTO UserData (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItemLink) VALUES ("; foreach ($queries[0] as $value) $query = $query.$value.", " $query = substr($query, 0, strlen($query)-2); $query = $query.")"; printr ($query); yields the error Parse error: syntax error, unexpected T_VARIABL - Sammy 2012-04-04 07:01
Maybe semicolon is missed or something. I'm not proficient with php unfortunately. Anyway, the error "Column count doesn't match value count" you're getting means that you're passing to many or to few arguments in the query to insert the row. There should be 6 of them. You should try converting your arrays to a valid string like ("1","2","3","4","5","6") and then combine it with your query - Dmitry Reznik 2012-04-04 07:16
ok, so it got ( "a0", "b0", "c0", "d0", "e0", "f0" ) Final $queries array content = Array ( [0] => ( "a0", "b0", "c0", "d0", "e0", "f0" ) ) and still received the error: Column count doesn't match value count at row - Sammy 2012-04-04 07:27
I finially got it to work. I changed the for loop items to: "'.mysqlrealescapestring($currenttopicarray[$i]).'", and then changed the mysqlquery to: mysqlquery("INSERT INTO $TableName (Topic, PostDate, PhoneNumber, OriginalItemTitle, ModifiedItemTitle, OriginalItem_Link) VALUES ".implode(',', $sql)); Thanks, for all your help - Sammy 2012-04-04 16:48
You're welcome : - Dmitry Reznik 2012-04-04 18:13
Ads