using fulltext in the searches giving errors

m3rajk posted this at 17:57 — 26th September 2003.

Joined: Jul 2003

searches with full text feilds get returns as bad sql. not sure why. wondering sf someone can explain it to me. here's the search construction:

<?php
function psd($db){ # search display
  // create search string
  $searchfor='SELECT users.username FROM users, bio, stats, interests WHERE
 users.uid=bio.uid AND users.uid=stats.uid AND users.uid=interests.uid';

  if($_GET['partial']){ // do a partial match
    if(isset($_GET['member'])&&($_GET['member']!='')){ # we're looking for someone
      $mem=clean($_GET['member']); $searchfor.=\" AND users.username LIKE '$mem'\"; }
  }else{ // look for the exact
    if(isset($_GET['member'])&&($_GET['member']!='')){ # we're looking for someone
      $mem=clean($_GET['member']); $searchfor.=\" AND users.username='$mem'\"; }
  } // username is added

note: php edited to prevent horizontal scrolling
  
  if(preg_match('/\d/', $_GET['llmonth'])){ // we have a login date
    // begining of day
    $lld1=\"{$_GET['llyear']}-{$_GET['llmonth']}-{$_GET['llday']} 00:00:00\"; 
    // end of day
    $lld2=\"{$_GET['llyear']}-{$_GET['llmonth']}-{$_GET['llday']} 23:59:59\"; 
    $searchfor.=\" AND users.last_login_date>='$lld1' AND 
users.last_login_date<='$lld2'\";
  } // last login date added (if applicable)

  // add gender 
  if($_GET['gen']){ $gen=$_GET['gen']; $searchfor.=\" AND users.gender='$gen'\"; } 

  if($_GET['country']!='-1'){ $co=$_GET['country']; $searchfor.=\" AND 
stats.country='$co'\"; } // add country

  if($_GET['eye']!='-1'){ $eye=$_GET['eye']; $searchfor.=\" AND 
stats.eye_color='$eye'\"; } // add eye color

  if($_GET['hair']!='-1'){ $hair=$_GET['hair']; $searchfor.=\" AND 
stats.hair_color='$hair'\"; } // add hair

  if($_GET['ms']!='-1'){ $ms=$_GET['ms']; $searchfor.=\" AND 
stats.mar_stat='$'\"; } // add marital status

  if($_GET['sp']!='-1'){ $sp=$_GET['sp']; $searchfor.=\" AND 
stats.sex_pref='$sp'\"; } // add sexual preference

  if($_GET['bt']!='-1'){ $bt=$_GET['bt']; $searchfor.=\" AND 
stats.body_type='$bt'\"; } // add body type

  if($_GET['emp']!='-1'){ $emp=$_GET['emp']; $searchfor.=\" AND 
stats.employment='$emp'\"; } // add employment

  if($_GET['el']!='-1'){ $el=$_GET['el']; $searchfor.=\" AND 
stats.education='$el'\"; } // add education level

  if($_GET['aff']){ $aff=$_GET['aff']; $searchfor.=\" AND 
stats.affiliation='$aff'\"; } // add affiliation

  if($_GET['sc']!='-1'){ $sc=$_GET['sc']; $searchfor.=\" AND 
stats.self_cat='$sc'\"; } // add self cat

  if($_GET['spt']!='-1'){ $spt=$_GET['spt']; $searchfor.=\" AND 
stats.spt='$spt'\"; } // add s/p/t

  if(isset($_GET['religion'])&&($_GET['religion']!='')){ # if there's a religion
    $rel=clean($_GET['religion']); $searchfor.=\" AND stats.religion='$rel'\"; }

  if(isset($_GET['ethnic'])&&($_GET['ethnic']!='')){ #if there's a ethnicity
    $eth=clean($_GET['ethnic']); $searchfor.=\" AND stats.ethnic='$eth'\"; } 

  if(isset($_GET['city'])&&($_GET['city']!='')){ # if there's a city
    $city=clean($_GET['city']); $searchfor.=\" AND stats.city='$city'\"; } 

  if(isset($_GET['aim'])&&($_GET['aim']!='')){ # if there's an aim
    $aim=clean($_GET['aim']); $searchfor.=\" AND stats.aim='$aim'\"; } // add aim

  if(isset($_GET['icq'])&&($_GET['icq']!='')){ # if there's an icq
    $icq=clean($_GET['icq']); $searchfor.=\" AND stats.icq='$icq'\"; } // add icq

  if(isset($_GET['mirc'])&&($_GET['mirc']!='')){ # if there's a mirc
    $mirc=clean($_GET['mirc']); $searchfor.=\" AND stats.mirc='$mirc'\"; } // add mirc

  if(isset($_GET['msn'])&&($_GET['msn']!='')){ # if there's an msn
    $msn=clean($_GET['msn']); $searchfor.=\" AND stats.msn='$msn'\"; } // add msn

  if(isset($_GET['yim'])&&($_GET['yim']!='')){ # if there's a yim
    $yim=clean($_GET['yim']); $searchfor.=\" AND stats.yim='$yim'\"; } // add yim

  if($_GET['dobspef']){ // we have a date of birth we wanna use
    $dob=$_GET['year'].'-'.$_GET['month'].'-'.$_GET['day']; // grab dob
    if($_GET['dobspef']=='On'){ $searchfor.=\" AND stats.dob='$dob'\"; } 
    if($_GET['dobspef']=='Before'){ $searchfor.=\" AND stats.dob>'$dob'\"; }
    if($_GET['dobspef']=='After'){ $searchfor.=\" AND stats.dob<'$dob'\"; }
  } // we've added the dob

  if($_GET['hspec']){ // we wanna use height
    $height=(12*$_GET['feet'])+$_GET['inches']; // get the height in inches
    if($_GET['hspec']=='equal'){ $searchfor.=\" AND stats.='$height'\"; }
    if($_GET['hspec']=='min'){ $height--; $searchfor.=\" AND stats.>'$height'\"; }
    if($_GET['hspec']=='max'){ $height++; $searchfor.=\" AND stats.='$height'\"; }
  } // we have the height

  if($_GET['wspec']){ // we wanna use waist
    if($_GET['wspec']=='equal'){ $wst=$_GET['waist']; $searchfor.=\" AND 
stats.waist='$wst'\"; } // use given
    if($_GET['wspec']=='min'){ $wst=($_GET['waist']--); $searchfor.=\" AND 
stats.waist>'$wst'\"; } // min given
    if($_GET['wspec']=='max'){ $wst=($_GET['waist']++); $searchfor.=\" AND 
stats.waist<'$wst'\"; } // max given
  } // waist is added

  if($_GET['lbsspec']){ // we care about weight
    if($_GET['lbsspec']=='equal'){ $lbs=$_GET['lbs']; $searchfor.=\" AND 
stats.weight='$lbs'\"; } // use given
    if($_GET['lbsspec']=='min'){ $lbs=($_GET['lbs']--); $searchfor.=\" AND 
stats.weight>'$lbs'\";} // min given
    if($_GET['lbsspec']=='max'){ $lbs=($_GET['lbs']++); $searchfor.=\" AND 
stats.weight<'$lbs'\";} // max given
  } // weight added

  if($_GET['general']){ // we want to go across ALL bio elements
    if(isset($_GET['all'])&&($_GET['all']!='')){ // they entered something
      $biostring=clean($_GET['all']); // get what they wanna find
      $searchfor.=\" AND MATCH 
( bio.bq1, bio.bq2, bio.bq3, bio.bq4, bio.auth, bio.bio ) AGAINST 
( '$biostring' )\"; } // add the stuff

  }else{ // we want a specific feild to have it

    if(isset($_GET['bq1'])&&($_GET['bq1']!='')){ // if they want bq1
      $bq1=clean($_GET['bq1']); // get what they want in bq1
      $searchfor.=\" AND MATCH ( bio.bq1 ) AGAINST ('$bq1')\"; } // add bq1

    if(isset($_GET['bq2'])&&($_GET['bq2']!='')){ // if they want bq2
      $bq2=clean($_GET['bq2']); // get what they want in bq2
      $searchfor.=\" AND MATCH ( bio.bq2 ) AGAINST ('$bq2')\"; } // add bq2

    if(isset($_GET['bq3'])&&($_GET['bq3']!='')){ // if they want bq3
      $bq3=clean($_GET['bq3']); // get what they want in bq3
      $searchfor.=\" AND MATCH ( bio.bq3 ) AGAINST ('$bq3')\"; } // add bq3

    if(isset($_GET['bq4'])&&($_GET['bq4']!='')){ // if they want bq4
      $bq4=clean($_GET['bq4']); // get what they want in bq4
      $searchfor.=\" AND MATCH ( bio.bq4 ) AGAINST ('$bq4')\"; } // add bq4

    if(isset($_GET['auth'])&&($_GET['auth']!='')){ // if they want the author
      $auth=clean($_GET['auth']); // get what they want for the author
      $searchfor.=\" AND MATCH ( bio.auth ) AGAINST ('$auth')\"; } // add author

    if(isset($_GET['bio'])&&($_GET['bio']!='')){ // if they want bio
      $bio=clean($_GET['bio']); // get what they want in the bio
      $searchfor.=\" AND MATCH ( bio.bio ) AGAINST ('$bio')\"; } // add bio
  } // bio has been added
  
  if((isset($_GET['books']))||(isset($_GET['hobbies']))||
(isset($_GET['misc']))||(isset($_GET['movies']))||
     (isset($_GET['pets']))||(isset($_GET['sports']))||
(isset($_GET['vacation']))){ // interests are sought

    $interests=clean(join(' ', $_GET['books'])).' '.
clean(join(' ', $_GET['hobbies'])).' '.
      clean(join(' ', $_GET['misc'])).' '.clean(join(' ', $_GET['movies'])).' '.
      clean(join(' ', $_GET['pets'])).' '.clean(join(' ', $_GET['sports'])).' '.
      clean(join(' ', $_GET['vacation'])); // make one LONG string of the interests

    $searchfor.=\" AND MATCH ( interests.books, interests.hobbies, interests.misc, 
interests.movies, interests.pets, interests.sports, interests.vacation ) AGAINST 
('$interests')\"; // add the interest searching

  } // interests has been added
  
  $findmems=squery($searchfor, $db); # search for the query

  // if we have members that meet the criteria/make an empty array
  if(count($findmems)){ 
    bgnpg('Search Results'); nav(); // send the results to be displayed
echo \"<p>$searchfor</p>\n\"; dispres($findmems); clspg();

  }else{ // nothing met the criteria
    bgnpg('Search Results'); nav(); echo \"<p>$searchfor</p>\n\"; // begin the result
    echo'<p>There were no members that met your criteria</p>'; clspg(); 
  }
}
?>

i'm not sure what's wrong with the current set up. if someone could help me... maybe there's a more efficient way to search as well....all help/advice greatly appreciated

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Mark Hensler posted this at 22:31 — 26th September 2003.

He has: 4,048 posts

Joined: Aug 2000

I've never used fulltext searches.

Can you print the mysql_error() and the actual query being run?

m3rajk posted this at 17:33 — 27th September 2003.

They have: 461 posts

Joined: Jul 2003

ok. last time it did't like the seelct statement. (i checked against the bio) this time it's other things (checking against interests)

Quote:
Warning: Bad arguments to join() in /var/www/html/findyourdesire/search.php on line 220

Warning: Bad arguments to join() in /var/www/html/findyourdesire/search.php on line 221

Warning: Bad arguments to join() in /var/www/html/findyourdesire/search.php on line 222

Warning: Bad arguments to join() in /var/www/html/findyourdesire/search.php on line 223

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/findyourdesire/search.php on line 720

SELECT users.username FROM users, bio, stats, interests WHERE users.uid=bio.uid AND users.uid=stats.uid AND users.uid=interests.uid AND users.gender='M' AND MATCH ( interests.books, interests.hobbies, interests.misc, interests.movies, interests.pets, interests.sports, interests.vacation ) AGAINST (' Computer Animation )
0:

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

m3rajk posted this at 17:39 — 27th September 2003.

They have: 461 posts

Joined: Jul 2003

i tried bio again. general match against all of them. this time i didn't get an error (not sure why) but i didn't get a match and i just checked the db... 3 ppl have the workd later in tehir bio, whcih is why i used it.

Quote: SELECT users.username FROM users, bio, stats, interests WHERE users.uid=bio.uid AND users.uid=stats.uid AND users.uid=interests.uid AND MATCH ( bio.bq1, bio.bq2, bio.bq3, bio.bq4, bio.auth, bio.bio ) AGAINST ( 'later' )
0:

the error meessage print out is

<?php
 echo \"<p>$searchfor<br />$errno: $error</p>\";
?>

$search for is the search string, $errno is the mysql error number and $error is the error

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

m3rajk posted this at 17:45 — 27th September 2003.

They have: 461 posts

Joined: Jul 2003

i guess it doesn't like the word later. using the word fill i got a return for the bio.

i also noticed in one fo the feilds in the genreal bio in an older version i have two . in bio.bio (reads bio..bio) looking through the back ups i think i know what gave the error last time i tested it. guess i need to fix the issue with the interests.

i figured i'd get back to it after adding and before testing a search on the forums. i didn't even realize i switched that. i must have noticed it and just done that when copying to get the structure

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

m3rajk posted this at 18:12 — 27th September 2003.

They have: 461 posts

Joined: Jul 2003

i changed the interests section to

<?php
  $interests='';
  if(isset($_GET['books'])){ $interests=clean(join(' ', $_GET['books'])); }
  if(isset($_GET['hobbies'])){ # there's hobbies
    if($interests==''){ $interests=clean(join(' ', $_GET['hobbies'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['hobbies'])); }} # add hobbies
  if(isset($_GET['misc'])){ # there's misc
    if($interests==''){ $interests=clean(join(' ', $_GET['misc'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['misc'])); }} # add misc
  if(isset($_GET['movies'])){ # there's movies
    if($interests==''){ $interests=clean(join(' ', $_GET['movies'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['movies'])); }} # add movies
  if(isset($_GET['pets'])){ # there's pets
    if($interests==''){ $interests=clean(join(' ', $_GET['pets'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['pets'])); }} # add pets
  if(isset($_GET['sports'])){ # there's sports
    if($interests==''){ $interests=clean(join(' ', $_GET['sports'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['sports'])); }} # add sports
  if(isset($_GET['vacation'])){ # there's vacation
    if($interests==''){ $interests=clean(join(' ', $_GET['vacation'])); }
    else{ $interests.=' '.clean(join(' ', $_GET['vacation'])); }} # add vacation
  if($interests!=''){ // there's interests
    $searchfor.=\" AND MATCH ( interests.books, interests.hobbies, interests.misc, 
interests.movies, interests.pets, interests.sports, interests.vacation ) AGAINST 
('$interests')\"; // add the interest searching
  } // interests has been added
?>

and when i used it with aninterests that i know is in the database i got...

Quote: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/findyourdesire/search.php on line 735

SELECT users.username FROM users, bio, stats, interests WHERE users.uid=bio.uid AND users.uid=stats.uid AND users.uid=interests.uid AND MATCH ( interests.books, interests.hobbies, interests.misc, interests.movies, interests.pets, interests.sports, interests.vacation ) AGAINST ('Computer Animation')
0:

line 735 is the while loop begining in this function

<?php
  function squery($querystring, $db){ # do the actual search
  include(\"/home/joshua/includes/fyd.altincs.php\"); # alt includes file (no functions)
  $start=0; $page=1; $foundmems=array(); # variables
  if(isset($_GET['page'])){ $_GET['page']; } // which page of members?
  
  if($page>1){ # we're not on the first page
    $start=20*$page; # how many records do we ignore?
  }
  
  $findmems=mysql_query($querystring, $db); // now we query the db
  
  if($start>0){ # we need to jump records
    $jump=mysql_data_seek($findmems, $start); # jump the records
  }
  while(($getmems=mysql_fetch_array($findmems))&&($i<20)){
    $foundmems[]=$getmems['username']; # get the people
    $i++; # increase the limiter
  }
  return $foundmems;
}
?>

it's called int eh line

<?php
  $findmems=squery($searchfor, $db); # search for the query
?>

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.