Hey
i have a mysql database and one of the fields is a date.
I want to have it set so if the date falls withing the last month then it shows it with a NEW graphic. If it's any other time it doesn't.
I read through all the stuff on php.net and nothing really helped me. I learn better from examples and couldnt really get what all it was saying.
So anyways this is what I had so far but it will only do the if statement.
<?PHP
$db = mysql_connect("localhost", "name", "pass");
mysql_select_db("db",$db);
$curMonth = date("m-d"); //get the current month
$time = mktime(0,0,0,$curMonth-1); //get a timestamp for the next month
$nextMonth = date("m-d",$time); //get the text representation of the next month
if ($result = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")) {
do {
printf("NEW - %s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result));
} elseif ($result2 = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<='$nextMonth')")) {
do {
printf("%s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result2));
}
?>
im sure thats all wrong but I cant really figure anything else out.
Thanks for any help!I don't have time to look into this at the moment as I am shooting off to work, but one question.
When you say "within the last month" are you referring to calendar month (i.e. within the month of June, for example, irrespective of what day of month it is when the query is executed), or lunar month (within the last 28 days)?
If no one else answers during the day I will take a look this evening when I return from work.Last month meaning the last 28-30 days. so considering the date today being the 19th, the last month being between now & June 19th.
ThanksI was never big on do..while loops. they seem to look funny to me.
so with that said, why not do 1 query and then see what the date is and echo whatever you like.
actually, you don't need elseif in there. make them seperate if statements so you get both sets. that way you have will only do one or the other but not both.
if ($result = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")) {
do {
printf("NEW - %s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result));
}
if ($result2 = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<='$nextMonth')")) {
do {
printf("%s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result2));
}well the database is a list of Roms. I want it to list all the Roms in alphabetical order and then the ones that fall into the date range have the 'NEW' be next to them.
They way you suggest, would list all the new ones and then all the others.well then you need 1 query, not 2.Taken directly from the MySQL site:Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
Note that the query also will select records with dates that lie in the future.Well thats not the problem.
The code I had setup works for getting only the dates from the last month.
"SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")
The problem is that it only shows those rows and I want it to show all of them. Then place 'NEW' next to the ones that fall within that date range.like I said, one query
if ($result = mysql_query("SELECT (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth') AS $NewRoms, * FROM nuke_rroms")) {
do {
if ($myrow['NewRoms']) {
printf("NEW - %s<br>\n", $myrow["rom"]);
} else {
printf("%s<br>\n", $myrow["rom"]);
}
} while ($myrow = mysql_fetch_array($result));
}
untested and I don't think it wil lwork but you should get the idea.thanks for the help... but I am still unable to get it working right.
I spent the past 3 hours trying to figure it out and reading through php.net with no luck.
It's not really that big of a deal but if anyone has some extra time and wouldnt mind helping me out it would be appreciated.
Thanks!how are you storing the date?YYYY-MM-DDthat way will mess you up.
the dash is the problem.
I never had good luck with the dash in dates. also it would be better to show it as a timestamp. personal opinion that is
it maybe that 02-01 is considered smaller than 01-20 because of the dash. it sees the last number and goes by that. if I rmemeber right that is hat happened to me when I was storing dates like that. let me check.
edit: nevermind, that wasn't it.
I guess you can do it this way.
if ($result = mysql_query("SELECT (RIGHT(romadddate,5)>='$nextMonth') AS NewRoms, * FROM nuke_rroms")) {
do {
if ($myrow['NewRoms'] <= date("Y-m-d")) {
printf("NEW - %s<br>\n", $myrow["rom"]);
} else {
printf("%s<br>\n", $myrow["rom"]);
}
} while ($myrow = mysql_fetch_array($result));
}OK, I just played around with that for a while now too, and it just doesnt seem to want to work.
if I use 2 different queries it works, but when I put it into one I either get an error or nothing shows up at all.then use the two different queries.well thats what im doing right now, but like I said before when I do it like that it lists all the new ones first and I would prefer to have them mixed in so it lists all of them in order.
But it doesnt seem to be working that way, so oh well.
Thanks!if you have two and they are working correctly, well besides out of order. then just combine. but you tried it and it didn't work so I don't know what to tell you. I gave you the code I thought would work but I can't run it as I don't have the structure or the code. there has to be a way to make it as one if you have it working as two.I agree, but since I can't get it to work I'm just gonna stick with this.
Thanks for all the help!!After a bunch of reading, thinking, and testing I have finally figured out how to do this. I am using this in a module I created in PHPNuke, and haven't been able to get it to work by itself but this is basically what it looks like in the phpnuke page.
function full() {
global $module_name, $sys, $letter, $func, $lastMonth;
include("header.php");
OpenTable();
$sql = "SELECT * FROM nuke_rroms, nuke_rsystems WHERE nuke_rroms.sysid = nuke_rsystems.sysid AND rom LIKE '$letter%' ORDER BY rom";
echo "<center><table border='1' width='90%'><tr align=center><td>Game Name</td><td width=100>System</td><td width=110>Zip File Contains</td><td width=80>Download Link</td></tr>";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
$romid = $row[romid];
$sysid = $row[sysid];
$rom = $row[rom];
$romcontains = $row[romcontains];
$romfile = $row[romfile];
$adddate = $row[adddate];
$sys1 = $row[sys];
if ($adddate >= $lastMonth) {
echo"<tr><td><img src=http://www.htmlforums.com/archive/index.php/\"modules/$module_name/images/new_1.gif\" alt=\"New\">$rom</td><td>$sys1</td><td>$romcontains</td><td align=center><a href=http://www.htmlforums.com/archive/index.php/\"files/roms/$sys/$romfile\">Download </a></td>";
} else {
echo"<tr><td>$rom</td><td>$sys1</td><td>$romcontains</td><td align=center><a href=http://www.htmlforums.com/archive/index.php/\"files/roms/$sys/$romfile\">Download </a></td>";
}
}
echo"</tr></table></center>";
CloseTable();
include("footer.php");
}
maybe this while be of some help to someone else.
Thanks!
i have a mysql database and one of the fields is a date.
I want to have it set so if the date falls withing the last month then it shows it with a NEW graphic. If it's any other time it doesn't.
I read through all the stuff on php.net and nothing really helped me. I learn better from examples and couldnt really get what all it was saying.
So anyways this is what I had so far but it will only do the if statement.
<?PHP
$db = mysql_connect("localhost", "name", "pass");
mysql_select_db("db",$db);
$curMonth = date("m-d"); //get the current month
$time = mktime(0,0,0,$curMonth-1); //get a timestamp for the next month
$nextMonth = date("m-d",$time); //get the text representation of the next month
if ($result = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")) {
do {
printf("NEW - %s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result));
} elseif ($result2 = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<='$nextMonth')")) {
do {
printf("%s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result2));
}
?>
im sure thats all wrong but I cant really figure anything else out.
Thanks for any help!I don't have time to look into this at the moment as I am shooting off to work, but one question.
When you say "within the last month" are you referring to calendar month (i.e. within the month of June, for example, irrespective of what day of month it is when the query is executed), or lunar month (within the last 28 days)?
If no one else answers during the day I will take a look this evening when I return from work.Last month meaning the last 28-30 days. so considering the date today being the 19th, the last month being between now & June 19th.
ThanksI was never big on do..while loops. they seem to look funny to me.
so with that said, why not do 1 query and then see what the date is and echo whatever you like.
actually, you don't need elseif in there. make them seperate if statements so you get both sets. that way you have will only do one or the other but not both.
if ($result = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")) {
do {
printf("NEW - %s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result));
}
if ($result2 = mysql_query("SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<='$nextMonth')")) {
do {
printf("%s<br>\n", $myrow["rom"]);
} while ($myrow = mysql_fetch_array($result2));
}well the database is a list of Roms. I want it to list all the Roms in alphabetical order and then the ones that fall into the date range have the 'NEW' be next to them.
They way you suggest, would list all the new ones and then all the others.well then you need 1 query, not 2.Taken directly from the MySQL site:Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
Note that the query also will select records with dates that lie in the future.Well thats not the problem.
The code I had setup works for getting only the dates from the last month.
"SELECT * FROM nuke_rroms WHERE (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth')")
The problem is that it only shows those rows and I want it to show all of them. Then place 'NEW' next to the ones that fall within that date range.like I said, one query
if ($result = mysql_query("SELECT (RIGHT(romadddate,5)<=RIGHT(CURDATE(),5)) AND (RIGHT(romadddate,5)>='$nextMonth') AS $NewRoms, * FROM nuke_rroms")) {
do {
if ($myrow['NewRoms']) {
printf("NEW - %s<br>\n", $myrow["rom"]);
} else {
printf("%s<br>\n", $myrow["rom"]);
}
} while ($myrow = mysql_fetch_array($result));
}
untested and I don't think it wil lwork but you should get the idea.thanks for the help... but I am still unable to get it working right.
I spent the past 3 hours trying to figure it out and reading through php.net with no luck.
It's not really that big of a deal but if anyone has some extra time and wouldnt mind helping me out it would be appreciated.
Thanks!how are you storing the date?YYYY-MM-DDthat way will mess you up.
the dash is the problem.
I never had good luck with the dash in dates. also it would be better to show it as a timestamp. personal opinion that is
it maybe that 02-01 is considered smaller than 01-20 because of the dash. it sees the last number and goes by that. if I rmemeber right that is hat happened to me when I was storing dates like that. let me check.
edit: nevermind, that wasn't it.
I guess you can do it this way.
if ($result = mysql_query("SELECT (RIGHT(romadddate,5)>='$nextMonth') AS NewRoms, * FROM nuke_rroms")) {
do {
if ($myrow['NewRoms'] <= date("Y-m-d")) {
printf("NEW - %s<br>\n", $myrow["rom"]);
} else {
printf("%s<br>\n", $myrow["rom"]);
}
} while ($myrow = mysql_fetch_array($result));
}OK, I just played around with that for a while now too, and it just doesnt seem to want to work.
if I use 2 different queries it works, but when I put it into one I either get an error or nothing shows up at all.then use the two different queries.well thats what im doing right now, but like I said before when I do it like that it lists all the new ones first and I would prefer to have them mixed in so it lists all of them in order.
But it doesnt seem to be working that way, so oh well.
Thanks!if you have two and they are working correctly, well besides out of order. then just combine. but you tried it and it didn't work so I don't know what to tell you. I gave you the code I thought would work but I can't run it as I don't have the structure or the code. there has to be a way to make it as one if you have it working as two.I agree, but since I can't get it to work I'm just gonna stick with this.
Thanks for all the help!!After a bunch of reading, thinking, and testing I have finally figured out how to do this. I am using this in a module I created in PHPNuke, and haven't been able to get it to work by itself but this is basically what it looks like in the phpnuke page.
function full() {
global $module_name, $sys, $letter, $func, $lastMonth;
include("header.php");
OpenTable();
$sql = "SELECT * FROM nuke_rroms, nuke_rsystems WHERE nuke_rroms.sysid = nuke_rsystems.sysid AND rom LIKE '$letter%' ORDER BY rom";
echo "<center><table border='1' width='90%'><tr align=center><td>Game Name</td><td width=100>System</td><td width=110>Zip File Contains</td><td width=80>Download Link</td></tr>";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
$romid = $row[romid];
$sysid = $row[sysid];
$rom = $row[rom];
$romcontains = $row[romcontains];
$romfile = $row[romfile];
$adddate = $row[adddate];
$sys1 = $row[sys];
if ($adddate >= $lastMonth) {
echo"<tr><td><img src=http://www.htmlforums.com/archive/index.php/\"modules/$module_name/images/new_1.gif\" alt=\"New\">$rom</td><td>$sys1</td><td>$romcontains</td><td align=center><a href=http://www.htmlforums.com/archive/index.php/\"files/roms/$sys/$romfile\">Download </a></td>";
} else {
echo"<tr><td>$rom</td><td>$sys1</td><td>$romcontains</td><td align=center><a href=http://www.htmlforums.com/archive/index.php/\"files/roms/$sys/$romfile\">Download </a></td>";
}
}
echo"</tr></table></center>";
CloseTable();
include("footer.php");
}
maybe this while be of some help to someone else.
Thanks!