Returning Specific Results from Database

Hi All,

I'm new to PHP. I have a script I would like to manipulate but don't know how. Its a real estate script. When I click on a State the results show all the properties listed in the database for that State. What I need is for the search results to show all the "Rentals" in the "Property types" table.

How can this be done? Below is the script: Any help is appreciated. Thanks in advance.

//fetch states
$sql =
'Select Count(DISTINCT items.id) as `total`, state.stitle, state.title
FROM state LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.state = state.stitle
group by state.stitle
order by state.stitle asc';
$stateR = mysql_query($sql,$myconn) or die(mysql_error());

//fetch cities
$sql =
'Select Count(DISTINCT items.id) as `total`, counties.id, counties.title
FROM counties LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.countyID = counties.id
group by counties.title
order by counties.title asc
';
$countiesR = mysql_query($sql,$myconn) or die(mysql_error());

//start table
print '<table border=0 cellpadding=0 cellspacing=0 width=100%>';
print '<tr><td valign="top">';

//if cities are present in database
if(mysql_num_rows($countiesR) > 0){
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($countiesR)/4);
//start counties count
$count = 0;
//for each counties
while($counties = mysql_fetch_assoc($countiesR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a class="pageLink2" href="properties.php?countyID='.$counties['id'].'">'.$counties['title'] . ' <b>( '.$counties['total'].' )</b></a>';
print '<br>';
$count++;
}//end while

//finish table if needed
print '</td>';

//else no cities are present
}else{ //print states
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($stateR)/4);
//start state count
$count = 0;
//for each state
while($state = mysql_fetch_assoc($stateR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a href="properties.php?state='.$state['stitle'].'" class="statLink">'.$state['title'].'&nbsp;( '.$state['total'].' )</a>';
print '<br>';
$count++;
}//end while

//finish table if needed
if($count == 1)
print '</td>';
}//end ifelse

//finish table
print '</tr></table>';

What column connects this properties table with either of the two tables being queried in the code you posted? By the way you should use PHP tags around your code to make it readable.

See Code below. I think the two tables are Item & Cities table

I hope this help.


<?

require('Connections/myconn.php');
mysql_select_db($database_myconn, $myconn);

//fetch states
$sql =
'Select Count(DISTINCT items.id) as `total`, state.stitle, state.title
FROM state LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.state = state.stitle
group by state.stitle
order by state.stitle asc';
$stateR = mysql_query($sql,$myconn) or die(mysql_error());

//fetch cities
$sql =
'Select Count(DISTINCT items.id) as `total`, counties.id, counties.title
FROM counties LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.countyID = counties.id
group by counties.title
order by counties.title asc
';
$countiesR = mysql_query($sql,$myconn) or die(mysql_error());

//start table
print '<table border=0 cellpadding=0 cellspacing=0 width=100%>';
print '<tr><td valign="top">';

//if cities are present in database
if(mysql_num_rows($countiesR) > 0){
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($countiesR)/4);
//start counties count
$count = 0;
//for each counties
while($counties = mysql_fetch_assoc($countiesR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a class="pageLink" href="properties.php?countyID='.$counties['id'].'">'.$counties['title'] . ' <b>( '.$counties['total'].' )</b></a>';
print '<br>';
$count++;
}//end while

//finish table if needed
print '</td>';

//else no cities are present
}else{ //print states
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($stateR)/4);
//start state count
$count = 0;
//for each state
while($state = mysql_fetch_assoc($stateR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a href="properties.php?state='.$state['stitle'].'" class="statLink">'.$state['title'].'&nbsp;( '.$state['total'].' )</a>';
print '<br>';
$count++;
}//end while

//finish table if needed
if($count == 1)
print '</td>';
}//end ifelse

//finish table
print '</tr></table>';


?>

please use the PHP tags!

//Latoya is an unknown quantity at this point

//See Code below. I think the two tables are Item & Cities table

//I hope this help.


<?

require('Connections/myconn.php');
mysql_select_db($database_myconn, $myconn);

//fetch states
$sql =
'Select Count(DISTINCT items.id) as `total`, state.stitle, state.title
FROM state LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.state = state.stitle
group by state.stitle
order by state.stitle asc';
$stateR = mysql_query($sql,$myconn) or die(mysql_error());

//fetch cities
$sql =
'Select Count(DISTINCT items.id) as `total`, counties.id, counties.title
FROM counties LEFT JOIN items
ON (items.expires > NOW()) and items.active="Yes" and items.countyID = counties.id
group by counties.title
order by counties.title asc
';
$countiesR = mysql_query($sql,$myconn) or die(mysql_error());

//start table
print '<table border=0 cellpadding=0 cellspacing=0 width=100%>';
print '<tr><td valign="top">';

//if cities are present in database
if(mysql_num_rows($countiesR) > 0){
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($countiesR)/4);
//start counties count
$count = 0;
//for each counties
while($counties = mysql_fetch_assoc($countiesR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a class="pageLink" href="properties.php?countyID='.$counties['id'].'">'.$counties['title'] . ' <b>( '.$counties['total'].' )</b></a>';
print '<br>';
$count++;
}//end while

//finish table if needed
print '</td>';

//else no cities are present
}else{ //print states
//fetch total per tabel cell
$total_per_cell = ceil(mysql_num_rows($stateR)/4);
//start state count
$count = 0;
//for each state
while($state = mysql_fetch_assoc($stateR)){
if($count == $total_per_cell){
print '</td><td valign="top">';
$count = 0;
}
print '<a href="properties.php?state='.$state['stitle'].'" class="statLink">'.$state['title'].'&nbsp;( '.$state['total'].' )</a>';
print '<br>';
$count++;
}//end while

//finish table if needed
if($count == 1)
print '</td>';
}//end ifelse

//finish table
print '</tr></table>';


?>
see how much prettier it looks (atleast to the regulars here)

See Code below. I think the two tables are Item & Cities table

Yeah I saw the code the first time. You mentioned you need to check a table called property types in your first post, and I asked how that table is connected to the tables currently being queried.