Help PULLING DATA FOR SPECIFIC DATE

[uswege]

hello guys, i have a table  called 'exchange' with the following fields [id,Currency,Buying,Selling,date]

 

I want to create an archive page using a drop down date, ie three boxes, one fo date, another for month and the last one for year, when a user specifies the date/month/year he will click 'go' then be taken to the exchange rates page of the date he chooses.

 

I have the script to put data into the database, it is working fine, but i can't make one to pull data from the database in a way i want it to.

 

The current code i have just pulls the whole data. I want to get something similar to http://www.nbctz.com/exchange-rates

 

The code i have now is

 

THIS BEFORE THE OPENING HTML TAG

<?

php include 'connection_file.php';

?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  $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_exc = 100;

$pageNum_exc = 0;

if (isset($_GET['pageNum_exc'])) {

  $pageNum_exc = $_GET['pageNum_exc'];

}

$startRow_exc = $pageNum_exc * $maxRows_exc;

 

mysql_select_db($database_pmo, $pmo);

$query_exc = "SELECT * FROM exchange order by `date` desc";

$query_limit_exc = sprintf("%s LIMIT %d, %d", $query_exc, $startRow_exc, $maxRows_exc);

$exc = mysql_query($query_limit_exc, $pmo) or die(mysql_error());

$row_exc = mysql_fetch_assoc($exc);

 

if (isset($_GET['totalRows_exc'])) {

  $totalRows_exc = $_GET['totalRows_exc'];

} else {

  $all_exc = mysql_query($query_exc);

  $totalRows_exc = mysql_num_rows($all_exc);

}

$totalPages_exc = ceil($totalRows_exc/$maxRows_exc)-1;

?>

 

 

 

 

THEN THIS IN THE BODY SECTION

              <?php do { ?>

              <tr>

                <td height="17" align="center" bgcolor="#EAEAEA"><?php echo $row_exc ['Currency']; ?></td>

                <td height="17" align="center" bgcolor="#EAEAEA"><?php echo $row_exc ['Buying']; ?></td>

                <td height="17" align="center" bgcolor="#EAEAEA"><?php echo $row_exc ['Selling']; ?></td>

              </tr>

              <?php } while ($row_exc = mysql_fetch_assoc($exc)); ?>

 

will appreciate your help

[uswege]

hello guys,

 

PM me if you can help me get this work, will give out some dollars for this!

[the182guy]

Is the date stored as a MySQL DATE type?

 

// user has submitted 3 dropdown menus for the day, month, year

$day = $_POST['day'];
$month = $_POST['month'];
$year = $_POST['year'];

// add validation and clean the input for SQL

$fulldate = $year . '-' . $month . '-' . $day; // $fulldate now looks like  2010-04-25

// find all records where the date matches the inputted date
$sql = "SELECT * FROM exchange WHERE `date` = '$fulldate'";

 

Is that what you're looking for?

[uswege]

hello the182guy,

 

the date field in mysql is of DATETIME type and Yes date is stored in MySQL format

 

will try the codes you've given me and post the output right here.. . . just a moment!

 

Thanx for reply!

 

 

[uswege]

hello the182guy,

 

the page i am working on is http://www.azaniabank.co.tz/currency.php i am trying to get something similar to http://www.nbctz.com/exchange-rates (look the last part of the page where user can choose date and month then click go to retrieve forex rates of the selected date)

 

I have put the code you've given just below the code  i already have (of course in a separate div), but it is blank.

 

any idea?

[the182guy]

hello the182guy,

 

the date field in mysql is of DATETIME type and Yes date is stored in MySQL format

 

will try the codes you've given me and post the output right here.. . . just a moment!

 

Thanx for reply!

 

The code in my first post will only work on a date field, not datetime. Below is the modified code for a datetime field

 

// user has submitted 3 dropdown menus for the day, month, year

$day = $_POST['day'];
$month = $_POST['month'];
$year = $_POST['year'];

// add validation and clean the input for SQL

$fulldate = $year . '-' . $month . '-' . $day; // $fulldate now looks like  2010-04-25

// find all records where the date matches the inputted date
$sql = "SELECT * FROM exchange WHERE DATE_FORMAT(`date`, '%Y-%m-%d) = '$fulldate'";

