Hi Volker,
after studying your code (not so easy to find without formating and
lots of html), I think what you want to do is somthing like this:
$abfrage = 'SELECT A.id AS aid, A.autor AS aauthor, A.zeit,AS azeit'
.' A.betreff AS abetreff, A.inhalt AS ainhalt,'
.' U1.vorname AS autorv, U1.nachname AS autorn,'
.' K.autor AS kautor, K.zeit AS kzeit,
.' K.betreff AS kbetreff, K.inhalt AS kinhalt'
.' U2.vorname AS autorkv, U2.nachname AS autorkn,'
.' FROM ts_artikel AS A'
.' INNER JOIN ts_users AS U1 ON A.author=U1.id'
.' INNER JOIN ts_kommentar AS K ON A.id=K.artikel_id'
.' INNER JOIN ts_users AS U2 ON K.author=U2.id'
.' ORDER BY A.zeit, K.zeit DESC ' . $limit;
$resultat = mysql_query($abfrage) or die (mysql_error());
while ($row = mysql_fetch_assoc($resultat)) {
// take care if the article id is changing
echo 'whatever';
}
Ciao,
Lutz
[EMAIL PROTECTED] (Volker Hartmann) writes:
> Hi,
>
> I've got a problem with database abstraction. There are two tables. One with
> articles (id,content,author,time) and one with comments on those articles
> (id,article_id,content,author,time). My script looks like this:
>
> function show_news($limit){
> /* get db parameters and connect to db */
>
> require ("inc/db_parameter.inc.php");
> $connect = mysql_connect($dbhost,$dbuser,$dbpasswd) or die("Can't connect to
> database");
> mysql_select_db($dbname,$connect) or die(mysql_error());
>
> $abfrage = "SELECT id,autor,zeit,betreff,inhalt FROM ts_artikel ORDER BY
> zeit DESC" . $limit;
> $resultat = mysql_query($abfrage) or die (mysql_error());
>
> /* get articles and display*/
>
> While ($row = mysql_fetch_array($resultat, MYSQL_ASSOC)) {
>
> /* get author */
>
> $abfrage2 = "SELECT vorname,nachname FROM ts_users WHERE id='" .
> $row["autor"] ."'";
> $resultat2 = mysql_query($abfrage2) or die (mysql_error());
> list($autorv, $autorn) = mysql_fetch_row($resultat2);
>
> /* get comments */
>
> $abfrage3 = "SELECT autor,zeit,betreff,inhalt FROM ts_kommentar WHERE
> artikel_id='" . $row["id"] . "' ORDER BY zeit ASC";
> $resultat3 = mysql_query($abfrage3) or die (mysql_error());
>
> /* generate display */
>
> echo "<table width='100%' cellspacing='0' cellpadding='0' class='formtable'
> style='width:100%;margin:0'><tr><td class='artikelhead'
> style='text-align:left;'>" . htmlentities($autorv). " " .
> htmlentities($autorn);
> echo "</td><td class='artikelhead' style='text-align:right'>" . strftime("%A
> %d.%m.%Y,%H:%M:%S", $row["zeit"]);
> echo "</td></tr><tr><td colspan='2'><b>" . nl2br(chop($row["betreff"])) .
> ":</b><br />" . nl2br(chop($row["inhalt"])) . "</td></tr>";
> /* get comments of there are some */
> if (!mysql_num_rows($resultat3) < 1) {
> while ($row2 = mysql_fetch_array($resultat3)) {
> $abfrage4 = "SELECT vorname,nachname FROM ts_users WHERE id='" .
> $row2["autor"] . "'"; /*get author for comments */
> $resultat4 = mysql_query($abfrage4) or die (mysql_error());
> list($autorkv, $autorkn) = mysql_fetch_row($resultat4);
> echo "<tr><td class='commenthead' style='text-align:left'><img
> src='img/arrow.png' border='0' height='10' width='10'> " .
> htmlentities($autorkv) . " " . htmlentities($autorkn) . "</td><td
> class='commenthead' style='text-align:right'>" . strftime("%A
> %d.%m.%Y,%H:%M:%S", $row2["zeit"]);
> echo "</td></tr><tr><td colspan='2' class='commentcell'><b>" .
> nl2br(chop($row2["betreff"])) . ":</b><br />" . nl2br(($row2["inhalt"]));
> echo "</td></tr>";
> }
> }
> echo "<tr><td colspan='2' class='artikelcell' style='border-top:1px solid
> #808080'><a href='submit_comment.php?artikel=" . $row["id"] . "'>Kommentar
> schreiben</a></td></tr></table><br />";
> }
> }
>
> As you can see, there are some queries within while-loops.
>
> Is there a more elegant and performant way to do those queries? I thought of
> JOIN, but I cannot see how to do it.
>
> Any help would be appreciated
>
> TIA Volker
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
