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>&nbsp;</td>
    </tr>
    <tr>
      <td><div align="center"> <?php echo $month_name; ?> <?php echo $year; ?></div></td>
    </tr>
    <tr>
      <td>&nbsp;</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>&nbsp;</td>
    </tr>
    <tr>
      <td>&nbsp;</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

Member Avatar for diafol

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

With respect, if it returns 0 rows, then it appears that your SQL does not satisfy the condition where a single row should be returned. Use backticks - I think DATE is a reserved word.

SELECT `date`,`item`,`price` FROM `expenses` WHERE `ic_no`='$ic' AND SUBSTRING(`date`,3,-5) = '$substrMnth' AND SUBSTRING(`date`,6) = '$substrYear'"

I apologize if I said earlier didn't make any sense. Anyhow, I tried using backticks and it still doesn't work. Since you mentioned DATE being a reserved word, I renamed the attribute 'date1' and there is still no output. 'date1' having varchar as type couldn't have caused the problem, could it?

Regards,
Atikah

Member Avatar for diafol

echo the SQL statement and print it out here:

echo "SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='$ic' AND SUBSTRING(`date1`,3,-5) = '$substrMnth' AND SUBSTRING(`date1`,6) = '$substrYear'";

BTW - you'd probably be better extracting the data in php and creating a date in the DB format in order to dispense with teh mysql SUBSTRINGs. You can use the BETWEEN ... AND ... syntax to get all dates between two limits.

This looks needlessly complicated.

SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='900314106355' AND SUBSTRING(`date1`,3,-5) = '01' AND SUBSTRING(`date1`,6) = '2010'

when I select 'January' and '2010' from the drop down menus.

I also tried to change the code a bit along with the date format.

$sql2 = "SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='$ic' AND  `date1` BETWEEN '2010-01-01' AND '2010-01-31'";
	$query2 = mysql_query($sql2) or die("Error: " . mysql_error());
    $row2 = mysql_num_rows($query2);
	$data11 = mysql_fetch_assoc($query2);
	$substrDate = $data11['date1'];
	echo $sql2;
	echo $substrDate;

--

SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='900314106355' AND `date1` BETWEEN '2010-01-01' AND '2010-01-31'2010-01-11

That was what I got. The last bit 2010-01-11 is the specific date I have in my database for one of the records. Purposely selected 'January 2010'. Regarding the date format, I didn't consider using yyyy/mm/dd because we never use that format here, so I was wondering if it'd be wise to opt for that. Or is there any way I can change the default format to dd/mm/yyyy? I'm using DBManager Professional.

If I may ask, what are the possible causes of data not output, although it can retrieve from the database? Or in my case it's not retrieved yet? :(

Member Avatar for diafol

The date format you should employ is yyyy-mm-dd as you can use > or < with it.

For retrieving all records for a particular month:

Say you want month 04 (April) and year 2011:

//if $month = '04' (string) or $month = 4 (int)

if($month == '12'){
  $month2 = '01'
  $year2 = $year + 1;
}else{
  $month2 = str_pad(intval($month)+1,2,'0',STR_PAD_LEFT);
  $year2 = $year;
}

$r = mysql_query("SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='900314106355' AND `date1` >= '{$year)-{$month}-01' AND `date1` < '{$year2}-{$month2}-01'");

Sorry for the late respond. With some help from a friend, I eventually got this date part right using the code below.

<?php
	
	$ic = $_SESSION['ic'];
	
	$date2 = $month.$year;
    $sql2 = "SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='$ic' AND `date1` LIKE '%$date2'";
	$query2 = mysql_query($sql2) or die("Error: " . mysql_error());
    $row2 = mysql_num_rows($query2);

    if($row2 == 0)
		echo 'There are no expenses to view for this month.';
	else{
    	while($data_try = mysql_fetch_assoc($query2)){
		
    ?>

I did take a look at the code you provided though. Will try it as an alternative. Another PHP function learned :) Thanks a lot for your help ardav. I really appreciate it.

Member Avatar for diafol

I know you've solved this, but I couldn't help thinking that there was an easier way to do this - use MONTH and YEAR:

$month = intval($_POST['month']);
$year = intval($_POST['year']);
$results = mysql_query("SELECT `date1`,`item`,`price` FROM `expenses` WHERE `ic_no`='$ic' AND MONTH(`date1`) = $month AND YEAR(`date1`) = $year");

That's been bugging me for a while, but I couldn't find this thread! :)

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

This last one definitely looks the easiest! :) Thanks again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.