[RESOLVED] Navigation links

[Hymeno]

I am having trouble creating successive navigation links in PHP. (I'm not sure what the term for them is; probably something like "contextual links.") The topic is biological taxonomy. I use only one MYSQL table for this part of the website. Biologists enter data via a form and it gets placed in that table as a unique index "insect_id" and a value for an ordinary index "genus&species." Each entry includes mandatory values for "order" and "family." The information is not stored as a hierarchy. But I hope to create navigational links that recreate the taxonomic hierarchy: "order - family - genus&species."
The navigation should start out showing only the names of available orders - as links, of course, but without repetitions. If I have 500 records spread out over 10 orders, I don't want 500 links right off the bat: I want 10 links. And they should link to the next layer down: the families. That's the second problem: how does one PHP-generated link, clicked on, allow several new links to appear?
When one clicks on a particular family name, the related genus&species names appear. Click on one of them and you can view all the information about a particular record.
Here's the kind of code I've used until now:

$query = 'SELECT * FROM ecology ORDER BY familie ASC';
$result = mysql_query($query, $db) or die(mysql_error($db));

And

while($item = mysql_fetch_array($result)){ 
	echo '<li><a href="view.php?insect_id='.$item['insect_id'].'">'. 
	     $item[familie].'</a>'; 
	echo "\t</li>\n";    

	}

Thanks

[bpat1434]

Well without knowing exactly how your DB is structured, it may require some restructuring or just a slight code modification. For example....

Let's start off with Kaua'i Cave Wolf Spider (a.k.a. "Blind Wolf Spider") as our insect. We know the following about it:
Kingdom: Animalia
Phylum: Arthropoda
Class: Arachnida
Order: Araneae
Suborder: Araneomorphae
Family: Lycosidae
Genus: Adelocosa
Species: Adelocosa anops

I'm assuming we're storing from Order down. So from that we gather we need to store:
Aranae / Lycosidea / Adelocosa / Adelecosa anops

Now, we can do this in one of two ways. Either for each entry we have 2 columns: insect_id, parent_id OR we store the "path" as a forward slash separated string of insect_ids.

If we did two columns, the "parent_id" would by default be null, and those with null parent_ids would be your "Orders".

insect_id | parent_id | name
     1    |    NULL   | Araneae

Next, we would have our "Family" with a parent_id the value of that order:

insect_id | parent_id | name
     1    |    NULL   | Araneae
     2    |     1     | Lycosidae

And then our genus...

insect_id | parent_id | name
     1    |    NULL   | Araneae
     2    |     1     | Lycosidae
     3    |     2     | Adelocosa

And finally our species

insect_id | parent_id | name
     1    |    NULL   | Araneae
     2    |     1     | Lycosidae
     3    |     2     | Adelocosa
     4    |     3     | Adelocosa anops

Then when querying for certain items, you know the parent (because you should be passing it via the URL) and you can get the children.

SELECT * FROM table WHERE `parent_id` = {$parentID}

where $parentID would be null if you're looking for just the root items, or an integer. For example, if I were looking for the genus (but not species) I might do the following:

SELECT *
FROM table
WHERE `parent_id` = 2

Now, that would bring back all those rows that are a part of the Lycosidae family. But you can filter from there with joins and other where clauses.

---

The other option is to use a more "path" based approach. Where you will have two columns (insect_id and path) but path would be a forward slash delimited list of parents.
Using the same table data as above, it would be:

insect_id |   path  | name
----------+---------+----------------
     1    |   NULL  | Araneae
     2    | 1       | Lycosidae
     3    | 1/2     | Adelocosa
     4    | 1/2/3   | Adelocosa anops

Then searching would be more like:

SELECT *
FROM table
WHERE path LIKE '1/%'
  AND path NOT LIKE '1/%/%'

to find all those families in the Araneae order.

This one is a little bit more complicated, but could be easier to maintain and definitely easier to see exactly where that insect belongs in the taxonomy.

Hope that helps.

[Hymeno]

Thanks for your reply. It resembles replies to topics similar to this. But there is at least one difference: it is not me, but other people (field biologists) who "populate" the table "ecology," so even if I create a column 'parent_id,' no user can wisely fill it in. So I am limited to using names they use (of order, family and genus&species) to create variables that I use for navigation links. I'm still working on it, but am not yet successful.
Because my website only concerns the class insects, other kingdoms, phyla and classes are not important.
Any suggestions about navigating from order to genus?

I use only one table for this information. Each row is a separate entry of a particular insect by a field biologist. Some of the columns are: insect_id, order, family, genus&species.

[bpat1434]

Well, you can let your field biologists select a "parent" or the heirarchy when they input an insect. For example, the form could look like:

Insect Name: _____________________________
Genus / Species: [ -- Select -- ]  Other: ________________
Family: [ -- Select -- ] Other: _____________
Order: [ -- Select -- ] Other: ______________

Each select field would have an "other" option which would allow them to input the name of the order / genus / species / family. Then when you save the data, either (a) use the ID from the selected family / genus / species / order or (b) do a look up for that family / genus / species / order and use that ID or (c) insert a new row for that family / genus / species / order and use that new ID.

So your database would relate by IDs; however, from the front, the biologists would never know the difference. And as for urls, you could use a good apache rewrite to force the listing page and then list those insects by order, family, genus & species.

Hope that helps.

[DaiLaughing]

From your original post I thought your question was in the area of displaying of data rather than storing it. However I do think you need a related group of tables one for order, one for species etc. (not a biologist!).

Then the problem is how to get the sub-groupings to appear when you click on an order. PHP as such cannot do that because it only works when the page is requested/loaded. You would need to reload the page each time. However, two ways spring to mind to do it:

1) AJAX (which is not as hard as it at first seems if you already know Javascript and PHP a bit)
2) a CSS dropdown menu system with all of the data loaded on page load but hidden until needed

The first involves you learning and the second means the page has to include lots of data and so will be slow.

So, the easiest way would be to have a series of three or four pages narrowing down to species.

Of course if i misunderstood what you were asking none of that will help!

[Hymeno]

Thanks for your comments. I'm still not convinced that I can do things with numbers that I can't do with names. At the moment I use three tables. One of them is used to store input from biologists. Its mandatory fields are order, family and latin_name (= genus&species). The interesting part is the field "suggestions for conservation."
My problems concern displaying the data. I have since followed the advice of DaiLaughing and split the search-by-links into several pages. And that's been successful.
To start:

$query = 'SELECT DISTINCT order FROM ecology';
$result = mysql_query($query, $db) or die(mysql_error($db));

And then:

while($item = mysql_fetch_array($result)){ 
	echo '<li><a href="view3.php?order='.$item['order'].'">'. 
	     $item['order'].'</a>';
	echo "\t</li>\n";
	$_GET['orde'] = $item['orde'];
	}

This $_GET variable gets passed to the next page, view3.php. It gets displayed somewhat similarly.

     $orde3 = $_GET['orde'];
     $query3 = "SELECT DISTINCT familie FROM ecologie WHERE orde = '$orde3'";
     $result3 = mysql_query($query3, $db) or die(mysql_error($db));

And then:

while($item3 = mysql_fetch_array($result3)){ 
	echo '<li><a href="view4.php?family='.$item3['family'].'">'. 
	     $item3['family'].'</a>';
	echo "\t</li>\n";
	$_GET['family'] = $item3['family'];
	}

This works reasonably well over several pages. Is there any way to prevent $_GET['variable'] from inheriting the last value in a list, before processing, so that I can compress all these pages to one? Then I only have to refresh the page with a link to the next level "down" in the taxonomic hierarchy.

[bpat1434]

Here's an example I whipped up for you:
Navigation Links

Here's the code behind it:

<?php

include_once('db.php');
/** GLOBAL NAVIGATION **/

$current = array(
        'order' => (empty($_GET['o']) ? null : intval($_GET['o'])),
        'family' => (empty($_GET['f']) ? null : intval($_GET['f'])),
        'species' => (empty($_GET['s']) ? null : intval($_GET['s']))
);

$insects = array(); // For storing data about our navigation
$orders = array();
$families = array();

// Find all Orders that we have:
$result = mysql_query("SELECT `id`, `name` FROM `insects` WHERE `path` = ''");
if(!$result)
{
        die('No orders found.');
}

while($row = mysql_fetch_assoc($result))
{
        $insects[$row['id']] = array(
                'name' => $row['name'],
                'current' => ($current['order'] !== null && $current['order'] == $row['id']),
                'family' => array()
        );
        $orders[] = $row['id'];
}

$result = mysql_query("
        SELECT `id`, `name`, `path`
        FROM `insects`
        WHERE (
                        `path` LIKE '&#37;'
                AND
                        `path` NOT LIKE '%/%'
        )
        AND
                `id` NOT IN (".implode(',', $orders).")
");
if($result)
{
        while($row = mysql_fetch_assoc($result))
        {
                $order = $row['path'];
                $insects[$order]['family'][$row['id']] = array(
                        'name' => $row['name'],
                        'current' => ($current['family'] !== null && $current['family'] == $row['id']),
                        'species' => array()
                );
                $families[] = $row['id'];
        }
}

$result = mysql_query("
        SELECT `id`, `name`, `path`, `suggestions`
        FROM `insects`
        WHERE
                `path` LIKE '%/%'
          AND
                `id` NOT IN (".implode(',', array_merge($orders, $families)).")
");
if($result)
{
        while($row = mysql_fetch_assoc($result))
        {
                list($order, $family) = explode('/', $row['path']);
                $insects[$order]['family'][$family]['species'][$row['id']] = array(
                        'name' => $row['name'],
                        'current' => ($current['species'] !== null && $current['species'] == $row['id']),
                        'suggestions' => $row['suggestions']
                );
        }
}
?>
<html>
<head>
        <title>Insects</title>
        <link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<div id="top">
        <ul id="navigation">
                <?php foreach($insects as $orderID => $order): ?>
                <li<?php if($order['current'] == true) { ?> class="current"<?php } ?>>
                        <a href="?o=<?php echo $orderID; ?>" title="<?php echo $order['name']; ?>">
                                <?php echo $order['name']; ?>
                        </a>
                        <ul>
                                <?php foreach($order['family'] as $familyID => $family): ?>
                                <li<?php if($family['current'] == true) { ?> class="current"<?php } ?>>
                                        <a href="?o=<?php echo $orderID; ?>&f=<?php echo $familyID; ?>" title="<?php echo $family['name']; ?>">
                                                <?php echo $family['name']; ?>
                                        </a>
                                        <ul>
                                                <?php foreach($family['species'] as $speciesID => $species): ?>
                                                <li<?php if($species['current'] == true) { ?> class="current"<?php } ?>>
                                                        <a href="?o=<?php echo $orderID; ?>&f=<?php echo $familyID; ?>&s=<?php echo $speciesID; ?>" title="<?php echo $species['name']; ?>">
                                                                <?php echo $species['name']; ?>
                                                        </a>
                                                </li>
                                                <?php endforeach; ?>
                                        </ul>
                                </li>
                                <?php endforeach; ?>
                        </ul>
                </li>
                <?php endforeach; ?>
        </ul>
</div>
<div id="bottom">
<?php
// View a specific Species
if(!empty($_GET['o']) && !empty($_GET['f']) && !empty($_GET['s']))
{
        $result = mysql_query("
                SELECT *
                FROM `insects`
                WHERE `id` = ".intval($_GET['s'])."
                  AND `path` = '".intval($_GET['o']).'/'.intval($_GET['f'])."'
                LIMIT 1
        ");
        if(!$result)
        {
                die('Unknown Species');
        }
        $species = mysql_fetch_assoc($result);
        ?>
        <h1><?php echo $species['name']; ?></h1>
        <p><?php echo (!empty($species['suggestions']) ? $species['suggestions'] : 'No suggestions presented'); ?></p>
        <?php
}
// View a specific family
elseif(!empty($_GET['o']) && !empty($_GET['f']) && empty($_GET['s']))
{
        $result = mysql_query("
                SELECT *
                FROM `insects`
                WHERE `id` = ".intval($_GET['f'])."
                  AND `path` = ".intval($_GET['o'])."
                LIMIT 1
        ");
        if(!$result)
        {
                die('Unknown Family');
        }
        $family = mysql_fetch_assoc($result);
        ?>
        <h1><?php echo $family['name']; ?></h1>
        <p><?php echo (!empty($family['suggestions']) ? $family['suggestions'] : 'No suggestions presented'); ?></p>

    <h2>Genus &amp; Species</h2>
    <ul>
            <?php foreach($insects[$_GET['o']]['family'][$_GET['f']]['species'] as $speciesID => $species): ?>
            <li>
                    <a href="?o=<?php echo intval($_GET['o']); ?>&f=<?php echo intval($_GET['f']); ?>&s=<?php echo $speciesID; ?>" title="<?php echo $species['name']; ?>">
                            <?php echo $species['name']; ?>
                    </a>
            </li>
            <?php endforeach; ?>
    </ul>
    <?php
}
// View a specific order
elseif(!empty($_GET['o']) && empty($_GET['f']) && empty($_GET['s']))
{
        $result = mysql_query("
                SELECT *
                FROM `insects`
                WHERE `id` = ".intval($_GET['o'])."
                  AND `path` = ''
                LIMIT 1
        ");
        if(!$result)
        {
                die('Unknown Order');
        }
        $order = mysql_fetch_assoc($result);
        ?>
        <h1><?php echo $order['name']; ?></h1>
        <p><?php echo (!empty($order['suggestions']) ? $order['suggestions'] : 'No suggestions presented'); ?></p>

    <h2>Families</h2>
    <ul>
            <?php foreach($insects[$_GET['o']]['family'] as $familyID => $family): ?>
            <li>
                    <a href="?o=<?php echo intval($_GET['o']); ?>&f=<?php echo $familyID; ?>" title="<?php echo $family['name']; ?>">
                            <?php echo $family['name']; ?>
                    </a>
            </li>
            <?php endforeach; ?>
    </ul>
    <?php
}
else
{
        ?>
<p>Please choose an item from the left.</p>
        <?php
}
?>
</div>
</body>
</html>

Hope that helps. All on one page.

[Hymeno]

Thanks. I'll give it a try. I'll report back in a few days.

[Hymeno]

Hello bpat1434,
I've got your suggestion up and running. Borrowing from a previous reply of yours about hierarchies. (I've avoided hierarchies until now, but OK, if they work...!) But I don't understand your interpretation of the row "suggestions" nor can I get it to work.
My plan was to provide (conservation) suggestions for each Genus&Species, only, when possible. What I get from your code is the echo ' No suggestions presented' at every level: order, family and genus&species.
It seems to me there are at least three possibilities: you change something in your original code, now that you understand me better; I add a field 'suggestions' to the row of genus&species, whereby a particular genus&species gets particular 'suggestions' if available; Or I add a new entry 'name' lower in the hierarchy, just under the genus&species that it applies to. Any suggestions?
Many thanks up till now, by the way.

[bpat1434]

You could create a separate table called "conservations" and link that to the ID of the genus & species. Then as do a join in the Genus & Species query and display it.

My demo code was generally a high-level look at what you can do. You can break it down as needed in any way you want. If you only want the extra stuff spewed out if a genus & species is displayed, then move that section of the code into the part that displays a genus & species 😉

[Hymeno]

[FONT="Georgia"]Thanks. That's what I've been working on since. As far as I'm concerned: resolved.[/FONT]