Re: shorten SELECT query

[Peter Brawley]

Do you need something like ...

SELECT tour.id, tour.showid, tour.date, tour.status,
                 stat.id, stat.statusmesg
FROM tour INNER JOIN stat USING (id)
WHERE tour.date>$daysInMonths
     AND tour.date<$enddate
     AND tour.ontheroad=0
ORDER BY tour.date ASC;

?

PB
  ----- Original Message -----
  From: [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Friday, March 12, 2004 9:31 AM
  Subject: shorten SELECT query


  Hi,

  I have multiple queries nested inside each other and was wondering, if
  there is a way to shorten this thing, e.g. run it within a single query
  with subqueries? I couldn't find anything in the subquery section of the
  docs or maybe I just don't know how to put it.

  At the moment my code fragment looks like this:

  $query = "SELECT id, showid, date, status FROM tour WHERE
  date>$daysInMonths AND date<$enddate AND ontheroad=0 ORDER BY date ASC";
  $result = mysql_query($query);

  if ($num = mysql_num_rows($result)) {

      echo("<table width=\"535\" class=\"copy\" border=\"0\">\n");

      for($i=0;$i<$num;$i++) {

          //$id = mysql_result($result,$i,"id");
          $showid = mysql_result($result,$i,"showid");
          $date = mysql_result($result,$i,"date");
          $stat = mysql_result($result,$i,"status");


          $query3 = "SELECT id, statusmsg FROM stat WHERE id=$stat";
          $result3 = mysql_query($query3);

          $statusmsg = mysql_result($result3,0,"statusmsg");


          if ($stat != 1) {

              $statusmessage = "<span class=\"alert\">Hinweis: " .
  $statusmsg . "</span>";

          }

          $showdaynum = strftime('%u', $date);
          $showdate = strftime('%d.%m.%Y', $date);
          $showtime = strftime('%H:%M', $date);

          $query2 = "SELECT id, darsteller, showname, pseudo FROM show
  WHERE id=$showid";
          $result2 = mysql_query($query2);

          if ($num2 = mysql_num_rows($result2)) {

              $id = mysql_result($result2,0,"id");
              $darsteller = mysql_result($result2,0,"darsteller");
              $showname = mysql_result($result2,0,"showname");
              $pseudo = mysql_result($result2,0,"pseudo");

          }
      }
  }


  Any ideas to clean this up would be appreciated.

  Thanks,
  Holger

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]