...

View Full Version : can't insert more than 2503 values...



martynball
04-16-2012, 07:13 PM
Hey, having issues with adding more than 2503 values into a table. If I re-run the code the same values are added AGAIN so it's not the table limiting the rows.



<?php
include("php/connect.php");
$data = file("word.php");
$i = 0;

foreach($data as $val) {
$sql = "INSERT INTO dictionary (word) VALUES ('".$val."')";
mysql_query($sql);
echo $i.": ".$val."
<b>Status: ".(!mysql_query($sql, $con) ? "<span style='color:red;'>Failed!</span>" : "<span style='color:green;'>Ok!</span>")."</b><br />";
$i++;
}
?>

chunky
04-16-2012, 07:26 PM
What are you looking for? Do you want it to stop at 2503?

ALSO, you would not need i++ as the variable i does not do anything at all in this case :)

litebearer
04-16-2012, 07:31 PM
what does this display


<?php
include("php/connect.php");
$data = file("word.php");
echo count($data);
exit();

martynball
04-16-2012, 07:39 PM
What are you looking for? Do you want it to stop at 2503?

ALSO, you would not need i++ as the variable i does not do anything at all in this case :)

If I wanted it to stop I wouldn't make a thread saying that I can't inset more than 2503 values... And I added the $i so I can echo the word number... so then I know how many have added.


what does this display


<?php
include("php/connect.php");
$data = file("word.php");
echo count($data);
exit();


That produces 26878

Fou-Lu
04-16-2012, 07:49 PM
So you intend to issue 27K commands to a MySQL server?
Um, no. That's too many requests. As is you could be suffering from memory or time limitations.
Split them up into blocks of 750 and see how it does.


$aParts = array_chunk($data, 750);
$iParts = count($aParts);
$iInserted = 0;
for ($i = 0; $i < $iParts; ++$i)
{
$iWords = count($aParts[$i]);
if ($iWords > 0)
{
$sQry = 'INSERT INTO dictionary (word) VALUES ';
for ($j = 0; $j < $iWords; ++$j)
{
if ($j > 0)
{
$sQry .= ', ';
}
$sQry .= '(' . $aParts[$i][$j] . ')';
}
if (mysql_query($sQry, $con))
{
$iInserted += mysql_affected_rows($con);
}
}
}
printf("Successfully inserted %d records", $iInserted);

That will limit the number of queries to 35 instead of near 27,000. Try that.

martynball
04-16-2012, 07:59 PM
"Successfully inserted 0 records"

Dormilich
04-16-2012, 08:05 PM
esp. for that (multiple inserts) I’d use Prepared Statements.

Fou-Lu
04-16-2012, 08:07 PM
^
I'd also use a bind. But I'll assume you don't have a PDO or MySQLi library to work with and that's why you are using mysql library.
Biffed it since these are strings, not numbers. Use this for the concat in the loop:


$sQry .= "('" . $aParts[$i][$j] . "')";

Dormilich
04-16-2012, 08:10 PM
But I'll assume you don't have a PDO or MySQLi library to work with and that's why you are using mysql library.

would be a good question to verify.

(though most (new) coders donít even know about PDO/MySQLi))

martynball
04-16-2012, 08:12 PM
"Successfully inserted 8250 records", would a delay help?

Edit: It's basically just a dictionary.

Fou-Lu
04-16-2012, 08:15 PM
Enable your error reporting:


ini_set('display_errors', 1);
error_reporting(E_ALL);

Do you run out of time?
Consider adding an else to the mysql_query if as well to see if it dumps an error. I wouldn't suspect that this is caused by a forced max queries per minute, but it is possible.

martynball
04-16-2012, 08:33 PM
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

include "php/connect.php";
$data = file("word.php");
$aParts = array_chunk($data, 750);
$iParts = count($aParts);
$iInserted = 0;
for ($i = 0; $i < $iParts; ++$i)
{
$iWords = count($aParts[$i]);
if ($iWords > 0)
{
$sQry = 'INSERT INTO dictionary (word) VALUES ';
for ($j = 0; $j < $iWords; ++$j)
{
if ($j > 0)
{
$sQry .= ', ';
}
$sQry .= "('" . $aParts[$i][$j] . "')";
}
if (mysql_query($sQry, $con))
{
$iInserted += mysql_affected_rows($con);
} else {
echo mysql_error();
}
}
}
printf("Successfully inserted %d records", $iInserted);
?>


