• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

search multiple fields from two tables with one string


I'm trying to query two tables
used_cons & used_ag

for these fields
used_cons.MAKE, used_cons.MODEL, used_cons.DESCRIPTION,
used_ag.MAKE, used_ag.MODEL, used_ag.DESCRIPTION

that match a string passed through a url
example: used/search.php?find=New%20Holland

Please recommend if I should do as above or...

I'm not sure if my url's are formatted properly.  Should I search the exact string or firstword and/or secondword.  I don't want douplicate results.

  • 5
  • 4
1 Solution
TherosEquipmentAuthor Commented:
After reviewing my first post in this thread,  I should mention that I need the query as well as advice/implimentation of my search method.

TherosEquipmentAuthor Commented:
I hope everyone had a nice weekend!
(1) get a working query
SELECT DISTINCT used_cons.MAKE, used_cons.MODEL, used_cons.DESCRIPTION,
used_ag.MAKE, used_ag.MODEL, used_ag.DESCRIPTION
FROM used_cons,used_ag
ON condition_which_should_be_obvious_to_you

if the condition is not obvious, the tables are probably not linked : use two querys

(2)passing of the request.
if you use a single field for the input, this will result as your first syntax.
use explode(' ',$input) to separate words

(3)query where clause construction
use the IN('thing1','thing2'...syntax)
-construct the in or like clause (search exact value or word present in the field)
-add ' OR $field $clause' to the query foreach field the lookup must be made in.

(4)display results in an array

this does not take in account the pertinence of the results.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

TherosEquipmentAuthor Commented:
Hey skull,

I've been playing with this by running the query on one table at a time.  I'm having a problem because it's douplicating my output for each field in the row because I'm having it search those three fields.

I'm thinking I will request a delete on this thread if you don't mind skull.  I have some more pressing stuff I want to address right now.  Let me know.

forget about the point stuff and delete of thread unless this becomes useless ; but please let me know what the querys are.
you probably can get it alright using a SELECT DISTINCT... and/or a ..GROUP BY syntax.
please tell me wether there is /what is the relation between the two tables.

i have a searching page ready.
i'll give it as an example as something that works.
is u feel like using it as is, please let me know and i'll give u the detailed possible values for input.
the function affich() echoes a field of a specific type (menu, textfield...) and mode (display, update....) + gets extra inputs in some cases.
folloxs the code.

code of MDR.php

<? //NOTE_on pourrait aisément transformer cette page en fonction (voire moteur pour visualiser les arguments)

//generation des conditions $wheres(champ=>condition) de la requete
if(!is_array($champs))die("pas de champs pour ce moteur ?!");//$_POST as $champ=>$val... on evite en changeant ça le bug si on modifie un chp puis on clique sur un lien ou le chp n'existe pas
foreach($champs[$exe_moteur] as $champ){$ch=substr($champ,0,3);
      switch($ch){case "SEL":$champ=substr($champ,strpos($champ,"SEL",3));}
      if($$champ=="" and $ch!="INF")continue;
            case "MOT": $mots=explode(" ",$$champ);
                              foreach($mots as $mot){
                                    $wheres[$champ].=" AND (0";
                                    foreach($champscles[$champ] as $chp)if(${"EXE".$champ.$chp}=="on")$wheres[$champ].=" OR $chp LIKE '%$mot%'";
        case "EXE": break;
            case "TXT":      break;
        case "VAR": break;
            case "FCT": break;//a développer appel à fonction ds la page appelante si necessaire.
            case "INF": $champ="INF".substr($champ,strpos($champ,"SUP")+3);if(strpos($chp,"SUP"))$chp=substr($chp,strpos($chp,"SUP")+3);
                                    else echo "$red <b>DEV -> </b>INF...SUP requis pour le champ $champ";
                              $wheres[$champ]=" AND ".$chp." >= '".frtostamp($$champ,0,1)."'";  //$wheres[$champ] => date du jour
                              if(frtostamp(${"SUP".substr($champ,3)},0,1)>frtostamp($$champ,0,1))$wheres[$champ].=" AND $chp <= '".frtostamp(${"SUP".substr($champ,3)},0,1)."'";
                              elseif(${"SUP".substr($champ,3)})echo"<p align=\"center\"><font color=\"red\">la date maximale spécifiée est inferieure à la date minimale et sera ignorée<br></font></p>";break;
        //case "SUP": break;
        case "LIK": $mots=explode(" ",$$champ);foreach($mots as $mot)$wheres[$champ].=" AND $chp LIKE '%$mot%'";unset($mots);break;
        //case "___": encore faisable aussi            
            case "CHK": if($$champ=='on')$wheres[$champ]=" AND $chp='1'";break;
            case "REQ":
            case "SEL": if($$champ!="")$wheres[$champ]=" AND $chp='".$$champ."'";break;}}

//generation du formulaire
?>      <table border=0 cellspacing=0 cellpadding=0 width="<? echo $WIDTH;?>" align="center"><tr>
      <td colspan=100><?
//echo titre("moteur",$liens_get."<td>".lien_post($liens,0,array_merge(array(exe_moteur=>$exe_moteur,LIMIT=>$LIMIT,PAS=>$PAS,NOLINK=>$NOLINK,def=>0,tri=>$tri),$variables))
echo"<table align=\"center\" width=\"$WIDTH\"><tr><td nowrap><font size=\"-1\" color=\"#000099\">".$liens_get."<td width=\"100%\"><font size=\"-1\" color=\"#000099\">".
      "</font></table><table  border=0 cellspacing=\"5\" class=\"cadre\" align=\"center\" width=\"$WIDTH\">";
