Problem With ")" pulled from a string into a select statement.

IS there a special way to handle this character?

[googalot]

I use preg_replace to convert a database entry field string into a useable page name and then to convert it back on a target page to create individual entry pages from a list page.

Company - widget Name 2.0 (Color1/Color2) - Gender's

is converted to:

/target-page/company-widget_name_2-point-0-in-color1-and-color2-for-gender.html

and at the target-page the query is converted back to:

$string="Company - widget Name 2.0 (Color1/Color2) - Gender\'s"

I then pull the string like so:

"SELECT * FROM <table_name> WHERE <column_name> like '%$string%'" (statement has been shortened)

The problem is that it doesn't work unless I use:

$string = substr($string,0,-11)

To remove the closing bracket and then it works fine.

My problem is that this bracket can appear in random places and I am hoping someone can steer me in the right direction to handle this problem with my selects.

I have searched php.net and other sources but can not find any reference to a requirement for a closing bracket when used in a select statement.

Any help and/or advice would be greatly appreciated :)

[StupidScript]

Although I'm not aware of problems with parentheses in a query, perhaps they need to be escaped?

$string=str_replace(")","\)","Company - widget Name 2.0 (Color1/Color2) - Gender\'s");

Maybe the end paren character is prematurely ending the select statement?

[mcibor]

I think the problem could be with brackets in $string: (Color1/Color2)

However I don't know why.

[googalot]

Thanks for the Input :)

I have tried escaping the closing bracket to no avail so I am assuming that the closing bracket is ending the select statement early as you surmised.

I wonder if anyone has successfully used closing brackets in a select statement and if so how?

[StupidScript]

Hmmm ...

How about converting the end paren into HTML code?

$string=str_replace(")",")",$string);

I suppose you could str_replace again before you use it, if it appears in a drop-down menu or something.

I actually haven't used parens in my dbs, before, oddly enough.

[googalot]

Hi StupidScript,

Thanks for the Idea :) However that will not work within the select statement. (It is one of the variations I tried.)

Yesterday I did however come up with a 100% reliable workaround which I have implemented and and I am reproducing my code here in the hope that it will help anyone who has this problem in the future.

My .htaccess/Php/MySQL knowledge is limited so if anyone can see any errors with my code or anyway to improve on the way I do it please let me know :)

Firstly the .htaccess to enable .html parsing, pagination in the format URL/Page/[product no]/ and of course my variable to be passed as a legal page name.

AddType application/x-httpd-php .htm .html //Parse .htm and .html files as Php
Options +FollowSymlinks
RewriteEngine On
RewriteRule ^([^/]+)/([0-9]+)$ /$1/$2/ [R] //Add a trailing slash where 2nd variable is numeric
RewriteRule ^([^/]+)/([0])/$ /$1.htm [R] //If Products=0 re_write the URL to URL/Page.htm
RewriteRule ^([^/]+)/([0-9]+)/$ /$1.htm?products=$2 [L] //If 2nd is Numeric URL/Page/No/ to URL/Page.htm?products=[No]
RewriteRule ^pagename/([^/]+).htm$ /pagename.htm?$1 [L] // Pass the non numeric var to the page you specify

Next we need to convert Company - widget Name 2.0 (Color1/Color2) - Gender's on the originating page into a legal URL to be passed as a variable to the target page.

So I create a variable and fill it with a string:

$string = trim($row["<YourColumn>"]);

Next I create my patterns and replacements and convert the string with preg_replace:

$patterns[0] = "/\) - /";
$patterns[1] = "/ \(/";
$patterns[2] = "/ - /";
$patterns[3] = "/\//";
$patterns[4] = "/\./";
$patterns[5] = "/ /";
$patterns[6] = "/&quot;/";
$patterns[7] = "/&amp;/";
$patterns[8] = "/\+/";

$replacements[8] = "---";
$replacements[7] = "-in-";
$replacements[6] = "--";
$replacements[5] = "-and-";
$replacements[4] = "-point-";
$replacements[3] = "_";
$replacements[2] = "-quote-";
$replacements[1] = "-n-";
$replacements[0] = "-plus-";

$string = preg_replace($patterns, $replacements, $string);

The URL is sent like this: <a href="/pagename/<?=$string?>.html">

And is now in the format: Company--widget_Name_2-point-0-in-Color1-and-Color2---Gender's.html

The target page receives this and decodes it as follows:

$Product = urldecode($QUERY_STRING);
// Decode the query and keep as a variable in case I want to use it for anything on the page.

$patterns[0] = "/-point-/";
$patterns[1] = "/---/";
$patterns[2] = "/-in-/";
$patterns[3] = "/-and-/";
$patterns[4] = "/-quote-/";
$patterns[5] = "/-plus-/";
$patterns[6] = "/-n-/";
$patterns[7] = "/--/";
$patterns[8] = "/_/";

