need a little help replacing a comma with apostrophe comma apostrophe

[sptrsn]

It's probably not the right way, but it'll do for now. This is part of a query builder.

 

I want to replace a comma with apostraphe comma apostraphe.

 

in other words this......

 

city1, city2, city3

 

becomes

 

city1', 'city2', 'city3

 

Ultimately this is helping me build a dynamic query that will read.....

select * from table where city in ('city1','city2','city3')

the surrounding parens and apostraphes are in the whereclause[] statement, so I don't need them here.

 

I've tried a multiple iterations, but they all end up looking similar to this, which obviously doesn't work...

$city= preg_replace(",", "', '", $city); 

 

I appreciate any help.

 

[incubator140]

Hi,

 

Please try the following:

 

$city ='city1, city2, city3';

$newCity = str_replace(',', "', '", $city);

echo $newCity;

[sptrsn]

I get the following error...

A fatal MySQL error occured.

Query:

Error: (1136) Column count doesn't match value count at row 1

 

Not completely sure why. My insert statement works fine without the str_replace.

I've been able to get it to insert several city names with the commas.

 

Is there something about the apostrophe that makes mysql think it's an additional column?

The field is varchar 200 (innodb)

[incubator140]

 

$city ='city1, city2, city3';

$newCity = str_replace(',', "', '", $city);

$sql = "select * from test where city in ('$newstr')";

 

 

This is working for me..

[sptrsn]

You're correct. That statement works great. But the moment I add the insert statement back in, I get that same error.

 

Is there something about this value ......"city1',' city2',' city3 " that would cause a problem that you're aware of?

[sptrsn]

well it's not going to let me insert that, so I'll apply that script when I take the values out of the db. That should work.

[sptrsn]

yea, that worked great. Thank you very much for your help.

I just insert the comma separated values in the db. (cleaner that way anyway)

Then when I build the query, I applied your str_replace right before building the array.

 

if ($row['city'] !='')
$city = str_replace(',',"','",$row['city']);								
$whereClause[]="prop_city in ('".$city."')";

 

Thanks again.