Solved

search multiple fields from two tables with one string

Posted on 2004-04-16
11
160 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now