Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Reading in rows from csv and using values to query db

Codes here !

Moderators: macek, egami, gesf

Reading in rows from csv and using values to query db

Postby flowon » Fri Sep 21, 2012 7:28 am

I am new to PHP and dont really know what I am doing, but basically I am trying to read in a csv file line by line and create a variable from the line contents and then query a db with it.
Problem is, the csv file has 1000's of lines and my script is hitting a timeout...
originally it was stopping after printing about 250 lines of the table to the html page (about 30 seconds after page load) so I added:
Code: Select all
set_time_limit(0);

to the top of my php file. Then it went past the 30 second mark but times out with an IIS error

HTTP Error 500.0 - Internal Server Error
C:\PHP\php-cgi.exe - The FastCGI process exceeded configured activity timeout
Module: FastCgiModule
Notification: ExecuteRequestHandler
Handler: PHP_via_FastCGI
Error Code: 0x80070102


I thought it might be in the iis fastcgi.ini file but I can't even FIND that file on my pc in system32

I can set the php timeout to
Code: Select all
set_time_limit(60);

and it renders about twice as much as if I dont have any timeout at all, so that is definitely overriding the default of about 30 seconds.

My question then would be:
Is there a better way to script this? I wonder because if I break these two things apart (read in all CSV lines and print them to screen) and (select all items in DB table and print them to screen) into seperate php files they both render their webpage with the correct amount of lines and dont timeout at all... so is it the combination of how I am doing this?

Thanks!!

Code: Select all
<?php

// Set it to no-limit
set_time_limit(0);

// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'user', 'password');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}
//Select the database
if (!@mysql_select_db('databasename')) {
exit('<p>Unable to locate the ' .
'database at this time.</p>');
}
//print table header
print "<table id=gridtable><tr bgcolor=#ffff66><td>CSV Line</td><td>ProxyPath</td><td>ProxyName</td><td>8 characters after #</td><td>SELECT name FROM media WHERE source_name like %proxyname%</td><td>media_id</td></tr>";
$counter=1;

// open proxy list csv file
$file_handle = fopen("proxies.csv", "r");


while (!feof($file_handle) ) {
$proxypath = fgetcsv($file_handle, 1024);
$proxyname = substr($proxypath[0],65,-4);
$tapenumber = substr($proxypath[0],65,8);

//Request the name of all the media
$result = @mysql_query("SELECT hex(media_id), name FROM media WHERE source_name like '%$proxyname%'");
$num=mysql_numrows($result);
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
// print other info from CSV File
print "<tr onmouseover=this.style.backgroundColor='lightgrey'; onmouseout=this.style.backgroundColor='#ffffff';><td>".$counter . "</td><td>".$proxypath[0] . "</td><td>" . $proxyname . "</td><td>" . $tapenumber . "</td>";

// Display the results
//if more than one row returned
if ($num>'1'){
echo '<td><font color=red>' . $num . ' rows returned</td><td><font color=red>' . $num . ' rows returned</td></tr>';
$counter++;
}
//if one row returned
if ($num=='1'){
while ($row = mysql_fetch_array($result)) {
echo '<td bgcolor=#ADFFC3>' . $row['name'] . '</td><td bgcolor=#ADFFC3>' . $row['hex(media_id)'] . '</td></tr>';
$counter++;
}
}
//if less than one row returned
if ($num=='0') {
echo '<td><font color=red>No Result</td><td><font color=red>No Result</td></tr>';
$counter++;
}



}

fclose($file_handle);

echo "done!";

?>
flowon
New php-forum User
New php-forum User
 
Posts: 2
Joined: Fri Sep 21, 2012 7:10 am

Re: Reading in rows from csv and using values to query db

Postby flowon » Fri Sep 21, 2012 8:03 am

Its ok i fixed it. Needed to run this from command line:

%windir%\system32\inetsrv\appcmd.exe set config -section:system.webServer/fastCgi /[fullPath='C:\php\php-cgi.exe',arguments=''].requestTimeout:"500" /commit:apphost


%windir%\system32\inetsrv\appcmd.exe set config -section:system.webServer/fastCgi /[fullPath='C:\php\php-cgi.exe',arguments=''].activityTimeout:"500" /commit:apphost
flowon
New php-forum User
New php-forum User
 
Posts: 2
Joined: Fri Sep 21, 2012 7:10 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron