Database sorted by a specific string then random

Question

Is there any way to order a database by a specific name stored in a function then random? For example, if $name = 'Hasan' then I want the query to select Hasan row first then the other rows randomly

<?php 

/* DATA BASE
id          |  Name
--------------+--------------------------------
'1'         |  Hasan
'2'         |  Ahmad   
'3'         |  Majid 
'4'         |  Hazem  
*/

if(isset($_GET['name'])) {
$name = $_GET['name'];
}
else {
$name = 0;
}

$query = mysqli_query($con, "SELECT * FROM database ORDER BY $name, rand()");
?>

Nick · Accepted Answer · 2019-11-13 06:07:42Z

1

You can make a test on the name in the ORDER BY clause, sorting by whether it matches or not. You should use a prepared statement to protect yourself from SQL injection. For example:

$stmt = $con->prepare('SELECT * FROM database ORDER BY Name = ? DESC, rand()');
$stmt->bind_param('s', $_GET['name']);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // ... do something
}

This works because MySQL treats boolean expressions as 0 (false) or 1 (true) in a numeric context, so the value of Name = $name will be 1 when the name matches and 0 otherwise, which you can then sort on descending.

Note that mysqli_stmt::get_result is only available with the mysqlnd native driver installed. Without that, you will need to use mysqli_stmt::bind_result, mysqli_stmt::store_result and mysqli_stmt::fetch to get your data.

edited Nov 13, 2019 at 6:07

answered Nov 13, 2019 at 5:54

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2019-11-13 05:54:12Z

0

You should be using a prepared statement here, something along these lines:

SELECT *
FROM database
ORDER BY name != ?;

Assuming you bound Hasan to the ? placeholder, this would give:

SELECT *
FROM database
ORDER BY name != 'Hasan';

This would place all Hasan records first (since false evaluates to 0), with all non Hasan records appearing after that. Note that your second sort level RAND() may not even be necessary, since MySQL does not guarantee any further default sorting order.

answered Nov 13, 2019 at 5:54

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

Database sorted by a specific string then random

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related