I'm struggling to understand this.
I have the following piece of code:
$result=$db->query(" select `job_id`,`jobs`.`client`
from `stores`
left join `jobs`
on `jobs`.`client`=`stores`.`id`
where `stores`.`postcode`='BD5 8HP'
and `stores`.`company`='MATALAN'
and `jobs`.`description`='MONTHLY external - Clean Both sides of External Glazing, Frames, Ledges & Entrance Doors (up to 1.8m Height) - Including Black out windows - Removing cobwebs and dust from all areas' ")->fetch();
echo "info:";
print_r($result);
die();
The query returns absolutely nothing, aka $result is an empty array.
If I run exactly the same query through PhpMyAdmin or from the MySQL binary client (on the server) I have reults.
The PHP code was a prepare -> execute construction, but since I was getting an empty result I tried to replace it (for test purpose only) with a direct query.
I had a look at a very similar topic on SO here which suggested that the problem was related to the binding, now I have no binding at all in above query, yet the result is an empty string.
Running other queries like, "select * from table" do return results, so I have NO clue on what can be wrong here.
Any idea/advice please?
As a footnote I'm running the query against a remote server. Can it be something with the charset?
EDIT:
While I received suggestion to change the value of DESCRIPTION, I cannot do that. That value is read out from a PDF and one location can have similar values, so using a LIKE and a % won't really work for me. I need to get the exact value matched.
EDIT 2 Ok the problem seems to be a bit more complex and the root cause of it might be somewhere deeper. If I manually write the string into the PHP code I do get the correct result, however when I pass it through as a variable it doesn't seem to work any more. Here is a bigger chunk of my script. The script is checking email with IMAP if an email matches some rules and it has attachment it opens the attachment which is a PDF, converts it to text, extract some images and updates a database accordingly.
$invoicedata=$a['attachment'];
$descriptorspec = array(
0 => array("pipe", "r"), // stdin is a pipe that the child will read from
1 => array("pipe", "w"), // stdout is a pipe that the child will write to
2 => array("file", "/tmp/error-output.txt", "a") // stderr is a file to write to
);
$cwd = '/tmp';
$process = proc_open('pdftotext - -', $descriptorspec, $pipes, $cwd);
if (is_resource($process)) {
fwrite($pipes[0], $invoicedata);
fclose($pipes[0]);
$all_text=stream_get_contents($pipes[1]);
fclose($pipes[1]);
proc_close($process);
preg_match("/ARL SUPPORT SERVICES (.*)/",$all_text,$extracted);
$company=$extracted[1];
preg_match("/[A-Z]{1,2}[0-9][0-9A-Z]?\s?[0-9][A-Z]{2}/",$all_text,$extracted);
$postcode=$extracted[0];
preg_match("/Date\n*(.*-)/",$all_text,$extracted);
$date=trim(str_replace("/","-",str_replace("-","",$extracted[1])));
$date=date("Y-m-d H:i:s",strtotime($date));
preg_match("/SPECIFICATION:((.|\n)*)EQUIPMENT/",$all_text,$extracted);
$job_desc=trim($extracted[1]));
preg_match("/Resource\n*(.*)/",$all_text,$extracted);
$who_signed=trim(str_replace("-","",$extracted[1]));
$db = new PDO('mysql:host=XXX.XXX.XXX.XXX;dbname=ZZZZZZ;charset=utf8', 'USER','PASSWORD');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result=$db->query("select `job_id`,`jobs`.`client` from `stores`
left join `jobs` on `jobs`.`client`=`stores`.`id`
where `stores`.`postcode`='$postcode' and `stores`.`company`='$company'
and `jobs`.`description`='$job_desc'")->fetch();
echo "info: ";
print_r($result);
die();
In this case the $result is empty!
If I do:
echo "select `job_id`,`jobs`.`client` from `stores`
left join `jobs` on `jobs`.`client`=`stores`.`id`
where `stores`.`postcode`='$postcode' and `stores`.`company`='$company' and `jobs`.`description`='$job_desc'"
die();
It will just print:
select `job_id`,`jobs`.`client` from `stores` left join `jobs` on `jobs`.`client`=`stores`.`id` where `stores`.`postcode`='BD5 8HP' and `stores`.`company`='MATALAN' and `jobs`.`description`='MONTHLY external - Clean Both sides of External Glazing, Frames, Ledges & Entrance Doors (up to 1.8m Height) - Including Black out windows - Removing cobwebs and dust from all areas'
If I do copy back this into my PHP code then the code executes correctly, so the problem might be somewhere with converting the characters. I thought that it might be due to endash/emdash but checking the $job_desc with ord() returned char 45 for the - which is correct. also the & seem to be correct.
I'm running out of ideas, is there something so obvious which just doesn't jump out to me?
