ok, i zipped the file and uploaded it:

http://www.driveway.com/g9v9x5q1c1

thanks for looking, it's appreciated 

Curiously I could not match the characters (viewed in hex characters etc.) in that text for anything, finally I had to add a separator character in for the preg_match_all function and then remove it from the resulting array after the operation.  It's a kludge but it's the method that worked for me:

<pre>
<?php
$sourcestring=file_get_contents('xbackup_2008-07-12.txt');
$sourcestring=preg_replace('/\n\n--\n--\x20Table\x20structure\x20for\x20table\x20/s',chr(1).'\0',$sourcestring);
preg_match_all('/\x1[^\x1]+/',$sourcestring,$matches);
function removechr1($match){
  return preg_replace('/\x1/','',$match);
}
$matches=array_map('removechr1',$matches);
echo count($matches[0]).' matches found.<hr>';
echo print_r($matches,true);
?>

It did seem very strange, when the pattern you gave me worked perfectly on everything else written in the same structure. Would it have anything to do with the fact it was extracted from a compressed archive (gzipped)?

The modified pattern works brilliantly, thank you for your help.

 
I have just one more thing to ask. I slightly modified the pattern you initially posted:

preg_match_all('/-- Table structure for table .*?(?=-- Table structure for table |$)/si', $contents, $matches);

to this:

preg_match_all('/-- dumping data for table .*?(?=-- Table structure for table |$)/si', $contents, $matches);
 

...so that i could extract just the insert statements from the file. I think i understand it to say "match from the beginning of 'dumping data' and grab everything until you encounter 'table structure' comments". It worked perfectly, but i'm wondering how to incorporate this into the hex pattern?

That last regex looks fine for your purpose, if it doesn't work for me please let me know.

the pattern:

/-- dumping data for table .*?(?=-- Table structure for table |$)/si

works fine until it comes across one of those information tables, then dies a mysterious death. I was trying to achieve the same results using the hex pattern you provided. I modified it a bit:

 
            $contents = preg_replace('/\x20Dumping\x20Data\x20for\x20table\x20/si', chr(1).'\0', $contents);
            preg_match_all('/\x1[^\x1]+(?=-- Table structure for table |$)/si',$contents, $matches);
            $matches = array_map(array($this, 'removechr1'),$matches);

That seems to get just the inserts, including the information tables (and beyond)!

Thanks for all the help over the past couple of days :) 

Final question, i promise!

How can i make it use "UNLOCK TABLES;" instead of the "--table structure" comment as the end of the marker for the insert statements match? I've tried:

            $contents = preg_replace('/\x20Dumping\x20Data\x20for\x20table\x20/si', chr(1).'\0', $contents);
            preg_match_all('/\x1[^\x1]+(?=unlock tables;-- |$)/si',$contents, $matches);
 

but that seems to fudge everything up (nothing is matched)? The only reason i ask is that the last match (matching up to the presence of a "--table structure comment") in the array has a lot of sql comments like:

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

i'm not sure i'd like these in there, as they are referring to variables set at the beginning of the dump file. 

This is what that text string looks like in regex notation:

UNLOCK\x20TABLES;\n\n--\n

To get a text sample, I copied a section from the file in notepad then pasted it into a new notepad text file

called sample.txt, then I processed it with the following script.  It might prove useful if you need to see the

regex notation of text in the future:

<?php
$sourcestring=file_get_contents('sample.txt');
preg_match_all('/./s',$sourcestring,$sourcearray);
  for ($i = 0; $i < count($sourcearray[0]); $i++)
   {
    $ascii=ord($sourcearray[0][$i]);
    if (($ascii<33) or ($ascii==92) or ($ascii>126))
     {
      $result.='<font color=gray>\\';
      switch($ascii)
       {
        case(0): case(1): case(2): case(3): case(4): case(5): case(6): case(7): case(8):
        {
           $result.='x0'.$ascii;
           break;
        }
        case(9): 
        {
           $result.='t';
           break;
        }
        case(10): 
        {
           $result.='n';
           break;
        }
        case(11): 
        {
           $result.='v';
           break;
        }
        case(12): 
        {
           $result.='f';
           break;
        }
        case(13): 
        {
           $result.='r';
           break;
        }
        case(92): 
        {
           $result.='\\';
           break;
        }
        default:
        {
           preg_match_all('/./s',strtoupper(dechex($ascii)),$chararray);
           $result.='x';
           for ($j = 0; $j < count($chararray[0]); $j++) {
             $result.=$chararray[0][$j];
             }
           break;
         } 
        }
       $result.='</font>';
     } else {
       $result.='<font color=blue>'.htmlentities($sourcearray[0][$i]).'</font>';
     }
   }
echo "<pre>$result";
?>