Tag Archives: PHP

Lost connection to MySQL server during query rows

Posted on by
Reply

While working on a project today a script I had that turns a database into a simple tab delimited file stopped working and was producing the following error:

Lost connection to MySQL server during query rows

Google searching didn’t turn up anything useful because most people said just to “use mysqldump instead” which wasn’t really an option for me. After running analyze, repair and optimize table and using phpMyAdmin to make a copy of the table I was pretty confident that it wasn’t a corruption problem so I added the following bit of code around what I already had:

 

define(‘C_NUM_PER_LIMIT’, 100000);

$iteration = 0;

do
{
$limit_from = C_NUM_PER_LIMIT * $iteration; $iteration++;

$sql = “SELECT * FROM `table` ORDER BY `id` LIMIT $limit_from, ” . C_NUM_PER_LIMIT;
$res = mysql_query($sql);
// way more stuff

} while (mysql_num_rows($res) > 0);

I hope this helps. :)

Working with CSV files in PHP

Posted on by
Reply

This is the best and simplest way to work with a CSV file that has the headers on the first row in PHP.


if (($handle = fopen('csv.csv', 'r')) !== false)
{
$length = 0;
$delimiter = ',';
$enclosure = '"';
$escape = '\\';

if(($header_line = fgetcsv($handle, $length, $delimiter, $enclosure, $escape)) !== false)
{
while(($line = fgetcsv($handle, $length, $delimiter, $enclosure, $escape)) !== false)
{
$data = array_combine($header_line, $line);
//do stuff here
}
}

fclose($handle);
}

Got a packet bigger than ‘max_allowed_packet’ bytes

Posted on by
Reply

Ran in to this problem today at work so I’m just making a note of it for future reference. I was running a PHP script crawler today that was inserting the downloaded data into a MySQL database and the script crash with an error:

Got a packet bigger than ‘max_allowed_packet’ bytes

The simple fix was to add in the code:

$sql = "SET GLOBAL max_allowed_packet=16*1024*1024;"; // 16 * 1 MB
mysql_query($sql);

Hope this helps someone else as well. :)

curl function

Posted on by
Reply

My plan for 2011 is to mix in more programming related posts. I figure the best place to start is a powerful PHP function that I wrote that uses curl and has helped me many many times.

function grab($url, $postparams = '', $cookiefilepath = '', $referer = '')
{
  sleep(2);

  $postheaders = array();

  $url_parts = parse_url($url);

  $postheaders[] = 'Host: ' . $url_parts['host'];
  $postheaders[] = 'User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; ' .
    'rv:1.8.1.9) Gecko/20071025 Firefox/2.0.0.9';
  $postheaders[] = 'Accept: text/xml,application/xml,application/xhtml+xml,text/' .
    'html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5';
  $postheaders[] = 'Accept-Language: en-us,en;q=0.5';
  $postheaders[] = 'Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7';
  $postheaders[] = 'Keep-Alive: 300';
  $postheaders[] = 'Connection: keep-alive';

  if($referer != '')
  {
    $postheaders[] = "Referer: $referer";
  }

  if($postparams != '')
  {
    $postheaders[] = 'Content-Type: application/x-www-form-urlencoded';
    $postheaders[] = "Content-length: " . strlen($postparams);
  }

  $ch = curl_init();
  curl_setopt($ch, CURLOPT_HTTP_VERSION, 'CURL_HTTP_VERSION_1_1');
  curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 15);
  curl_setopt($ch, CURLOPT_HTTPHEADER, $postheaders);  // $headers

  if($postparams != '')
  {
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $postparams);
  }

  curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
  curl_setopt($ch, CURLOPT_URL, $url);

  if ($cookiefilepath != '')
  {
    curl_setopt($ch, CURLOPT_COOKIEFILE, $cookiefilepath);
    curl_setopt($ch, CURLOPT_COOKIEJAR, $cookiefilepath);
  }
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

  $got_page = curl_exec($ch);
  $headers = curl_getinfo($ch);
  $error = curl_error($ch);

  curl_close($ch);

  $retval = array('result' => $got_page, 'headers' => $headers, 'error' => $error);

  return $retval;
}

This function is great for writing crawlers as you can sent over POST variables, it can pass over and store cookies in a cookiejar and passes over a “User-Agent” header so site you’re connecting to can’t tell that you’re aren’t really a browser and a referrer so you can really mimic regular page navigation :) .

MongoDB / PHP problem

Posted on by
1

While at work I was looking into something that we could replace MySQL with since the two trends these days are to not do any JOINS between tables and to work with large datasets. I came across a database called MongoDB which was apparently very good at both of these things and used guides on the internet to get it going on a local Linux box.

I got to a point where it was almost working 100% but for some reason my simple test script couldn’t connect when run by Apache but could when run via command line. I would get the error:

PHP Fatal error: Uncaught exception 'MongoConnectionException' with message 'Permission denied'

I scoured the internet and found only a single reference to my problem but couldn’t find a solution. After direct emails with Kristina Chodorow the primary maintainer for the MongoDB Perl and PHP drivers I found that the solution to my problem was to run the command:

$ /usr/sbin/setsebool -P httpd_can_network_connect 1

Hopefully this helps someone else. Thanks again Kristina :D .