Solved

search multiple fields from two tables with one string

Posted on 2004-04-16
11
162 Views
Last Modified: 2013-12-12
Hello,

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...
used/search.php?find=New&Holland

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.

Thanks,
Gabe
0
Comment
Question by:TherosEquipment
  • 5
  • 4
11 Comments
 

Author Comment

by:TherosEquipment
ID: 10845140
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.

Thanks
0
 

Author Comment

by:TherosEquipment
ID: 10858444
I hope everyone had a nice weekend!
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 10860069
(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
WHERE 1

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.
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:TherosEquipment
ID: 10860455
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.

Gabe
0
 
LVL 26

Accepted Solution

by:
skullnobrains earned 100 total points
ID: 10865324
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($liens))$liens=array();
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));}
      $chp=str_replace("§",".",substr($champ,3));
      if($$champ=="" and $ch!="INF")continue;
    switch($ch){
            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%'";
                              $wheres[$champ].=")";}break;
        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\">".
      lien_post($liens,0,array_merge(array(exe_moteur=>$exe_moteur,LIMIT=>$LIMIT,PAS=>$PAS,NOLINK=>$NOLINK,def=>0,tri=>$tri),$variables)).
      "</font></table><table  border=0 cellspacing=\"5\" class=\"cadre\" align=\"center\" width=\"$WIDTH\">";
//détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----détaillé-----
if(is_array($champs[$exe_moteur]) and $exe_moteur>0)foreach($champs[$exe_moteur] as $key=>$champ){
      $ch=substr($champ,0,3);unset($where,$defopt);
      switch($ch){case "SEL":$defopt=substr($champ,3,strpos($champ,"SEL",3)-3);
                                          if(!strpos($champ,"SEL",3) or $defopt=='')$defopt='non precise';
                                          $champs[$exe_moteur][$key]=substr($champ,strpos($champ,"SEL",3));
                                          $champ=substr($champ,strpos($champ,"SEL",3));}
      if($ch=="EXE" or $ch=="REQ" or $ch=="___")continue;      $n++;
    switch($ch){//case"EXE":case"REQ":case"___":break;
            case"TXT":echo substr($champ,3);unset($champs[$exe_moteur][$key]);break;
            case"SEL":$chp=str_replace("§",".",substr($champ,3));$req=$chp.",".str_replace("id_","nom_",$chp);
                  $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>";
                  while($line=mysql_fetch_row($res)){if(substr($champ,strpos($champ,"§")+1,4)=="date")$line[1]=stamptofr($line[1],1,2);
                        if(!$line[0])continue;if($line[0]==$line[1])$line[1]=convert($line[0],$affich[substr($champ,strpos($champ,"§")+1)]);
                        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++;
                              $champs[$exe_moteur][$key]="INF$ch_seul";break;
            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($n%2==1)echo"<td><td>";

//mots-clés
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
---------------------------------------------------------------------------

//croisiere

/*********
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");

/*QUERY
always the same query but the where clause changes ($where)
*/
$requete[2]="
ville_cie
inner join escale on escale.id_ville_cie=ville_cie.id_ville_cie,
compagnie,bateau
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 ";

/*ORDER + FILTERING
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");
$recherche[2]=array('date',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.
*/
0
 

Author Comment

by:TherosEquipment
ID: 10867867
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.

Gabe
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 10869426
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
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 11128579
thanks venabili
i still have the rest of the codes around if u neeed them some time to work it out Theros
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 11179513
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.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question