how to use php to parse text and insert into mysql

text parsing php

[hanglide]

Hello,

I need to read a text file and then insert the values into different fields in a mysql database.

Each line in the data is in the form of:

last, first title, department

Where the fields are last, first, title and department. How would I parse this and insert it into a table? As you can see, not all fields are comma delimited but the order is consistent. Is there a simple way to somehow read it in a word at a time and strip the commas entirely and rely on the whitespace as a delimiter?

I have searched the web for php parsing tutorials but they all seem to be xml related.

Thanks

Scott

[siMKin]

if you are sure you can use the spaces as delimiter it's fairly easy:

$string = "first, last title, department";
$string2 = str_replace(",", "", $string)
$words = explode(" ", $string2);

[edited by: siMKin at 8:08 pm (utc) on Aug. 2, 2006]

[supermoi]

Use this line of code that will return an array with all the words that are separated either by:
- a space
- a comma
- several spaces
- a comma followed by one or several spaces

$variable_to_assign = preg_split('/[,? ]+/',$string_to_parse);

[hanglide]

Thanks to all for your help.

This is what I came up with in the end, any comments and corrections would be appreciated. It's the first php or mysql stuff I've done I'm pretty happy with the fact that it worked.. lol

BTW If anybody needs a mysql database with all the players in it, I have one now and you're welcome to it. :-)

// initial database stuff
$host = "myserver";
$user = "myname";
$pass = "mypassword";
$db = "mydb";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");

// open file stuff
$filename = "qb.txt";
$fp = fopen($filename, "r") or die("Couldn't open $filename");

// parsing stuff
$count = 0;
$player_last = "";
$player_first = "";
$player_position = "";
$city = "";
$team = "";

while (!feof($fp)){
$string = fgets($fp, 1024);
$string2 = str_replace(",", "", $string);
$count = count(explode(" ", $string2));
$player_info = explode(" ", $string2);
$player_last = $player_info[0];
$player_first = $player_info[1];
$player_position = $player_info[2];

if ( $count > 5 ) { // City names may be one or more words
$team = $player_info[$count-1]; // 1 word,always last word
for ( $j = 3; $j < $count-1; $j++ ){
// Concatenate -> ex "San" "Francisco" = "San Francisco"
$city = $city.$player_info[$j]." ";
}
}
if ( $count == 5 ) {
$city = $player_info[3];
$team = $player_info[4];
}

// insert stuff
mysql_query("INSERT INTO nfl_players (player_last, player_first, position, city, team) VALUES('$player_last', '$player_first', '$player_position', '$city', '$team') ") or die(mysql_error());

$city = "";
}

Thanks again,

Scott

[hanglide]

-supermoi

I'll try the regex stuff next time -I really should learn it as I could have really used it in this case to help escape some of the names with ' in them and to combine the "Billy Bob" types of first names.

That stuff just looks so obscure it's a bit intimidating. :-(