PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Oct 17, 2017 1:26 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
PostPosted: Sun Sep 17, 2017 7:34 am 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
Hello guys,

I need a little help with some php and MySQL queries. I have one application made by someone from GitHub that I will like to modify a little for my needs. The aplications is a FIAS-PMS from pbxinaflash with witch you can checkin, checkout and set wakeup call for one extension. The goal is to create a different report for extension used by office and guests. For guests is working perfectly.

The code looks like this :


Syntax: [ Download ] [ Hide ]
$dbconnection = mysql_connect($dbhost, $dbuser, $dbpass) or die("Database connection failed");
    mysql_select_db($dbname) or die("data base Hotel-Rooms open failed");

         $query = "SELECT `ID`, `Desc` FROM `Rooms` ORDER BY `Desc` ASC";
         $result = mysql_query($query) or die ("Couldn't execute SQL query on Hotel-Users table.") ;
    mysql_close($dbconnection);
    echo " <SELECT ID=\"RoomsComboBox\" NAME=\"RoomsComboBox\">";
    echo "<OPTION VALUE=\"ALL\" SELECTED> ".$SecLab22." </OPTION>";
    while ($row = mysql_fetch_array($result))  {
    echo "<OPTION VALUE=\"$row[0]\">" . $row[1] . "</OPTION>";
    }
    echo "</SELECT>";
echo "</TD>" ;
echo "<TD><INPUT TYPE=\"SUBMIT\" NAME=\"SearchButton\" VALUE=\"".$SecLab21."\"></TD>\n" ;
echo "</TR>\n" ;
echo "</TABLE></div>\n";
echo "</FORM>\n";

if(isset($_POST['SearchButton'])) :


echo

 "<TABLE cellSpacing=0 cellPadding=0 width=900 border=0>\n" ;
echo "<TR><TD>Id</TD><TD>".$SecLab7."</TD><TD>".$SecLab14."</TD><TD>".$SecLab15."</TD><TD>".$SecLab17."</TD><TD>".$SecLab31."</TD><TD>".$SecLab1."</TD>" ;
    $dbconnection

= mysql_connect($dbhost, $dbuser, $dbpass) or die("Database connection failed");
    mysql_select_db($dbname) or die("data base Hotel-Rates open failed");

    $between = '';
    $room = '';

        $query = "SELECT * FROM `Users`";
        $RoomsComboBox = $_POST['RoomsComboBox'];

    $FROMD = $_POST['FROMD'] ;
    $FROMM = $_POST['FROMM'] ;
    $FROMY = $_POST['FROMY'] ;

    $TOD = $_POST['TOD'] ;
    $TOM = $_POST['TOM'] ;
    $TOY = $_POST['TOY'] ;

    if ($RoomsComboBox !== 'ALL') {
        $room = " `Room` = '" . $RoomsComboBox . "'";
    }

    if ($FROMD !== '' AND $FROMM !== '' AND $FROMY !== '') {
        $from = " `Checkout` >= '" . $FROMY . "-" . $FROMM . "-" . $FROMD . "'";
        if ($TOD !== '' AND $TOM !== '' AND $TOY !== '') {
            $to = " AND `Checkout` <= '" . $TOY . "-" . $TOM . "-" . $TOD . "'";
        }
    $between = $from . $to;
    }


    If ($between !== '' OR $room !== '') {
        $query = $query . " WHERE" ;
    }

    If ($between !== '' AND $room !== '') {
        $query = $query . $between . " AND" . $room;
    }

    If ($between !== '' AND $room == '') {
        $query = $query . $between;
    }

    If ($between == '' AND $room !== '') {
        $query = $query . $room;
    }

    $query = $query . " ORDER BY ID DESC";
    //printf($query);



What I did :
From :
Syntax: [ Download ] [ Hide ]
$query = "SELECT `ID`, `Desc` FROM `Rooms` ORDER BY `Desc` ASC" ;

to
Syntax: [ Download ] [ Hide ]
$query = "SELECT `ID`, `Desc` FROM `Rooms` WHERE `Desc` = 'Frontoffice' OR `Desc` = 'Backoffice' OR `Desc` = 'Manager' ORDER BY `Desc` ASC" ;


from :
Syntax: [ Download ] [ Hide ]
$query = "SELECT * FROM `Users`";

to
Syntax: [ Download ] [ Hide ]
$query = "SELECT * FROM `Users` WHERE `Desc` = 'Frontoffice' OR `Desc` = 'Backoffice' OR `Desc` = 'Manager' " ;



Partial is working, I can see in the combobox only office extensions, i can search if i peek " all " from dropdown , but if i choose something else for exemple Frontoffice and click search the page is replaying with one error.

