how to cut and paste from sql

[qxygene]

i have an old sql file from phpbb lines are like this;

 

INSERT INTO `phpbb_users` VALUES (702, 1, 'Harley Beau', '827ccb0eea8a706c4c34a16891f84e7b', 0, 0, 0, 1185428727, 0, 0, -12.00, 1, 'albanian', 'D M d, Y g:i a', 0, 0, 0, 0, 0, NULL, 1, 1, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0, '', 0, '57b16b7c49@todoscol.org', '', 'http://www.seamcat.org/xwiki/bin/download/XWiki/NoraMilton/amateurpics.html', 'Somethere', '', '', '', '', '', '', '', '', NULL);

 

INSERT INTO `phpbb_users` VALUES (703, 1, 'Fertassal', '91f004cf5aa5e8f7580b3a8001b17acd', 0, 0, 0, 1185473363, 0, 0, -12.00, 1, 'albanian', 'D M d, Y g:i a', 0, 0, 0, 0, 0, NULL, 0, 1, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0, '', 0, 'fertassa@mail.ru', '135664', 'http://www.mulka.ru', 'Russia', '', '', '', '', '', 'news and links', 'download MP3', '', NULL);

 

i would like to create a new text document and copy paste only email addresses from this sql file..

 

 

Text Doc. will be like this;

 

57b16b7c49@todoscol.org

fertassa@mail.ru

 

any idea?

 

[Sesquipedalian]

Maybe you should be more specific, how are you trying to find the information? By searching name? Zipcode? What?

 

And what's the point of having the information in a text file when you can easily just look in the mysql database itself?

[qxygene]

very helpfull

[cunoodle2]

You would want to use the following php functions...

 

1. 'strpos'  It is a string searching function that can be used to find the position of first occurrence of one string within another.

2. 'strrpos' (note this function has 2 "r's" in it.  It is a string searching function that can be used to find the LAST occurrence of one string within another.

3. 'substr'  It is a string tool that is used to create 1 string out of another based up on a certain length

4. 'strlen'  It is a string tool that returns the length of a given string.

 

This has NOT been tested and will NOT work but will hopefully give you the idea of how to write the code for this...

 

<?php

//This is the variable containing all of the info you mentioned from said text file
$long_string = (((READ FILE NAME IN HERE AND STORE IN VARIABLE)))

//initialize and set some kind of counter to keep track of your position in the string
$working_location = 0;

//create a loop that basically says continue reading from the file until you have reached the end
while ($working_location < strlen($long_string))
{
   //find the location of the '@' symbol
   $at_location = strpos($long_string, "@", $working_location);

   //then find the location of the FIRST space AFTER the '@' symbol
   $end_of_email = strpos($long_string, "', ", $at_location);

   //now create a temporary substring from the beginning of the file to the location of this '@' symbol
   $temp_string = substr($long_string, 0, $at_location);

   //now find the begining of the e-mail address (so the last space BEFORE the '@' symbol)
   $beginning_of_email = strrpos($temp_string, " '");

   //determine the current e-mail address based upon the above given data.
   $working_email = substr($long_string, $beginning_of_email, $end_of_email - $beginning_of_email);

   $working_location = $end_of_email;

   echo $working_email."<br />\n";
} //end of for loop

?>

 

That should get you off to a solid start.  There is going to be some issues with the loop not stopping properly but you will have to figure that out as you go along.  Also you may have to trim off a single quote, comma, and or space here and there.  Work with the above code and modify accordingly.  Keep working on and then post back here to let us know how you are doing.

[qxygene]

yes this is the output;

 

 

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

 

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

<br />

 

and 100%cpu

[cunoodle2]

Maybe you should try doing a little code troubleshooting.  Also I'm sure the CPU usage is high as the loop might not be exiting properly.  I will start the troubleshooting for you and then you take it to the next level to see what it is...

 

I'm taking the existing code and commenting out the while loop to have the searching print more information to the screen.

 

<?php

//This is the variable containing all of the info you mentioned from said text file
$long_string = (((READ FILE NAME IN HERE AND STORE IN VARIABLE)))

//initialize and set some kind of counter to keep track of your position in the string
$working_location = 0;

//create a loop that basically says continue reading from the file until you have reached the end
//while ($working_location < strlen($long_string))
//{
   //find the location of the '@' symbol
   $at_location = strpos($long_string, "@", $working_location);

   //then find the location of the FIRST space AFTER the '@' symbol
   $end_of_email = strpos($long_string, "', ", $at_location);

   //now create a temporary substring from the beginning of the file to the location of this '@' symbol
   $temp_string = substr($long_string, 0, $at_location);

   //now find the begining of the e-mail address (so the last space BEFORE the '@' symbol)
   $beginning_of_email = strrpos($temp_string, " '");

   //determine the current e-mail address based upon the above given data.
   $working_email = substr($long_string, $beginning_of_email, $end_of_email - $beginning_of_email);

   //change the location of where you are searching the string so that it starts on the next e-mail address
   $working_location = $end_of_email;

  ##### troubleshooting #####
   echo "Email Beginning: ".$beginning_of_email."<br />\n";
   echo "@ Symbol: ".$at_location."<br />\n";
   echo "Email Ending: ".$end_of_email."<br />\n";
   echo "Temp String: ".$temp_string."<br /><br />\n";
  ##### END OF troubleshooting #####

   //print the e-mail address to the screen
   echo $working_email."<br />\n";

//} //end of while loop

?>

 

Basically what the new troubleshooting section does is print the location of where the beginning of the e-mail address is, the @ symbol is and then the end of the e-mail address is.  They should be 3 numbers and ALL be IN order.  If the numbers are NOT in order then you need to go through the code and figure out WHY it is not in order.  If for example the first number looks out of place then you need to specifically look at how the "$beginning_of_email" is formed and then move on from there.

 

Take a look and then get back to us/me.

[qxygene]

thank you.. worked..

[Psycho]

Well, that seems like a lot of work when you conside that you can simply change the extension of the file to csv and open in Excel. All of the email addresses would be in a single column and you could just copy and paste that column into a text document. Took me all of about 5 seconds to do.

 

By the way:

very helpfull

The poster was simply asking for more information. Not everything that seems obvious to you will be so to someone else.

[cunoodle2]

Well, that seems like a lot of work when you conside that you can simply change the extension of the file to csv and open in Excel. All of the email addresses would be in a single column and you could just copy and paste that column into a text document. Took me all of about 5 seconds to do.

Yes, definitely lots of work.  In addition I over commented this in an attempt to get this noooob a little more information as to how everything with string functions work.  Also yes csv is sooo much easier and faster.  However in some cases there are files that need to be read on the file without human interaction.  I've actually used a very similar script numerous times with great success in the past (great for things like using curl to fill out other pages or to access certain text from another page).  I used curl to log into my bank account and then find the amount of my IRA.  The info was stored in a database and then later used for a graph to keep track of my money.

 

In any case I hope this string searching can be used to help someone else in the future.

[trq]

In Linux.

 

grep -o '[a-z0-9]*@[a-z0-9]*' sqltextfile > newtextfile

 

Done.

 

I fail to see what this question has to do with php.

[keeB]

^ Grandma.