Jump to content

sql query issues


Go to solution Solved by Barand,

Recommended Posts

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

 

Link to post
Share on other sites
  • Solution
Posted (edited)

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 by Barand
Link to post
Share on other sites
$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?  

Link to post
Share on other sites
<?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

Link to post
Share on other sites

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";    

image.png.37a1d55af0309f29f4586ae828fe91a7.png

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 by Barand
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.