SQL problem

Anon

I have been trying to create a sql statement.

to explain, I have three fields gender, city age now I want the person to be able to do a search based on:

Gender, then City and then Age Range.

Now I don't want to force a person to have to choose an option except Gender. I know that if I use "OR" it will pickup any field as long as a condition is met. If I use an AND join, you have to have an option within each field. Below is the statement I am working with.

WHERE match_identify = '$match_identify' AND self_location = '$self_location' OR self_age BETWEEN '$age1' AND '$age2'

I have tried every possible solution that I could find and it doesn't quite work. It works just fine when I don't use the self_age range condition. If I use AND instead of OR it require a person to put in an age range and I don't want to require them to.

Does anyone have any solutions for me to try?

thanks,

Brett

rrijnders

You probably want to construct your sql statement based upon what <i>was</i> entered by the user:

===================
$set = 0;
$sql = "select * from table where ";

if (!empty($sex)) {
  $sql .= "tablefield = '" . $sex . "'";
  $set = 1;
}

if (!empty($city)) {
  if ($set) {
    $sql .= " AND ";
  }
  $sql = "table_city = '" . $city . "'";
  $set = 1;
}

if (!empty($age1) and !empty($age2)) {
  if ($set) {
    $sql .= " AND ";
  }
  $sql = "(table_age >= " . $age1 . " AND table_age <= " . $age2 . ")";

}

This way, your sql is not determining if the user wants to include an option into the selection, your code is.

HTH

-- Rich Rijnders
-- Irvine, CA US

btaylor

Richard,

Thanks for your help, but I must be missing somehthing so I though I would include the whole script. This is a next/prev script and I am not too sure where to implement your solution. You will notice I did attempt to put it in, but I get a supplied argument warning, any addition help to put me in the right direction, I would appreciate.

thanks,

Brett

$limit=20; // rows to return

$numresults=mysql_query("select *
FROM $table_A

");

$numrows=mysql_num_rows($numresults);

if ($numrows == 0) {
echo "<font size=3 color=\"Red\">You search resulted in 0, please select again</font>";
exit;
}

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
$offset=0;
}

// get results
$set = 0;
$result=mysql_query("SELECT ProfileID, self_location,self_caption,self_name,self_gender,self_age,self_img_sm,match_identify
FROM $table_A
WHERE (if something suppose to reference here?)
ORDER BY self_name
LIMIT $offset,$limit");

if (!empty($match_identify)) {
$result .="match_identify = '" . $match_identify . "'";
$set = 1;
}
if (!empty($self_location)) {
if ($set) {
$result .= " AND ";
}
$result = "self_location = '" . $self_location . "'";
$set = 1;
}
if (!empty($age1) and !empty($age2)) {
if ($set) {
$result .= " AND ";
}
$result = "(self_age >= " . $age1 . " AND self_age <= " . $age2 .")";
}

// now you can display the results returned
while ($data=mysql_fetch_array($result)) {
$ProfileID = $data['ProfileID'];
$self_location = $data['self_location'];
$self_caption = $data['self_caption'];
$self_name = $data['self_name'];
$self_gender = $data['self_gender'];
$self_age = $data['self_age'];
$self_img_sm = $data['self_img_sm'];
$match_identify = $date['match_identify'];

print "<table border=0 cellpadding=3 cellspacing=0>
<tr bgcolor=\"#ffffcc\">
<td width=70 valign=top>$self_img_sm</td>
<td width=250>Caption: <a href=\"profile-detail.php?ProfileID=$ProfileID\">$self_caption</a>
<div class=\"dircomment\">City: $self_location</div>
<div class=\"dircomment\">Profile Name: $self_name</div>
<div class=\"dircomment\">Gender: $self_gender</div>
<div class=\"dircomment\">Age: $self_age</div></td>
</tr>
<tr>
<td colspan=2 align=\"center\"><hr size=1 width=300 color=\"#99cc99\"></td>
</tr></table>";
}

// next we need to do the links to other results
print "<center>";
if ($offset>=$limit) { // bypass PREV link if offset is 0
$prevoffset=$offset-$limit;
print "<a href=\"$PHP_SELF?self_location=$self_location&match_identify=$match_identify&offset=$prevoffset\">««</a>  | ";
} else {
print " | ";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
print "<a href=\"$PHP_SELF?personal_city=$self_location=$self_location&match_identify=$match_identify&offset=$newoffset\">$i</a> ";
}

// check to see if last page
if ($offset!=$limit*($pages-1)) {
// not last page so give NEXT link
$newoffset=$offset+$limit;
print " | <a href=\"$PHP_SELF?self_location=$self_location&match_identify=$match_identify&offset=$newoffset\">»»</a>";
} else {
print " | ";
}
print "</center><p> </p>";
?>

rrijnders

You have got the logic a bit backward. It appears that you are trying to select everything in your table and then pick records out of the result set that match the user's search criteria. Wouldn't it be better to only select the records they asked for?

To do this, you must encorporate the selection criteria into the SQL that you execute:

==================

$limit=20; // rows to return

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
$offset=0;
}

$set = 0;

$sql = "SELECT ProfileID, self_location,self_caption,self_name,self_gender,self_age,self_img_sm,match_identify
FROM $table_A
WHERE ";

if (!empty($match_identify)) {
   $sql .= "match_identify = '" . $match_identify . "'";
   $set = 1;
}

if (!empty($self_location)) {
   if ($set) {
      $sql .= " AND ";
   }
   $sql = "self_location = '" . $self_location . "'";
   $set = 1;
}

if (!empty($age1) and !empty($age2)) {
   if ($set) {
      $sql .= " AND ";
   }
   $sql = "(self_age >= " . $age1 . " AND self_age <= " . $age2 .")";
}

$sql .= " ORDER BY self_name LIMIT $offset,$limit");

$result=mysql_query($sql);
$numrows=mysql_num_rows($result);

if ($numrows == 0) {
   echo "You search resulted in 0, please select again";
   exit;
}
else {

  // <i>NOW</i> you output the results...

======================

HTH
-- Rich