Hi. I need to retrieve and list out the records from 'expenses' according to the date (eg. January 2011). I used substring to get the month (01 for January, 02 for February, etc.) and year. My date format is DD/MM/YYYY. I've tried echoing out $sql2 and the values obtained are correct but for some reason, the records are not listed out. Here's the code.
<?php
include("dbconn.php");
session_start();
if(isset($_SESSION['name']) && ($_SESSION['ic']))
{
$randomnumber = mt_rand(1,150);
function dropdown_empty()
{
echo"<script language='Javascript'>
alert('You must select both month and year.');
location.href='exp_month.php';
</script>";
}
if(isset($_POST['Submit']))
{
if(isset($_POST['select']) && !empty($_POST['select']) && isset($_POST['select2']) && !empty($_POST['select2']))
{
$month = $_POST['select'];
$year = $_POST['select2'];
//START: GENERATE RANDOM NUMBER
$sql= "SELECT MAX(exp_id) FROM expenses";
$query = mysql_query($sql) or die("Error: " . mysql_error());
$row = mysql_num_rows($query);
$data = mysql_fetch_assoc($query);
$maxid = $data['MAX(exp_id)'];
//echo $maxid;
mysql_query("UPDATE expenses SET price = '$randomnumber' WHERE exp_id='$maxid' AND price=0");
//END: GENERATE RANDOM NUMBER
if($month==01)
$month_name = 'January';
else if ($month==02)
$month_name = 'February';
else if ($month==03)
$month_name = 'March';
else if ($month==04)
$month_name = 'April';
else if ($month==05)
$month_name = 'May';
else if ($month==06)
$month_name = 'June';
else if ($month==07)
$month_name = 'July';
else if ($month==08)
$month_name = 'August';
else if ($month==09)
$month_name = 'September';
else if ($month==10)
$month_name = 'October';
else if ($month==11)
$month_name = 'November';
else
$month_name = 'December';
}
else
{
dropdown_empty();
}
}
?>
<!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=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body bgcolor="#CCCCCC">
<form id="form1" name="form1" method="post" action="Untitled-2.php">
<table width="830" border="0" align="center" bgcolor="#FFFFFF">
<tr>
<td><img src="../expensetracker/img/header.jpg" width="830" height="140" /></td>
</tr>
<tr bgcolor="#6699CC">
<td><div align="right"><a href="user_mainpage.php">Home</a> | <a href="logout0.php">Logout</a> </div></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><div align="center"> <?php echo $month_name; ?> <?php echo $year; ?></div></td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td><p><table width="736" border="1" align="center">
<tr>
<th width="80" scope="col"><div align="center" class="">
<h5>Date</h5>
</div></th>
<th width="80" scope="col"><div align="center" class="">
<h5>Item</h5>
</div></th>
<th width="80" scope="col"><div align="center" class="">
<h5>RM</h5>
</div></th>
</tr>
<?php
$ic = $_SESSION['ic'];
$sql_date = "SELECT date FROM expenses";
$query_date = mysql_query($sql_date) or die("Error: " . mysql_error());
$row_date = mysql_num_rows($query_date);
$data_date = mysql_fetch_assoc($query_date);
$substrMnth = $_POST['select'];
$substrYear = $_POST['select2'];
//$substrDate = $data_date['date'];
//$substrMnth = substr($substrDate, 3,-5);
//$substrYear = substr($substrDate, 6);
$sql2 = "SELECT date,item,price FROM expenses WHERE ic_no='$ic' AND SUBSTRING(date,3,-5) = '$substrMnth' AND SUBSTRING(date,6) = '$substrYear'";
$query2 = mysql_query($sql2) or die("Error: " . mysql_error());
$row2 = mysql_num_rows($query2);
echo $sql2;
if($row2 != 0){
while($data2 = mysql_fetch_assoc($query2)){
?>
<tr>
<td><div align="center"><?php echo $data2['date']; ?></div></td>
<td><div align="left"><?php echo $data2['item']; ?></div></td>
<td><div align="center"><?php echo $data2['price']; ?></div></td>
<?php
}
}
?>
</table> </td>
</tr>
<tr>
<td> </td>
</tr>
<tr bgcolor="#6699CC">
<td><div align="center">Copyright 2011. All Rights Reserved. </div></td>
</tr>
</table>
</form>
</body>
</html>
<?php
}
else{
header("Location: index.php");
}
?>I just read another recent thread about checking the SQL statements in phpMyAdmin. I did that and my SQL statements returned zero rows when I actually have one record in the database that satisfies the condition :(
Regards,
Atikah
