sql query issues

[stevochegeoj]

I have this code that i tried running it on my phpmyadmin it works fine ,but  when i use it on my php code to query its not working.What could be the issue?

 SELECT * FROM `roombook` ORDER BY (CASE stat
   WHEN 'Checked in' 	 THEN 1
   WHEN 'Booked' 	 THEN 2
   WHEN 'Deposit Confirmation' 	 THEN 3
   WHEN 'Email/phone' THEN 4
   WHEN 'Checked Out' 	THEN 5
   ELSE 'Cancelled' END) ASC, stat ASC

 

[Barand]

Define "it's not working".

Are you getting a PHP error, or a MySQL error message?

Why are you ordering by stat twice?

As an aside, your case statement looks weird in that you give numeric vlue to all statuses to sort on but as a default you have the string value "Cancelled". Have you considered making the stat column type ENUM?

Or, perhaps

ORDER BY FIELD(stat, 'Checked in', 'Booked', 'Deposit Confirmation', 'Email/phone', 'Checked Out', 'Cancelled')

 

Edited March 10, 2021 by Barand

[stevochegeoj]

$tsql = "select * from roombook ORDER BY FIELD(stat, 'Checked in', 'Booked', 'Deposit Confirmation', 'Email/phone', 'Checked out')";

 i re wrote my code as above but still it does not give the desired result. however when i run the same code on phpmyadmin it seems to work. what could be the issue?  

[Barand]

Show the rest of that query's processing code.

[stevochegeoj]

<?php
$tsql = "select * from roombook ORDER BY FIELD(stat, 'Checked in', 'Booked', 'Deposit Confirmation', 'Email/phone', 'Checked out')";
                                     $tre = mysqli_query($con, $tsql);
                                        while ($trow = mysqli_fetch_array($tre)) {

                                            $co = $trow['stat'];
                                            if ($co != "Cancelled" )  {
                                                echo "<tr>
                                                                           
                                                                            <th>" . $trow['FName'] . " " . $trow['LName'] . "</th>
                                                                            <th>" . $trow['Email'] . "</th>
                                                                            
                                                                            <th>" . $trow['TRoom'] . "</th>
                                                                         
                                                                            <th>" . $trow['cin'] . "</th>
                                                                            <th>" . $trow['cout'] . "</th>
                                                                                                                                                   
                                                                            <th>" . $trow['stat'] . "</th>
                                                                                                                                                        
                                                                            <th><a href='roombook.php?rid=" . $trow['id'] . " ' class='btn btn-primary'>Action</a></th>
                                                                            </tr>";
                                            }
                                        }
                                        ?>

here it is

[Barand]

Worked for me with a test table I created.

$tsql = "select * from roombook ORDER BY FIELD(stat, 'Checked in', 'Booked', 'Deposit Confirmation', 'Email/phone', 'Checked out')";
$tre = mysqli_query($con, $tsql);

echo "<table border='1' style='border-collapse: collapse;'>\n";

while ($trow = mysqli_fetch_array($tre)) {

    $co = $trow['stat'];
    if ($co != "Cancelled" )  {
        echo "<tr>
                                   
            <th>" . $trow['FName'] . " " . $trow['LName'] . "</th>
            <th>" . $trow['Email'] . "</th>
            
            <th>" . $trow['TRoom'] . "</th>
         
            <th>" . $trow['cin'] . "</th>
            <th>" . $trow['cout'] . "</th>
                                                                                   
            <th>" . $trow['stat'] . "</th>
                                                                                        
            <th><a href='roombook.php?rid=" . $trow['id'] . " ' class='btn btn-primary'>Action</a></th>
            </tr>";
    }
}
echo "</table>\n";    

I suggest you

• check your mysqli connection.
• Change your <th> to <td> for data rows
• Add "WHERE stat <> 'Cancelled'" to your query since you don't want to list them

Edited March 11, 2021 by Barand