/    Sign up×
Community /Pin to ProfileBookmark

is there a limit on mysql queries for php

Copy linkTweet thisAlerts:
Mar 04.2007

i’m trying to upload a csv file with 6000+ rows, each row having 6 columns.

when each line is processed the data is stored in different tables. some columns need a bit of referencing from other database tables to get a converted result before being inserted into the database.

howevwer, whenever i reach around row 500, i start getting a “Couldn’t fetch mysqli in” error. the strange thing is that this select that references a table after a certain input is read from the csv file, is used in a loop that went through the 400+ times before it.

if i reduce the rows of the csv file to 450 entries, the processing is ok.

another strange thing is that i have uploaded another csv file just before this with more lines, give or take 3mb in size. and there were no hitches, only difference is that the data is directly inserted into the database once it is read from the file. unlike the problematic one where the troubles always happens in the same loop that references a file to convert a number read from the file.

last thing i noted is that the error always happens in the same function, but never in the same entry, for example, in run 1, itll occur at row 491, in run 2, at row 522, and so on.

hope someone can help and point out what is happening, because i’m out of ideas at this point.

thanks.

to post a comment
PHP

2 Comments(s) ↴

Copy linkTweet thisAlerts:
@michael879Mar 04.2007 — it good be a memory issue. You said you were storing all the values into memory and THEN inserting them into the database? While MySQL has no real limit (except the harddrive capacity), the server's memory does. Can you post some code? Maybe the problem is somewhere else.
Copy linkTweet thisAlerts:
@arzooauthorMar 07.2007 — btw, i'm also having speed issues. it takes quite a long time around 30 mins to go through 6000+ employees. am open to optimization suggestions.

here's the code:

[code=php]

if($_SERVER['REQUEST_METHOD'] == "POST")
{
$rewardsdb = getdb();
$today = date("Y-m-d H:i:s");

$somename = basename($_FILES['targetfile']['name']);
$uploadfile = $_FILES['targetfile']['tmp_name'];
$target_path = getUploadPath() . $somename;
move_uploaded_file($uploadfile, $target_path);

$fcontents = file($target_path);
$num = 1;
$weight = 1;
$totalWeight = 0;
$ratingArray = array();

foreach($fcontents as $line)
{
if ($num == 1)
{
$data = explode("t", $line);
$numCols = count($data);

if (strtoupper($data[0]) == "TIN NUMBER")
{
//column 5 is the start of the ratings
for ($i = 4; $i < $numCols; $i++)
{
$ef_id = getEmpFactorIdFromName(trim($data[$i]));
$contents .= "$data[$i] == $ef_id<br>rn";
$ratingArray[$i] = $ef_id;
}

$del = "DELETE FROM rew_employee_ratings";
$rewardsdb->query($del);
$del2 = "DELETE FROM rew_employees";
$rewardsdb->query($del2);
$del3 = "DELETE FROM system_var WHERE variable LIKE 'empe_%'";
$rewardsdb->query($del2);

$qry = "INSERT INTO system_var VALUES ('empe_last', '$today')";
$rewardsdb->query($qry);
$qry = "INSERT INTO system_var VALUES ('empe_user', '$user')";
$rewardsdb->query($qry);
$qry = "INSERT INTO system_var VALUES ('empe_file', '$somename')";
$rewardsdb->query($qry);
$message = "Individual Evaluation File uploaded.";
}
else
{
$message = "Wrong individual evaluation file format.";
break;
}
}
else
{
$data = explode("t", $line);
$numCols = count($data);
$tin = $data[0];
$lname = $data[1];
$lname = trim($lname, """);
$lname = addslashes($lname);
$fname = $data[2];
$fname = trim($fname, """);
$fname = addslashes($fname);
$mi = trim($data[3]);
$qry = "INSERT INTO rew_employees (tin_num, last_name, first_name, middle_init)
VALUES ($tin, '$lname', '$fname', '$mi')";
echo $qry . "<br>";
$contents .= "$qry<br>rn";
$rewardsdb->query($qry);

//column 5 is the start of the ratings
for ($i = 4; $i < $numCols; $i++)
{
echo $ratingArray[$i] . "-" . trim($data[$i]) . ":<br>";
$factor = getEmpCategoryFactor($ratingArray[$i], trim($data[$i]));
$qry = "INSERT INTO rew_employee_ratings VALUES ($tin, $ratingArray[$i], $factor)";
echo $qry . "<br>";
$contents .= "&rArr;$qry<br>rn";
$rewardsdb->query($qry);
$weight *= $factor;
}

//get rating weight
$qryWeight = "UPDATE rew_employees SET weight = $weight WHERE tin_num = $tin";
$rewardsdb->query($qryWeight);

$totalWeight += $weight;
$weight = 1;
}
$num++;
}

$contents .= <<<EOT
<strong>$message</strong>
EOT;
}
else
{
$updated = getLastUpdated("empe");
$contents .= <<<EOT
<h2> Upload Employee Ratings </h2>
<hr>
File last uploaded on <b>{$updated[1]}</b> by <b>{$updated[2]}</b>. <br>
Filename uploaded: <b>{$updated[0]}</b>
<form enctype="multipart/form-data" action="uploadEmpData.php" method="POST">
File name: <input name="targetfile" type="file" />
<br><br>
<input type="submit" value="Upload" />
</form>
EOT;
}

[/code]


the function getempcategoryfactor is listed below: (this gets the rating criteria)

[code=php]

function getEmpCategoryFactor($ef_id, $category)
{
$db = getdb();
if (empFactorHasRange($ef_id))
$query = "SELECT factor FROM rew_employee_values WHERE category = floor($category) AND ef_id = $ef_id";
else
$query = "SELECT factor FROM rew_employee_values WHERE UPPER(category) = UPPER('$category') AND ef_id = $ef_id";
$result = $db->query($query);
echo $query . "<br>";
$row = $result->fetch_assoc();
mysqli_free_result($result);
return $row["factor"];
}

[/code]

the part that causes the strange error is empfactorhasrange, shown below. this is used because some factors base their score on a hi-low range as opposed to others that are fixed.

for ex. in sales, an annual sales of 700,000-1,000,000 gives an employee a rating of 10, 500,000-699,000 a 9 rating, and so on... this is for ranged.

for others, its fixed like, a manager gets a 5 under the position criteria, a clerk gets a 3, and so on.

[code=php]

function empFactorHasRange($ef_id)
{
$db = getdb();
$qry = "SELECT ranged_yn FROM rew_employee_factors WHERE ef_id = $ef_id";
$result = $db->query($qry);
echo $qry . "<br>";
$row = $result->fetch_assoc();
mysqli_free_result($result);
if ($row["ranged_yn"] == 1)
return true;
else
return false;
}

[/code]

thanks again.
×

Success!

Help @arzoo spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.5,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...