Problem with curdate and interval, also missing first result

[fr600]

OK, I've a weird situation here.

 

In the database there is a row called added_date which is in date format.

 

When I run the following query:

 

$cats = categorylist();
$display = "";

foreach ($cats as $rowc)
{
$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, users.id FROM items 
LEFT JOIN categories ON category = categories.id 
LEFT JOIN users ON items.owner = users.id 
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 4  DAY) 
AND category = " . $rowc['id'] . " 
ORDER BY iid DESC");

while($row = mysql_fetch_array($res))
$display .= itemstable($res);
}
echo "$display";

 

First a list of categories are selected (with categorylist()), then for each category mysql_query is run, then the result is transferred to a function (itemstable).

 

Here are the weird results I'm getting:

 

1. Some rows are missing (for example, it's not showing 1st or 2nd August rows but shows July 28-31)

 

2. Items outside the interval are listed (like july 28-31 instead of july30-aug02 with interval of 4 days)

 

3. It show blank table (via function) when mysql_query is false even though it's set otherwise.

 

4. Just noticed that the first row of each category is also missing.

 

Any idea?

 

PS. I'm using mysql 5.1.25, php 5.2.6, and apache 2.2.9.

[fr600]

Well, I've created the complete scenario. Please take a look at it and help me out.

 

This is the database:

CREATE TABLE IF NOT EXISTS categories (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO categories (id, name) VALUES
(1, 'Porsche'),
(2, 'Ferrari'),
(3, 'Mercedes');

CREATE TABLE IF NOT EXISTS items (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(64) NOT NULL DEFAULT '',
  category int(10) unsigned NOT NULL DEFAULT '0',
  added_date date NOT NULL DEFAULT '0000-00-00',
  owner int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  UNIQUE KEY id (id),
  KEY owner (owner),
  KEY name (name),
  KEY added_date (added_date)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO items (id, name, category, added_date, owner) VALUES
(1, 'Boxter', 1, '2008-07-28', 1),
(2, '210 E-Class', 3, '2008-07-28', 3),
(3, 'Enzo', 2, '2008-07-28', 1),
(4, '575M', 2, '2008-07-29', 2),
(5, '208 CLK', 3, '2008-07-29', 2),
(6, '944 Cabriolet', 1, '2008-07-30', 3),
(7, '944 Coupe', 1, '2008-07-30', 2),
(8, '220 S-Class', 3, '2008-07-30', 2),
(9, '456M', 2, '2008-07-30', 3),
(10, 'F50', 2, '2008-07-31', 1),
(11, '210 Wagon', 3, '2008-07-31', 1),
(12, '911 Turbo', 1, '2008-07-31', 2),
(13, '201 Sedans', 3, '2008-08-01', 1),
(14, '575M', 2, '2008-08-01', 2),
(15, '612 Scaglietti', 2, '2008-08-02', 3),
(16, '356C Coupe', 1, '2008-08-02', 2);

CREATE TABLE IF NOT EXISTS users (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  username varchar(14) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY username (username)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO users (id, username) VALUES
(1, 'admin'),
(2, 'test'),
(3, 'engineer');

 

and this the the php file (I call it test.php):

<?php
$mysql_hostname = "localhost";
$mysql_username = "root";
$mysql_password = "pass";
$mysql_database = "test";

@mysql_connect($mysql_hostname, $mysql_username, $mysql_password);
mysql_select_db($mysql_database);

function categorylist()
{
$ret = array();
$res = mysql_query("SELECT id, name FROM categories ORDER BY id");
while ($row = mysql_fetch_array($res))
	$ret[] = $row;
return $ret;
}

function itemstable($res)
{
echo "<table cellpadding=0 cellspacing=0>";
echo "<tr>";
echo "<td width=50>ID</td>";
echo "<td width=100>Category</td>";
echo "<td width=150>Name</td>";
echo "<td width=150>Added Date</td>";
echo "<td width=150>Owner</td>";
echo "</tr>";
while ($row = mysql_fetch_assoc($res))
{
	echo "<tr>";
	echo "<td>" . $row['iid'] . "</td>";
	echo "<td>" . $row['catname'] . "</td>";
	echo "<td>" . $row['name'] . "</td>";
	echo "<td>" . $row['added_date'] . "</td>";
	echo "<td>" . $row['username'] . "</td>";
	echo "</tr>";
}
echo "</table>";
echo "<br>";
}

$cats = categorylist();
$display = "";
foreach ($cats as $rowc)
{
$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, categories.name as catname, users.id, users.username FROM items 
LEFT JOIN categories ON category = categories.id 
LEFT JOIN users ON items.owner = users.id 
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 7  DAY) 
AND category = " . $rowc['id'] . " 
ORDER BY iid DESC");
while($row = mysql_fetch_array($res))
$display .= itemstable($res);
}
echo "$display";
?>

 

It took me a long time to create this sample. Now it should be easy for you to identify what's wrong.

 

Thanks a lot.

[fenway]

First, you should really prefix your column names with table aliases in your where clause, too.

 

Second, you can't really filter properly on a LEFT JOIN'ed table field -- move this condition to the ON clause, if you want to.