-4

I've consistently noticed that when I try to use an array in a MySQL query that is within an Ajax Call (at least, I've only tried it from within an Ajax call) on a special-character-delimiter-separated string, my query only works if the character is a comma. For example, this works:

$myString = "String1,String2,String3,String4";
$ExplodedString =  explode(",", $myString);
$sql = mysqli_query($cxn, "SELECT user FROM login WHERE column IN ('".implode("','", $ExplodedString)."') ORDER BY user");

But I can never get another special character, even the underscore, to return ANYTHING:

$myString = "String1_String2_String3_String4";
$ExplodedString =  explode("_", $myString);
$sql = mysqli_query($cxn, "SELECT user FROM login WHERE column IN ('".implode("'_'", $ExplodedString)."') ORDER BY user");

//There are no error messages in my error log, even though error reporting is ON

Any ideas as to why?

The One and Only ChemistryBlob
  • 7,864
  • 10
  • 36
  • 69

1 Answers1

1

You imploded (glued) it again with an underscore, use ,:

$sql = "SELECT user FROM login WHERE column IN ('".implode("','", $ExplodedString)."') ORDER BY user";
                                                        //   ^ you glued it with comma not underscore

Note: Actually is quite easy to debug this.

Put the query first on a separate variable first, then just use echo $sql;. Check up commas, reserved words, etc. so that you know what to expect.

And of course don't forget to turn on error reporting:

error_reporting(E_ALL);
ini_set('display_errors', '1');

This is the correct usage on IN()

Kevin
  • 41,694
  • 12
  • 53
  • 70
  • yes, which is not valid in the mysql querry –  Sep 22 '14 at 01:10
  • OK @Dagon...thank you for the answer...I simply did not know it MUST be a comma, especially since the Manual at http://php.net/manual/en/function.implode.php shows an example implode() with a colon – The One and Only ChemistryBlob Sep 22 '14 at 01:13
  • @TheOneandOnlyChemistryBlob thats about it, Dagon summarized it, why are you imploding it with `_` – Kevin Sep 22 '14 at 01:13
  • 1
    that's the php manual, its the mysql syntax that requires a comma –  Sep 22 '14 at 01:14
  • Because I needed another delimiter for my table....lots of comma separated strings and for mundane purposes of clarity for me to edit my table I wanted to introduce an underscore delimiter – The One and Only ChemistryBlob Sep 22 '14 at 01:15
  • no db should have delimited strings, sounds like poor db design –  Sep 22 '14 at 01:17
  • @Dagon, delimiters work beautifully for me....try to keep an open mind – The One and Only ChemistryBlob Sep 22 '14 at 01:19
  • @Ghost...my error reporting is already turned on – The One and Only ChemistryBlob Sep 22 '14 at 01:19
  • says the man posting the question without checking the syntax for his select query. –  Sep 22 '14 at 01:20
  • @TheOneandOnlyChemistryBlob what are you saying? you want `('value1'_'value2'_'value3')` instead of `('value1', 'value2', 'value3')`? huh? – Kevin Sep 22 '14 at 01:22
  • No, the string "String1,String2,String3,String4" is written correctly...String1 is not a variable...I never said it was...String1 is a literal substring of $myString – The One and Only ChemistryBlob Sep 22 '14 at 01:26
  • @Dagon...my syntax is correct...the query works fine with commas and is 100% correct – The One and Only ChemistryBlob Sep 22 '14 at 01:27
  • @TheOneandOnlyChemistryBlob "Keep an open mind" doesn't apply to bad database design. There are objective measures about this sort of thing. You'll be much happier with your time on SO if you realize there are people here with more experience than you and learn from what they have to say. – Chris Hayes Sep 22 '14 at 01:27
  • @Chris Hayes...I read http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad. No delimiter separated strings in my table come from user input, the list size is kept to a maximum value that fits in the table, data type issues are not a problem where I've used delimiters. Rarely do users here ask a question "Why do you do that?"...bashing users with dogma is the theme of SO – The One and Only ChemistryBlob Sep 22 '14 at 01:31
  • 2
    @TheOneandOnlyChemistryBlob "bashing users with dogma is the theme of SO" - huh? Trying to encourage best practices is hardly bashing people with dogma. You might find [this link](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) interesting. (There's a great blog post somewhere on the same subject, and how people *really ought* to point out bad design where they see it and save others the headache, but I can't find it anywhere.) – Chris Hayes Sep 22 '14 at 01:42
  • OK..thanks for a good objective answer, so I'm upvoting your comment – The One and Only ChemistryBlob Sep 22 '14 at 01:44