why is mysql selectively adding only some entries and not others?

[johnyboy]

i've got quite a bit of text in the $proginfo array

this code prints it out on the screen just fine -

// ....more php code here

    foreach($proginfo[1] as $key => $val) {
    echo ($key)+1;
?>

    <textarea rows="1" cols="15"><?php print $proginfo[1][$key]; ?></textarea> 
    <textarea rows="2" cols="30"><?php print $proginfo[2][$key]; ?></textarea> 
    <textarea rows="10" cols="60"><?php print $proginfo[3][$key]; ?></textarea> <br>

<?php
        }
        
// more php code here...

the result of that above code is here
fine. great.

but using exactly the same sort of loop with exactly the same information in the array, but this time adding the data into a database table rather than outputting to screen, i end up with only 20 lines of results in the database table when infact there are 39 rows of data

this is the code i'm using to add the array data to the table -

$db = mysql_connect ("localhost", "xxxxxxx", "xxxxxxx") or die ('I cannot connect to the database.');
mysql_select_db("xxxxxxxxxxxx",$db);


foreach($proginfo[1] as $key => $val) {

$sql="INSERT INTO $tablename (gmttime,programme,description) VALUES ('{$proginfo[1][$key]}','{$proginfo[2][$key]}','{$proginfo[3][$key]}')";

$result = mysql_query($sql);

} 

for instance, rows 1,2,3 (refering to the numbers from the above page link) get added fine, but then rows 4,5,6,7 dissappear, then row 8 gets included in the database but then row 9 gets missed out. then 10, 11, 12 get included and so on. so nearly half of the rows get missed out.

the actual data itself came originally from an html document, had the html stripped out of it with strip_tags then a regular expression to split it up into the array.

this is the table description incase that helps -

mysql_query("CREATE TABLE $tablename (
 id tinyint(3) unsigned NOT NULL auto_increment,
  gmttime varchar(6) NOT NULL default '',
  programme varchar(60) NOT NULL default '',
  description longblob NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;"); 

any ideas why some rows are getting missed and others aren't?

[johnyboy]

anyone? (bump and pleading pathetically )

i think i've made it look more complicated than it is because i've given quite a few details to try and cover everything. here's the short, to the point version -

can anyone think of any reason why this data won't go into a database table - all the rows? - it misses out some rows irratically.

i'm using exactly the same loop as i used in my example that i just linked to (but with 'enter into database' rather than 'echo to screen' in the loop) with exactly the same array, and exactly the same data in the array and it irratically misses out rows from the array in the database. why?

the columns are a tinyint autoincrement for an id number then two varchar columns then a longblob for the last column as the text that goes into the last column is sometimes quite long.

[samsm]

Make sure everything is getting escaped. use addslashes on everything:

$proginfo[1][$key] = addslashes($proginfo[1][$key]);
$proginfo[2][$key] = addslashes($proginfo[2][$key]);
$proginfo[3][$key] = addslashes($proginfo[3][$key]); 

If that does it, than that was it. I sort of doubt that would be it because you would be getting a sea of error messages if it was a quote issue. Still, worth 30 seconds of copy and pasting to elliminate it.

<editted out an error in the php>

[johnyboy]

yup definetely worth a go, but no that wasn't it, but thanks for the suggestion

[samsm]

How about adding echo($sql); just after each time sql is assigned so we can see if 39 statements are being created and examine the ones that aren't working. Problem may become evident then. You might need to add some formatting in to make it readable.

[johnyboy]

this any use?

and this is the direct result of the above mysql entry

[samsm]

Common thread (I think): all the entries that are not getting inserted have quotes in them. All the entries that are successfully being inserted do not. Try addslashes again, inside the loop, before $sql is assigned.

[johnyboy]

Bingo!

thanks again sam