Displaying Data From 4 (Four) Tables in Mysql

1
votes

I just need a simple queries actually,

Here are my tables:

Information -> id_info, year, information_name, person_name, country_id, state_id, city_id

Country -> id, country_id, country_name

State -> id, state_id, country_id, state_name

City -> id, city_id, state_id, city_name

I have runs some queries, for example's:

                         SELECT * 
                         FROM information, country, state, city 
                         WHERE information.country_id =country.country_id 
                         AND information.state_id = state.state_id
                         AND information.city_id = city.city_id 
                         GROUP BY information.id_info;

My Simple Scripts:

echo '<td>'.$data['country_name'].'</td>

echo '<td>'.$data['state_name'].'</td>

echo '<td>'.$data['city_name'].'</td>

$data-> Is my while script with $query=mysql_query...

from the queries above only displaying two (2) data's from my database, but in the database it has five (5) data's.

Then I've trying to delete the Group statement, but the data kept looped and displaying almost 8000 data's, but I only got 5 data on tables.

I've tried everything, left join, right join, inner join....

I need help, I know it's quite simple, but how can I just display all the data normally.

Thanks.

Here My Full Scripts for displaying the data:

<table cellpadding="5" cellspacing="0" border="1">
    <tr bgcolor="#CCCCCC">
        <th>No.</th>
        <th>Year</th>
        <th>Information Name</th>
        <th>Person Name</th>
        <th>Country</th>
        <th>State</th>
        <th>City</th>
        <th>Act</th>
    </tr>

    <?php
    include('connect.php');

    $query = mysql_query("SELECT *
                  FROM information
                  INNER JOIN country ON information.country_id =country.country_id
                  INNER JOIN state ON information.state_id = state.state_id
                                  INNER JOIN city ON information.city_id = city.city_id
                                  GROUP BY information.country_id, information.state_id, information.city_id") or die(mysql_error());


    if(mysql_num_rows($query) == 0){


        echo '<tr><td colspan="6">No data!</td></tr>';

    }else{  


        $no = 1;    
        while($data = mysql_fetch_assoc($query)){   

            echo '<tr>';
                echo '<td>'.$no.'</td>';    
                echo '<td>'.$data['year'].'</td>';  
                echo '<td>'.$data['information_name'].'</td>';  
                echo '<td>'.$data['person_name'].'</td>';   
                echo '<td>'.$data['country_name'].'</td>';  
                echo '<td>'.$data['state_name'].'</td>';    
                echo '<td>'.$data['city_name'].'</td>'; 
                echo '<td><a href="viewinfo.php?id='.$data['id_info'].'">Detail</a> / <a href="edit.php?id='.$data['id_info'].'">Edit</a> / <a href="delete.php?id='.$data['id_info'].'" onclick="return confirm(\'Are You Sure?\')">Delete</a></td>';  
            echo '</tr>';

            $no++;  

        }

    }
    ?>
3
phpmysql
What is your table name ? – Ferrakkem Bhuiyan
Do you need to use all the fields? Or you need only the country, state and city names? – Edper
@FerrakkemBhuiyan: information, country, state, city – Kris
@Edper: I need all actually, because there are connection's in that queries, as my updated script's above. – Kris

3 Answers

2
votes

Try changing your GROUP BY and use INNER JOIN :

  SELECT *
  FROM information
   INNER JOIN country ON information.country_id =country.country_id
   INNER JOIN state ON information.state_id = state.state_id
   INNER JOIN city ON information.city_id = city.city_id
   GROUP BY information.country_id, information.state_id, information.city_id
3
votes

You can use INNER JOIN assuming that index key is properly used:

SELECT country.country_name,
       state.state_name,
       city.city_name
FROM information
INNER JOIN country ON information.country_id = country.country_id
INNER JOIN state ON information.state_id = state.state_id
INNER JOIN city ON information.city_id = city.city_id
0
votes

It worked now, *sigh the problem is the table in information, I have to empty the table first.

It quite a lot happened to me, the records was the problem source's, I've been through this a lot. Thank you guys for the help, now it's work perfectly fine.

The two answer above worked perfectly, Thank you.