[SOLVED] INSERT not inserting all data

[gary_rip]

Hello,

 

I have completed a limited search but i am unsure of the search term so look for a quick pointer.

 

I have 200 rows of data. When i INSERT this into mysql database using PHP only 184 rows are inserted.

 

I have a feeling that this is because there are "illegal" characters in what i am asking it to insert.

 

Is there some code that i can put into my PHP or mysql so that it can ignore any illegal data?

 

It might not even be illegal data? In which case could it be Null data? As you can see i am really unsure of my terminology!

 

I welcome any help!

 

Thanks

 

Gary

[Mchl]

Is it a single insert query or 200 separate queries?

If the latter, try echoing mysql_error to see why some of them fail.

[PFMaBiSmAd]

So, are you escaping string data using mysql_real_escape_string so that special sql characters in it won't break your queries?

[gary_rip]

This is what i have...

 

$con = mysql_connect("localhost","xxxxx","xxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create table
mysql_select_db("xxxxx_12345", $con);

$sqldelete = "DROP TABLE table1";
mysql_query($sqldelete,$con);

$sql = "CREATE TABLE table1
(
hotID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(hotID),
title text(10000),
deal_link text(10000),
mobile_deal_link text(10000),
submit_time text(10000),
hot_time text(10000),
temperature text(10000)
)";

// Execute query
mysql_query($sql,$con);

for ($i=0; $i<=199; $i=$i+1)
{
mysql_query("INSERT INTO tophotdeals (title, deal_link, mobile_deal_link, submit_time, hot_time, temperature)
VALUES ('{$title[1][$i]}', '{$deal_link[1][$i]}', '{$mobile_deal_link[1][$i]}', '{$submit_time[1][$i]}', '{$hot_time[1][$i]}', '{$temperature[1][$i]}')");
}

mysql_close($con);

 

As you can see... very basic. I have changed it to text (10000) as i was just trying to get it to work. I will try the suggestions above.

[gizmola]

I don't see an issue with the code.  I actually suspect that you might be running out of execution time.  Furthermore, for a bulk query like this, it's a lot more efficient to build one giant insert rather than doing 200 queries. 

 

This is set in the php.ini via the max_execution_time = directive, and defaults to 30 seconds.

 

You can try overridding the default with

 

set_time_limit(120) ; 

 

at the top of your script, to see quickly if that fixes your issue.

[gary_rip]

I don't see an issue with the code.  I actually suspect that you might be running out of execution time.  Furthermore, for a bulk query like this, it's a lot more efficient to build one giant insert rather than doing 200 queries. 

 

This is set in the php.ini via the max_execution_time = directive, and defaults to 30 seconds.

 

You can try overridding the default with

 

set_time_limit(120) ; 

 

at the top of your script, to see quickly if that fixes your issue.

 

I have tried the set_time_limit(120); and this has not worked.

 

Although there is 200 rows with 5 columns there isn't much data and it takes only about 2 seconds to complete.

[gizmola]

Check your error log then.  The only other thing I can think is that your array does not in fact have 200 elements.

[gevensen]

or add a echo count of your array so you can see quickly if the array is short as suggested

 

for example

 

before the sql $count=1;

 

 

in the while

 

echo $count."<br />;

$count++;

[gary_rip]

So, are you escaping string data using mysql_real_escape_string so that special sql characters in it won't break your queries?

 

Having a look the ones that are missing contain '

 

So it would seem that i do need to add mysql_real_escape_string

 

Im having trouble working out where i would need to place this as all of the examples seem to be using this with form data. Could anyone point me in the right direction?

 

Thanks for all your help!

[PFMaBiSmAd]

For more than one or two pieces of data, I would use sprintf as demonstrated at this link - http://www.phpfreaks.com/forums/index.php/topic,273584.msg1292539.html#msg1292539

[gary_rip]

Using your example i have done this...

 

// Execute query
mysql_query($sql,$con);

for ($i=0; $i<=199; $i=$i+1)
{
mysql_query("INSERT INTO tophotdeals (title, deal_link, mobile_deal_link, submit_time, hot_time, temperature)
VALUES ('%s','%s','%s','%s','%s','%s')";

$query = sprintf($sql,

mysql_real_escape_string('{$title[1][$i]}'),
mysql_real_escape_string('{$deal_link[1][$i]}'),
mysql_real_escape_string('{$mobile_deal_link[1][$i]}'),
mysql_real_escape_string('{$submit_time[1][$i]}'),
mysql_real_escape_string('{$hot_time[1][$i]}'),
mysql_real_escape_string('{$temperature[1][$i]}'));
}

mysql_close($con);

 

But it is showing error...

 

Parse error: syntax error, unexpected ';' in /home/nawaj/public_html/hukd/top/top.php on line 82

 

 

Where am i going wroong? If i remove the : then i get a T_String error on 84?

[PFMaBiSmAd]

In order to put the result of the sprintf() into the mysql_query, the mysql_query would need to come after the springf() -

 

$sql = "INSERT INTO tophotdeals (title, deal_link, mobile_deal_link, submit_time, hot_time, temperature)
VALUES ('%s','%s','%s','%s','%s','%s')";

$query = sprintf($sql,

mysql_real_escape_string($title[1][$i]),
mysql_real_escape_string($deal_link[1][$i]),
mysql_real_escape_string($mobile_deal_link[1][$i]),
mysql_real_escape_string($submit_time[1][$i]),
mysql_real_escape_string($hot_time[1][$i]),
mysql_real_escape_string($temperature[1][$i]));

mysql_query($query);

 

The syntax error you were getting was because the closing ) was missing on the mysql_query(... statement.

[gary_rip]

In order to put the result of the sprintf() into the mysql_query, the mysql_query would need to come after the springf() -

 

$sql = "INSERT INTO tophotdeals (title, deal_link, mobile_deal_link, submit_time, hot_time, temperature)
VALUES ('%s','%s','%s','%s','%s','%s')";

$query = sprintf($sql,

mysql_real_escape_string($title[1][$i]),
mysql_real_escape_string($deal_link[1][$i]),
mysql_real_escape_string($mobile_deal_link[1][$i]),
mysql_real_escape_string($submit_time[1][$i]),
mysql_real_escape_string($hot_time[1][$i]),
mysql_real_escape_string($temperature[1][$i]));

mysql_query($query);

 

The syntax error you were getting was because the closing ) was missing on the mysql_query(... statement.

 

Brill... showed me the correct way and explained it so i fully understand why.

 

I really do appreciate your help! All working now as i desired!