...

View Full Version : Finding & replacing text at the start of a string..



cyphix
09-14-2005, 04:23 PM
I have some entries in my database which contains certain characters through each entry, however I wanted to remove these characters if they appeared at the start of the entry; I tried the below code but ended up screwing up my DB where most of the updated fields ended up being blank & now I have to get a backup of my DB.. :(



<?php

$count = 0;

mysql_connect($dbhost, $dbuser, $dbpass) or die("Couldn't connect to server.");
@mysql_select_db($dbname) or die("Couldn't connect to database.");

$q="SELECT id,lyrics FROM lyrics WHERE lyrics LIKE '<br><br>%' ORDER BY id ASC";
$sql=mysql_query($q);

while (list($lid,$lyrics)=mysql_fetch_array($sql)) {

if (preg_match('/^<br><br>/', $lyrics)) {

$lyrics = preg_replace('/<br><br>/','',$lyrics,1);
$lyrics = addslashes($lyrics);

$q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1";
$sql2=mysql_query($q2);

$count++;

}

}

mysql_close();

echo "Done! Updated $count results!";

?>

NancyJ
09-14-2005, 05:01 PM
I think the issue is you need to escape your <>



if (preg_match('/^\<br\>\<br\>/', $lyrics)) {

$lyrics = preg_replace('/\<br\>\<br\>/','',$lyrics,1);

cyphix
09-14-2005, 05:28 PM
According to this page you don't: http://weblogtoolscollection.com/regex/regex.php



In order to be taken literally, you must escape the characters "^.[$()|*+?{\" with a backslash ('\'), as
they have special meaning.

cyphix
09-15-2005, 10:08 PM
Anyone know? :confused:

CrzySdrs
09-15-2005, 10:55 PM
I see a few things that may or may not be of consequence. I noticed that $q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1"; is referring to the id column as text, when normally most people use a number, if that is incorrect, things simply won't be updating in your database, not being blank. Other than that I don't see anything blatantly obvious with your code. It would help if you had samples of the text you are attempting the regexes on, just for reference.

I have an idea for you to try out, why not next time when you decide to update the database (after having restored it), disable the update query and output all the results that you are working with. Then you can see what you are changing at every step and then when you have it exactly the way you want it, re-enable the update query and it should work just fine. Mainly verify that the SQL queries look right and aren't just inserting blanks.

raf
09-15-2005, 11:49 PM
I see a few things that may or may not be of consequence. I noticed that $q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1"; is referring to the id column as text, when normally most people use a number, if that is incorrect, things simply won't be updating in your database, not being blank.
Thats not the reason. MySQL will ignore quotes around numeric values.

i don't see any reason to use regex though. Simply


if (strpos($lyrics, '<br><br>') === 0) {
$lyrics = substr($lyrics, 8);
...

would be faster.

i also don't relly understand why you check this at all, sinde your select already has a LIKE '<br><br>%' ...
you should be able to do it directly in sql like this
UPDATE lyrics SET lyrics=SUBSTRING(lyrics,9) WHERE lyrics LIKE '<br><br>%'



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum