Replace command in a mysql where statement

Problems with str_replace, replace, strtr

[txbigden1]

I am trying to pull a record using a select statement on a php page and am having issues.

I can get the str_replace command to echo correctly on the record I pull, but the second I put it in the 'where' portion of the select statement I get nothing but errors.

I've got titles of stories that the parameter I will be passing will have "_" instead of spaces, so I want my where statement to look something like

select * from written where str_replace(" ","_",Story_name) = '$title'"

or

select * from written where strtr(Story_name," ","_",) = '$title'"

or

select * from written where replace(Story_name," ","_") = '$title'"

Each one gives me errors.

Can someone please help? I'm fairly new to this, but have can get all of these commands to work outside of the where statement.

Thanks
Dennis

[Anyango]

Welcome to WebmasterWorld Dennis!

This should work

"select * from written where REPLACE(Story_name,' ','_') = '$title'"

What error do you get ?
If it was just a Parse error it was due to the improper use of quotation marks.

[edited by: Anyango at 6:04 pm (utc) on Nov. 27, 2008]

[txbigden1]

I don't know what I was doing wrong, but you got it perfect. Thank you so much for the assistance.

I was getting a parsing error, and had added/removed/changed for 2 days now. I knew I was close, but just couldn't get it right.

Thanks again!
Dennis

[txbigden1]

Ok, I got a bit ahead of myself, and should of mentioned this up front. Is there a way to do an array with this? If i saw correctly, I should be able to create a line like:

$symbols3 = array(" " => "_", "&" => "", "@" => "");

and then change the select to

$result2 = mysql_query ("select * from written where REPLACE (Story_name,'$symbols3') = '$title'" )

I appreciate the help again
Dennis

[Anyango]

About your error, it was simply the fact that double quotes were being used within double quotes without any escaping.

If your sql query statement is wraped in single quotes then you use double quotes within the query itself for anything that you might want to do, and vice versa. But if you want to still use same quotation marks then that would need to be escaped by a slash. following are some good patterns

$sql=" select * from myTable where myField='my Value' ";

$sql=' select * from myTable where myField="my Value" ';

$sql=" select * from myTable where myField=\"my Value\" ";

And about REPLACE on array, hmm i am not sure if there is a *function* like that but maybe we can write any "not so simple" query for that.

This might Help

[webmasterworld.com...]

[txbigden1]

Ok, I got something to work, but talk about messy. I nested Replace() as so:

$result2 = mysql_query ("select * from written where replace((replace((replace((replace((replace((replace((Replace((replace((Replace(Story_name,' ','_')),',','$no_space')),'\'','$no_space')),'-','_')),'?','$no_space')),'&','$no_space')),'.','$no_space')),'!','$no_space')),':','$no_space') = '$title'" )

I will have to decipher the function that you pointed me to. I'm afraid that it might not want to work at the 'where' point. I know if it wasn't in the where clause I could do this 3 different ways.