Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
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.


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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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 dynamically set the form action using jQuery.
Suggested Courses

610 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