0

I'm trying to do a simple query but just can't get it to work and can't find an answer online that's working correctly. All I want to do is query a table with a where clause, then order those results based on some value. Here is the query:

SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY FIELD(status, '$sortValue')

Here is what the data looks like (id is an int, campaign_id is an int, and status is an enum where the values default to undecided, but include good_fit, bad_fit, added):

id | campaign_id | status
----------------------------
 1 |          80 | good_fit
 2 |          80 | good_fit
 3 |          81 | bad_fit
 4 |          85 | added
 5 |          80 | added
 6 |          80 | bad_fit
 7 |          85 | undecided

Any thoughts on what could be going wrong? It currently works if I remove either the where clause or the Field function. Thanks

3
  • Yes sorry I'm not using prepared statements for demo purposes. $sortValue is coming from an ajax call, it's a dropdown value as a string that is either 'good_fit', 'bad_fit', 'added', 'undecided' Nov 2, 2020 at 17:02
  • @thesofaking Remember it's very easy for a "user" to mess up your ajax-call. The only defense against SQL injection is the prepared statement. Nov 2, 2020 at 18:17
  • 1
    Could you please be a little more specific about what the problem is? You've shown what the data looks like, but what do results look like and for what parameter values, and what do you expect them to look like instead? Please update your question.
    – Andriy M
    Nov 3, 2020 at 8:36

1 Answer 1

1

I didn't know MySQL understood "$campaignid" as a variable, in the examples I see "@" being used, as in "@campaignid". Anyway, in stead of FIELD (which needs a list of arguments, not an argument containing a list) I'd use LOCATE, with sortValue containing "good_fit,bad_fit,added,undecided":

SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY locate(status, '$sortValue')

"undecided" will have the highest location, preceded by added, etc.

db fiddle

PHP MySQL prepared statements

1
  • Again, use a prepared statement, and have a variable $sortValue without quotes, just comma separated values. A piece of cake for PHP. See the link I added to my answer. Nov 2, 2020 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.