Problem joining separate tables

Just build the data into a usable array you can then loop through. Here’s a sample. Not tested.

<?php
$hotels_array =array();
// SAMPLE Data array building for hotel //
$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
r.room_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
	LEFT JOIN rooms AS r
		ON r.hotel_id = h.hotel_id
	LEFT JOIN hotels_features AS hf
		ON hf.hotel_id = h.hotel_id
	LEFT JOIN features AS f
		ON f.features_id = hf.features_id
WHERE h.category = '$category'";

$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
	$hotels_array[$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
	$hotels_array[$row['hotel_id']]['address'] = $row['address'];
	$hotels_array[$row['hotel_id']]['city'] = $row['city'];
	$hotels_array[$row['hotel_id']]['state'] = $row['state'];
	$hotels_array[$row['hotel_id']]['country'] = $row['country'];
	$hotels_array[$row['hotel_id']]['zipcode'] = $row['zipcode'];
	$hotels_array[$row['hotel_id']]['phone'] = $row['phone'];
	$hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_name'] = $row['room_name'];
	$hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_beds'] = $row['room_beds'];
	$hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_floor'] = $row['room_floor'];
	$hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_number'] = $row['room_number'];
	$hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_description'] = $row['room_description'];
	$hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_name'] = $row['feature_name'];
	$hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_description'] = $row['feature_description'];
}
	//////////////////////////////////////////////
	//   All data is now in a usable array.     //
	//   Loop through the parts you want.       //
	//   $v = value array, just identify key    //
	//////////////////////////////////////////////

	foreach($hotels_array as $hotel_id => $v){
		$hotel_name = $v['hotel_name'];
		$address = $v['address'];
		$city = $v['city'];
		$state = $v['state'];
		$country = $v['country'];
		$zipcode = $v['zipcode'];
		$phone = $v['phone'];			
		// echo or build display of hotel info here
		
		//Go through rooms array
		foreach($v['rooms'] as $room_id => $r){
			$room_name = $r['room_name'];
			$room_beds = $r['room_beds'];
			$room_floor = $r['room_floor'];
			$room_number = $r['room_number'];
			$room_description = $r['room_description'];		
			// echo or build display of room info here			
		
		}
		//Go through hotels_features array
		foreach($v['hotels_features'] as $feature_id => $f){
			$feature_name = $r['feature_name'];
			$feature_description = $r['feature_description'];		
			// echo or build display of feature info here			
		
		}				
	}
?>

Hi there Drummin,

thank you very much - the code is working perfectly. You sure know your way around arrays. It must have taken some time to write the above code, so thanks again.

Now that this problem has been sorted out, I see that there’s another problem. I’m displaying a certain number of hotels per page, which is why I’m using the COUNT() function to count the number of hotel_ids. But again there’s unwanted multiplication of hotel_ids going on. Do you know of a way to just count the hotel_ids in the hotel table?

If you could please take a moment to look at the query.

Thank you!

$q = "SELECT COUNT(h.hotel_id), other columns...

FROM hotels AS h
INNER JOIN rooms AS r
ON r.hotel_id = h.hotel_id
INNER JOIN hotels_features AS hf
ON hf.hotel_id = h.hotel_id
INNER JOIN features AS f
ON f.features_id = hf.features_id
WHERE h.category = '$category'";

$r = @mysqli_query ($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];



And what if you use my query then use
count($hotels_array);

What does that show?

Thank you for getting back to me.

And what if you use my query then use
count($hotels_array);

What does that show?

I have done that and it displays 13, which is accurate since there are 13 hotels in the table at present. However, something is still not right. I set the number of hotels to display per page to 3, but there is only 1 hotel showing. If I set the number of hotels to 6, then only 2 hotels will display. Is it possible that the count($hotels_array) is accurate but that hotels take up 3 rows each? Since it is a multidimensional array? If that makes any sense…

