Jump to content

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


Gotharious

Recommended Posts

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

 

 

Link to comment
Share on other sites

$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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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' )

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

}
?>

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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
}

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.