Automatic site navigation problem

[sjthomas]

Hi guys, sorry for the rather cryptic subject but I can't think of a better way to describe it! I'm a bit new to this so you'll have to bear with me. Basically I've got a database with three fields, ID, Section and parentid. I'm using autonumber to generate ID and parentid has to be a current ID of a record. What I'm trying to do is output the table in a list format and I'm having a problem looping through the table successfully. I think the problem lies in determining ho wmany records there are in the DB. I can find out how many rows but obviously if I delete a few records and add some in then using the number of rows as an identifier isn't going to work as it cuts short. I've been using:

SELECT id FROM table ORDER BY id DESC LIMIT 1

To try and get the value of the highest ID but its not working right. Basically its returning #5 when it should be returning 28! I checked by outputting it. The exact code I'm using is:

$query1 = "SELECT id FROM table ORDER BY id DESC LIMIT 1";
$result1 = mysql_query($query1);
$y = $result1;
echo $y;
for ($x=1; $x<27; $x++) {
etc....
}

If anyone could help me out it would be great!

[coopster]

First, to get the number of rows in your table, just use a COUNT.

SELECT count(*) AS number_of_rows FROM table;

You're right, you can't "count" on the ID being the number of rows when you are deleting them here and there. Second, if you want to loop through all of them try retrieving all the rows first, then using a loop in PHP to control the processing.

$sql = "SELECT id FROM table ORDER BY id DESC"; 
$rows = mysql_query($sql); 
if (mysql_num_rows($rows) > 0) { 
 while ($row = mysql_fetch_array($rows)) { 
  print $row['id'] . '<br />'; 
 } 
}

...or something along those lines...

[sjthomas]

I don't think that will work for what I want it to do. I've had a bit of a play around and I'm almost there. I've got it printing them all out except for the last one. I also realised I was posting out of date code as well. I've been playing about with it for a while, this is the full code (almost). I realise its not very elegant so any suggestions would be greately appreciated!

$query1 = "SELECT id FROM table ORDER BY id DESC LIMIT 1";
$result1 = mysql_query($query1) or die("Error: " . mysql_error());;
$row1 = mysql_fetch_array($result1);
$y = $row1["id"];
echo $y;
echo("<br /><br />");
echo "<ul><li><a href=index.php>HOME</a></li>";
for ($x=1; $x<$y; $x++) {
$query = "SELECT * FROM table where ID = $x and parentid = 0";  
 $result = mysql_query($query) or die("Error: " . mysql_error());
$row = mysql_fetch_array($result);
echo("<li><a href=viewcat.php?catid=" . $x . ">" . $row["name"] . "</a>");
$query = "SELECT * FROM table where parentid = $x";
$subResult = mysql_query($query) or die("Error: " . mysql_error());
if(mysql_fetch_array($subResult)!= "") {
echo("<ul>");
while($subRow = mysql_fetch_array($subResult)){
$z = $subRow["ID"];
  echo("<li><a href=viewcat.php?catid=" . $subRow["ID"] . ">" . $subRow["name"] . "</a></li>");
$query = "SELECT * FROM table where parentid = $z";
$subSubResult = mysql_query($query) or die("Error: " . mysql_error());if(mysql_fetch_array($subSubResult)!= "") {
echo("<ul>");
while($subSubRow = mysql_fetch_array($subSubResult)){
  echo("<li><a href=viewcat.php?catid=" . $subSubRow["ID"] . ">" . $subSubRow["name"] . "</a></li>");
} // END WHILE
echo("</ul>");
} // END IF
  } // END WHILE
echo("</ul>");
} // END IF
 echo("</li>");
} // END FOR!
echo "</ul>";

As you can see I'm very much a beginner finding his way by trying stuff out :D . I'm trying to get an automatically generated site map from sections and sub sections. Many thanks.

[sjthomas]

OK I've been playing around with it for a bit and I've solved one of the problems. It no longer print sout lots of blank list items because of the flags but I really don't understand why it snot printing out the last record (last as in bottom of the table not last numerically). Heres the code and it would be great if anyone could have a wuick look at it. I think it needs a fresh pair of eyes!

$query1 = "SELECT id FROM table ORDER BY id DESC LIMIT 1";
$result1 = mysql_query($query1) or die("Error: " . mysql_error());;
$row1 = mysql_fetch_array($result1);
$y = $row1["id"];
echo $y;
echo("<br /><br />");
echo "<ul><li><a href=index.php>HOME</a></li>";
for ($x=1; $x<=$y; $x++) {
$query = "SELECT * FROM table where ID = $x and parentid = 0";  
 $result = mysql_query($query) or die("Error: " . mysql_error());
$row = mysql_fetch_array($result);
$flag1 = "0";
$flag2 = "0";
$flag3 = "0";
if ($row['name']!= "" ) {
$flag1 = "1";
echo("<li><a href=viewcat.php?catid=" . $x . ">" . $row["name"] . "</a>");
}
$query = "SELECT * FROM table where parentid = $x";
$subResult = mysql_query($query) or die("Error: " . mysql_error());
if(mysql_fetch_array($subResult)!= "") {
echo("<ul>");
$flag2 = "1";
while($subRow = mysql_fetch_array($subResult)){
$z = $subRow["ID"];
  echo("<li><a href=viewcat.php?catid=" . $subRow["ID"] . ">" . $subRow["name"] . "</a></li>");
$query = "SELECT * FROM table where parentid = $z";
$subSubResult = mysql_query($query) or die("Error: " . mysql_error());
if(mysql_fetch_array($subSubResult)!= "") {
echo("<ul>");
$flag3 = "1";
while($subSubRow = mysql_fetch_array($subSubResult)){
  echo("<li><a href=viewcat.php?catid=" . $subSubRow["ID"] . ">" . $subSubRow["name"] . "</a></li>");
} // END WHILE
if ($flag2 == "1") { echo("</ul>"); }
} // END IF
  } // END WHILE
if ($flag1 == "1") { echo("</ul>"); }
} // END IF
 echo("</li>");
} // END FOR!
echo "</ul>";

Many thanks.