The date search is not working at all. I have to advice you guys, i don`t know php and MySQL just some very basics :) Please can someone help me with some syntaxs ? Thanks a lot.


Top
 Profile  
 
PostPosted: Sun Sep 17, 2017 11:04 am 
Online
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6571
Location: WA, USA
tbird wrote:
from :
$query = "SELECT * FROM `Users`";
to
$query = "SELECT * FROM `Users` WHERE `Desc` = 'Frontoffice' OR `Desc` = 'Backoffice' OR `Desc` = 'Manager' " ;

Why did you do that? I wouldn't think Users has a Desc column.


Top
 Profile  
 
PostPosted: Sun Sep 17, 2017 12:50 pm 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
Well because i dont know another way to query just those 3 extensions. In the database in the table Users it is a Desc field.
Also it works i can see that phones if i click ALL in the combobox, but if i click on one of that 3 extensions and after that the search button i have one error.


Top
 Profile  
 
PostPosted: Sun Sep 17, 2017 2:37 pm 
Online
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6571
Location: WA, USA
Users really has a Desc?

Okay. Well, if you have an error then you need to look into it. What is the error? If it's the message from some generic "... or die('Oh no!')" (which is bad practice, by the way) then you need to get more information.

Also, the mysql extension and its mysql_* functions are so old and bad that recent versions of PHP don't have it anymore. Keep that in mind in case/when you want to upgrade.


Top
 Profile  
 
PostPosted: Sun Sep 17, 2017 4:19 pm 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
By mistake i forgeted to put the last part of the code:

Syntax: [ Download ] [ Hide ]
$query = $query . " ORDER BY ID DESC";
        //printf($query);

        $result = mysql_query($query) or die("Web site query failed");
        mysql_close($dbconnection);
        while ($row = mysql_fetch_array($result)) {
        echo "<TR><TD><FONT face=verdana,sans-serif>" . $row["ID"] . "</TD><TD>" . $row["Desc"]  . "</TD><TD>" .$row["Name"] ."</TD><TD>" . $row["Checkin"] . "</TD><TD>" . $row["Checkout"] . "</TD><TD>" . $row["Total"] . "</TD><TD><a href=\"ec.php?Ext=" .$row["Ext"] . "&Checkin=" . $row["Checkin"] . "&Checkout=" . $row["Checkout"] ."\">".$SecLab29."</a></TD></TR>\n" ;
        }
echo "</TABLE>\n";
endif;



Is strange because i do not have a error in mysql log ( i enabled first ) , and also i do not have in php log or apache log.
The error ocurs only in web page " Web site query failed " I assume that is part of the syntax with problem...


Attachments:
desc.jpg
desc.jpg [ 61.48 KiB | Viewed 351 times ]
Top
 Profile  
 
PostPosted: Mon Sep 18, 2017 4:10 am 
Online
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6571
Location: WA, USA
As I implied, you need to use mysql_error() to get an actual error message. Something that will be useful, which "Web site query failed" is not.
And I do see the problem but the point right now is to get that message; with it you may see the problem yourself.

The Desc values being empty won't cause the query to fail but would mean that none of the rows match your conditions. So maybe the conditions shouldn't be in there after all...


Top
 Profile  
 
PostPosted: Mon Sep 18, 2017 7:04 am 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
Well i spoted something in mysql log :D But i don`t know how to fixed :)
So with the original code without any modification the log looks like this :
Syntax: [ Download ] [ Hide ]
6433 Connect   root@localhost on
                 6433 Init DB   Hotel
                 6433 Query     SELECT `ID`, `Desc` FROM `Rooms` Where `desc` = 'frontoffice' ORDER BY `Desc` ASC
                 6433 Quit
                 6434 Connect   root@localhost on
                 6434 Init DB   Hotel
                 6434 Query     SELECT * FROM `Users`   ORDER BY ID DESC
                 6434 Quit
170918 14:01:05  6435 Connect   root@localhost on
                 6435 Init DB   Hotel
                 6435 Query     SELECT `ID`, `Desc` FROM `Rooms` Where `desc` = 'frontoffice' ORDER BY `Desc` ASC
                 6435 Quit
                 6436 Connect   root@localhost on
                 6436 Init DB   Hotel
                 6436 Query     SELECT * FROM `Users`   WHERE  `room` = '95' ORDER BY ID DESC
 



With the modify of the code :

Syntax: [ Download ] [ Hide ]
 6445 Connect   root@localhost on
                 6445 Init DB   Hotel
                 6445 Query     SELECT `ID`, `Desc` FROM `Rooms` Where `desc` = 'frontoffice' ORDER BY `Desc` ASC
                 6445 Quit
                 6446 Connect   root@localhost on
                 6446 Init DB   Hotel
                 6446 Query     SELECT * FROM `Users` where `desc` = 'frontoffice'   WHERE  `room` = '95' ORDER BY ID DESC
                 6446 Quit
 



Looks like the second query from here :

Syntax: [ Download ] [ Hide ]
If ($between !== '' OR $room !== '') {
                $query = $query . " WHERE" ;
        }
       
        If ($between !== '' AND $room !== '') {
                $query = $query . $between . " AND" . $room;
        }

        If ($between !== '' AND $room == '') {
                $query = $query . $between;
        }

        If ($between == '' AND $room !== '') {
                $query = $query . $room;
        }

        $query = $query . " ORDER BY ID DESC";


Is my problem, any tips how can i avoid this ? Thanks a lot.


Top
 Profile  
 
PostPosted: Mon Sep 18, 2017 8:01 am 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
Well with your help i think i got`it work :D
What i did :
Syntax: [ Download ] [ Hide ]
$query = "SELECT `ID`, `Desc` FROM `Rooms` WHERE `Desc` IN ('frontoffice' , 'backoffice' , 'manager') ORDER BY `Desc` ASC" ;

Syntax: [ Download ] [ Hide ]
 $query = "SELECT * FROM `Users`  WHERE `Desc` IN ('frontoffice' , 'backoffice' , 'manager')  ";


Syntax: [ Download ] [ Hide ]
 If ($between !== '' OR $room !== '') {
                $query = $query . " WHERE ( Changed to AND ) " ;
        }
 


And now looks that everything is running perfectly, it is a better aproch to this ?


Top
 Profile  
 
PostPosted: Mon Sep 18, 2017 9:56 am 
Online
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6571
Location: WA, USA
"WHERE changed to AND" is exactly what I was hoping you would find :D


Top
 Profile  
 
PostPosted: Mon Sep 18, 2017 12:13 pm 
Offline
Forum Newbie

Joined: Sun Sep 17, 2017 7:29 am
Posts: 6
Thanks, i`m so proud :drunk:

Moderators : please close this topic, the problem no longer exists.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Bing [Bot], Exabot [Bot], Google [Bot] and 17 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group