CODEWITHSUNDEEP
phpmysqli
MarkH76
MarkH76

Reputation: 11

Double quotes in PHP

I don't know PHP at all, so I am struggling through this. I need to add an or section to a MySQL query, but the values I'm searching have double quotes. I need to figure out how to add them in PHP so they are passed in to MySQL. The current query looks like:

$query = 'SELECT * FROM ' .$tableName.' WHERE allowed_countries LIKE "%'.$regionId.'%" and skurules  REGEXP "i:'.$secondlastdigit.';" and status = 1 ORDER BY id DESC LIMIT 1';

But I need to add an or statement to search for string values that looks like:

$query = 'SELECT * FROM ' .$tableName.' WHERE allowed_countries LIKE "%'.$regionId.'%" and skurules  REGEXP "i:'.$secondlastdigit.';" or skurules  REGEXP "s:1:'.$secondlastdigit.';" and status = 1 ORDER BY id DESC LIMIT 1';

with double quotes surrounding the second instance of '.$secondlastdigit.'; when passed into MySQL.

My JSON string I'm searching looks like this:

a:12:{i:1;s:2:"15";i:2;s:2:"10";i:3;s:2:"30";i:4;s:2:"50";i:5;s:3:"120";i:6;s:3:"240";i:7;s:3:"480";i:8;s:3:"960";i:9;s:4:"3786";s:1:"A";s:3:"100";s:1:"C";s:2:"60";s:1:"B";s:5:"18930";}

Upvotes: 1

Views: 115

Answers (2)

MarkH76
MarkH76

Reputation: 11

Agreed, searching serialized string is not the best solution and what the developer did despite having a bottle_size table available. I needed a quick fix and no time/skill to rewrite a tax calculation magento extension so I used replace in the query to solve my problem for now.

Since "s:1:X" will always be just one alpha character after the 1 and will not match anything else. I change the query to:

$query = 'SELECT * FROM ' .$tableName.' WHERE allowed_countries LIKE "%'.$regionId.'%" and skurules REGEXP "i:'.$secondlastdigit.';" or replace(skurules,char(34),0) REGEXP "s:1:0'.$secondlastdigit.'0;" and status = 1 ORDER BY id DESC LIMIT 1';

Very hackish fix but gets me out of a bind for now..

Mark

Upvotes: 0

Ivan Kvasnica
Ivan Kvasnica

Reputation: 806

First of all: DON'T.

If you still want to, then...REALLY DO NOT.

Making SQL queries on serialized arrays is just hell. You should try to avoid it at all costs.

Either:

  • Convert the serialized column into a standard SQL table
  • or select the column into a PHP variable, unserialize it and search through it.

Example:

$properPhpArray = unserialize($sqlResult['column_name']);

Upvotes: 2

Related Questions