SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE dateid = colname OR currencyrate.dateid = now() LIMIT 7 Code (markup): I need some help with the above SQL Statement. What I want is to display the exchange rate of today if the dateid=null (not mentioned) so when I pass on the /exchangerate.php?dateid=10032009 link it should show the exchange rate of 10-03-2009 date this is my page <?php require_once('Connections/cms.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { if (PHP_VERSION < 6) { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; } $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? doubleval($theValue) : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $maxRows_exchangerate = 10; $pageNum_exchangerate = 0; if (isset($_GET['pageNum_exchangerate'])) { $pageNum_exchangerate = $_GET['pageNum_exchangerate']; } $startRow_exchangerate = $pageNum_exchangerate * $maxRows_exchangerate; mysql_select_db($database_cms, $cms); $query_exchangerate = "SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) ORDER BY `date` DESC LIMIT 0, 7"; $query_limit_exchangerate = sprintf("%s LIMIT %d, %d", $query_exchangerate, $startRow_exchangerate, $maxRows_exchangerate); $exchangerate = mysql_query($query_limit_exchangerate, $cms) or die(mysql_error()); $row_exchangerate = mysql_fetch_assoc($exchangerate); if (isset($_GET['totalRows_exchangerate'])) { $totalRows_exchangerate = $_GET['totalRows_exchangerate']; } else { $all_exchangerate = mysql_query($query_exchangerate); $totalRows_exchangerate = mysql_num_rows($all_exchangerate); } $totalPages_exchangerate = ceil($totalRows_exchangerate/$maxRows_exchangerate)-1;$maxRows_exchangerate = 10; $pageNum_exchangerate = 0; if (isset($_GET['pageNum_exchangerate'])) { $pageNum_exchangerate = $_GET['pageNum_exchangerate']; } $startRow_exchangerate = $pageNum_exchangerate * $maxRows_exchangerate; $colname_exchangerate = "-1"; if (isset($_GET['date'])) { $colname_exchangerate = $_GET['date']; } mysql_select_db($database_cms, $cms); $query_exchangerate = sprintf("SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE dateid = %s OR currencyrate.dateid = now() LIMIT 7", GetSQLValueString($colname_exchangerate, "int")); $query_limit_exchangerate = sprintf("%s LIMIT %d, %d", $query_exchangerate, $startRow_exchangerate, $maxRows_exchangerate); $exchangerate = mysql_query($query_limit_exchangerate, $cms) or die(mysql_error()); $row_exchangerate = mysql_fetch_assoc($exchangerate); if (isset($_GET['totalRows_exchangerate'])) { $totalRows_exchangerate = $_GET['totalRows_exchangerate']; } else { $all_exchangerate = mysql_query($query_exchangerate); $totalRows_exchangerate = mysql_num_rows($all_exchangerate); } $totalPages_exchangerate = ceil($totalRows_exchangerate/$maxRows_exchangerate)-1; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Exchange Rate</title> <link rel="stylesheet" type="text/css" href="css/screen.css"/> </head> <body> <table border="0"> <tr> <td>date</td> <td>unit</td> <td>buying</td> <td>selling</td> <td>currencycode</td> <td>currencyname</td> </tr> <?php do { ?> <tr> <td><?php echo $row_exchangerate['date']; ?></td> <td><?php echo $row_exchangerate['unit']; ?></td> <td><?php echo $row_exchangerate['buying']; ?></td> <td><?php echo $row_exchangerate['selling']; ?></td> <td><?php echo $row_exchangerate['currencycode']; ?></td> <td><?php echo $row_exchangerate['currencyname']; ?></td> </tr> <?php } while ($row_exchangerate = mysql_fetch_assoc($exchangerate)); ?> </table> </body> </html> <?php mysql_free_result($exchangerate); ?> Code (markup): This is the structure of my Database -- Table: currencyrate -- DROP TABLE IF EXISTS `currencyrate`; CREATE TABLE `currencyrate` ( `currencyrateid` int AUTO_INCREMENT NOT NULL, `date` date NOT NULL, `dateid` int, `currencyid` int NOT NULL, `unit` int, `buying` decimal(10,2), `selling` decimal(10,2), /* Keys */ PRIMARY KEY (`currencyrateid`) ) ENGINE = MyISAM; ------------------------------------------ -- Table: currency -- DROP TABLE IF EXISTS `currency`; CREATE TABLE `currency` ( `currencyid` int AUTO_INCREMENT NOT NULL, `currencycode` varchar(3) NOT NULL, `currencyname` varchar(50) NOT NULL, /* Keys */ PRIMARY KEY (`currencyid`) ) ENGINE = MyISAM; CREATE UNIQUE INDEX `currencycode` ON `currency` (`currencycode`); Code (markup):
I would do similar to: if(isset($_GET['dateid'])){ mysql_select_db($database_cms, $cms); $query_exchangerate = sprintf("SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE dateid = %s LIMIT 7", GetSQLValueString($colname_exchangerate, "int")); $query_limit_exchangerate = sprintf("%s LIMIT %d, %d", $query_exchangerate, $startRow_exchangerate, $maxRows_exchangerate); $exchangerate = mysql_query($query_limit_exchangerate, $cms) or die(mysql_error()); $row_exchangerate = mysql_fetch_assoc($exchangerate); }else { $today = date(mdY); mysql_select_db($database_cms, $cms); $query_exchangerate = sprintf("SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE dateid = $today" , GetSQLValueString($colname_echangerate, "int")); $exchangerate = mysql_query($query_limit_echangerate, $cms) or die(mysql_error()); $row_exchangerate = mysql_fetch_assoc($exchangerate); } Code (markup): Might be buggy, if it is, let me know what the error is and I'll fix
Thank you for the suggestion, but I am looking for more SQL statement than php. @koko5 Yes I would like some help please so far I have got SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE (currencyrate.`date` = CURdate()) Code (markup): and there is a problem in CURdate() as well. I would like to convert that date into my local date.
$query="SELECT * FROM currencyrate INNER JOIN currency ON (currencyrate.currencyid=currency.currencyid) WHERE CASE WHEN currencyrate.`date` IS NULL THEN currencyrate.`date`=CURDATE() ELSE currencyrate.`date`='".mysql_real_escape_string($_GET['dateid'])."' END"; Code (markup): To prevent bugs you have to pass CURDATE() and mysql_real_escape_string($_GET['dateid']) to MySQL function DATE_FORMAT using exact-format parameters. Date and Time functions Example: ... WHERE CASE WHEN currencyrate.`date` IS NULL THEN currencyrate.`date`=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ELSE... Code (markup): You can use more statements in CASE clause and you can use CASE comparison in ORDER BY clause too for appropriate sorting. Regards