There are 2 queries, the one just counts the number of hotels, and sets the $records variable. The other query is the same except that it fetches the data and includes a LIMIT clause. Currently the LIMIT clause would be LIMIT 0, 3 (to display 3 hotels per page). $start keeps track of the pagination. If the LIMIT clause in the query is the problem then I don’t know what to do.

Sorry to bother you with this, and I really appreciate your help.

Thanks again.

I have shortened the code somewhat:

 <?php
$hotels_array =array();
// SAMPLE Data array building for hotel //
$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
r.room_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
    LEFT JOIN rooms AS r
        ON r.hotel_id = h.hotel_id
    LEFT JOIN hotels_features AS hf
        ON hf.hotel_id = h.hotel_id
    LEFT JOIN features AS f
        ON f.features_id = hf.features_id
WHERE h.category = '$category' LIMIT $start, $display";

$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
    $hotels_array[$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
    $hotels_array[$row['hotel_id']]['address'] = $row['address'];
   // etc...
}
    foreach($hotels_array as $hotel_id => $v){
        $hotel_name = $v['hotel_name'];
        $address = $v['address'];

                // echo or build display of hotel info here

        //Go through rooms array
        foreach($v['rooms'] as $room_id => $r){
            $room_name = $r['room_name'];
            $room_beds = $r['room_beds'];

           // echo or build display of room info here

        }
        //Go through hotels_features array
        foreach($v['hotels_features'] as $feature_id => $f){
            $feature_name = $r['feature_name'];
            $feature_description = $r['feature_description'];

            // echo or build display of feature info here

        }
    }
?> 

Yes, the limit clause is the problem. Each join combination will produce a new row.

The easiest solution is to remove the limit clause and then just show the first three hotels. Disadvantage of course it that it will always query all hotels and possibly be quite slow.

Second solution is to break your query in two. The first query just get the hotel id for the first three hotels.

SELECT hotel_id FROM hotels WHERE hotel.category = ‘$category’ LIMIT $start, $display";

The do your big join query with

SELECT blah blah blah WHERE hotel.id IN (id1,id2,id2)

Third solution - After getting the second solution working and understanding what is going on then it should be possible to go back to a single query using a sub-query. But get the second solution running first. Easier to conceptualize and to debug.

No need to get all in a fuss over pagination, just add an extra incremental [KEY] to your hotels array and use these new keys to target records to show. (untested)

<?php
session_start();
// include connection


$hotels_array =array();
// SAMPLE Data array building for hotel //
$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
r.room_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
	LEFT JOIN rooms AS r
		ON r.hotel_id = h.hotel_id
	LEFT JOIN hotels_features AS hf
		ON hf.hotel_id = h.hotel_id
	LEFT JOIN features AS f
		ON f.features_id = hf.features_id
WHERE h.category = '$category'";

