Page 1 of 1

PHP Query MYSQL - little help

Posted: Sun Sep 17, 2017 7:34 am
by tbird
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 :

Code: Select all

$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 :

Code: Select all

$query = "SELECT `ID`, `Desc` FROM `Rooms` ORDER BY `Desc` ASC" ;
to

Code: Select all

$query = "SELECT `ID`, `Desc` FROM `Rooms` WHERE `Desc` = 'Frontoffice' OR `Desc` = 'Backoffice' OR `Desc` = 'Manager' ORDER BY `Desc` ASC" ;
from :

Code: Select all

$query = "SELECT * FROM `Users`";
to

Code: Select all

$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.

Re: PHP Query MYSQL - little help

Posted: Sun Sep 17, 2017 11:04 am
by requinix
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.

Re: PHP Query MYSQL - little help

Posted: Sun Sep 17, 2017 12:50 pm
by tbird
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.

Re: PHP Query MYSQL - little help

Posted: Sun Sep 17, 2017 2:37 pm
by requinix
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.

Re: PHP Query MYSQL - little help

Posted: Sun Sep 17, 2017 4:19 pm
by tbird
By mistake i forgeted to put the last part of the code:

Code: Select all

$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...

Re: PHP Query MYSQL - little help

Posted: Mon Sep 18, 2017 4:10 am
by requinix
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...

Re: PHP Query MYSQL - little help

Posted: Mon Sep 18, 2017 7:04 am
by tbird
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 :

Code: Select all

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 :

Code: Select all

 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 :

Code: Select all

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.

Re: PHP Query MYSQL - little help

Posted: Mon Sep 18, 2017 8:01 am
by tbird
Well with your help i think i got`it work :D
What i did :

Code: Select all

$query = "SELECT `ID`, `Desc` FROM `Rooms` WHERE `Desc` IN ('frontoffice' , 'backoffice' , 'manager') ORDER BY `Desc` ASC" ;

Code: Select all

 $query = "SELECT * FROM `Users`  WHERE `Desc` IN ('frontoffice' , 'backoffice' , 'manager')  ";

Code: Select all

 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 ?

Re: PHP Query MYSQL - little help

Posted: Mon Sep 18, 2017 9:56 am
by requinix
"WHERE changed to AND" is exactly what I was hoping you would find :D

Re: PHP Query MYSQL - little help

Posted: Mon Sep 18, 2017 12:13 pm
by tbird
Thanks, i`m so proud :drunk:

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