Something to do with the query :S
Error:


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd '), ('I'll '), ('I'm '), ('I've '), ('IA '), ('IBM '), ('ICC '), ('ID '), ('IE' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brien '), ('O'Connell '), ('O'Connor '), ('O'Dell '), ('O'Donnell '), ('O'Dwyer ' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('aardvark '), ('aback '), ('abacus '), ('abalone '), ('abandon '), ('abas' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('air '), ('airborne '), ('airbrush '), ('aircraft '), ('airdrop '), ('air' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd '), ('anyhow '), ('anyone '), ('anyplace '), ('anything '), ('anyway '), ('any' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('cab '), ('cabal '), ('cabana '), ('cabaret '), ('cabbage '), ('cabdriver' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('coulomb '), ('council '), ('councilman '), ('councilmen '), ('councilwom' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('die '), ('died '), ('diehard '), ('dieldrin '), ('dielectric '), ('diem ' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'er '), ('e's '), ('e.g '), ('each '), ('eager '), ('eagle '), ('ear '), ('eardru' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('fable '), ('fabric '), ('fabricate '), ('fabulous '), ('facade '), ('fac' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('gab '), ('gabardine '), ('gabbing '), ('gabble '), ('gabbro '), ('gable ' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('ha '), ('habeas '), ('haberdashery '), ('habit '), ('habitant '), ('habi' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('hasp '), ('hassle '), ('hast '), ('haste '), ('hasten '), ('hasty '), ('' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('isochronal '), ('isochronous '), ('isocline '), ('isolate '), ('isomer '' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('ma '), ('macabre '), ('macaque '), ('macaw '), ('mace '), ('macerate '),' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('mighty '), ('mignon '), ('migrant '), ('migrate '), ('migratory '), ('mi' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('musty '), ('mutagen '), ('mutandis '), ('mutant '), ('mutate '), ('mutat' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('ounce '), ('our '), ('ourselves '), ('oust '), ('out '), ('outermost '),' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('qua '), ('quack '), ('quackery '), ('quad '), ('quadrangle '), ('quadran' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('sa '), ('sabbath '), ('sabbatical '), ('sable '), ('sabotage '), ('sabra' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('shank '), ('shanty '), ('shape '), ('shard '), ('share '), ('sharecrop '' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('tab '), ('tabbing '), ('tabernacle '), ('table '), ('tableau '), ('table' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'd '), ('they'll '), ('they're '), ('they've '), ('thiamin '), ('thick '), ('thic' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's '), ('ubiquitous '), ('ubiquity '), ('ugh '), ('ugly '), ('ulcer '), ('ulcerat' at line 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't '), ('wasp '), ('waspish '), ('wast '), ('wastage '), ('waste '), ('wastebaske' at line 1Successfully inserted 8250 records

Dormilich
04-16-2012, 08:35 PM
looks like an escaping problem ('). does your host support MySQLi or PDO?

martynball
04-16-2012, 08:51 PM
I do not know, and I can't find anything in the control panel which would tell me, I use 1freehosting.com

Edit: I don't think it does:
http://www.1freehosting.com/free-website-and-hosting.html

Dormilich
04-16-2012, 08:56 PM
you can find that info in the output of the phpinfo() function.

and I wouldn’t expect it in the control panel.

other than that, simply try new PDO() or new MySQLi(). if those are not supported you get an error message saying something like "class PDO not found". (otherwise you get errors about not connecting/missing arguments/etc.)

martynball
04-16-2012, 09:03 PM
Hmm, im going to guess that my host has both?



Warning: PDO::__construct() expects at least 1 parameter, 0 given in /home/u600992017/public_html/add_words.php on line 33

Catchable fatal error: Object of class mysqli could not be converted to string in /home/u600992017/public_html/add_words.php on line 35

Dormilich
04-16-2012, 09:06 PM
that would be a correct assumption. choose the one you like more.

personally I recommend PDO because
- it can use Exceptions (nothing beats that in error handling)
- it uses PHP’s Iterators (you can loop directly over a result set without the need to convert it to an array first)
- leaner Interface

martynball
04-16-2012, 09:08 PM
I will research them both tomorrow as I have not come across either before. I have work soon.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum