Re: [PHP-DB] newbie MySQL question
On Sat, 19 May 2001, Matt Nigh wrote: > hi, i'm building a website right now with a shows page on it which will > scroll horizontally in a window. i have each show listed in a table called > shows (db is called almavale_board) with the following fields: > > id - bandplaying - venue - date - time - cover - description you could put the band info in another table and reference them by bandid or something. this would make it easier to expand, like if you ever wanted to collect stats on each band (like members, genre, etc.). i only mention this because i did a calendar of events page a while back and eventually went to such a format. it is much easier this way because each event was only listed in the database once. just a thought... > what i need to do is have the shows listed by date, having the latest date > listed first. here's the code i have presently: > > [ code snip ] > > oh and the date is in this format : Month-Day-,Year (ex. May 19, 2001) > > if anyone can help me out on this at all (or has any questions about my > problem), please reply to this post, i'd be very appreciative. how is the date column defined in mysql... is it a DATE type? ... if so the date would look something like 2001-05-19 in mysql, and you could use something like: SELECT DATE_FORMAT(date, '%M %d, %Y'), ... FROM shows ORDER BY date DESC and mysql would sort and format the date as necessary. like this: mysql> select DATE_FORMAT(date, '%M %d, %Y') from dates order by date desc; +--+ | DATE_FORMAT(date, '%M %d, %Y') | +--+ | March 31, 2001 | | March 29, 2001 | | March 28, 2001 | | March 28, 2001 | | March 27, 2001 | | March 24, 2001 | ... hope this helps... :) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] newbie MySQL question
this would work, and does for the day, but for the month it doesn't. also, is there code to have: $result = mysql_query("select * from shows ORDER BY date DESC"); (or whatever code works) and $result = mysql_query("select * from shows where id >= $show order by id limit 8"); combined together to make it so that there's only 8 rows per page and yet it displays the date (ex, May 19, 2001) in descending format ? Also, is there a way to have the PHP figure out when to go the next page after 8 rows, or to not put a link if there's 7 or less rows? like http://www.thesite.com/shows/main.php?show=1 when it has 8 rows, it displays a link to http://www.thesite.com/shows/main.php?show=12, but when there's 7 or less rows, it displays no link? There's probably some if/else statement to run, but I have no idea what it would be. If I sound confusing, I'm really sorry, I'm pretty bad at explaining things well, so ask if you don't understand what i mean by something. Thanks again, Matt > > $result = mysql_query("select * from shows"); > Change to > > $result = mysql_query("select * from shows ORDER BY date DESC"); > > > On Saturday 19 May 2001 21:45, Matt Nigh wrote: > > hi, i'm building a website right now with a shows page on it which will > > scroll horizontally in a window. i have each show listed in a table called > > shows (db is called almavale_board) with the following fields: > > > > id - bandplaying - venue - date - time - cover - description > > > > > > what i need to do is have the shows listed by date, having the latest date > > listed first. here's the code i have presently: > > > > > > > > > $link = mysql_connect("localhost","almavale","violet"); > > if(!$link) die ("Could not connect to MySQL!"); > > mysql_select_db("almavale_board",$link); > > $result = mysql_query("select * from shows"); > > $num = mysql_numrows($result); > > for($x=0;$x<$num;$x++) { > > $row = mysql_fetch_object($result); > > print " > cellspacing=4 border=0> > size=1> > > $row->bandplaying$row->venue$row->date$row->time$row > >- > > > > >cover$row->description > > > > > > "; > > } > > mysql_close($link); > > ?> > > > > > > > > > > oh and the date is in this format : Month-Day-,Year (ex. May 19, 2001) > > > > if anyone can help me out on this at all (or has any questions about my > > problem), please reply to this post, i'd be very appreciative. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] newbie MySQL question
> $result = mysql_query("select * from shows"); Change to > $result = mysql_query("select * from shows ORDER BY date DESC"); On Saturday 19 May 2001 21:45, Matt Nigh wrote: > hi, i'm building a website right now with a shows page on it which will > scroll horizontally in a window. i have each show listed in a table called > shows (db is called almavale_board) with the following fields: > > id - bandplaying - venue - date - time - cover - description > > > what i need to do is have the shows listed by date, having the latest date > listed first. here's the code i have presently: > > > > $link = mysql_connect("localhost","almavale","violet"); > if(!$link) die ("Could not connect to MySQL!"); > mysql_select_db("almavale_board",$link); > $result = mysql_query("select * from shows"); > $num = mysql_numrows($result); > for($x=0;$x<$num;$x++) { > $row = mysql_fetch_object($result); > print " cellspacing=4 border=0> size=1> > $row->bandplaying$row->venue$row->date$row->time$row >- > > >cover$row->description > > > "; > } > mysql_close($link); > ?> > > > > > oh and the date is in this format : Month-Day-,Year (ex. May 19, 2001) > > if anyone can help me out on this at all (or has any questions about my > problem), please reply to this post, i'd be very appreciative. > > > > > Thanks, > > Matt -- Paul Campbell http://www.cmm.uklinux.net/ mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] newbie MySQL question
hi, i'm building a website right now with a shows page on it which will scroll horizontally in a window. i have each show listed in a table called shows (db is called almavale_board) with the following fields: id - bandplaying - venue - date - time - cover - description what i need to do is have the shows listed by date, having the latest date listed first. here's the code i have presently: $row->bandplaying$row->venue$row->date$row->time$row- >cover$row->description "; } mysql_close($link); ?> oh and the date is in this format : Month-Day-,Year (ex. May 19, 2001) if anyone can help me out on this at all (or has any questions about my problem), please reply to this post, i'd be very appreciative. Thanks, Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]