Sort or ORDER BY database results by a column after removing irrelevant keywords

Question

I currently have a problem regarding splitting strings stored in a variable in PHP.

I have a website and a php script that takes a string (4-6 words e.g. The University of Reading) from a database field, store it in a variable and displays it on the screen. It loops around the database and outputs many different strings.

The strings contain names of universities, e.g. Brunel University, University of Reading etc. The php script displays the names of the universities in alphabetical order. Obviously it reads 'University of...' first, therefore sorts many universities in the wrong order.

e.g. the list:

Brunel University
University of Reading
University of Birmingham
Cardiff University 
University of Essex

is currently sorted like:

Brunel University
Cardiff University
University of Birmingham
University of Essex
University of Reading

whereas it should be sorted like:

Birmingham, University of 
Brunel University
Cardiff University 
Essex, University of 
Reading, University of 

I need to search through each string before it is displayed to check whether it contains the words 'University of', if it does, I need to remove these words and put them at the end of the string. I then need to display the string.

I hope this makes sense, if anyone could advise me on how to do this I would appreciate it.

EDIT

The code I'm using is below. I figured I may need to use the 'explode' function somewhere?

<tr>
<td><table class="flat-table flat-table-2" width="100%">
<tr>
</tr>

<tr style="background-color:#FFF;">
<td class="table-head">Name</td>
<td class="table-head">Average Hall Rating</td>
</tr>

<?php
//run a query to find all the fields in the hall table that belong to the specific     university, using the id in the url ($current_id)
if ($s = $db->prepare("SELECT * FROM university ORDER BY name ASC")) { 
    $s->execute(); // Execute the prepared query.

    //search table for all fields and save them in $hall
    $unitemp = $s->fetchAll();
    foreach( $unitemp as $uni) {

    //store each halls id
    $uni_id = "university.php?id=$uni[id]";

    //loop and output hall names below
?>
<tr>
<td><? echo $uni['name'];?></td>
<td><? echo $uni['rating']; }}?></td>
</tr>
</table>
</td>
</tr>
</table> 

Answer 1

From your sample data, it's clear that you merely need to ORDER BY the name values after sanitizing the unwanted words. You don't need to bother with a prepare() call because you aren't injecting any variables into your SQL query.

MySQL implementation: (PHPize Demo)

$sql = <<<SQL
SELECT *
FROM university
ORDER BY REGEXP_REPLACE(name, ' ?University(?: of)? ?', '')
SQL;

foreach ($mysqli->query($sql) as $row) {
    echo "<div>{$row['name']}</div>\n";
}

---

If you are interested in sorting via PHP (for some unknown reason), call array_multisort() and pass a flat array of sanitized column values as the first parameter so that the rows of the result set are sorted in the second paramter.

Code: (PHPize Demo)

$result = $mysqli->query('SELECT * FROM university')->fetch_all(MYSQLI_ASSOC);
array_multisort(
    preg_replace('/ ?University(?: of)? ?/', '', array_column($result, 'name')),
    $result
);

foreach ($result as $row) {
    echo "<div>{$row['name']}</div>\n";
}

Answer 2

Glancing at this, it seems like you want to create an updated list, where the University names are listed as Reading, University for Universities that start with University. You would need to loop through your list, generating a new one that does this.

$universities = $UNIVERSITY_LIST_HERE
$uniNames[];
foreach ($unversities as $uni){
    $uniStr = substr($uni, 0, 12);
    if($uniStr == "University of"){
        // split the string
        $uniNames[] = substr($uni, 13) . ', ' . $uniStr;
    } else {
        $uniNames[] = $uni;
    }
}

You can then pass the $uniNames into your sort algorithm. PHP has a nice sort(); algorithm that you can use. Below, I'm printing out the names inside the foreach loop.

sort($uniNames);
foreach ($uniNames as $key => $val) {
    echo "uniNames[" . $key . "] = " . $val . "\n";
}

Answer 3

I have updated some lines in the code:

     <?php
     //run a query to find all the fields in the hall table that belong to the specific        university, using the id in the url ($current_id)
     if ($s = $db->prepare("SELECT * FROM university ORDER BY name ASC")) { 
         $s->execute(); // Execute the prepared query.

         //search table for all fields and save them in $hall
         $unitemp = $s->fetchAll();
         foreach( $unitemp as $uni) {

             $pieces = explode("of", $uni['name']);

             if(count($pieces)>1)
                 $response = $pieces[1].", ".$pieces[1]." of";
             else
                 $response = $pieces[0]; 

             echo $response;

      }