mySQL duplicate date column - PHP needs to be displayed once
Posted: Tue Nov 27, 2007 3:41 pm
Hey All,
I have a little issue. I can't seem to get around this, simple (I think) task. I have a mySQL database that holds this table that has 4 columns: ID (auto_increment)- user - date - post. The ID is of course an auto_incrementing number for uniqueness. User is the user who posts to the DB. date is the current date of the post .. which the format of yyyy-mm-dd. Finally, the post is just as you would think - the post content. This all works by the way. I just wanted to explain what the table looked like.
My problem is with the date column. Users are allow to have multiple posts in the same day, and I want this. However, when running a query against the database:
dbtable is just a table, it will select the date column, and the $authUser is just a variable (that works) that returns the currently logged in user (that authenticates with the mysql database). However, here's the results I get for the "testaccount" user:
While this is fine, this will not work for what I'm about to show you. I need to actually retrieve some information from the database - for the currently logged in user. I need to have a drop down menu that holds the month and year. Once you click on the option, and click "filter" it will then retrieve all the posts for that month/year. This works, however, the drop down menu (when there are multiple posts for the month), displays multple entries in the drop down menu. Let me show you.
Per the database output above, the drop down menu shows this:
"November 2007"
"November 2007"
Of course, no one would want this. I would need to for it to save "November 2007" once and when you click it and then click filter, it should show you the two results as links. I want to say this, so far, everything works (even the links). the only problem I actually have is the drop down menu showing a multiple month/year, when I want it to just have one month/year based on the database results.
Here's some code to sample:
Also, just to pull it out - the actual drop down menu to note, is this part of the above code:
So, overall, I'm pretty much wanting a simple PHP function or snippet that will take a mysql result of the "date" column and be able to get multiple results, but not show multiple date/year in the drop down.
Any help is appriciated.
I have a little issue. I can't seem to get around this, simple (I think) task. I have a mySQL database that holds this table that has 4 columns: ID (auto_increment)- user - date - post. The ID is of course an auto_incrementing number for uniqueness. User is the user who posts to the DB. date is the current date of the post .. which the format of yyyy-mm-dd. Finally, the post is just as you would think - the post content. This all works by the way. I just wanted to explain what the table looked like.
My problem is with the date column. Users are allow to have multiple posts in the same day, and I want this. However, when running a query against the database:
Code: Select all
SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESCCode: Select all
date
2007-11-27
2007-11-27Per the database output above, the drop down menu shows this:
"November 2007"
"November 2007"
Of course, no one would want this. I would need to for it to save "November 2007" once and when you click it and then click filter, it should show you the two results as links. I want to say this, so far, everything works (even the links). the only problem I actually have is the drop down menu showing a multiple month/year, when I want it to just have one month/year based on the database results.
Here's some code to sample:
Code: Select all
<div id="archives">
<h1 id="form"><?php echo $row['realname']; ?>'s Saved DC Walkthroughs</h1>
<?php
$userQuery = "SELECT * FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
$userResult = mysql_query($userQuery) or die(mysql_error());
if (mysql_num_rows($userResult) == 0) {
echo "<p class=\"regindent\">You currently do not have any saved Walkthroughs</p>";
}
else {
?>
<form id="getwalkthroughs" name="getwalkthroughs" method="get" action="<?php $_SERVER['PHP_SELF']; ?>">
<select id="dcdate" name="dcdate">
<optgroup label="Please Select Date">
<option name="dropdate" value="0">Please Select</option>
<?php
$i=0;
$dateQuery = "SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
$dateResult = mysql_query($dateQuery) or die(mysql_error());
$num = mysql_num_rows($dateResult);
while ($i<$num) {
$zDate = mysql_fetch_assoc($dateResult);
$yDate = $zDate['date'];
$xDate = date('M (Y)', strtotime($yDate));
echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
$i++;
}
?>
</optgroup>
</select>
<input id="submit" type="submit" value="Filter Walkthroughs" />
</form>
<?php
$returnedDate = $_GET['dcdate'];
$returnedQuery = "SELECT date FROM walkthroughs WHERE date='$returnedDate' ORDER BY date DESC";
$returnedResult = mysql_query($returnedQuery) or die(mysql_error());
if (isset($_GET['dropdate']) !== "0") {
while ($row = mysql_fetch_object($returnedResult)) {
$mysqlDate = $row->date;
$newDate = date('M d, Y', strtotime($mysqlDate));
// Return the links
while ($row = mysql_fetch_object($userResult)) {
$mysqlDate = $row->date;
$newDate = date('M d, Y', strtotime($mysqlDate));
echo "<p><a href=\"details/$row->id\">".$newDate."</a></p>\n";
}
}
}
else {
// do nothing
}
?>
<?php
}
?>
</div>Code: Select all
<select id="dcdate" name="dcdate">
<optgroup label="Please Select Date">
<option name="dropdate" value="0">Please Select</option>
<?php
$i=0;
$dateQuery = "SELECT date FROM walkthroughs WHERE user='$authUser' ORDER BY date DESC";
$dateResult = mysql_query($dateQuery) or die(mysql_error());
$num = mysql_num_rows($dateResult);
while ($i<$num) {
$zDate = mysql_fetch_assoc($dateResult);
$yDate = $zDate['date'];
$xDate = date('M (Y)', strtotime($yDate));
echo "<option name=\"dropdate\" value=\"$yDate\">".$xDate."</option>";
$i++;
}
?>
</optgroup>
</select>Any help is appriciated.