I have a database with two tables. "speechesLCMcoded" includes 400K lines of coded text, and "concreteness" includes 80k words with scores.
I wrote a script that looks into the table with parsed text (speechesLCMcoded), I check for each word in another table after removing the tags (concreteness table) and I add up the resulting scores.
I am a beginner in PHP and my code is not optimized at all. I do not mind if my script runs for the entire day, but I cannot have it run for a week. How would you advise me to optimize my script?
My scripts performs everything I need. It is just way too slow.
<?php
//Include functions
include "functions.php";
ini_set('max_execution_time', 900000);
echo 'Time Limit = ' . ini_get('max_execution_time');
//Conecting the database
if (!$conn) {
die('Not connected : ' . mysql_error());}
// make LCM the current db
mysql_select_db('senate');
$data = mysql_query("SELECT `key`, `tagged` FROM speechesLCMcoded") or die(mysql_error());
// puts the "data" info into the $info array
while($info = mysql_fetch_array( $data) ){
$key=$info['key'];
$tagged=$info['tagged'];
unset($weight);
unset($count);
$weight=0;
$count=0;
// Print out the contents of the entry
Print "<b>Key:</b> ".$info['key'] . " <br>";
// Explodes the sentence
$speech = explode(" ", $tagged);
// Loop every word
foreach($speech as $word) {
//Print each word
//Print "<b>Key:</b> ".$word . " <br>";
//Check if string contains our tag
if(!preg_match('/({V}|{J}|{N}|{RB})/', $word, $matches)) {} else{
//Removes our tags
$word = str_replace("{V}", "", $word);
$word = str_replace("{RB}", "", $word);
$word = str_replace("{J}", "", $word);
$word = str_replace("{N}", "", $word);
$word = str_replace("{/V}", "", $word);
$word = str_replace("{/RB}", "", $word);
$word = str_replace("{/J}", "", $word);
$word = str_replace("{/N}", "", $word);
//print $word . " <br>";
//Check for the score
$checksql = "SELECT word, score FROM concreteness WHERE word = '$word'";
$query = mysql_query("$checksql");
$check_count = mysql_num_rows($query);
if($check_count > 0 ){
$data2 = mysql_fetch_assoc($query);
$weight=$weight+$data2['score'];
$count=$count +1;
// echo $weight;
// print "<br>";
// echo $count;
// print "<br>";
} else {
// echo"The word was NOT found.<br>";
} }
}
$sql = "UPDATE speechesLCMcoded SET weight='$weight', count='$count' WHERE `key`='$key';" ;
$retval = mysql_query( $sql, $conn );
if(! $retval )
{die('Could not update data: ' . mysql_error());}
echo "Updated data successfully\n";
}?>
For every row from speechesLCMcoded (400K rows), you exec str_replace and sql query.
You can remove tags into first sql query use replace function (http://dev.mysql.com/doc/refman/5.0/en/replace.html). You do not need exec str_replace x 8 for every row.
It's first step.
To second step you can use one query only with use join to get all data from two tables.