Why is my data displaying twice?

PHP
1

I want to create a calendar section on my website where each URL is in the form of a date, which in turn matches a value in my database table. For example, MySite/Calendar/January_1 would match the first three rows in the DB table below…

N | Date | Year | Content
1 | January_1 | 1830 | Buffalo Bill was born.
2 | January_1 | 1969 | Mt. Vesuvius erupted.
3 | January_1 | 1969 | He was elected President.
4 | January_2 | 1944 | World War II started.

I want to display the following:

1830 - Buffalo Bill was born.
1969 - Mt. Vesuvius erupted.
1969 - He was elected President.

Here’s my query…


    $stm = $pdo->prepare("SELECT Cal2.N, Cal2.Date2, Cal2.Year, Cal2.Brief
        FROM calendar_px Cal2
        WHERE Cal2.Date2 = :MyURL
        GROUP BY Cal2.Brief");
    $stm->execute(array(
        'MyURL'=>$MyURL
    ));

    break;
}

while ($row = $stm->fetch())
{
    $Year = $row['Year'];
    $Brief[] = ''.$Year.' – '.$row['Brief'].'';
}

And this is what I have on the display page…


echo join( $Brief, '<br>' );

It works fine, except that it displays the data twice. It displays all three rows, then it starts over and displays all three rows again.

I turned on error reporting, and there are no errors on the page. I pasted the query into phpMyAdmin > SQL, and it displays fine.

I thought this was what the GROUP BY function is supposed to be used for, which is why I added the GROUP BY clause. But it doesn’t work, no matter what field name I GROUP BY.

I must be making some really simple mistake, but I can’t figure it out. Any tips?

2

Can you please post the output of a SHOW CREATE TABLE for the table?

3 

CREATE TABLE `calendar_px` (
 `N` int(3) NOT NULL DEFAULT '0',
 `Date` char(5) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Date2` char(12) COLLATE latin1_general_ci DEFAULT NULL,
 `Year` year(4) DEFAULT NULL,
 `Brief` text COLLATE latin1_general_ci NOT NULL,
 PRIMARY KEY (`N`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
4

You should store the dates in a field of type “datetime”, then you’ll be able to make use of MySQL date and time functions

5

GROUP BY Cal2.Brief");

Why are you grouping? Surely a Brief is a unique thing within a date and year?
$stm = $pdo->prepare(“SELECT Cal2.N, Cal2.Date2, Cal2.Year, Cal2.Brief
FROM calendar_px Cal2
WHERE Cal2.Date2 = :MyURL
ORDER BY Cal2.Year”);

Makes more sense to me - order your entries by year, so you get the oldest entry first. (Unless you start doing BC stuff).

Also, I spy an errant break and close bracket… are you running your PDO Execute in a loop?

6

Sorry about the sloppy code; I’m not sure how that break got in there. :wink:

Anyway, I revised my table a little big (no significant changes), and this is the complete script:


 $stm = $pdo->prepare("SELECT Cal2.N, Cal2.URL, Cal2.Date, Cal2.Year, Cal2.Brief
  FROM calendar_px Cal2
  WHERE Cal2.URL = :MyURL
  ORDER BY Cal2.Year");
 $stm->execute(array(
  'MyURL'=>$MyURL
 ));

while ($row = $stm->fetch())
{
 $Year = $row['Year'];
 $Brief[] = $row['Brief'];

The field URL (char) holds values that match page URL’s, like this: September_1. The field Year (year) just holds years (e.g. 1972). I’m not currently using the Date field.

It’s still displaying the data twice.

SpacePhoenix:

I changed the field from date to datetime in another database table, and it now gives me this error message:

Fatal error: Call to a member function format() on a non-object

The error appears to be associated with the third line below:

$Born = $row[‘Born’];
$Birth = DateTime::createFromFormat(‘Y-m-d’, $Born);
$Birth_Record = ‘’.$Birth->format(‘M. j, Y’).’

So how would I modify that to work with a datetime format?

Thanks.