Problem with "where item in($array)" query

[Perfidus]

Hi there, I have been running a website with no problems in a shared server, recently I have moved the files to a cheaper one and I'm having some issues in my PHP, even though versions (php, mysql) seem to be equal or newer, one of those issues is the following; I have this query:

SELECT * FROM articles WHERE ref IN (".implode(',', $articles).") AND title LIKE '%$search%' ORDER BY date DESC LIMIT $startvalue, $amount

 

This used to work fine in my old server, now I'm getting this error:

 

Unknown column '455875' in 'where clause'

 

Where 455875 is one of the references in the array, why?

[kickstart]

Hi

 

Could you echo out the generated SQL and post that?

 

All the best

 

Keith

[Perfidus]

Unknown column '455875' in 'where clause'

 

SELECT * FROM articles WHERE ref IN (455875,99854,33266) AND title LIKE '%coffe%' ORDER BY date DESC LIMIT 0, 50

[trq]

What field type is ref ?

[Perfidus]

is a varchar field

[trq]

Then you need quotes around its values.

 

SELECT * FROM articles WHERE ref IN ('".implode("','", $articles)."') AND title LIKE '%$search%' ORDER BY date DESC LIMIT $startvalue, $amount

[Perfidus]

Yes, this works like a charm, but, why was it working previously in the other server?

Which things have changed in the new one so I can take them in account for future developments?

[trq]

I have no idea why it would have worked in the other server. Varchar field values always need to be surrounded by quotes.

[PFMaBiSmAd]

A more serous question would be why are you storing integers using a varchar data type. That increases the storage requirements, makes every query that references the value slower, and prevents direct ordering and greater-than/less-than comparisons. Integers should be stored using an INT data type.