Code works fine in PHPMyAdmin but doesn't work in PHP

Gotharious · May 29, 2011

Hello everyone,

I'm having this problem which is really annoying, tried to solve it but couldn't, I write that code in PHPMyAdmin and it works great, but it doesn't work in the website it self

ok long story short, there are three tables, hotels, cities, countries

hotels include in addition to hotel info, 2 columns (city_id) and (country_id)

Cities include id and name and also countries include id and name

what I was trying to do, that when a person inputs a city or country name in the search form, it should get the hotels that exists in this city or country, but unfortunately it shows all the hotels in all cities and countries, although the pagination code for number of pages works just fine, it count the number of hotels in that city or country and show the number of pages correctly

so here is the code for both

for hotel search

class hotelManager
{


public function getHotel($where)
{		
	$where = isset($_POST['where']) ? $_POST['where'] : "";
	$dbObj = new DB();
	$sql = "select * from hotels where city_id = (select id from cities where name = '$where' ) or country_id = (select id from countries where name = '$where' )";
					$result = MYSQL_QUERY($sql);
	$arr = array();
	echo "<table>";
	while($row = mysql_fetch_array($result))
		{
			echo "<tr>";
			echo "<td valign=\"top\" width=\"120px\">";
			$rowid = $row['id'];
			$imageqry=mysql_query("SELECT * FROM `hotelphotos` where hotel_id='$rowid' LIMIT 1");
			$image=mysql_fetch_array($imageqry);
			$imagename=$image['attachmentName'];
			echo "<img src=\"foxmaincms/webroot/files/small/$imagename\"/>";
			echo "</td>";
			echo "<td valign=\"top\">";

			echo "<table>
			<tr>
			<td valign=\"top\">
			<a href=\"hotels.php?id=".$row['id']."\" class=\"titleslink\">".$row['name']."</a>
			</td>
			</tr>
			<tr>
			<td class=\"text\" valign=\"top\">
			".$row['location']."
			</td>
			</tr>
			</table>";

			echo "</td>";
			echo "</tr>";
		}
	echo "</table>";

for hotel pagination

<?php
		  include("includes/hotelsManager.php");
		  $hotelObj = new hotelManager();
		  $where = isset($_POST['where']) ? $_POST['where'] : "";
		  if(isset($_POST['where']))
		  {
			    
				$hotelObj -> getHotel($where);


					$per_page = 9;

					//Calculating no of pages
					$sql = "select * from hotels where city_id = (select id from cities where name = '$where' ) or country_id = (select id from countries where name = '$where' )";
					$result = MYSQL_QUERY($sql) or die("<br />No Hotels found in this city, please check the city name and try again");
					$count = mysql_num_rows($result);
					$pages = ceil($count/$per_page)
					?>


					<div id="loading" ></div>
					<div id="maincontent" ></div>
					<ul id="pagination">

thank you in advance

spiderwell · May 29, 2011

$hotelObj -> getHotel($where); this line of code, whatever it is you are passing, will never be used in the function getHotel due to this line in the function:

$where = isset($_POST['where']) ? $_POST['where'] : "";

so you have a function you pass a variable to it, that immediately get surpassed by the POST variable or set to ''. thats a very odd thing for starters. i believe also that name is a restricted word in mysql, so put `backticks` around it, just in case

please re write this so i can understand it:

but unfortunately it shows all the hotels in all cities and countries, although the pagination code for number of pages works just fine, it count the number of hotels in that city or country and show the number of pages correctly

mgoodman · May 29, 2011

Your query is correct so it seems that you are having a problem with $where = isset($_POST['where']) ? $_POST['where'] : "";

First off, you need to sanitize that input using mysql_real_esacpe_string. If you don't then that leaves your site open to an SQL injection attack, which could allow the attacker to destroy your database. If someone was to type

a'; DROP TABLE hotels --

in the location field then your entire hotels table would be gone. They could do the same for cities and countries. Or, if you had sensitive user information they could possibly take that as well.

As far as your problem getting the wrong results is concerned, try echoing out $where and see what it is being set to.

Gotharious · May 29, 2011

Thanks Spiderwell and mgoodman,

I will try to explain it again for you Spiderwell, for example if the table hotels has 1000 hotel, and only 50 hotels located in france

and I set the paginating that only 10 results appear per page

the user input is france, so I get 50 results ordered alphabetically that some is in france and some isn't, and I also get them no matter what city or country I type, but in the same time, I only get 5 pages as for 50 results, and if I type thailand, which has 200 in the list, then I get 200 hotel from different cities and countries, but only 20 pages with 200 result

so it's more of the calculation or count of hotels is right, it just brings out the wrong hotels

Mgoodman, thanks alot for your advice, will do that real_escape

I'm sorry to ask this, but what do you mean by echo $where?

Hope you guys would pardon me, English isn't my first language, so it's sometimes hard to understand or explain what i want

and yes SpiderWell, I'm a total noob in this, but unfortunately I'm in a situation that I have to get this fixed tonight :/

mgoodman · May 29, 2011

I'm sorry to ask this, but what do you mean by echo $where?

<?php echo $where; ?>

Basically you just want to see what $where is getting set to. I think that's probably what is breaking your query. You could also try to echo out the query, but just doing where would be simpler and save time.

spiderwell · May 29, 2011

in the function getHotel, at this line:

echo "<table>";

before it put

echo $sql . "<br />";

and it will show you exactly what is passed into the sql. very good for working out why things dont work!

do the same thing in the pagenation bit too, so you can see what is going on in that section and why the count works, but the hotels are wrong.

Gotharious · May 29, 2011

Mgoodman,

I removed the includes and pagination code and instead I typed

<?php echo $where; ?>

and it showed the same input I put in the search form

I typed france and the result came out as france

Gotharious · May 29, 2011

SpiderWell, I did as you said echo $sql and here is the result i've got

select * from 'hotels' where city_id like (select id from cities where name like 'france' or country_id like ( select id from countries where name like 'france' )

select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )

mgoodman · May 29, 2011

Your query is flawed. You need to remove the like part next to city_id an country_id.

Gotharious · May 29, 2011

but still if I replaced it with = it would give the same result

or should I replace it with something else?

Gotharious · May 30, 2011

ok, I made or die(sql_error)) and that's what it showed

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in *********hotelsManager.php on line 22

Unknown column 'france' in 'where clause'

mikesta707 · May 30, 2011

Did you just paste your query a bunch of times, or is your actual query that mass of text

select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )

if the former, would it be safe to say that your query is this

select * from hotels where city_id = (select id from cities where name = 'france' ) or country_id = (select id from countries where name = 'france' )

and where does this query fit in

select * from 'hotels' where city_id like (select id from cities where name like 'france' or country_id like ( select id from countries where name like 'france' )

that last query is missing a closing parenthesis after "like 'france'"

as for your usage of like, since you don't use any wildcard, in most instances using LIKE and = (equal to) would produce the same thing.

Now just to clarify, so I understand where you are in the process of fixing this, when you do this query you get a bunch of different hotels not relating to the $where variable, but the count of them is correct (IE if they input france. you get back 50 hotels (the number in france) but they are not french hotels?)

Also, as for the line in your objects method that overwrites the $where parameter, that is bad practice. One of the main concepts in Object oriented programming (OOP) is encapsulation, which is basically the practice of keeping object data contained within objects, and non-object data outside of objects. By using the $_POST super global in your object, you kind of break this rule.

It works because $_POST is a super global, but now you object isn't very general. What if you want to change the name of that $_POST variable, or want to use that object in another script. Now you can't because it depends on a $_POST variable from outside the class that may or may not be available. The kicker is, your method is set up to use a passed on argument, and you actually do the same checking before you call the object method (where you SHOULD be doing the check of the $_POST variable, and assigning it a useful value). Checking the variable for a useful value is good to do in a method, but using non-object data is kind of defeating the purpose of an object.

EDIT:

as for your latest reply I don't see you erroneously using your $where variable as a column. could you post (or repost) the query that is causing this error?

Gotharious · May 30, 2011

Ok here is the whole queries, mate

Hotel.php

<?php
		  include("includes/hotelsManager.php");
		  $hotelObj = new hotelManager();
		  $where = isset($_POST['where']) ? $_POST['where'] : "";
		  if(isset($_POST['where']))
		  {
			    
				$hotelObj -> getHotel($where);


					$per_page = 9;

					//Calculating no of pages
					$sql = "select * from hotels where city_id = (select id from cities where name = '$where' ) or country_id = (select id from countries where name = '$where' )";
					$result = MYSQL_QUERY($sql) or die("<br />No Hotels found in this city, please check the city name and try again");
					$count = mysql_num_rows($result);
					$pages = ceil($count/$per_page)
					?>


					<div id="loading" ></div>
					<div id="maincontent" ></div>
					<ul id="pagination">
					<?php
					//Pagination Numbers
					for($i=1; $i<=$pages; $i++)
					{
					echo '<li class="paging" id="'.$i.'">'.$i.'</li>';
					}
				?>

hotelsManager.php

class hotelManager
{


public function getHotel($where)
{		
	$where = isset($_POST['where']) ? $_POST['where'] : "";
	$dbObj = new DB();
	$where = $_POST['where'];
	$sql = "select * from hotels where city_id =(select id from cities where name = '$where') or country_id =(select id from countries where name = '$where')";
	$result = mysql_query($sql);
	$arr = array();
	echo "<table>";
	while($row = mysql_fetch_array($result)
		or die(mysql_error()))
		{
			echo "<tr>";
			echo "<td valign=\"top\" width=\"120px\">";
			$rowid = $row['id'];
			$imageqry=mysql_query("SELECT * FROM `hotelphotos` where hotel_id='$rowid' LIMIT 1");
			$image=mysql_fetch_array($imageqry);
			$imagename=$image['attachmentName'];
			echo "<img src=\"foxmaincms/webroot/files/small/$imagename\"/>";
			echo "</td>";
			echo "<td valign=\"top\">";

			echo "<table>
			<tr>
			<td valign=\"top\">
			<a href=\"hotels.php?id=".$row['id']."\" class=\"titleslink\">".$row['name']."</a>
			</td>
			</tr>
			<tr>
			<td class=\"text\" valign=\"top\">
			".$row['location']."
			</td>
			</tr>
			</table>";

			echo "</td>";
			echo "</tr>";
		}
	echo "</table>";

paginate_hotels.php

<?php
include('config.php');
include('textManager.php');
$per_page = 9;
if($_GET)
{
$page=$_GET['page'];
}

$where = isset($_POST['where']) ? $_POST['where'] : "";
$datein = isset($_POST['indate']) ? $_POST['indate'] : "";
$dateoout = isset($_POST['dateout']) ? $_POST['dateout'] : "";
$notspecified = isset($_POST['notspecified']) ? $_POST['notspecified'] : "";
$groupwithchildren = isset($_POST['groupwithchildren']) ? $_POST['groupwithchildren'] : "";


$start = ($page-1)*$per_page;
$sql = "select * from hotels where city_name = '$where' or country_name like (select id from cities where name = '$where'
	or name like '$where') order by id limit $start,$per_page";
$result = mysql_query($sql);
?>
<table width="100%">
<?php
while($row = mysql_fetch_array($result))
or die(mysql_error())
{
echo "<tr>";
			echo "<td valign=\"top\" width=\"120px\">";
			$rowid = $row['id'];
			$imageqry=mysql_query("SELECT * FROM `hotelphotos` where hotel_id='$rowid' LIMIT 1");
			$image=mysql_fetch_array($imageqry);
			$imagename=$image['attachmentName'];
			echo "<img src=\"foxmaincms/webroot/files/small/$imagename\"/>";
			echo "</td>";

				echo "<td valign=\"top\" width=\"400px\">";
				echo "<table>
				<tr>
				<td valign=\"top\" class=subtitle4>
				<strong>".$row['name']."</strong>					
				</td>
				</tr>
				<tr>
				<td class=\"text2\" valign=\"top\">
				".$row['location']."
				</td>
				</tr>

				<tr>
				<td class=text>";
				echo (($row['details'] != "") ? textManager::substrTopic($row['details'],300) : "No details");
				echo "<a href=\"hotels.php?id=".$row['id']."\" class=\"titleslink2\"> See More..</a>";
				echo "</td>
				</tr>
				</table>";

			echo "</td>";

			echo "<td valign=\"bottom\" align=right>";
			echo "<a href=\"hotels.php?id=".$row['id']."\" class=\"seemore\">Book Now</a>";
			echo "</td>";
echo "</tr>";

echo "<tr><td colspan=\"3\" height=30px align=center><div class=hrdecoration></div></td></tr>";

}
?>

mikesta707 · May 30, 2011

I asked for the single query that is causing the error you mentioned. not all of your code

Gotharious · May 30, 2011

then I believe this is what's causing the error

$sql = "select * from hotels where city_id =(select id from cities where name = '$where') or country_id =(select id from countries where name = '$where')";

mikesta707 · May 30, 2011

after doing some research on nested SELECT queries, it appears that you should be using the command IN.

For example

$sql = "select * from hotels where city_id IN (select id from cities where name = '$where') or country_id IN (select id from countries where name = '$where')";

Gotharious · May 30, 2011

mikesta707, You're a genius, I've spent the last 3 weeks trying to figure this out, thank you, really

I know this may sound odd, but could you recommend me a place where i can learn more about php and such, as I can't find neither courses nor books in Egypt :/

again, thanks alot

mikesta707 · May 30, 2011

tizag.com is a website I used when trying to learn the basics of php/mysql/javascript. I don't know how reliable the data is now, but a few years ago it was good. Also, googling for certain topics (like nested select statements) usually works well. The problem lies in figuring out what the topic is known by on the internet. All in all, practice is really the best way to learn.

Gotharious · May 30, 2011

Thank you very much.

Another thing if you could help me with, I have a slide show for images, that I want it to load images from a folder on the server where the folder name matches the id

I mean say if the link is http://www.mydomain.com/hotels.php?id=204

I want the images located in folder 204 to be loaded on the slide show

mikesta707 · May 30, 2011

You can use a combination of $_GET variables (for which I will not provide a link as I assume you know how to use it) and glob(). THe get variable will be what you use to select the folder, and glob to get all the images. Glob will give you an array of the filenames, and you can use this array combined with a foreach loop to output the images. for example

$id = $_GET['id'];//we should probably sanitize this just in case.
$images = glob("path/to/folder/$id/*.jpg");//this will grab all the jpgs in the folder using pattern matching.
//for more information on what the wildcard charactr * means go to the manual page, linked above.
//if i wanted to get all the files in the folders (meaning there is a chance we can get non image files 
//we can do this
$images = glob("path/to/folder/$id/*.*");
//if we want to limit it to 2 or more types of extensions, we can use the brace operator
$images = glob("/path/to/folder/$id/{*.jpg,*.png}", GLOB_BRACE);
//the above would get all .jpgs and .pngs
//now we just loop through and do what we want
foreach($images as $image){
//in this case ill just echo it
echo $image;
//this will just echo the image name though.
//you probably want to echo it inside the src attribute of an img tag
}

Gotharious · May 30, 2011

Mikesta, thanks a lot, you're amazing, and the best part is that you actually explain everything to me in a simple way, thanks a lot

mikesta707 · May 30, 2011

No problem. I'm a tutor, so I get a lot of practice explaining programming problems in a simplistic way. If your thread is resolved, you can click the topic solved button at the bottom of the thread

Gotharious · May 30, 2011

Ok thank you, but one last thing

this used to work before but now it gives me error when i try to book a room

Fatal error: Cannot redeclare hotelManager::getHotel() in /home/www/gotharious.net/hotelsManager.php on line 47

mikesta707 · May 30, 2011

It appears you are redefining the getHotel method. Without seeing the current code I can't be of much help, but If you look in your code, and you find getHotel defined twice, you should delete the oldest version of the function.

spiderwell · May 30, 2011

hey good to see this got sorted, i had to go last night. and as above, the function will have been defined more than once for that error to occur

Sign In

Code works fine in PHPMyAdmin but doesn't work in PHP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information