Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


search multiple fields from two tables with one string

Posted on 2004-04-16
Medium Priority
Last Modified: 2013-12-12

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.

Question by:TherosEquipment
  • 5
  • 4

Author Comment

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.


Author Comment

ID: 10858444
I hope everyone had a nice weekend!
LVL 27

Expert Comment

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

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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.

LVL 27

Accepted Solution

skullnobrains earned 400 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($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.

Author Comment

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.

LVL 27

Expert Comment

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
LVL 27

Expert Comment

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

Expert Comment

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…
Suggested Courses

564 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