#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    31
    Rep Power
    15

    Question mysql problem with joining tables , complicated!


    Hi there!

    while developing my website i ran into a problem i couldnt figure out myself. i am developing a car website, and therefore i made a script to search cars.
    now you have the oppertunity to search for the nickname of owners, eg if i input tox it will display the car of owner toxical etc.

    to do this i do the following:
    first i search table 'user' for the nickname that is entered and pick the id and then display the car with the owner id i just picked.
    works fine when there is only one result but i dont know how to do this if i got 2 matching nicknames.
    for example i have 3 users whose nicknames are toxic, toxical and toxikan or something and then it would only output the first...

    onto the code... because you can search for different things in the search script i build the mysql query based on what is searched, and if nothing he just lists everything (used for the top ten rated cars)

    PHP Code:
    $query_head="SELECT 
                cars.car_id,
                cars.marke,
                cars.bezeichnung,
                cars.detailpic,
                user.id,
                user.nick,
                carvote.votes,
                carvote.ranking
            FROM
                cars,
                user,
                carvote "
    ;
    $query_where="WHERE
                cars.user_id=user.id and cars.car_id=carvote.car_id "
    ;
    $query_bez="";
    $query_nick="";
    $query_end="ORDER by carvote.ranking DESC LIMIT ".$offset.",".$ende;

    if (isset(
    $_POST['bezeichnung'])) 

        
    $search=$_POST['bezeichnung'];
        
    $query_bez="and cars.bezeichnung LIKE '%$search%' ";
        
    $titel="Suchergebnis";
    }

    if (isset(
    $_POST['nickname'])) 

        
    $search=$_POST['nickname'];
        
    $query="SELECT id FROM user WHERE nick LIKE '%$search%'";
        
    $result=mysql_query($query);
        
    $read_id=mysql_fetch_object($result);
        
    $search_uid=$read_id->id;
        
    $query_nick="and cars.user_id='$search_uid' ";
        
    $titel="Suchergebnis";
    }

    $query=$query_head.$query_where.$query_bez.$query_nick.$query_end;

    $result=mysql_query($query) or die (mysql_error()); 
    as you can see he alters the query if one ore more search fields are entered, and you can see that the way i do it with the nickname field it can only output one result, but i dont know how to get it working if it finds more than one nickname matching the search string


    rest of the code:

    PHP Code:
    echo "<table width=\"550\" cellspacing=\"1\" cellpadding=\"3\" border=\"0\">";
    echo 
    "<tr>";
    echo 
    "    <th colspan=\"2\" class=\"thLeft\" nowrap=\"nowrap\"><div align=\"center\">".$titel."</div></th>";
    echo 
    "</tr>";
    if (
    mysql_num_rows($result))
    {
    $platz=1;
    while (
    $carlist=mysql_fetch_object($result))
    {
        
    $query="SELECT id FROM carcomments WHERE auto_id='$carlist->car_id'";
        
    $result2=mysql_query($query) or die (mysql_error());
        
    $anzcomments=mysql_num_rows($result2);
        
    $query="SELECT id FROM usercarpics WHERE car_id='$carlist->car_id'";
        
    $result2=mysql_query($query) or die (mysql_error());
        
    $anzbilder=mysql_num_rows($result2);
        echo 
    "<tr>";
        echo 
    "    <td class=\"row1\" width=\"160\">";
        echo 
    "<table cellspacing=\"1\" cellpadding=\"3\" border=\"0\">";
        echo 
    "<tr>";
        echo 
    "<td class=\"row2\"><div align=\"left\"><b><font size=\"2\">Platz ".$platz."</font></b></div></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "<td class=\"row2\"><div align=\"center\">Bewertung: ".round($carlist->ranking,2)."</div></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "<td class=\"row2\"><div align=\"center\"><a href=\"?action=showcar&id=".$carlist->car_id."\"><img src=\"./thumbs/".$carlist->detailpic."\" border=\"0\"></a></div></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "<td class=\"row2\"><div align=\"center\">Votes: ".$carlist->votes."</div></td>";
        echo 
    "</tr>";
        echo 
    "</table>";
        echo 
    "</td>";
        echo 
    "<td class=\"row1\" valign=\"top\">";
        echo 
    "<table cellspacing=\"1\" cellpadding=\"3\" border=\"0\">";
        echo 
    "<tr>";
        echo 
    "    <td colspan=\"2\" class=\"row2\">&nbsp;<b><font size=\"2\">".$carlist->marke."&nbsp;".$carlist->bezeichnung."</font></b></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "    <td width=\"60\" class=\"row2\">&nbsp;Besitzer:</td>";
        echo 
    "    <td width=\"80\" class=\"row2\">&nbsp;<a href=\"?action=showuser&id=".$carlist->id."\">".$carlist->nick."</a></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "    <td width=\"60\" class=\"row2\">&nbsp;Kommentare:</td>";
        echo 
    "    <td width=\"60\" class=\"row2\">&nbsp;".$anzcomments."</a></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "    <td width=\"60\" class=\"row2\">&nbsp;Bilder:</td>";
        echo 
    "    <td width=\"60\" class=\"row2\">&nbsp;".$anzbilder."</a></td>";
        echo 
    "</tr>";
        echo 
    "<tr>";
        echo 
    "    <td colspan=\"2\" height=\"10\"></td>";
        echo 
    "</tr>";
        echo 
    "</table>";
        echo 
    "<table align=\"left\">";
        echo 
    "<tr>";
        echo 
    "<td>";
        echo 
    "&nbsp;<img src=\"./media/images/arrow.gif\" border=\"0\" align=\"bottom\"></td>";
        echo 
    "<td><a href=\"http://www.speedsociety.de/?action=showcar&id=".$carlist->car_id."\">[url]http://www.speedsociety.de/?action=showcar&id=[/url]".$carlist->car_id."</a></td>";
        echo 
    "</tr></table>";
        echo 
    "</td>";
        echo 
    "</tr>";
        
    $platz++;
    }
    } else
        {
            echo 
    "<tr>";
            echo 
    "<td class=\"row1\"><div align=\"center\">Kein Auto gefunden!</div></td>";
            echo 
    "</tr>";
        }
    $offset=$ende+1;
    echo 
    "<tr>";
    echo 
    "    <th colspan=\"2\" class=\"thLeft\" nowrap=\"nowrap\">";
    if (
    mysql_num_rows($result)>$ende)
    {
        echo 
    "<div align=\"right\"><a href=\"?action=listcars&offset=".$offset."\">Nächste >></a></div>";
    }
    echo 
    "</th>";
    echo 
    "</tr>";
    echo 
    "</table>"
    i hope somebody can look through that and understands what i mean AND can eventually help me
    would be great!
    thx in advance

    Arne
  2. #2
  3. Shes dancing (obviously)
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2002
    Location
    the far side
    Posts
    527
    Rep Power
    17
    i think this question would be be posted in the sql forums, there is some really good sql gurus in there

    microsofts butterfly is their way off telling u their systems have a **** load of buggs
    Advocating Linux Guide
    Lesbian Linux
    Great & Practical Computer Books

    like the links?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2001
    Location
    In a multi-cultural mess.
    Posts
    569
    Rep Power
    18
    toxical,

    Can you simplify by substituting for --
    PHP Code:
    if (isset($_POST['nickname'])) 

      
    $search=$_POST['nickname'];
      
    $query="SELECT id FROM user WHERE nick LIKE '%$search%'";
      
    $result=mysql_query($query);
      
    $read_id=mysql_fetch_object($result);
      
    $search_uid=$read_id->id;
      
    $query_nick="and cars.user_id='$search_uid' ";
      
    $titel="Suchergebnis";

    with this
    PHP Code:
    if (isset($_POST['nickname'])) 

      
    $search=$_POST['nickname'];
      
    $query_nick="and user.nick LIKE '$search' ";
      
    $titel="Suchergebnis";

    I also usually use LEFT JOIN but this may work also.

    Hope this helps,
    Robert
    Robert
    ---
    If it's hard, it's probably wrong.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Chicago, IL
    Posts
    47
    Rep Power
    15
    I would do something like this
    PHP Code:
    if (isset($_POST['nickname'])) 

      
    $search=$_POST['nickname'];
      
    $query="SELECT id FROM user WHERE nick LIKE '%$search%'";
      
    $result=mysql_query($query);
      while (
    $read_id=mysql_fetch_array($result))
      {
        
    $nick_uids .= "$comma$read_id[id]";
        
    $comma ", ";
      }
      
    $query_nick="and cars.user_id IN ($nick_uids)";
      
    $titel="Suchergebnis";

  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    31
    Rep Power
    15
    thank you!
    @rtaylor

    your solution works great, you just forgot the % before and after $search...
    damn the solution was so simply and i figured a really circuitous solution yesterday which worked but was way more code and therefor alot slower i guess...and the solution was so near and i didnt think of that lol, almost awkward

    thank you alot for making my website a bit faster and poiting me to such a simple solution! great work
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    31
    Rep Power
    15
    @weevil
    of course thank you to you too, but i just tried rtylors solution first cause it was so simply and it worked flawless, but thank you for your effort anyway!
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2001
    Location
    In a multi-cultural mess.
    Posts
    569
    Rep Power
    18
    toxical,

    You are right; I left out both % signs.
    I did intend to leave out the trailing %.

    The leading % is needed.
    The trailing % is not needed unless you were looking for 2 substrings in a big string.
    -------------------------------------------------

    "The quick brown fox jumped over the lazy dog."

    '%qui%over'
    will match the big string.
    ---------------------

    HTH,
    Robert

IMN logo majestic logo threadwatch logo seochat tools logo