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++;
}
}
?>