select last fifty starting from max

michaelk

hi,

i'm trying to figure out the correct way to select the last fifty records inputted into the database, under a certain condition...

SELECT id, DATE_FORMAT(date_approved, '%e.%c') AS date_add,
type, band, movie, publication, headline_tour, game, 
random_name FROM $table1 WHERE approved = '1' 
ORDER BY $sort_recent LIMIT 0,50"

I've got it down to fifty entries. but not the ones i want. i want the last 50 id's inputted where approved = 1.. but some how i can't do the SQL / math to get it to work. i always just get the die error. any ideas ?

bogu

I dont know exactly what your problem is because I dont see where is your die function.
First ORDER BY it comes with type of sorting ASC or DESC and I dont know what your var $sort_recent it return.
Die function could be at your connection sql and from what you are telling me I cant see the problem.
Only the ORDER BY problem I see at the moment.

bogu

If you whant to make a selection by a count() function then create an alias for that function and then order by that alias:

SELECT count($field) AS alias FROM $table GROUP BY $field ORDER BY alias

Sxooter

The usual way to get the last x entries from a certain ordering is to "turn it around" with desc.

select * from table order by somefield desc limit 50;

Note that limit isn't ANSI SQL yet, so the ANSI SQL way would techincally involve a cursor in a transaction. Since most people use mysql, and it doesn't do cursors, it's a moot point, plus it's really no faster unless you're going to hold the connection open and page through your data.

michaelk

okay. i'm half way there.
some more background first:

i have a 'reviews' table of albums, movies, video games, shows, random things, and publications.... i want to select the 50 newest submitted reviews that have been 'approved' by the admin hence WHERE approved = 1..

i also want two options in this. one. to sort by date.. (or in this case, id number... since the bigger id's are the newest...).. and by name... i can get it to sort by id DESC no problem. but, when i try to sort by the 'title' columns, it does it sequentially, instead of ordering them all together.... like it alphabetically sorts bands, then movies, then shows... etc. instead of sorting them alphabetically as a compiled list.

is there a way to consider six columns as one list ? to be ordered alphabetically ?

here's the code thus far.....


if (!isset($sort)) {
  $sort = 0;
}

if($sort == 0) {
  $sort_recent = "id DESC";
} else {
  $sort_recent = "movie, publication, headline_tour, game, random_name, band";
}

$sqlmax = "SELECT id FROM $table1 
    WHERE approved = 1 ORDER BY $sort_recent LIMIT 50";

$resmax = mysql_query($sqlmax,$connection)
  or die("couldn't select recent rev_id's");

while($rowmax = mysql_fetch_array($resmax)) {

  $recent_id = $rowmax['id'];

  $sqlnew = "SELECT id, DATE_FORMAT(date_approved, '%c.%e') AS date_add, type, 
    band, movie, publication, headline_tour, game, random_name
    FROM $table1 WHERE id = \"$recent_id\"";

  $resnew = mysql_query($sqlnew,$connection)
    or die("couldn't get newest reviews");

  while($rownew = mysql_fetch_array($resnew)) {
    $id = $rownew['id'];
    $type = $rownew['type'];
    $date_add = $rownew['date_add'];
    $band = $rownew['band'];
    $band = stripslashes($band);
    $movie = $rownew['movie'];
    $movie = stripslashes($movie);
    $headline_tour = $rownew['headline_tour'];
    $headline_tour = stripslashes($headline_tour);
    $publication = $rownew['publication'];
    $publication = stripslashes($publication);
    $game = $rownew['game'];
    $game = stripslashes($game);
    $random_name = $rownew['random_name'];
    $random_name = stripslashes($random_name);

if($type == 1) { 

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\"> 
  <IMG SRC=\"images/music/mus_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $band</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $date_add</FONT></TD>
  </TR>";

} elseif($type == 2) {

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\"> 
      <IMG SRC=\"images/movies/mov_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $movie</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $date_add</FONT></TD>
  </TR>";        

} elseif($type == 3) {

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\"> 
  <IMG SRC=\"images/pubs/pub_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">$publication</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">$date_add</FONT></TD>
  </TR>";  

} elseif($type == 4) {

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\">
      <IMG SRC=\"images/shows/show_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $headline_tour</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $date_add</FONT></TD>
  </TR>";

} elseif($type == 5) {

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\">
      <IMG SRC=\"images/games/game_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $game</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $date_add</FONT></TD>
  </TR>";

} else {

$recent_list .= "
  <TR>
    <TD WIDTH=20 VALIGN=\"TOP\">
      <IMG SRC=\"images/random/rand_icon.gif\" WIDTH=10 HEIGHT=10></TD>
    <TD WIDTH=130 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $random_name</FONT></TD>
    <TD WIDTH=30 VALIGN=\"TOP\">
    <FONT SIZE=2 FACE=\"Arial, Helvetica, sans-serif\">
      $date_add</FONT></TD>
  </TR>";

}

  }

}

Sxooter

Can you rewrite it so that the two queries the resmax and sqlnew are one query? Like:

SELECT 
id, 
DATE_FORMAT(date_approved, '%c.%e') AS date_add, 
type, 
band, 
movie, 
publication, 
headline_tour, 
game, 
random_name
FROM $table1
ORDER BY id desc
LIMIT 50

Or does that miss something else you're doing?