CODEWITHSUNDEEP
phpmysql
Graham
Graham

Reputation: 1473

querying multiple tables outputs duplicate results

I have an ajax search engine. If none of the filters are on, I want to query titles from every table. In this example, there are only two tables. music_page AND restaurant_page.

public function Any_Category_Name($search_title) {

                $query = mysql_query("SELECT music_page.title AS music_title, 
                                             restaurant_page.title AS restaurant_title
                                      FROM music_page,
                                           restaurant_page
                                      WHERE music_page.title 
                                            LIKE '%$search_title%' OR
                                              restaurant_page.title
                                              LIKE '%$search_title%'");
                while($fetch = mysql_fetch_assoc($query)) {
                    $music_title = $fetch['music_title'];
                    $restaurant_title = $fetch['restaurant_title'];
                    echo '<b>Restaurant:</b> '.$restaurant_title.'<br />';
                    echo '<b>Music Spot:</b> '.$music_title.'<br />';
                    }

                }

This is the output when you enter a letter / search:

Restaurant: Giordanoes
Music Spot: jazz showcase
Restaurant: Giordanoes
Music Spot: The Green Mill
Restaurant: Giordanoes
Music Spot: ayooooo
Restaurant: Giordanoes
Music Spot: ayoooooooo
Restaurant: Giordanoes
Music Spot: new one

Giordanoes is the only row in restaurant_page table. It's outputted for every row that's in music_page. How can I remedy this?

Upvotes: 0

Views: 200

Answers (3)

Jonathan Kuhn
Jonathan Kuhn

Reputation: 15301

That is exactly what happens when you join tables. what I usually do is just as you loop through, only output the restaurant if it changes from the last loop. like:

$last = '';
while($fetch = mysql_fetch_assoc($query)) {
    $music_title = $fetch['music_title'];
    $restaurant_title = $fetch['restaurant_title'];
    if($last != $restaurant_title){
        echo '<h1>Restaurant: '.$restaurant_title.'</h1>';
    }
    echo '<b>Music Spot:</b> '.$music_title.'<br />';
    $last = $restaurant_title;
}

Upvotes: 1

Phill Pafford
Phill Pafford

Reputation: 85298

Alternative 

while($fetch = mysql_fetch_assoc($query)) {
    // Create the listing array
    $listing[$fetch['restaurant_title']] = array($fetch['music_title'] => 'music_title');
}

// Check if we have Restaurants
if(is_array($listing)) {
    // Loop through the new listings array
    foreach($listing as $restaurant_title => $music_titles) {
        echo '<b>Restaurant:</b> '.$restaurant_title.'<br />';

        // Check if we have Music
        if(is_array($music_titles)) {
            // Loop through the Music
            foreach($music_titles as $music_title => $placeholder) {
                echo '<b>Music Spot:</b> '.$music_title.'<br />';
            }
        } else {
            echo '<b>Music Spot:</b> No Music found<br />'; 
        }            
    }
} else {
    echo '<b>Restaurant:</b> No Restaurants found<br />';
}

Upvotes: 1

DaveL
DaveL

Reputation: 339

You'll need to look into ways of properly joining your tables. As you have it, you will get a record from the restaurant_page table for every record in the music_page table.

I recommend W3schools.com

An alternative is to have two queries, each selecting records from a separate table, and two while loops to output the results, one after another.

Upvotes: 1

Related Questions