OK, I need help on a script. I seem to be making headway, but the last bit is not coming willingly, and I don't know what else to try.

I have one table in a mysql db that I call "stories" and another that I call "dates". For each date, there can either be 0 stories up to an unlimited amount of stories. The script I'm trying to create lists each date, and then directly underneath lists all the stories that occurred on that date, then continues on to the next date and does the same. Both tables have a 'dateid' field to help identify what stories belong to which date. I've gotten code working to list all the dates, and all the stories under the first date, but for some reason, no stories get returned for any of the following dates. I do have some dummy stories in the database that should be returned for all the dates listed.

I've attached the db structure to this post, and will include the php code below. Any and all help would be appreciated! Thanks!

Here's some code:
<?php require_once('Connections/mediareview.php'); ?>
<?php
mysql_select_db($database_mediareview, $mediareview);
$query_datesrecordset = "SELECT * FROM dates";
$datesrecordset = mysql_query($query_datesrecordset, $mediareview) or die(mysql_error());
$row_datesrecordset = mysql_fetch_assoc($datesrecordset);
$totalRows_datesrecordset = mysql_num_rows($datesrecordset);

mysql_select_db($database_mediareview, $mediareview);
$query_storiesrecordset = "SELECT * FROM stories";
$storiesrecordset = mysql_query($query_storiesrecordset, $mediareview) or die(mysql_error());
$row_storiesrecordset = mysql_fetch_assoc($storiesrecordset);
$totalRows_storiesrecordset = mysql_num_rows($storiesrecordset);
?>

<?php do { ?>
<br />
<?php echo $row_datesrecordset['dateid']; ?>
<?php echo date("F j, Y", strtotime($row_datesrecordset['date'])); ?><br />

<?php do { ?>

<?php if ($row_storiesrecordset['dateid'] == $row_datesrecordset['dateid'])
echo $row_storiesrecordset['storytitle']; ?>
<br />
<?php } while ($row_storiesrecordset = mysql_fetch_assoc($storiesrecordset)); ?>

<br />
<br />
<?php } while ($row_datesrecordset = mysql_fetch_assoc($datesrecordset)); ?>
<strong>

<?php
mysql_free_result($storiesrecordset);
mysql_free_result($datesrecordset);
?>

Hmm... I guess I don't personally see the point in using two tables as opposed to one. So I would merge the tables and do the following:


<?php

mysql_select_db($database_mediareview, $mediareview);
// Ordering and grouping by date means that MySQL will already have it sorted the way we want it
$query_storiesrecordset = "SELECT * FROM stories ORDER BY date ASC, GROUP BY date";
$storiesrecordset = mysql_query($query_storiesrecordset, $mediareview) or die(mysql_error());

