[SOLVED] Problem with updating mysql

[philwhite]

Please excuse me if I'm doing something really dozy. I'm an absolute beginner from the ASP world and am migrating a Snitz forum to PHPBB.

 

One thing I have to do is to replace all HTML code with BBCode, so I'm trying to cycle through the posts, do some regular expression searching and replacing and updating the rows. The searching and replacing is done in a subroutine html_bbcode_format which works fine. If I run the code below, it seems to update random numbers of rows (1 and then misses 1, 2 and then misses 3, 1 and misses 2 and so on). This is the relevant code section:

 

// Cycle through the results and apply html_bbcode_format ($replace, $bbcode_uid)
mysql_connect($snitz_server,$snitz_user,$snitz_password);
@mysql_select_db($snitz_db) or die( "Unable to select database");
echo "<b>Converting topics...</b><br>";
$query = "SELECT * FROM " . $snitz_topics;
$result=mysql_query($query);	
if (!$result) {
    echo 'Could not run query: ' . mysql_error() . "<br>";
    echo $query;
}
//echo $query."<br>";
$todo=mysql_numrows($result);
echo $todo." results"."<BR>";
$cc=0;
while($data = mysql_fetch_array($result))
{
	$cc=$cc+1;
	$writeback = html_bbcode_format($data['T_MESSAGE']);
	$query2= "update ".$snitz_topics." set T_MESSAGE = '".$writeback."' where TOPIC_ID = ".$data[topic_ID]." LIMIT 1";
	$dummy=mysql_query($query2);
	//echo "topic ". $data[topic_ID]."<br>";
	//echo "$nbsp;".$query2."<br>";
	if ($cc % 500 == 0)
	{
	echo "<i>".$cc." records done</i><br>";
	}
}	

 

That's repeated for 3 tables, all with the same symptoms.

Has anyone any idea what I've done wrong?

Another odd thing is that the page only refreshes after each block of code (after each table) and not after 500 records as intended. That's not a serious issue, as the converter is fast enough to love without a progress indicator, but it would be nice to know why.

 

Thanks a million in advance!

 

[cooldude832]

any time you run a query make sure you run it in a version relative to

<?php
$q = "Select Stuff from `table`";
$r = mysql_query($q) or die(mysql_error());
?>

that or die will give you a heads up on if it is erroring, also if you think a query has an issue echo it out and see what it gives you

 

[philwhite]

Just tried the "die" and it didn't report any errors. Also, the two lines that are commented out already echoed the query, and I couldn't see anything there.

[philwhite]

Silly me. I wasn't escaping the single quotes. I feel really embarrassed.

Now I do need the counter to refresh the page, however, as the routine takes considerably longer (still running).