// Because we're adding pagination we will force an extra key $x for paging
$x=0;
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
	$hotels_array[$x][$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
	$hotels_array[$x][$row['hotel_id']]['address'] = $row['address'];
	$hotels_array[$x][$row['hotel_id']]['city'] = $row['city'];
	$hotels_array[$x][$row['hotel_id']]['state'] = $row['state'];
	$hotels_array[$x][$row['hotel_id']]['country'] = $row['country'];
	$hotels_array[$x][$row['hotel_id']]['zipcode'] = $row['zipcode'];
	$hotels_array[$x][$row['hotel_id']]['phone'] = $row['phone'];
	$hotels_array[$x][$row['hotel_id']]['rooms'][$row['room_id']]['room_name'] = $row['room_name'];
	$hotels_array[$x][$row['hotel_id']]['rooms'][$row['room_id']]['room_beds'] = $row['room_beds'];
	$hotels_array[$x][$row['hotel_id']]['rooms'][$row['room_id']]['room_floor'] = $row['room_floor'];
	$hotels_array[$x][$row['hotel_id']]['rooms'][$row['room_id']]['room_number'] = $row['room_number'];
	$hotels_array[$x][$row['hotel_id']]['rooms'][$row['room_id']]['room_description'] = $row['room_description'];
	$hotels_array[$x][$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_name'] = $row['feature_name'];
	$hotels_array[$x][$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_description'] = $row['feature_description'];
	$x++;
}
										
	////////////////////////////////////////
	////////////// Pagination //////////////
	////////////////////////////////////////
	if (isset($_POST['numrecords'])){
		$_SESSION['numrecords'] = "{$_POST['numrecords']}";
	}
	
	// Set number of Hotels to show by $_SESSION['numrecords'] OR Default number
	$records_per_page = (isset($_SESSION['numrecords']) ? "{$_SESSION['numrecords']}" : 3);	
									
	// $page defined as $_GET['page'] or 1 //
	$page = (isset($_GET['page']) ? (int)$_GET['page'] : 1);
	 		
	//////////// Total Records //////////////
	$total = count($hotels_array);
	 			
	//////////////Total Pages ///////////////
	$pages = ceil($total / $records_per_page);
					
	/*///////////////////////////////////////
	Our $hotels_array array starts with KEY 0, so adjust
	offset of $page - 1	times records per page
	*////////////////////////////////////////
	$offset = ($page - 1)  * $records_per_page;
								
	// Add 1 to offset for showing to user //
	$start = $offset + 1;
											
	////// Get last record being shown //////
	$end = min(($offset + $records_per_page), $total);
					
	/////////// Previous link ///////////////
	$prevlink = ($page > 1) ? "<a href=\\"?page=1\\" title=\\"First page\\">&laquo;</a> <a href=\\"?page=" . ($page - 1) . "\\" title=\\"Previous page\\">&lsaquo;</a>" : "<span class=\\"disabled\\">&laquo;</span> <span class=\\"disabled\\">&lsaquo;</span>";
				
	///////////// Next link /////////////////
	$nextlink = ($page < $pages) ? "<a href=\\"?page=" . ($page + 1) . "\\" title=\\"Next page\\">&rsaquo;</a> <a href=\\"?page=" . $pages . "\\" title=\\"Last page\\">&raquo;</a>" : "<span class=\\"disabled\\">&rsaquo;</span> <span class=\\"disabled\\">&raquo;</span>";
						
	//////// Paging information /////////////
	$paginate = "<div class=\\"paging\\">" . $prevlink . " Page " . $page . " of " . $pages . " pages, displaying " . $start . "-" . $end . " of " . $total . " results " . $nextlink . " </div>";
	
	////////////////////////////////////////
	////////////////////////////////////////

?>

<html>
<body>
<form method="post" action="">
<select name="numrecords" onchange="this.form.submit()">
<?php
foreach(range(5,$total,5) as $nr){
	$selected_numrecord = (isset($_SESSION['numrecords']) && $_SESSION['numrecords'] == $nr ? " selected=\\"selected\\"" : "");
	echo "<option value=\\"$nr\\"$selected_numrecord>$nr</option>\\r";
}
?>
</select>
</form>
<?php

if (($page > 0) && ($page <= $pages)){
	$start = ($page-1) * $records_per_page;		
	for($i=$start;$i<=($records_per_page+$start-1) && $i<$total;$i++){	
	
		//////////////////////////////////////////////
		//   All data is now in a usable array.     //
		//   Loop through the parts you want.       //
		//   $v = value array, just identify key    //
		//////////////////////////////////////////////
		
		foreach($hotels_array[$i] as $hotel_id => $v){
			$hotel_name = $v['hotel_name'];
			$address = $v['address'];
			$city = $v['city'];
			$state = $v['state'];
			$country = $v['country'];
			$zipcode = $v['zipcode'];
			$phone = $v['phone'];			
			// echo or build display of hotel info here
			
			//Go through rooms array
			foreach($v['rooms'] as $room_id => $r){
				$room_name = $r['room_name'];
				$room_beds = $r['room_beds'];
				$room_floor = $r['room_floor'];
				$room_number = $r['room_number'];
				$room_description = $r['room_description'];		
				// echo or build display of room info here			
			
			}
			//Go through hotels_features array
			foreach($v['hotels_features'] as $feature_id => $f){
				$feature_name = $r['feature_name'];
				$feature_description = $r['feature_description'];		
				// echo or build display of feature info here			
			
			}	
		}
	}
}

echo $paginate;
?>

Hi Drummin and ahundiak,

thanks to both of you for helping me with this. I can say that everything seems to be working now. :slight_smile:

@ Drummin,

thank you so much for your thorough example! It’s great and I appreciate the time you have spent to create the code and add all of the comments. I already have an existing pagination script so I’ve continued to use that, although your script will come in handy should I need something similar in the future.

@ ahundiak,

Second solution is to break your query in two. The first query just get the hotel id for the first three hotels.

SELECT hotel_id FROM hotels WHERE hotel.category = ‘$category’ LIMIT $start, $display";

The do your big join query with

Thank you, that’s what I have done. The LIMIT clause proved to be a problem in the query.

So, that’s it! Thanks again.

Cheers

Hi Drummin,

how are you doing?

Sorry to bother you again but could you please help me with a query? The past few hours have gotten me no where.

I’m using the query you gave me and I’m looping all of the content into an array. Now I need to use SUM() on one of the columns but it’s not returning the correct “sum”. The intermediary tables are messing things up again.

This is how I’m using the SUM() function:

Pseudo code:

$q = "SELECT columns... SUM(r.room_beds)total_rooms, other columns...INNER JOINS...GROUP BY h.hotel_name"

The query works when I use MIN() so I don’t know why SUM() doesn’t work…

If you have a moment to look at this. Thank you very much!!!

Your code from earlier:

 <?php
$hotels_array =array();
// SAMPLE Data array building for hotel //
$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
r.room_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
    LEFT JOIN rooms AS r
        ON r.hotel_id = h.hotel_id
    LEFT JOIN hotels_features AS hf
        ON hf.hotel_id = h.hotel_id
    LEFT JOIN features AS f
        ON f.features_id = hf.features_id
WHERE h.category = '$category'";

$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
    $hotels_array[$row['hotel_id']]['hotel_name'] = $row['hotel_name'];
    $hotels_array[$row['hotel_id']]['address'] = $row['address'];
    $hotels_array[$row['hotel_id']]['city'] = $row['city'];
    $hotels_array[$row['hotel_id']]['state'] = $row['state'];
    $hotels_array[$row['hotel_id']]['country'] = $row['country'];
    $hotels_array[$row['hotel_id']]['zipcode'] = $row['zipcode'];
    $hotels_array[$row['hotel_id']]['phone'] = $row['phone'];
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_name'] = $row['room_name'];
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_beds'] = $row['room_beds'];
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_floor'] = $row['room_floor'];
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_number'] = $row['room_number'];
    $hotels_array[$row['hotel_id']]['rooms'][$row['room_id']]['room_description'] = $row['room_description'];
    $hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_name'] = $row['feature_name'];
    $hotels_array[$row['hotel_id']]['hotels_features'][$row['features_id']]['feature_description'] = $row['feature_description'];
}
    //////////////////////////////////////////////
    //   All data is now in a usable array.     //
    //   Loop through the parts you want.       //
    //   $v = value array, just identify key    //
    //////////////////////////////////////////////

    foreach($hotels_array as $hotel_id => $v){
        $hotel_name = $v['hotel_name'];
        $address = $v['address'];
        $city = $v['city'];
        $state = $v['state'];
        $country = $v['country'];
        $zipcode = $v['zipcode'];
        $phone = $v['phone'];
        // echo or build display of hotel info here

        //Go through rooms array
        foreach($v['rooms'] as $room_id => $r){
            $room_name = $r['room_name'];
            $room_beds = $r['room_beds'];
            $room_floor = $r['room_floor'];
            $room_number = $r['room_number'];
            $room_description = $r['room_description'];
            // echo or build display of room info here

        }
        //Go through hotels_features array
        foreach($v['hotels_features'] as $feature_id => $f){
            $feature_name = $r['feature_name'];
            $feature_description = $r['feature_description'];
            // echo or build display of feature info here

        }
    }
?> 

Be sure to have a comma after the sum() and it’s helpful to define what the sum will be called, e.g.

$q = "SELECT columns... SUM(r.room_beds) AS total_beds, total_rooms, other columns...INNER JOINS...GROUP BY h.hotel_name"

Not sure what all your tables and fields are but you should probably be using LEFT JOIN for sub table queries.

Also note that you can add the value up while making a data set like so


    $hotels_array[$row['hotel_id']]['total_beds'] += $row['room_beds']; 

Hi Drummin,

thanks for getting back to me!

Sorry, the query I posted was incorrect. Instead of

SUM(r.room_beds)total_rooms,

I meant to use

SUM(r.room_beds)total_beds,

I believe the AS isn’t necessary so I left it out. I’m not using a subquery, it’s basically one big query where I select all columns, and join all tables using inner joins. Still can’t understand why MIN() returns the correct result and not SUM(). It definitely has to do with the intermediary tables.

Also note that you can add the value up while making a data set like so
PHP Code:
$hotels_array[$row[‘hotel_id’]][‘total_beds’] += $row[‘room_beds’];

Thank you. I’ll see what I can do.

Note: INNER JOIN allows rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause.
As you never show your real query it’s hard to say what’s going on.
if you print your results, is total_beds a key in the array with the value of SUM()?

Hey Drummin,

sorry for only getting back to you now, I have been sick.

As you never show your real query it’s hard to say what’s going on.

Sorry, I’m using a query such as the following:

$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
SUM (r.room_beds) AS total_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
INNER JOIN rooms AS r
ON r.hotel_id = h.hotel_id
INNER JOIN hotels_features AS hf
ON hf.hotel_id = h.hotel_id
INNER JOIN features AS f
ON f.features_id = hf.features_id
WHERE h.category = '$category'"; 

Let’s say I have 3 rooms each having 1 bed, then SUM should return 3. However, I’m getting back 171. When I remove my intermediary tables from the query, then SUM is again 3. As I mentioned MIN works using the same query so why wouldn’t SUM work? Looking online I couldn’t find any info saying “under certain conditions, compared to MIN, SUM won’t blah blah blah…”

if you print your results, is total_beds a key in the array with the value of SUM()?

Yes.

Do you think a subquery would work?

Thanks! Hope you’re well.

“such as” - heh

i hope the query you are actually using has a GROUP BY clause

Okay, does this help?

$q = "SELECT
h.hotel_id,
h.hotel_name,
h.address,
h.city,
h.state,
h.country,
h.zipcode,
h.phone,
r.room_id,
r.room_name,
SUM (r.room_beds) AS total_beds,
r.room_floor,
r.room_number,
r.room_description,
hf.features_id,
hf.feature_name,
f.feature_description
FROM hotels AS h
INNER JOIN rooms AS r
ON r.hotel_id = h.hotel_id
INNER JOIN hotels_features AS hf
ON hf.hotel_id = h.hotel_id
INNER JOIN features AS f
ON f.features_id = hf.features_id
WHERE h.city = 'berlin' GROUP BY h.hotel_name";

you tell me – are the results different?

No, still getting a lot more beds than there should be.

you’re seeing cross join effects within each hotel, because of the multiplicity of one-to-many joins

a hotel with 6 rooms and 5 features will return 30 rows in your query

put the rooms into a subquery

i’d do it for you but i gotta go out for a few hours right now

put the rooms into a subquery

i’d do it for you but i gotta go out for a few hours right now

Thank you Rudy, I’m in no rush, whenever you have some time. My attempts at subqueries haven’t been that successful, so I appreciate any assistance. The one thing that I’m still wondering about is why MIN() works using the same query given all of the multiplication going on?

okay, i’m back, but i have a few questions

you’re trying to SUM the beds in a room, or the beds in a hotel?

if the former, why would a room have more than one row in the rooms table?

if the latter, why do you need to see any room columns at all?