-1

I have an array:

$array = array ( option( 'one' ), option( 'two' ) );

output:

array(2) { [0]=> string(7) "result1" [1]=> string(7) "result2" }

and a sql select:

SELECT * FROM xx WHERE url IN (".implode(',',$array).") ORDER BY id DESC

but the output is:

array(0) { }

If I run the SELECT into mysql, the results are correct, but in php page not working.

Erfo
  • 37
  • 1
  • 8
  • http://dev.mysql.com/doc/en/string-literals.html – Funk Forty Niner Feb 01 '16 at 17:20
  • Have you tried echoing out `SELECT * FROM xx WHERE url IN (".implode(',',$array).") ORDER BY id DESC` and seeing what it returns? The result should make it obvious! – drmonkeyninja Feb 01 '16 at 17:22
  • @Fred-ii- I'm pretty sure there is a duplicate of this question on SO somewhere, but I'm not sure if the one you marked as duplicate quite fits the bill... – Elias Van Ootegem Feb 01 '16 at 17:27
  • @EliasVanOotegem Marc B closed questions just like this before (I have a very good memory for stuff like this) ;-). The string literals in the dupe apply to this one too. – Funk Forty Niner Feb 01 '16 at 17:29
  • 1
    @Fred-ii-: Ok, just thought I'd comment about it and give you a chance to motivate the choice of dupe, before reopen were being cast because at first glance, the linked question seems to be more about MySQL syntax in general. If there is a bit about quoting strings in there, then cool – Elias Van Ootegem Feb 01 '16 at 17:32
  • @Fred-ii- I opened a new discussion because I didn't know the problem (single quotes, double quotes, ...). – Erfo Feb 01 '16 at 17:58

2 Answers2

4

If the values in $array are strings, then your query has invalid syntax. Consider this:

$array = ['foo', 'bar'];
$q = "SELECT * FROM xx WHERE url IN (".implode(',',$array).") ORDER BY id DESC";

The resulting query will be:

SELECT * FROM xx WHERE url IN (foo, bar) ORDER BY id DESC

Whereas you need:

SELECT * FROM xx WHERE url IN ("foo", "bar") ORDER BY id DESC

Change the implode to something like this:

$q = "SELECT * FROM xx WHERE url IN ('".implode("','",$array)."') ORDER BY id DESC";

Note the single quotes I added, now the resulting query will contain quoted strings in the IN clause:

SELECT * FROM xx WHERE url IN ('foo', 'bar') ORDER BY id DESC

Having said this, concatenating strings into a query still leaves you vulnerable to SQL injection. You should consider learning about prepared statements, and start using them

Elias Van Ootegem
  • 74,482
  • 9
  • 111
  • 149
2

You forgot some quotes:

SELECT * FROM xx WHERE url IN ('".implode("','",$array)."') ORDER BY id DESC
                                          ^---^
                               ^-------------------------^

Without those, you're generating

 ... IN (one','two) ...

which is an outright syntax error. And since you didn't mention getting any errors, you probably have absolutely NO error handling in your code, and simply assumed success - which is exactly the WRONG thing to do.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • The query the OP is generating will not contain `(one', 'two)`. The quotes are the delimiter of the concatenation string (`,`: a simple comma). He should add the outer quotes like you did, ***and*** add quotes to the delimiter – Elias Van Ootegem Feb 01 '16 at 17:22
  • op should then show the ACTUAL code, and not partial/invalid snippets. – Marc B Feb 01 '16 at 17:23
  • Thanks @MarcB, it's ok. – Erfo Feb 01 '16 at 18:00