PHP MySQL Different results between query on web server and phpmyadmin

Question

I have a field that looks like this:

Intelligence: 0/75

I have a form that users can search the value of this string. On my live web server, this is the query:

SELECT * FROM `test` WHERE `id` IS NOT NULL AND `file` REGEXP CONCAT('Intelligence(: | : )([', :var2, '-9]|[0-9]{2,3})[ ]{0,1}/[ ]{0,1}([', :var2, '-9]|[0-9]{1,3})')
$var2 = $_POST['int'];
$stmt->bindValue(':var2', $var2);

:var2 is the $_POST value. I've been using the case of 4. This is the literal regexp when 4 is plugged in:

Intelligence(: | : )([4-9]|[0-9]{2,3})[ ]{0,1}/[ ]{0,1}([4-9]|[0-9]{1,3})

Since the field in my database is Intelligence: 0/75 this query shouldn't yield any results. But it does, on my live web server. It returns the field Intelligence: 0/75.

If I go and run this EXACT query in phpmyadmin, I get no results. I've lined up the query from phpmyadmin and my server in my text editor and they're identical. Just so I'm not crazy, here's the query copy/pasted from phpmyadmin:

SELECT *
FROM `test`
WHERE `id` IS NOT NULL
AND `file`
REGEXP CONCAT( 'Intelligence(: | : )([4-9]|[0-9]{2,3})[ ]{0,1}/[ ]{0,1}([4-9]|[0-9]{1,3})' )

Can anyone see why the hell this is yielding two diff. results?

Update:

I think I've narrowed it down to the PHP. If I change the value being bound to :var2, to like

$stmt->bindValue(':var2', '6284');

The results are the same. All rows with Intelligence: 0/75 are returned.

If I delete the bindValue code, so there is nothing being bound to :var2, the results are still the same!

print_r($stmt) =

SELECT * FROM `test` WHERE `id` IS NOT NULL AND `file` REGEXP
                    CONCAT('(INT:[  ]{1,2}([', :var2, '-9]|[0-9]{2,3})[  ]{1,2}/[  ]{1,2}([', :var2, '-9]|[0-9]{1,3})|Intelligence(: | : )([', :var2, '-9]|[0-9]{2,3})[ ]{0,1}/[ ]{0,1}([', :var2, '-9]|[0-9]{1,3}))')

@MarkGarcia I did, it's the last example in my original post. — Norse
– Norse, Commented Sep 25, 2012 at 4:10
$stmt->bindValue(':var2', $var2); where is var2 in the query you gave us? — Mihai Matei
– Mihai Matei, Commented Sep 25, 2012 at 4:22
Couldn't see anything wrong with this, PDO or mySQL messing it up. It could be something else - can you post a little more code, just to check everything else is going right - e.g. you're looking at the right results set, for example, or executing the right query - just in case you missed it while concentrating on the regex? — Robbie
– Robbie, Commented Sep 25, 2012 at 6:03

Cedric · Accepted Answer · 2012-09-25 05:48:33Z

1

Norse,

When you say " on my live web server", do you mean on your web page rendered by PHP, or the SQL command line utility ?

Make the sql demon log the queries. You will be able to compare what exact queries are executed ! log query (MySQL doc)

You seem to have more unit of data in the file column. In general, try to have one unit of data in each column, and respect as much as possible the Normal Forms (Database_normalization) . You will have faster, simpler query, with no regex. However, it may not be possible in your case.

answered Sep 25, 2012 at 5:48

Cedric

5,32311 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Robbie Over a year ago

I tried logging queries in mySQL to see if PDO was corrupting it, and it wasn't. So I did a SELECT CONCAT... to check mysql wasn't messing the concat, and it wasn't. Did you see anything different?

Cedric Over a year ago

So you are logging the queries. You execute the first method. Then you execute the second one 5 minutes later. You see distinctively the two queries that are executed in the log file that you have put in the demon parameter. And they are exactly the same?

Robbie Over a year ago

I did using the code provided by the OP - via PDO and then via phpMyAdmin and they are the same. Just wondering if you saw something different?

Cedric Over a year ago

I am not. And apparently few haven't so far. The SQL query executed by MySQL engine and the one that you see in phpMyAdmin is probably different. phpMyAdmin does its best, but in the end, it is a interface. If you tell the MySQL demon to log your queries, you will have the exact queries that are executed by MySQL. No interface in-between.

Cedric Over a year ago

(I am just writing that to double-double check with you that it is the same MySQL engine executing the queries, that nothing is cached, modified or anything in between. :) )

mtk · Accepted Answer · 2012-09-25 07:02:08Z

1

I think, the queries are different.

Do a print_r() or var_dump() on $stmt to get the exact query being executed via php to be sure the regex part is going as it is and not getting tweaked in middle by any other operations like bind.

answered Sep 25, 2012 at 7:02

mtk

13.8k16 gold badges75 silver badges117 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Different results between query on web server and phpmyadmin

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related