[SOLVED] Shorten/simplify code

[soycharliente]

I used to have this code as independent functions for each year (so I would have 4 right now had I continued to do it that way). I thought that I could write it so that it would just update itself and I wouldn't have to keep adding functions.

 

This is what I have, but I feel like it's super roundabout and maybe a bit overkill. Am I just being dumb or is there a simpler way to do this?

 

I have a table with some data. I want to display the data for each year ago, that data exists, since today.

<h3>This day (<?php echo $today = date('F d'); ?>) years ago</h3>
<?php
$i = floor((date('U') - date('U',strtotime(firstLunch()))) / (60*60*24*7*52));
$count = 0;
while ($count <= $i)
{
$ago = date("Y-m-d", strtotime("-{$count} year"));
$year = date('l, Y', strtotime($ago));
$sql = "SELECT el.`loc`,dee.`pid`,dee.`rating`,dee.`thedate`
			FROM `lunch_locations` el
			JOIN `lunch_data` dee ON el.`id`=dee.`pid`
			WHERE `thedate`='{$ago}'";
$result = mysql_query($sql) OR DIE ("/Stats Error #5.");
$rows = mysql_num_rows($result);
if ($rows > 0)
{
	$r = mysql_fetch_assoc($result);
	$loc = $r['loc'];
	$rating = (empty($r['rating'])) ? "no rating" : "a rating of {$r['rating']}";
	echo "{$year} : {$loc} with {$rating}.";
}
else { echo "{$year} : Must not have been a workday."; }
if ($count<=$i) { echo '<br />'; }
$count++;
}
dbclose();
?>

 

[xtopolis]

Would something like this work?

SELECT el.`loc`,dee.`pid`,dee.`rating`,dee.`thedate`
            FROM `lunch_locations` el
            JOIN `lunch_data` dee ON el.`id`=dee.`pid`
            WHERE MONTH(`thedate`)= MONTH(CURDATE())
            AND DAY(`thedate`) = DAY(CURDATE())

 

SELECT ... WHERE ... (the Month and Day matches the current Month and Day) ?

[Zane]

what is the datatype for thedate in your table

[soycharliente]

TABLE `lunch_data` (
  `id` int( NOT NULL auto_increment,
  `pid` int(4) NOT NULL default '0',
  `thedate` date NOT NULL default '0000-00-00',
  `rating` int(4) default NULL,
  `spent` float default NULL,
  `comment` text,
  `publish` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `thedate` (`thedate`)
)

 

TABLE `lunch_locations` (
  `id` int(4) NOT NULL auto_increment,
  `loc` varchar(255) NOT NULL default '',
  `menu` text,
  `map` text,
  `tally` int( NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `loc` (`loc`)
)

[soycharliente]

Would something like this work?

SELECT el.`loc`,dee.`pid`,dee.`rating`,dee.`thedate`
            FROM `lunch_locations` el
            JOIN `lunch_data` dee ON el.`id`=dee.`pid`
            WHERE MONTH(`thedate`)= MONTH(CURDATE())
            AND DAY(`thedate`) = DAY(CURDATE())

 

SELECT ... WHERE ... (the Month and Day matches the current Month and Day) ?

 

In theory this def seems to be the way to go. Maybe add an OR statement to get all the years I want. Then I have all the results in 1 simple query.

 

BRB for this one.

[soycharliente]

Ok. This statements works great and cuts down A LOT of the PHP, except I wanted to be able to echo statements saying there wasn't data for that year. I can't return a result for a year that doesn't have data, so it makes it difficult to echo something out this way.

 

SELECT el.`loc`,dee.`pid`,dee.`rating`,dee.`thedate` FROM `lunch_locations` el
JOIN `lunch_data` dee ON el.`id`=dee.`pid`
WHERE MONTH(`thedate`)=MONTH(CURDATE()) AND DAY(`thedate`)=DAY(CURDATE())
AND (YEAR(`thedate`)>='2005' OR YEAR(`thedate`)>=YEAR(CURDATE()))

[xtopolis]

What happens when change JOIN to LEFT JOIN or RIGHT JOIN in the query?  This is not my strong point, but if I remember correctly, one of those will retrieve null values as well.

 

(only modified the last part for BETWEEN

SELECT el.`loc`,dee.`pid`,dee.`rating`,dee.`thedate` FROM `lunch_locations` el
JOIN `lunch_data` dee ON el.`id`=dee.`pid`
WHERE MONTH(`thedate`)=MONTH(CURDATE()) 
  AND DAY(`thedate`)=DAY(CURDATE())
  AND ( YEAR(`thedate`) BETWEEN 2005 AND YEAR(CURDATE()) )

 

(edit: also I'm not sure which ordering of the AND statements would be more efficient)

[soycharliente]

Both LEFT JOIN and RIGHT JOIN returned the same results as JOIN.

[soycharliente]

It's cool though. I think the new SQL code will do some good.

[xtopolis]

I would suggest reposting the query in the MySQL section to try to get what you want.  I'm fairly certain it's entirely plausible.  Glad it helped.

[soycharliente]

I wasn't really asking for help shortening just the MySQL code, But that's just how it turned out

 

The code is about the same length, but is it better for MySQL to bear the burden of doing the calculations over PHP?

[xtopolis]

MySQL laughs in the face of your date calculations.  I would say keep all the data gathering/sorting/crunching with MySQL and leave the displaying to PHP.  IMO.