Sql query prioritising values in php array

Question

column_x of a mySQL database contains values that might exist in a php array.

I want to query the database and prioritise any rows where the value for column_x exists in the PHP array.

My initial idea was to select a column_y that is 1 if column_x exists in the php array and 0 otherwise and then ORDER BY column_y DESC. Is this possible? If so, how do I go about it? Or, is there a better way to go about this?

Thanks!

Barmar · Accepted Answer · 2016-05-31 20:05:15Z

1

You can use ORDER BY column_x IN (list of values) DESC. When the condition is true, the value is 1, otherwise it's 0.

$array = array(1, 3, 4, 6);
$str = implode(', ', $array);

$sql = "SELECT * FROM yourTable
        ORDER BY column_x IN ($str) DESC";

If the values come from user input, make sure you sanitize them before inserting into the SQL.

answered May 31, 2016 at 20:05

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

4 Comments

GoldenLab88 Over a year ago

Thanks a lot! This is exactly what I needed :)

Barmar Over a year ago

You need to add additional parameters to ORDER BY if you want to control the ordering within the two groups. Otherwise the database orders them arbitrarily.

Barmar Over a year ago

Nothing in my code forces the blank rows to the end.

GoldenLab88 Over a year ago

OK, I've fixed it. Thanks Barmar!

Collectives™ on Stack Overflow

Sql query prioritising values in php array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related