No Error in SQL But No Output Returned

Newbie Poster

23 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

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'"

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

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

Newbie Poster

23 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

Show Comments

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.

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

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 bit2010-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? :(

Newbie Poster

23 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

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'");

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

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.

Newbie Poster

23 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

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

Rhod Gilbert Fan (ardav)

Moderator

7,792 posts since Oct 2006

Reputation Points: 1,170

Solved Threads: 1,080

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