[RESOLVED] need help on search

harkly

I have an array that is pulling zip codes within a certain distance from a desinated zip code. Have this working.

Now I want to use the zip codes found and do a search based on them.
How do I do that?

I know this is incorrect and I am probably so far off but maybe you can see my logic.

$result = mysql_query("SELECT userID FROM user WHERE zip IN (". $zips_in_range .")");

while ($row = mysql_fetch_assoc($result) ) {

foreach ($row as $userID)
{

extract($row, EXTR_PREFIX_SAME, "userID");

echo "This is the userID - $userID <br>";

echo "Select * FROM photos WHERE userID = $userID <BR>";

$query = ("Select * FROM photos WHERE userID = $userID ");
  while ($r=mysql_fetch_array($query))
    {
      $userID=$r["userID"]; 
      $photo_1=$r["photo_1"];

      echo " $userID & $photo_1";
    }
}
}

my results

This is the userID - kelly
Select * FROM photos WHERE userID = kelly

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/i/d/2/id2294/html/singles/results.php on line 32
This is the userID - adfggg
Select * FROM photos WHERE userID = adfggg

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/i/d/2/id2294/html/singles/results.php on line 32
This is the userID - neer
Select * FROM photos WHERE userID = neer

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/i/d/2/id2294/html/singles/results.php on line 32

dagon

unless $userID is an int, you need to quote the string

  $query = ("Select * FROM photos WHERE userID ='$userID' ");

harkly

Thanks! That worked at pulling the requested info but it will only pull 1 set of data, shouldn't it be pulling all sets?? If I take out this part of the code below I get a list of matching data.

        $query = ("Select * FROM photos WHERE userID = '$userID' ");
        $result = mysql_query($query);

      while ($r=mysql_fetch_array($result))
        {
          $userID=$r["userID"]; 
          $photo_1=$r["photo_1"];

          echo " UserID: $userID <br> $photo_1";
        }

<?php
require_once('zipcode.class.php');

     include('library/login.php');
     login();

     mysql_select_db('test');

$z = new zipcode_class;
$zips = $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASC, true);

if ($zips === false) {
	echo 'Error: '.$z->last_error;
} else {
	$zips_in_range = implode(',', array_keys($zips) );
}
echo "SELECT userID FROM user WHERE zip IN (". $zips_in_range .")";

$result = mysql_query("SELECT userID FROM user WHERE zip IN (". $zips_in_range .")");

while ($row = mysql_fetch_assoc($result) )
  {
    foreach ($row as $userID)
      {
        extract($row, EXTR_PREFIX_SAME, "userID");

    echo "This is the userID - $userID <br>";


    $query = ("Select * FROM photos WHERE userID = '$userID' ");
    $result = mysql_query($query);

      while ($r=mysql_fetch_array($result))
        {
          $userID=$r["userID"]; 
          $photo_1=$r["photo_1"];

          echo " UserID: $userID <br> $photo_1";
        }
  }
  }
?>

harkly

Got this to work!

bradgrafelman

Note that your SQL query structure above could be highly optimized. Any time you see a SQL query being executed inside the loop of another query, you should look into using JOINs (or possibly a subquery) as a more efficient way of doing things.

harkly

Do you know a site that shows how to do that?

bradgrafelman

http://www.w3schools.com/sql/sql_join.asp
http://dev.mysql.com/doc/refman/5.0/en/join.html
http://www.google.com/search?q=SQL+join

harkly

Any time you see a SQL query being executed inside the loop of another query, you should look into using JOINs

I should join these 2??

$result = mysql_query("SELECT userID FROM user WHERE zip IN (". $zips_in_range .")");

$query = ("Select * FROM photos WHERE userID = '$userID' ");

If I do that how to I get the results to display the info? Right now all I can get it to do it print_r ($row)

TheoGB

Yes you could write that query as follows:

SELECT photos.* FROM user, photos WHERE zip IN (".$zips_in_range.") and photos.userid = user.userid

Note, this will only work the same if every user has a photo. If some don't then you'll need to adjust it to use a different sort of join and pull the UserID off the user file.

EDIT Of course, your database structure seems a little off from my point of view in any case.

e.g. if your photo table stores ALL the photos on your site then it's your users who should have a PhotoID column on their table, not the other way about, since presumably other tables may have photos associated with them and you shouldn't have a column for each possible linking table on the photo table.

On the other hand if your photo table is only storing a photo that a user has and each user only has one possible photo then why are they on a separate table at all? Just add more columns for each user to include that data?

Finally if you want to have one-to-many relationships or maybe many-to-many with some photos being used on multiple users and/or users having multiple photos, the correct method is not to store the foreign key of either table on other. Instead you create a photo_to_user table with a key that's UserID and PhotoID, containing only those fields and you then use it to link between the two to pull the appropriate records.

harkly

Thanks TheoGB!