// We could set date to "" but on the off chance that one of the $stories['date'] is left blank
// we make it null.
$date = null;
while ($stories = mysql_fetch_assoc($storiesrecordset))
{
// If they don't equal eachother than we have a new date, so we need a new title
if ($date != $stories['date'])
{
echo '<br />Date: ' . date('F j, Y', $stories['date']) . '<br /><br />';
$date = $stories['date'];
}

// We don't have an elseif here, because it wouldn't print the first story.
if ($date == $stories['date']
{
echo $stories['storytitle'] . '<br />';
}
}
?>


I didn't test this so it'll probably have it's problems... but only one query and one loop, vs the two of each you are using.

You have a few problems. Let me see if I can explain them to you so you are able to solve the problem yourself (other than me writing the code for you).

The reason why its doing this is because the very first time your inner do loop runs, it steps through the entire resultset. Your loop will end when the last date is read from the date table. Obviously you don't have the same number of dates as you do stories. So if you have 5 dates, and 10 stories, you are only going to step through half of the stories result set. It doesn't "rewind" and start from the beginning.

Here is a simple example of what your loop is doing. Say you have three dates in your table. Your SELECT * FROM dates will return three records. Now you select your records from the stories table. Assume you have 4 stories. Two stories have the same date (that is, they have the same dateid).

Your loop will first, step through the dates array (the result set). It will do this three times (since there are only three dates). It will then step through the stories array (the result set). Eventually, you will reach the end of the date result set -- and there will still be stories to match.

On a design level, you are assuming that the order of the records entered is corresponds to the dates. This is a bad assumption, and can lead to these problems. The position of the records in a table should never be relied upon.

To clear things up, here is a simple case to highlight your problem. Two tables, a city list, and a country list:


mysql> select * from city_list;
+----+------------+-----------+
| id | country_id | city_name |
+----+------------+-----------+
| 1 | 1 | Orlando |
| 2 | 2 | Paris |
| 3 | 2 | Lyon |
| 4 | 1 | New York |
+----+------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from country_list;
+----+--------------+
| id | country_name |
+----+--------------+
| 1 | America |
| 2 | France |
+----+--------------+
2 rows in set (0.00 sec)


Now, I want to print for each country, a list of all cities in that country. Using your same logic:


<?php

mysql_connect('localhost','test','test');
mysql_select_db('test');

$result = mysql_query('SELECT * FROM city_list');
$result2 = mysql_query('SELECT * FROM country_list');
$country = mysql_fetch_assoc($result2);
$city = mysql_fetch_assoc($result);
echo "Before loop : result2 ".$result2."\n";
echo "Before loop : result ".$result."\n";
echo 'Number of cities : '.mysql_num_rows($result)."\n";
echo 'Number of countries : '.mysql_num_rows($result2)."\n";
do
{
echo 'Now checking country : '.$country['country_name']."\n";
do
{
echo 'Now checking city : '.$city['city_name']."\n";
if ($country['id'] == $city['country_id'])
{
echo $city['city_name']." is in ".$country['country_name']."\n";
}
} while($city = mysql_fetch_assoc($result));
} while ($country = mysql_fetch_assoc($result2));
echo 'After loop : result2 '.$result2."\n";
echo 'After loop : result '.$result."\n";
?>


Our result, which highlights the problem:


Before loop : result2 Resource id #6
Before loop : result Resource id #5
Number of cities : 4
Number of countries : 2
Now checking country : America
Now checking city : Orlando
Orlando is in America
Now checking city : Paris
Now checking city : Lyon
Now checking city : New York
New York is in America
Now checking country : France
Now checking city :
After loop : result2 Resource id #6
After loop : result Resource id #5


Hopefully, this clears things up. Let me know if you have any questions or don't understand my explanation. Things to note in the example are that the result sets (the resources) are still the same before and after the loops. Its just that you are stepping through them in the wrong order.

Hint: you can easily solve this problem at the database level using SQL

fyrestrtr,

I think I understand at least partially what you're explaining. Basically, the stories loop is only being evaluated the first time through, correct? I don't know if I understand exactly why, but I can see evidence of that in your example.

If this can be solved using SQL instead of PHP code, does the other poster's solution appear to be the right direction? i.e., group and order the query and then just print the results as they are spit out of mysql using one while loop? that solution makes sense to me (mostly), but I guess I'm still a little unclear if that's what you're suggesting I do, or if the two table model is still an acceptable way to go.

any other hints?? thanks!

The other posters solution is on the right track but there are several errors in his code.

If you want to use 2 seperate queries you can output the data to an array before you process it, or you can form your SQL query to output the data you want in the right order.
The correct mysql syntax to do that is "SELECT * FROM stories LEFT JOIN dates USING (dateid) ORDER BY dateid ASC" (dont use GROUP BY which would eliminate rows with duplicate dateid's if you were to use GROUP BY, you would group by the primary key in the stories table to prevent duplicate stories to be returned, this isnt a problem when you are using a key system on a single merge). From there you should be able to figure out how to process the data so you can format it on the page however you like.

If you really want to use your method, i suggest using functions like http://us2.php.net/manual/en/function.mysql-field-seek.php

I actually tested this one ;):


<?php

$mediareview = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('stories', $mediareview) or die(mysql_error());

// Ordering and grouping by date means that MySQL will already have it sorted the way we want it
$query_storiesrecordset = "SELECT * FROM stories ORDER BY date ASC";
$storiesrecordset = mysql_query($query_storiesrecordset, $mediareview) or die(mysql_error());

// We could set date to "" but on the off chance that one of the $stories['date'] is left blank
// we make it null.
$date = null;
while ($stories = mysql_fetch_assoc($storiesrecordset))
{
// If they don't equal eachother than we have a new date, so we need a new title
if ($date != $stories['date'])
{
echo '<br />Date: ' . date('F j, Y', $stories['date']) . '<br /><br />';
$date = $stories['date'];
}

// We don't have an elseif here, because it wouldn't print the first story.
if ($date == $stories['date'])
{
echo $stories['title'] . '<br />';
}
}
?>


Output:
Date: October 27, 2002

Book 3
Book 4

Date: December 27, 2005

Book 1
Book 2

JustinH,

Thanks for your follow up. The code works---kind of. The script keeps returning the date as 'December 31, 1969', an indication, apparently, that it sees the date value as null or 0. Is this due to the $date = null part of the code? I don't understand that portion of the code--could you explain why it is necessary (I'm not trying to be cheeky--I really don't understand).

Also, something else weird is happening. The date field in the table is a datetime field with both the date and time--in 0000-00-00 00:00:00 format. I had initially had all the dates set correctly with the times simply zeroed, and the script sorted them much like you show above (the first issue notwithstanding). When I went in and changed the time portion of the date field, it started sorting them all separately--even though the day portion of the field was the same. So...stories with the datetime of 2005-12-23 18:00:00 were being separated from the ones with a datetime of 2005-12-23 15:00:00. Does this ring any bells for you? Any suggestions of how to deal with this?

I really appreciate your time, and would welcome any additional help you (or anyone else!) can give. Thank you.

The script keeps returning the date as 'December 31, 1969', an indication, apparently, that it sees the date value as null or 0. Is this due to the $date = null part of the code?

Well I can see two problems. One, there is no column 'date' in your stories table (unless you changed the schema). I think this was just a typo.

The real reason for this is because what you are getting from MySQL is not a unix timestamp, but rather a MySQL datetime field. The php function date() expects a unix timestamp as the optional second argument. If you recall your original code had this line echo date("F j, Y", strtotime($row_datesrecordset['date'])); which will first convert the MySQL datetime to a timestamp.

Two options, either you do the date formatting in php using date() + strtotime() (which is not very efficient) or you do it on the database side itself using the date and time functions (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html) of MySQL.

I think I understand at least partially what you're explaining. Basically, the stories loop is only being evaluated the first time through, correct? I don't know if I understand exactly why, but I can see evidence of that in your example.

The first time the inner loop runs, it goes through the entire record set and doesn't 'rewind' itself to the beginning. This is evident in my example because you see the first time it runs, it prints all the cities. Then on the second run (when the second record is fetched from the outer loop) it has already finished going through the result set. So on the second run of the inner loop, $city is not set and since the record set has already been fetched, the while() condition is false, and the loop simply terminates. I hope this is clearer.

In my opinion, the best suggestion is from Korvan. Use SQL to return the proper results the first time.

Also, as a general rule -- don't use SELECT * -- its very inefficient. Especially in this example. All you are printing is the title of the story. You don't need to fetch the other 15 fields.

OK, thanks for the clarification on the date/strtotime. I actually had that correct in my original code, but somehow lost it in the shuffle. And the 'date' field wasn't a timestamp field, so I went ahead and changed it to DATE format, so we're just dealing with the day and not the time, since that's all we'll need. So overall, the script is just about 100% functional. There is one thing that's not functioning as desired, though.

Like I said, everything is sorting fine, but the very first row in the resultset is being omitted from the page. If I change the ASC to a DESC, it's still the first row that is being omitted. There are currently 17 rows in the 'stories' table, but only 16 are being returned. And, like I said, whether it is ordered ASC or DESC, it's always the first row of the resultset that's omitted, so it doesn't seem like a problem with the rows in the database, but more in how they're being parsed by the script. I tried the SQL query directly on the database from within phpmyadmin, and all of the rows are returned. So that leads me to believe that the problem lies within the PHP code somewhere.

I've also attached the current database structure for the 'stories' table.


<?php require_once('Connections/mediareview.php'); ?>
<?php
// Ordering and grouping by date means that MySQL will already have it sorted the way we want it
mysql_select_db($database_mediareview, $mediareview);
$query_storiesrecordset = "SELECT * FROM stories ORDER BY date DESC";
$storiesrecordset = mysql_query($query_storiesrecordset, $mediareview) or die(mysql_error());
$row_storiesrecordset = mysql_fetch_assoc($storiesrecordset);
$totalRows_storiesrecordset = mysql_num_rows($storiesrecordset);
// We could set date to "" but on the off chance that one of the $stories['date'] is left blank
// we make it null.
$date = null;
while ($stories = mysql_fetch_assoc($storiesrecordset))
{
// If they don't equal eachother than we have a new date, so we need a new title
if ($date != $stories['date'])
{
echo '<br /><strong>Date: ' . date('F j, Y', strtotime($stories['date'])) . '</strong><br /><br />';
$date = $stories['date'];

}
// We don't have an elseif here, because it wouldn't print the first story.
if ($date == $stories['date'])
{
echo $stories['storytitle'] . '<br />';

}
}

mysql_free_result($storiesrecordset);
?>

The first row is omitted because you have already fetched the first record before you enter the while loop:

$row_storiesrecordset = mysql_fetch_assoc($storiesrecordset);

Now $row_storiesrecordset has the details of your first record. Remove (comment out) this line and it should work as expected.

Great! Thanks, fyrestrtr, and everyone else who helped with this issue. I really appreciate it!