Problems with displaying results from a DB query

[erma]

Hi, I'm totally new at this so please excuse my ignorance! I've been reading around to try and fix this problem, but so far, no luck.

I am trying to create a catalog of web sites dealing with a particular group of topics. As part of this, I need to display the title, description, language, resource type, and url for each record in a certain category (or "topic" in the db). Tables are "resources" (containing title, description, and url) "lang" (containing languages) "Lang_dir" (an association table between "resources" and "languages") "Restype" (containing resource types) and "Restype_dir" (association table for "resources" and "restype").

Query is:
SELECT r.title, r.descr, r.url, y.lang, q.restype
FROM resources AS r LEFT JOIN topic_dir AS t USING (res_id) LEFT JOIN topic USING (topic_id) LEFT JOIN lang_dir AS x ON x.res_id=r.res_id LEFT JOIN lang AS y ON x.lang_id=y.lang_id LEFT JOIN restype_dir AS z ON z.res_id=r.res_id LEFT JOIN restype AS q ON z.restype_id = q.restype_id
WHERE t.topic_id = 36
ORDER BY r.title

I want my results to display like this:

Title: This is the first sample page
Description: Blah blah blah
Language: English
Resource Type: Academic Program, Research project
URL: http://whatever.com

Title: Sample page 2
Description: Blah blah blah
Language: Spanish
Resource Type: Research project
URL: http://whateveragain.com

Title: Web Page 3
Description: Blah blah blah
Language: Spanish
Resource Type: Personal web page, image library
URL: http://whateveroncemore.com

but I can't seem to make it work! Have tried all sorts of things. Here's the most recent go, with html formatting tags removed for brevity:

<?php

do {

if ($row['title'] != $title) {

echo $row['title'];
echo $row['descr'];
echo $row['lang'];
echo "{$restype1}";
echo $row['url'];
$restype = ''; // reset
}
$restype .= $row['restype'] . ', ';
$title = $row['title'];
$restype1 = substr($restype, 0,-2);
}

while ($row = mysql_fetch_assoc($query1));
?>

This does give me a nice table with all records returned, but the resource types are off by one. Using the previous example, my results look like this:

Title: This is the first sample page
Description: Blah blah blah
Language: English
Resource Type:
URL: http://whatever.com

Title: Sample page 2
Description: Blah blah blah
Language: Spanish
Resource Type: Academic Program, Research project URL: http://whateveragain.com

Title: Web Page 3
Description: Blah blah blah
Language: Spanish
Resource Type: Research project
URL: http://whateveroncemore.com

Can anyone tell me how to fix this???

Thanks and sorry for the length!

Erma

[onion2k]

<?php

do {

if ($row['title'] != $title) {
echo $row['title'];
echo $row['descr'];
echo $row['lang'];
echo "{$restype1}";
echo $row['url'];
$restype = ''; // reset
}
$restype .= $row['restype'] . ', ';
$title = $row['title'];
$restype1 = substr($restype, 0,-2);
}

while ($row = mysql_fetch_assoc($query1));
?>

Whats happening is that the code prints the first record coz $row['title'] != $title at the start as $title is empty. You then put $row['title'] into $title, so the next record that gets printed is the one where the title is different to the previous one. However, you actually want to print the $restype string BEFORE you print the details of the next row, not after.

I would rewrite the code as follows, it means you're doing a little less work (despite the fact theres more code), and it uses implode to make the comma seperated list of restypes, which is tidier (I reckon).

<?php

while ($row = mysql_fetch_assoc($query1)) {

if ($row['title'] != $title and $title != '') {
  echo $title;
  echo $descr;
  echo $lang;
  echo implode(",",$restypes);
  echo $url;
  unset($restypes);
}

$title = $row['title'];
$descr = $row['descr'];
$lang = $row['lang'];
$restypes[] = $row['restype'];
$url = $row['url'];

}

echo $title;
echo $descr;
echo $lang;
echo implode(",",$restypes);
echo $url;

?>

[erma]

Oh, fantastic!

That seems to have worked, except for one thing...for some reason, it's not displaying the first record in the set. Is there something I can do to fix this?

Thanks so much for your help!