Heiarchy only produces one set

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Heiarchy only produces one set

Post 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:)
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post by LiveFree »

Upon using the print_r on the $row var, i only get 1 row instead of all of them
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post by LiveFree »

It is the DB Abstraction Layer included with PHP Nuke

It works with multi-row queries in other pages with other queries
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

show us the method please.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post by LiveFree »

:oops:

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;
            }
        }
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post by LiveFree »

*Not intended as a BUMP :P *

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');
?>
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post 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)
Post Reply