No Error in SQL But No Output Returned

Light Poster

42 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

Skill Endorsements: 0

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

Keep Smiling

Moderator

10,655 posts since Oct 2006

Reputation Points: 1,628

Solved Threads: 1,510

Skill Endorsements: 57

0

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

Light Poster

42 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

Skill Endorsements: 0

1

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.

Keep Smiling

Moderator

10,655 posts since Oct 2006

Reputation Points: 1,628

Solved Threads: 1,510

Skill Endorsements: 57

0

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

Light Poster

42 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

Skill Endorsements: 0

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

Keep Smiling

Moderator

10,655 posts since Oct 2006

Reputation Points: 1,628

Solved Threads: 1,510

Skill Endorsements: 57

0

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.

Light Poster

42 posts since Oct 2010

Reputation Points: 10

Solved Threads: 0

Skill Endorsements: 0

Question Answered as of 1 Year Ago by diafol

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

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! :)

Keep Smiling

Moderator

10,655 posts since Oct 2006

Reputation Points: 1,628

Solved Threads: 1,510

Skill Endorsements: 57