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
: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;
            }
        }

Posted: Fri Jun 30, 2006 10:15 am
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');
?>

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)