SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying 1st 3 Letters of 2 Values

    Consider the following taxonomic terms:

    Class Mammalia (mammals)
    Order Rodentia (rodents)
    Order Carnivora (carnivores)
    Family Canidae (dog family)
    Family Felidae (cat family)
    Genus Panthera (big cats)
    Species Panthera leo (lion)

    Now consider four web pages that feature the following in their head sections, where $mycode equals the first three letters of a taxonomic grouping and $mycode2 equals the first three letters of its parent:

    $mycode = 'Car';
    $mycode2 = 'Mam';

    (This identifies this page as order Carnivora, parent Mammalia.)

    $mycode = 'Can';
    $mycode2 = 'Car';

    (Family Canidae, parent Carnivora)

    $mycode = 'Fel';
    $mycode2 = 'Car';

    (Family Felidae, parent Carnivora)

    $mycode = 'Pan';
    $mycode2 = 'Fel';

    (Genus Panthera, parent Felidae)

    Now, I'd like to write a query with a WHERE clause that combines $mycode2 and $mycode (in that order) and matches them to any taxonomic level. For example, consider the following example:

    $mycode = 'Can';
    $mycode2 = 'Fel';

    If you combined $mycode2 and $mycode, you wind up with carfel, a combination that should only occur once in my table The combination felpan also occurs just once. But I can't say, "WHERE $mycode2 + $mycode = S.Order + S.Family, because carfel = the first three letters of S.Order + S.Family, but felpan = the first three letters of S.Family + S.Genus.

    So I THINK I want to write something like this:

    $Animals = mysql_fetch_assoc(mysql_query("SELECT * FROM S as symbols
    WHERE [the 1st 3 letters of S.Class and the 1st 3 letters of S.Order] = '$mycode2' + '$mycode' OR [the 1st 3 letters of S.Order and the 1st 3 letters of S.Family] = '$mycode2' + '$mycode' OR [the 1st 3 letters of S.Family and the 1st 3 letters of S.Genus] = '$mycode2' + '$mycode' OR [the 1st 3 letters of S.Genus and the 1st 3 letters of S.Species] = '$mycode2' + '$mycode'");

    In case my explanation is too confusing, let me offer another explanation. Imagine a table with four fields, named A, B, C and D...

    A | B | C | D

    I'd like to create a page with this in the head section:

    $mycode = 'vex';
    $mycode2 = 'top';

    Then write a query that matches "vextop" to the first three letters of two consecutive words, whether those two words are in fields A-B, B-C or C-D.

    Incidentally, it probably isn't imperative that the code formed by joining two terms is in this format - abcdef. If you think it would be better to work with something like abc-def or abc/def, that's fine, too.

    Thanks.

    P.S. I should mention that these are the relevant fiels in table "symbols":

    symbols.SymClass
    symbols.Class
    symbols.Order
    symbols.Family
    symbols.Genus
    symbols.Family

    SymClass is either "animals", "plants" or null.

  2. #2
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about,
    Code:
    $sql = "SELECT * FROM S
    WHERE CONCAT( MID(S.a, 1,3), '-', MID(S.b, 1,3), '-', MID(S.c, 1,3), '-', MID(S.d, 1,3))
    LIKE '%" . $first . "-" . $second . "%'";
    
    $animals = mysql_fetch_assoc(mysql_query($sql));

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I think I understand what you're doing. I recognize CONCAT as an abbreviation for concatenation (spelling?), which means to condense or abbreviate items, I think. What does "MID" mean?

    At any rate, I'll give that a try...

  4. #4
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MID is synonym for SUBSTRING

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I think I basically understand your script, though I'm doing something wrong.

    Below is my entire master query, which is actually a series of queries. I'll try to simplify it as I learn more about it. But if you scroll down, you'll see your script at the end.

    PHP Code:
    <?php
    $qry 
    "SELECT * FROM basics as b, famarea as f
     WHERE f.IDArea = b.IDArea AND f.IDArea = '
    $mycode'";

    $result mysql_query($qry);
    // For troubleshooting: 
    $result mysql_query($qry) or die('Error: '.mysql_error());
    $type mysql_fetch_array($result);
    $mytype "my" $type['TypeArea'];
    $
    $mytype $type['Name'];

    if (
    phpversion() <= "4.1.0") { $vars array_merge($HTTP_GET_VARS$HTTP_POST_VARS); } else { $vars $_REQUEST; }
    $area_code_sql mysql_query("SELECT * FROM basics as b, famarea as f
     WHERE f.IDArea = b.IDArea AND f.IDArea = '"
    .$mycode);

    $type mysql_fetch_assoc($result);
    $mytype "my" $type['TypeArea'];
    $data = array();
    $data_details mysql_fetch_assoc(mysql_query("SELECT *
    FROM famarea WHERE IDArea = '
    $mycode'"));

    $query "SELECT famarea.*,  basics.* FROM famarea, basics
    WHERE famarea.IDArea = basics.IDArea AND famarea.IDArea = '
    $mycode'";
    $data = array();
    if (
    $res mysql_query($query)) { while ($temp mysql_fetch_assoc($res)) { $data[] = $temp;} };
    $foo = array("us-""ca-""jp-");
    $mycode str_replace($foo""$mycode);

    $SymClass mysql_fetch_assoc(mysql_query("SELECT F.IDArea, F.Name, S.IDArea, S.DesigGeneral,
      S.SymClass, S.Class, S.Order, S.ClassCommon
      FROM famarea AS F, symbols as S WHERE F.IDArea = S.IDArea AND SymClass = 'animals' AND S.SymClass LIKE '
    $mycode%'"));

     
    $Class mysql_fetch_assoc(mysql_query("SELECT F.IDArea, F.Name, S.IDArea, S.DesigGeneral,
      S.SymClass, S.Class, S.Order, S.ClassCommon
      FROM famarea AS F, symbols as S WHERE F.IDArea = S.IDArea AND SymClass = 'animals' AND S.Class LIKE '
    $mycode%'"));

    $Order mysql_fetch_assoc(mysql_query("SELECT * FROM symbols as S
     WHERE S.SymClass = 'animals' AND S.Order LIKE '
    $mycode%' AND S.Class LIKE '$mycode2%'"));

     
    $Family mysql_fetch_assoc(mysql_query("SELECT F.IDArea, F.Name, S.IDArea, S.DesigGeneral,
      S.SymClass, S.Class, S.Order, S.Family, S.Genus, S.Species, S.ClassCommon, S.FamilyCommon
      FROM famarea AS F, symbols as S WHERE F.IDArea = S.IDArea AND SymClass = 'animals' AND S.Family LIKE '
    $mycode%' AND S.Order LIKE '$mycode2%'"));



    // YOUR SCRIPT...

    $sql "SELECT * FROM symbols as S
    WHERE CONCAT( MID(S.Class, 1,3), '-', MID(S.Order, 1,3), '-', MID(S.Family, 1,3), '-', MID(S.Genus, 1,3))
    LIKE '%" 
    $first "-" $second "%'";

    $Animals mysql_fetch_assoc(mysql_query($sql));
    ?>
    And here's how I'm trying to plug it in. Let's start with a page focusing on the cat family (Felidae), which includes this in the head section:

    $mycode = 'Fel';
    $mycode2 = 'Car';

    Next, here's an included PHP switch that focuses on each row's class (field Class):

    PHP Code:
    <?php
    switch ($Animals['Class']) {
    case 
    'Mammalia':
    include(
    $_SERVER['DOCUMENT_ROOT'] . '/a1/articles/ani/mammals.php');
    break;
    case 
    'Reptilia':
    include(
    $_SERVER['DOCUMENT_ROOT'] . '/a1/articles/ani/reptiles.php');
    break;
    default:
    break;
    }
    ?>
    I try to connect to a row by matching the first three letters of two successive table fields. After I get a match, I link that row's value in the Class field to the PHP switch above, so everything that's class Mammalia is linked to the mammals page at mammals.php.

    $mycode = 'Fel'; + $mycode2 = 'Car'; combines to form CarFel, which belongs to class Mammalia.

    I suspect your script isn't working for me for one of two reasons:

    1) I haven't plugged in $mycode2 + $mycode...

    PHP Code:
    $sql "SELECT * FROM symbols as S
    WHERE CONCAT( MID(S.Class, 1,3), '-', MID(S.Order, 1,3), '-', MID(S.Family, 1,3), '-', MID(S.Genus, 1,3))
    LIKE '%" 
    $first "-" $second "%'";

    $Animals mysql_fetch_assoc(mysql_query($sql)); 
    I think the script should look something like this:


    PHP Code:
    $sql "SELECT * FROM symbols as S
    WHERE CONCAT( MID(S.Class, 1,3), '-', MID(S.Order, 1,3), '-', MID(S.Family, 1,3), '-', MID(S.Genus, 1,3))
    LIKE '%" 
    $first "-" $second "%' 

    // Here's what I added...
    AND the resulting six-letter code = 
    $mycode2 + $mycode [e.g. "felcar"]";

    $Animals mysql_fetch_assoc(mysql_query($sql)); 
    2) The other possible reason may be this '-' Does this insert a hyphen between the two abbreviations, like this?: car-fel? Actually, it may not matter; I think the most important thing now is simply connecting your script to $mycode2 + $mycode.

    Thanks.

  6. #6
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The hyphens prevent the CONCAT from creating words within the string that could cause a false match. Force of habbit for me to do that, you'ld have to take a good look at all possible values for the fields to make sure there's no possibility of the CONCAT creating a false match before removing the hyphens.

    As for plugging in the $mycode vars,
    Code:
    //Replace
    '%" . $first . "-" . $second . "%'
    
    //With
    '%" . $mycode . "-" . $mycode2 . "%'
    If you're using only the 4 fields listed above.

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BINGO! I made one or two minor changes, and it's working.

    Thanks for an awesome script. (I gave you some rep, too.)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •