Querying 1st 3 Letters of 2 Values

[geosite]

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.

[joebert]

How about,

$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));

[geosite]

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...

[joebert]

MID is synonym for SUBSTRING

[geosite]

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
$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
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...

$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:

$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.

[joebert]

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,

//Replace
'%" . $first . "-" . $second . "%'

//With
'%" . $mycode . "-" . $mycode2 . "%'

If you're using only the 4 fields listed above.

[geosite]

BINGO! I made one or two minor changes, and it's working.

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