if(is_array($champs[$exe_moteur]) and $exe_moteur>0)foreach($champs[$exe_moteur] as $key=>$champ){
      switch($ch){case "SEL":$defopt=substr($champ,3,strpos($champ,"SEL",3)-3);
                                          if(!strpos($champ,"SEL",3) or $defopt=='')$defopt='non precise';
      if($ch=="EXE" or $ch=="REQ" or $ch=="___")continue;      $n++;
            case"TXT":echo substr($champ,3);unset($champs[$exe_moteur][$key]);break;
                  $req="select distinct $req from $requete[$exe_moteur]";
                  if(is_array($wheres)&&!$NOLINK)foreach($wheres as $ch=>$where)if($ch!=$champ)$req.=$where;//$req.=" group by croisiere.id_croisiere";POSSIBLE QUE VIRER CECI PROVOQUE DES ERREURS ==> A GARDER
                  if(!$res=req($req." order by $chp"))echo "$red erreur de requete : essayez de réinitialiser le moteur de recherche<br>";
                $champ=str_replace(".","§",$champ);echo"<select name=\"$champ\" width=150><option value=\"\">$defopt</option>";
                        echo"<option value=\"$line[0]\"";if($$champ==$line[0])echo"selected";echo">$line[1]</option>";}
                  echo "</select>";break;
            case"INF":  $ch_seul=substr($champ,strpos($champ,"SUP")+3);
                              if(${'SUP'.$ch_seul}=="")${'SUP'.$ch_seul}=stamptofr(mktime( 0, 0, 0, date('m') + 4, date('j'), date('Y') )); //mois actuel + 2 pour le SUP
                              echo"<input type=\"text\" name=\"INF$ch_seul\" size=\"15\" value=\"".${'INF'.$ch_seul}."\">".substr($champ,3,strpos($champ,"SUP")-3).
                              "<input type=\"text\" name=\"SUP$ch_seul\" size=\"15\" value=\"".${'SUP'.$ch_seul}."\">";      $n++;
            case"LIK":echo "<input type=\"text\" name=\"$champ\" value=\"".$$champ."\">";break;
            case"FCT":break;//on fera appel à une fonction déclarée ds la page d'appel => ne développer que si utile
            case"VAR":echo $$champ;break;
            case"CHK":echo "<input type=\"checkbox\" name=\"$champ\"";if($$champ=="on")echo" checked";echo">";break;
            default: echo"erreur : traitement inexistant pour $champ -> $$champ";break;}

if(is_array($champscles) && $exe_moteur=="KEY"){//on peut le proposer alternativement ou en plus avec un minimum de magouille si necessaire
foreach($champscles as $chp=>$champs){
      echo"<tr><td colspan=10 class=\"forms\"><input name=\"$chp\" value=\"".$$chp."\" size=\"100\"><tr><td colspan=10 class=\"forms\">";
      foreach($champs as $champ){echo"$champ <input type=\"checkbox\" name=\"EXE$chp$champ\"";if(${"EXE".$chp.$champ}=="on" or $def)echo" checked";echo"> - ";}}}

if($exe_moteur)echo "<input type=\"submit\" value=\"rechercher\"></table>";echo"</form>";
$where=@implode(" ",$wheres);//print_r($wheres);
//echo $where;

input page + COMMENTS


FIELDS type of field is first capital letters
TXT(text field)
SEL(select menu of available choices)
LIK(search using sql 'like' (several field possible))
$champs[2]=array("TXT<tr><td>Date de départ<br>","LIKdate_croisiere§date_depart",
                        "TXT<br>Promos seules <br>","CHKdate_croisiere§discount",
                        "TXT<td>Bateau<br>","SELTous les bateauxSELbateau§id_bateau",
                        "TXT<br>Compagnie<br>","SELToutes les compagniesSELcompagnie§id_compagnie",
                        "TXT<td>Croisières<br>","SELToutes les croisièresSELcroisiere§nom_croisiere",
                        "TXT<br>Villes<br>","SELToutes les villesSELville_cie§ville","TXT&nbsp;&nbsp;&nbsp;&nbsp");

always the same query but the where clause changes ($where)
inner join escale on escale.id_ville_cie=ville_cie.id_ville_cie,
inner join croisiere on croisiere.id_compagnie=compagnie.id_compagnie and bateau.id_bateau=croisiere.id_bateau
and escale.id_croisiere=croisiere.id_croisiere inner join date_croisiere on croisiere.id_croisiere=date_croisiere.id_croisiere
where 1 ";

just a few lookup patterns for that search
$ordergroup[2]=array(" group by date_croisiere.id_date_croisiere order by date_depart"," group by croisiere.id_croisiere order by nom_croisiere");

several different searches are possible, obviously changing the id value of the 3 main variables.
i give this code for basic study to give an idea of a working code.
i'll give the missing functions to use it as-is though i doubt thap app has really great quality dev.
TherosEquipmentAuthor Commented:
Thanks Skull!

I'll look at this later.  I don't have a lot of time for it today, but I'll look at it tommorow.

cool if it helps u
the interesting parts for you are the last lines of MDR (under "//mots-clés")
and the where clause construction. case 'MOT' of the first switch

if u dig in SEL types, you'll probably notice that unless $NOLINK is set to true, the MDR will only display values for which a result is to be found.

see ya
thanks venabili
i still have the rest of the codes around if u neeed them some time to work it out Theros
thanks, both.
been some time to understand that 'forced accept' meant that you force the accept and not that you are forced to... thus my silence on previous questions.
i usually thank as a matter of being polite.
in such cases this may be a silly way to overbook you with too many messages.
if so, please let me know and i won't any more.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now