[uswege]

just changed it to the new code you''ve given, i am still getting a blank page, what might i getting wrong?

 

just read read somewhere that 'date' is a reserved field name for mysql, can it be a reason?

[the182guy]

just changed it to the new code you''ve given, i am still getting a blank page, what might i getting wrong?

 

just read read somewhere that 'date' is a reserved field name for mysql, can it be a reason?

 

The snippet I provided doesn't execute the query, it was just to show how to filter the results based on a date. You need to change your SQL statement in your code so that it includes the WHERE part that I supplied.

 

Or if you want this bit of code to be seperate then just add a mysql_query($sql) to it, and loop through the results.

[uswege]

i saw that it won't  but thought it'll at least bring the drop down dates :-), alright let me try to get other mysql query and see how it works

[uswege]

think am almost there!

 

here is my complete code

 

<?php include('Connections/pmo.php'); 
   // user has submitted 3 dropdown menus for the day, month, year

      $day = $_POST['day'];
      $month = $_POST['month'];
      $year = $_POST['year'];

     // add validation and clean the input for SQL
  
     $fulldate = $year . '-' . $month . '-' . $day; // $fulldate now looks like  2010-04-25


      // find all records where the date matches the inputted date
     $sql = "SELECT * FROM exchange WHERE DATE_FORMAT(`date`, '%Y-%m-%d) = '$fulldate'"; 
     $result=mysql_query($quey1) or die(mysql_error());
      ?>
     <style type="text/css">
      <!--
       .tdmido {
border: 1px none #000066;
text-align: center;
line-height: 22px;
background-color: #FFFFFF;
       }
        -->
      </style>

  <table width="100%" align="center" border="1" bordercolor="#BDD7FC" cellpadding="1" cellspacing="0" style="background-color:#FFFFFF; "class="tdmido" >
<tr class="tdmido">

<th>Currency</th>

<th>Buying</th>

<th>Selling</th>

</tr>

<?php
while($row=mysql_fetch_array($result)){
echo "</td><td>";
echo $row['CurrencyName'];
echo "</td><td>";
echo $row['Buying'];
echo "</td><td>";
echo $row['Selling'];
echo "</td></tr>";
}
echo "</table>";

while($row=mysql_fetch_array($result))
echo $row['Update'];

?>

</style>

 

Its says 'Query was empty' which i understand is not an error but a message that the request data is not there? but was this not suppossed to bring a drop down dates instead?

[the182guy]

The problem is you're trying to execute $query1 but the actual query is called $sql

[ignace]

The problem is you're trying to execute $query1 but the actual query is called $sql

 

That and a missing ' in the query

[the182guy]

That and a missing ' in the query

 

Well spotted. Should be

 

     $sql = "SELECT * FROM exchange WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$fulldate'"; 

[uswege]

thanx  the182guy and ignace, i've been out for nearly 40 hrs but hope someone will help

 

I have corrected the missing ' and changed $query1 to $sql, then it displayed, in boxes heading Currency, Buying and Selling, then realised that i was echo'ing them, so i further made some changes, the full code now is, after changes am getting blank page

 

<?php include('connect_file.php'); 
   // user has submitted 3 dropdown menus for the day, month, year

      $day = $_POST['day'];
      $month = $_POST['month'];
      $year = $_POST['year'];

     // add validation and clean the input for SQL
  
     $fulldate = $year . '-' . $month . '-' . $day; // $fulldate now looks like  2010-04-25


      // find all records where the date matches the inputted date
     $sql = "SELECT * FROM exchange WHERE DATE_FORMAT(`date`, '%Y-%m-%d') = '$fulldate'";
     $result=mysql_query($sql) or die(mysql_error());
      ?>
     <style type="text/css">
      <!--
       .tdmido {



border: 1px none #000066;



text-align: center;



line-height: 22px;



background-color: #FFFFFF;
       }
        -->
      </style>

<?php
while($row=mysql_fetch_array($result)){
echo "</td><td>";
echo $row['Currency'];
echo "</td><td>";
echo $row['Buying'];
echo "</td><td>";
echo $row['Selling'];
echo "</td></tr>";
}
echo "</table>";

while($row=mysql_fetch_array($result))
echo $row['Update'];

?>

</style>