Combine Sorty by Date AND by Category Feature?

[chaseman]

I have a script that allows me to sort user submitted data either by category OR by date and I'd like to accomplish both at the same time, so my question is how can I do that?

 

This is my script:

 

<?php
$select_category = $_REQUEST['sort_category'];
$sort_date_var = $_REQUEST['sort_date'];

// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (($select_category == 'All') || (!isset($select_category)) && (!isset($sort_date_var))) {

// Retrieve the chosen category from MySQL
$query3 = "SELECT * FROM con";
$data3 = mysqli_query($dbc, $query3);

//Loop through the array of data
while ($row3 = mysqli_fetch_array($data3)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row3['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row3['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . $row3['category'] . " | " . date('M d, Y', strtotime($row3['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);


} 

if ($select_category)  {


// Retrieve the chosen category from MySQL
$query2 = "SELECT * FROM con WHERE category = '$select_category'";
$data2 = mysqli_query($dbc, $query2);

//Loop through the array of data
while ($row2 = mysqli_fetch_array($data2)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row2['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row2['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row2['category']) . " | " . date('M d, Y', strtotime($row2['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);


} 

// SORT DATE BLOCK

if ($sort_date_var) {


// Retrieve the chosen category from MySQL
$query4 = "SELECT * FROM con ORDER BY contributed_date $sort_date_var";
$data4 = mysqli_query($dbc, $query4);

//Loop through the array of data
while ($row4 = mysqli_fetch_array($data4)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row4['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row4['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row4['category']) . " | " . date('M d, Y', strtotime($row4['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);
}

?>

 

The first block is the default state where ALL the data is being shown when the site is being visited. The second block sorts by category and the third by date.

The problem is that only ONE of the last two blocks work at a time and I would like to have a combination of date and category sorting.

 

Thanks for all the suggestions.

[gristoi]

SELECT * FROM con WHERE category = '$select_category' ORDER BY category, date ASC

[chaseman]

Your SQL statement didn't work for me, it also doesn't make sense for my example. I don't want to order by category, I want to SORT by category as in SORT OUT, or WEED OUT. The only thing that should be ORDERED is the date.

 

The statement should be like this:

 

- if sort_categry OR sort_date

- then select EVERYTHING from con table AND choose only the SELECTED category AND order it by date

 

This query does not produce any errors:

 

"SELECT * FROM con WHERE category = '$select_category' ORDER BY contributed_date $sort_date_var";

 

When I select a category, the category gets shown, as soon as I select a date everything gets lost and I end up with an empty space.

 

Here's the PHP block again:

 

$select_category = $_REQUEST['sort_category'];
$sort_date_var = $_REQUEST['sort_date'];

// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);


if (($select_category) || ($sort_date_var))  {

// Retrieve the chosen category from MySQL
$query2 = "SELECT * FROM con WHERE category = '$select_category' ORDER BY contributed_date $sort_date_var";
$data2 = mysqli_query($dbc, $query2) or die (mysqli_error($dbc));

//Loop through the array of data
while ($row2 = mysqli_fetch_array($data2)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row2['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row2['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row2['category']) . " | " . date('M d, Y', strtotime($row2['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);


} 

[chaseman]

ADDENDUM:

 

Ok it works with this query:

 

"SELECT * FROM con WHERE category = '$select_category' ORDER BY contributed_date $sort_date_var";

 

But only if I have sort_category and sort_date in ONE form with ONE submit button like this:

 

<form>
<select>
--sort_category--
</select>

<select>
--sort_date--
</select>
<input type="submit" />
</form>

 

The problem here is that it only works if I select the category AND date at the same time and THEN click submit, it does not work if I would select category FIRST click submit, and THEN choose date and click submit.

 

Maybe I'll figure out one day how to do that, for the first it's alright this way.

 

[chaseman]

Ok I solved this problem with a bunch of elseif statements, here's the code, for everybody who wonders the same thing:

 

<?php
$select_category = $_REQUEST['sort_category'];
$sort_date_var = $_REQUEST['sort_date'];
$sort_submit = $_POST['sortSubmit'];

// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (($select_category == 'All') || (!isset($select_category)) && (!isset($sort_date_var))) {

// Retrieve the chosen category from MySQL
$query	= "SELECT * FROM con";
$data = mysqli_query($dbc, $query);

//Loop through the array of data
while ($row = mysqli_fetch_array($data)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . $row['category'] . " | " . date('M d, Y', strtotime($row['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);


} elseif (isset($select_category) && !isset($sort_date_var)) {

// Retrieve the chosen category from MySQL
$query = "SELECT * FROM con WHERE category = '$select_category'";
$data = mysqli_query($dbc, $query) or die (mysqli_error($dbc));

//Loop through the array of data
while ($row = mysqli_fetch_array($data)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row['category']) . " | " . date('M d, Y', strtotime($row['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);

} elseif (!isset($select_category) && isset($sort_date_var)) {

// Retrieve the chosen category from MySQL
$query = "SELECT * FROM con ORDER BY contributed_date $sort_date_var";
$data = mysqli_query($dbc, $query) or die (mysqli_error($dbc));

//Loop through the array of data
while ($row = mysqli_fetch_array($data)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row['category']) . " | " . date('M d, Y', strtotime($row['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);

} elseif (isset($select_category) && isset($sort_date_var)) {

// Retrieve the chosen category from MySQL
$query = "SELECT * FROM con WHERE category = '$select_category' ORDER BY contributed_date $sort_date_var";
$data = mysqli_query($dbc, $query) or die (mysqli_error($dbc));

//Loop through the array of data
while ($row = mysqli_fetch_array($data)) 
{
	echo "<table class='knuffixTable'>";

	// Display the score data
	echo "<tr><td class='knuffix_name'>";
	echo "<strong>" . htmlentities($row['name']) . "</strong><br /></td></tr>";
	echo "<tr><td class='knuffix_contribution'><pre>" . $row['contribution'] . "</pre><br /></td></tr>";
	echo "<tr><td class='knuffix_categoryDate'>" . htmlentities($row['category']) . " | " . date('M d, Y', strtotime($row['contributed_date'])) . " </td></tr>";
	echo "</table>";
}
mysqli_close($dbc);

}

?>

 

 

I'm now able to:

- showcase every category by DEFAULT when someone comes to the page.

- sort only by category,

- sort only by date,

- sort by category AND date,