$replacements[8] = ".";
$replacements[7] = "%' AND <ColumnName> LIKE '%";
// I cheated and used the ")" to break the SELECT statement into an AND statement which works perfectly. :)

$replacements[6] = " (";
$replacements[5] = "/";
$replacements[4] = "&quot;";
$replacements[3] = "+";
$replacements[2] = "&amp;";
$replacements[1] = " - ";
$replacements[0] = " ";

$string = preg_replace($patterns, $replacements, $Product);

I now have "Company - widget Name 2.0 (Color1/Color2%' AND <ColumnName> LIKE '%Gender\'s"

This is now used in the SELECT statement like so:

$sql = mysql_query("SELECT *
FROM <TABLE>
WHERE (<ColumnName> like '%$string%')

If anyone can see how I can improve on this please let me know.

[StupidScript]

Wow! That's a heap 'o code, allright! Good work getting all of that together.

I have not been able to reproduce your problem using a test MySQL myisam-type db (My MySQL installation isn't set up for InnoDB-type dbs).

Here is the test row in my test db:

id ¦ teststr

-------------------------------

01 ¦ Test (2) is StupidScript's

Here is the code I have tested successfully:

$string="Test (2) is StupidScript\'s";

$gettest=mysql_query("select * from testt where teststr like '$string'") or die (mysql_error());

while($row=mysql_fetch_array($gettest)) {

$id=$row["id"];

$teststr=$row["teststr"];

echo "ID: ".$id.", TESTSTR: ".$teststr;

}

So ... in my arrogance, I would suggest looking at other elements of the code either before, during or shortly after the query. In other words ... I got nuthin'!

<edit>It occurs to me that your original "fix" cut the string off AFTER the close paren, not AT it. So perhaps the issue was/is with the hyphen following the paren? Maybe it thinks it's doing some math and gets hung up when you try to subtract a word instead of a number? Just thinking ...</edit>

[StupidScript]

Nope ... can't duplicate it.
Here're my current (final) test rows:

id¦teststr

-------------------------------------------------------

01¦Test (2) is StupidScript's

-------------------------------------------------------

02¦Company - widget Name 2.0 (Color1/Color2) - Gender's

And here's my test code:

<?

$doconn=mysql_connect("server","user","passwd") or die (mysql_error());

$setdb=mysql_select_db("test",$doconn) or die (mysql_error());

$string="Test (2) is StupidScript\'s";

$gettest=mysql_query("select * from testt where teststr like '%$string%'") or die (mysql_error());

while($row=mysql_fetch_array($gettest)) {

$id=$row["id"];

$teststr=$row["teststr"];

echo "ID: ".$id.", TESTSTR: ".$teststr."<br />\n";

}

mysql_free_result($gettest);

$string="(2)";

$gettest=mysql_query("select * from testt where teststr like '%$string%'") or die (mysql_error());

while($row=mysql_fetch_array($gettest)) {

$id=$row["id"];

$teststr=$row["teststr"];

echo "ID2: ".$id.", TESTSTR2: ".$teststr."<br />\n";

}

mysql_free_result($gettest);

$string="Company - widget Name 2.0 (Color1/Color2) - Gender\'s";

$gettest=mysql_query("select * from testt where teststr like '%$string%'") or die (mysql_error());

while($row=mysql_fetch_array($gettest)) {

$id=$row["id"];

$teststr=$row["teststr"];

echo "ID3: ".$id.", TESTSTR3: ".$teststr."<br />\n";

}

mysql_free_result($gettest);

mysql_close();

?>

That's about it. All functions as expected. I'm still thinking the problem lies elsewhere. :)

[googalot]

<quote>It occurs to me that your original "fix" cut the string off AFTER the close paren, not AT it. So perhaps the issue was/is with the hyphen following the paren? Maybe it thinks it's doing some math and gets hung up when you try to subtract a word instead of a number? Just thinking ... ~StupidScript<quote>

Sorry about that. The problem here is my flawed replication in my original substr example.

If I do a character by character substr last character removal it does indeed start working when i remove the closing bracket.

I will test your examples in my setup and see if they work.

[s1dev]

When you say the query 'doesnt work' do you mean it won't execute? Or does it execute but doesn't return the desired result?
Is this mysql?
Generally when using strings in queries, you'll want to escape the value, especially when the strings are coming from POST or GET variables. If you are using mysql, there is a function mysql_escape_string() that you can use that is supposed to take any string and ready it for use in a mysql sql statement.

From your original post, change the query to read:
$string="Company - widget Name 2.0 (Color1/Color2) - Gender's"
"SELECT * FROM <table_name> WHERE <column_name> like '%".mysql_escape_string($string)."%'" (statement has been shortened)

Note the removal of the \ from the $string assignment statement - as mysql_escape_string will handle this for you.