Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Question Unanswered: Is There a Better SELECT Statement?

    On my site, users can search a database for other users. The search results display information about each individual user. Currently, to get the information I need, I use several SELECT statements to query different tables. But I have a feeling that there is a simpler way to do it.

    The name of each table that I am using is as follows:

    members
    photos
    online
    friends

    Here is the code and select statements:


    //Find members with this zipcodeid and calculate distance

    $sql = "SELECT zipcode.zipcodeid, 3963 * ACOS(COS(RADIANS(90-40.615000)) * COS(RADIANS(90-zipcode.latitude)) + SIN(RADIANS(90-40.615000)) * SIN(RADIANS(90-zipcode.latitude)) * COS(RADIANS(-111.891300-zipcode.longitude))) AS dist, members.*, photos.* FROM members LEFT JOIN zipcode ON members.zipcodeid = zipcode.zipcodeid WHERE members.zipcodeid LIKE '28063' ORDER BY dist";

    $result = mysql_query($sql) or die(mysql_error());

    while($row = mysql_fetch_array($result)) {
    $memberid = $row['memberid'];

    //Check to see if they are online, 0 equals no, 1 equals yes
    $online_result = mysql_query("SELECT * FROM online WHERE
    memberid = '$memberid'");

    $is_online = mysql_num_rows($online_result);

    //Check to see if the member is a friend
    //0 equals no, 1 equals yes
    $friends_result = mysql_query("SELECT * FROM friends WHERE
    userid = '$userid' AND memberid = '$memberid'");

    $is_friend = mysql_num_rows($friends_result);

    //Get total number of public photos
    $public_result = mysql_query("SELECT * FROM photos WHERE
    memberid = '$memberid' AND album = 'public'");

    $total_public = mysql_num_rows($public_result);

    //Get total number of private photos
    $private_result = mysql_query("SELECT * FROM photos WHERE
    memberid = '$memberid' AND album = 'private'");

    $total_private = mysql_num_rows($private_result);
    }


    Is it possible to combine all of this into the first SELECT statement?
    Last edited by robdiablo; 07-07-03 at 16:27.

Posting Permissions

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