CODEWITHSUNDEEP
phpsortingmysqlidatatablessql-order-by
hasan hasan
hasan hasan

Reputation: 65

Database sorted by a specific string then random

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()");
?>

Upvotes: 1

Views: 55

Answers (2)

Nick
Nick

Reputation: 147206

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

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.

Upvotes: 0

Related Questions