Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>';

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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>';


    ?>

  • #4
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,039
    Thanks
    19
    Thanked 42 Times in 42 Posts
    please use the PHP tags!
    PHP Code:
    //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)

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •