Page 1 of 1
Heiarchy only produces one set
Posted: Thu Jun 29, 2006 10:37 pm
by LiveFree
Time for a tucker!
Well Im building a sort of catagory heirachry system for units. The way it works is: There are top units which have the unit_heiarchy id (in mysql) of 0. The units that fall under that have a catagory heircharchy of 1 (the 1 signifies the unit ID (Primary key) of the top unit). That repeats basically
So when I run this query in phpMyAdmin I get all the units I want:
(Remember the $id var here is the ID of the top unit you clicked on)
Code: Select all
$sql = $db->sql_query("SELECT unit_banner, unit_name, designation, unit_heirarchy, id FROM atfcs_units WHERE unit_heirarchy = $id ORDER BY id ASC");
But when I run that in PHP, I only get the top set. with this code:
Code: Select all
while ($row = $db->sql_fetchrow($sql)){
echo "<img src='" . $row['unit_banner'] . "' />" . '<br />';
echo "<b><a href='modules.php?name=" . $module_name . "&op=roster&id=" . $row['id'] . "'>" . $row['unit_name'] . '</a></b> - <i>' . $row['designation'] . '</i><br />';
Am I missing something herea about the code repitition in the while()?
Thanks for the great work here:)
Posted: Fri Jun 30, 2006 3:28 am
by jamiel
If you want to the column names to be the keys, you are better off using fetch_assoc rather than fetch row.
var_dump($row) in your loop and see if its what you expect to come out.
Posted: Fri Jun 30, 2006 7:05 am
by LiveFree
Upon using the print_r on the $row var, i only get 1 row instead of all of them
Posted: Fri Jun 30, 2006 7:46 am
by jamiel
Show us the sql_fetchrow method. If you query is definately meant to return more than one result your problem probably lies there.
Posted: Fri Jun 30, 2006 8:19 am
by LiveFree
It is the DB Abstraction Layer included with PHP Nuke
It works with multi-row queries in other pages with other queries
Posted: Fri Jun 30, 2006 9:02 am
by John Cartwright
show us the method please.
Posted: Fri Jun 30, 2006 9:40 am
by LiveFree
Code: Select all
function sql_fetchrow($query_id = 0)
{
$stime = get_microtime();
if (!$query_id) { $query_id = $this->query_result; }
if($query_id)
{
$row = $this->fetch_array($query_id);
$this->time += (get_microtime()-$stime);
return $row;
} else {
return false;
}
}
Posted: Fri Jun 30, 2006 10:15 am
by LiveFree
*Not intended as a BUMP

*
After doing some more testing and changing the query around, I found out that PHP DOES find 3 rows (how many there should be) but yet again, the while() only shows one.
EDIT- And when I replace the $db->sql_fetchrow with mysql_fetch_array() it still only produces one set
This is the entire script if it helps:
Code: Select all
<?php
if(!defined('IN_ATFCS')) {
exit('Access Denied');
}
include_once(NUKE_BASE_DIR.'header.php');
title($atfcsconfig['clan_name'] . ' Roster');
if ($HTTP_GET_VARS['id'] != null){
$id = (int)$HTTP_GET_VARS['id'];
$sql = $db->sql_query("SELECT unit_banner, unit_name, designation, unit_heirarchy, id FROM atfcs_units WHERE unit_heirarchy = $id ORDER BY id ASC");
OpenTable();
while ($row = mysql_fetch_array($sql)){
echo "<img src='" . $row['unit_banner'] . "' />" . '<br />';
echo "<b><a href='modules.php?name=" . $module_name . "&op=roster&id=" . $row['id'] . "'>" . $row['unit_name'] . '</a></b> - <i>' . $row['designation'] . '</i><br />';
echo "<table border='1' cellspacing='2' width='40%'>";
echo "<tr><td><b>Surname</b></td><td><b>Rank</b></td><td><b>Xfire</b></td></tr>";
$sql = $db->sql_query("SELECT surname, rank, xfire, id FROM atfcs_soldiers WHERE squad='$row[id]' ORDER BY rank DESC");
while ($row = $db->sql_fetchrow($sql)){
$rank = $ranks[$row['rank']];
echo "<tr><td>{$row['surname']}</td><td>$rank</td><td>{$row['xfire']}</td></tr>";
}
echo "</table><br />";
}
}else{
$sql = $db->sql_query("SELECT unit_banner, unit_name, designation, unit_heirarchy, id FROM atfcs_units WHERE unit_heirarchy = 0");
OpenTable();
while ($row = $db->sql_fetchrow($sql)){
echo "<img src='" . $row['unit_banner'] . "' />" . '<br />';
echo "<b><a href='modules.php?name=" . $module_name . "&op=roster&id=" . $row['id'] . "'>" . $row['unit_name'] . '</a></b> - <i>' . $row['designation'] . '</i><br />';
echo "<table border='1' cellspacing='2' width='40%'>";
echo "<tr><td><b>Surname</b></td><td><b>Rank</b></td><td><b>Xfire</b></td></tr>";
$sql = $db->sql_query("SELECT surname, rank, xfire, id FROM atfcs_soldiers WHERE squad='$row[id]' ORDER BY rank DESC");
while ($row = $db->sql_fetchrow($sql)){
$rank = $ranks[$row['rank']];
echo "<tr><td>{$row['surname']}</td><td>$rank</td><td>{$row['xfire']}</td></tr>";
}
echo "</table><br />";
}
if ($db->sql_numrows($sql) == 0){
echo "<b><h3>No Current Units Formed</h3></b>";
}
}
CloseTable();
include(NUKE_BASE_DIR.'footer.php');
?>
Posted: Fri Jun 30, 2006 10:29 am
by JayBird
You cant use the results resource stored in $sql twice
i.e. You cant do this
Code: Select all
$result = mysql_query("SOME `query` HERE `withData`");
while($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $line['someValue'];
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) // after this while loop, your result resource is now at the end, so your first while loop will not run again!
{
echo $line['anotherValue'];
}
}
You could use
mysql_data_seek() to change the internal pointer
Posted: Fri Jun 30, 2006 10:33 am
by LiveFree
Damn!
Thanks Pimptastic!
My way of thinking was that the $sql var would be dropped after each while() iteration (i think I read that somewhere)