Sort by Category Function. How Can I Solve It More Efficiently?

[chaseman]

Since I'm new to programming I still don't know how to solve problems the most efficient way, I'd like to have your advice on this one. This is how I solved it and it works.

 

I have my categories as input buttons (I'm planning to change it into a drop down menu later).

 

Sort by Category:
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
<input type='submit' name='All' value='All' />
<input type='submit' name='Smileys' value='Smileys' />
<input type='submit' name='Faces' value='Faces' />
<input type='submit' name='Love' value='Love' />
</form>

 

When the input boxes are clicked the corresponding script below is being run, very simple and to a point primitive, definitely not really efficient, because if you have a lot of categories you'll end up with a lot of code and if you wanted to change one thing you'd have to change it in all the IF BLOCKS. Basically every input button has its own IF BLOCK.

 

<?php
if (isset($_POST['All']) OR (!isset($_POST['Smileys']) && !isset($_POST['Faces']) && !isset($_POST['Love']) {
// Connect to the database 
  $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
  
  // Retrieve the score data from MySQL
  $query = "SELECT * FROM asciiart";
  $data = mysqli_query($dbc, $query);

  
   // Loop through the array of score data, formatting it as HTML 
   while ($row = mysqli_fetch_array($data)) { 
  echo '<table class="asciiartTable">';
// Display the score data
    echo '<tr><td class="asciiart_name">';
    echo '<strong>' . htmlentities($row['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row['asciiart_contribution']) . '</pre><br /></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row['asciiart_category']) . ' | ' . date('M d, Y', strtotime

($row['created_date'])) . ' </td></tr>';

echo '</table>';
    }
   mysqli_close($dbc);		
   
}

if (isset($_POST['Smileys'])) {
// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

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

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

// Display the score data
    echo '<tr><td class="asciiart_name">';
    echo '<strong>' . htmlentities($row2['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row2['asciiart_contribution']) . '</pre><br 

/></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row2['asciiart_category']) . ' | ' . date('M d, Y', strtotime

($row2['created_date'])) . ' </td></tr>';

echo '</table>';
    }
   mysqli_close($dbc);
}

if (isset($_POST['Faces'])) {
// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Retrieve the chosen category from MySQL
$query3 = "SELECT * FROM asciiart WHERE asciiart_category = 'Faces'";
$data3 = mysqli_query($dbc, $query3);

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

// Display the score data
    echo '<tr><td class="asciiart_name">';
    echo '<strong>' . htmlentities($row3['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row3['asciiart_contribution']) . '</pre><br 

/></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row3['asciiart_category']) . ' | ' . date('M d, Y', strtotime

($row3['created_date'])) . ' </td></tr>';

echo '</table>';
    }
   mysqli_close($dbc);
}

if (isset($_POST['Love'])) {
// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Retrieve the chosen category from MySQL
$query4 = "SELECT * FROM asciiart WHERE asciiart_category = 'Love'";
$data4 = mysqli_query($dbc, $query4);

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

// Display the score data
    echo '<tr><td class="asciiart_name">';
    echo '<strong>' . htmlentities($row4['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row4['asciiart_contribution']) . '</pre><br 

/></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row4['asciiart_category']) . ' | ' . date('M d, Y', strtotime

($row4['created_date'])) . ' </td></tr>';

echo '</table>';
    }
   mysqli_close($dbc);
}

 

My question is: is there a way to have just one block of script and the script automatically inserts the right INPUT BUTTON into the query in the script?

 

 

Something similar to this, even though it doesn't work I'm just showing it for showcase purposes:

 

if (isset($_POST['Smileys']) || (isset($_POST['Faces']) || (isset($_POST['Love'])) {
// Connect to the database 
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Retrieve the chosen category from MySQL
$query2 = "SELECT * FROM asciiart WHERE asciiart_category = 'Smileys' || 'Faces' || 'Love'";
$data2 = mysqli_query($dbc, $query2); 

 

 

Notice in the query, it is looking for THAT keyword that has been PRESSED on the INPUT BUTTON.

 

How would I be able to create a logic like this so I can create a whole chain of categories?

 

 

Thanks for advice.

[msaz87]

All you need to do is switch it to a drop down and then use a variable in your query:

 

<select name="sort_cat">

<option value="A">Sort by A</option>
<option value="B">Sort by B</option>
<option value="C">Sort by C</option>

</select>

 

$cat_select = $_REQUEST['sort_cat'];	

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

[chaseman]

Wow it's that simple? I guess I was over-thinking it haha...

 

Thanks for the help, I'll try it out in a minute.

 

After having grasped the basic concept, coding is becoming fun lol...

[chaseman]

Ok I tried it, but didn't work for me, what do I write in front of the block code, so the code knows its being activated?

 

I have it like this:

 

$select_category = $_REQUEST['sort_category'];




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

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

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

// Display the score data
    echo '<tr><td class="asciiart_name">';
    echo '<strong>' . htmlentities($row2['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row2['asciiart_contribution']) . '</pre><br /></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row2['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row2['created_date'])) . ' </td></tr>';

echo '</table>';
    }
   mysqli_close($dbc);

 

As soon as I choose an option in the drop down list, nothing happens. Do I have to make an if statement? If yes, how would it look like?

[kickstart]

Hi

 

Cleaned up version of your original code

 

<?php

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

$ItemArray = array();

if (isset($_POST['All']) OR (!isset($_POST['Smileys']) && !isset($_POST['Faces']) && !isset($_POST['Love'])
{
array_push($ItemArray, 'Smileys','Faces','Love');
}
else
{
if (isset($_POST['Smileys'])) $ItemArray[] = 'Smileys'
if (isset($_POST['Faces'])) $ItemArray[] = 'Faces'
if (isset($_POST['Love'])) $ItemArray[] = 'Love'
}

$query = "SELECT * FROM asciiart WHERE asciiart_category IN ('".implode("','",$ItemArray)."')";

$data = mysqli_query($dbc, $query);

// Loop through the array of score data, formatting it as HTML 
while ($row = mysqli_fetch_array($data)) 
{ 
echo '<table class="asciiartTable">';
// Display the score data
echo '<tr><td class="asciiart_name">';
echo '<strong>' . htmlentities($row['asciiart_name']) . '</strong><br /></td></tr>';
echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row['asciiart_contribution']) . '</pre><br /></td></tr>';
echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row['created_date'])) . ' </td></tr>';
echo '</table>';
}

mysqli_close($dbc);		
   
?>

 

It depends on what you need to cope with as far as multiple selections. The above code could easily be used to process some selection boxes so if you wanted 'Love' and 'Faces' but not 'Smileys' you could cope with it.

 

In the code you have you still need a submit button to submit the form (although you could use javascript to auto submit the when the selected item on the drop down changes).

 

All the best

 

Keith

[chaseman]

Thanks a lot kickstart,

 

your version worked great with the input buttons, but I couldn't get it to work with a drop down menu and submit button like you suggested.

 

If I use a drop down menu like this one:

 

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="sort_category">
    <option disabled selected>Choose Category...</option>
	<option value="Faces">Faces</option>
	<option value="Smileys">Smileys</option>
	<option value="Love">Love</option>
</select>
<input type="submit" />
</form>

 

Nothing will happen when an option is chosen and submit has been clicked, I must be missing something.

[kickstart]

Hi

 

Can't see any issue with that and to double check I knocked up the following script which seems to work

 

<html>
<head>
</head>
<body>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<select name="sort_category">
    <option disabled selected>Choose Category...</option>
	<option value="Faces">Faces</option>
	<option value="Smileys">Smileys</option>
	<option value="Love">Love</option>
</select>
<input type="submit" />
</form>
<?php
if (isset($_REQUEST['sort_category']))
{
$select_category = $_REQUEST['sort_category'];

// Connect to the database 
$dbc = mysqli_connect('localhost', 'root', '', 'test');

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

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

	// Display the score data
	echo '<tr><td class="asciiart_name">';
	echo '<strong>' . htmlentities($row2['asciiart_name']) . '</strong><br /></td></tr>';
	echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row2['asciiart_contribution']) . '</pre><br /></td></tr>';
	echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row2['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row2['created_date'])) . ' </td></tr>';
	echo '</table>';
}
mysqli_close($dbc);
}
?>

 

All the best

 

Keith

[chaseman]

Thanks a lot I got it to work now. I wasn't able to get it to work with your initial version of the script where you used the arrays, but with the version where you improved msaz script it worked great!

 

I think the REQUEST version is simple and efficient enough since I'm not looking for multiple selection for now.

[chaseman]

One more question tho, how can I do an "All" option in the options list?

 

How can I do a statement like:

 

if (isset($_REQUEST['sort_category'] == 'All')) {

--- RUN SCRIPT -- 

query = "SELECT * FROM asciiart"

}

 

How can I do this?

[chaseman]

Addendum:

 

Ok I solved it this way for anybody who's interested:

 

$select_category = $_REQUEST['sort_category'];


if (($select_category == 'All') || (!isset($_REQUEST['sort_category']))) {

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

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

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

	// Display the score data
	echo '<tr><td class="asciiart_name">';
	echo '<strong>' . htmlentities($row3['asciiart_name']) . '</strong><br /></td></tr>';
	echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row3['asciiart_contribution']) . '</pre><br /></td></tr>';
	echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row3['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row3['created_date'])) . ' </td></tr>';
	echo '</table>';
}
mysqli_close($dbc);
}

 

Don't know  if it's the most efficient way, but it works.

 

Here's the complete script:

 

$select_category = $_REQUEST['sort_category'];


if (($select_category == 'All') || (!isset($_REQUEST['sort_category']))) {

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

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

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

	// Display the score data
	echo '<tr><td class="asciiart_name">';
	echo '<strong>' . htmlentities($row3['asciiart_name']) . '</strong><br /></td></tr>';
	echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row3['asciiart_contribution']) . '</pre><br /></td></tr>';
	echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row3['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row3['created_date'])) . ' </td></tr>';
	echo '</table>';
}
mysqli_close($dbc);
}


if (isset($_REQUEST['sort_category'])) {

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

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

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

	// Display the score data
	echo '<tr><td class="asciiart_name">';
	echo '<strong>' . htmlentities($row2['asciiart_name']) . '</strong><br /></td></tr>';
	echo '<tr><td class="asciiart_contribution"><pre>' . htmlentities($row2['asciiart_contribution']) . '</pre><br /></td></tr>';
	echo '<tr><td class="asciiart_categoryDate">' . htmlentities($row2['asciiart_category']) . ' | ' . date('M d, Y', strtotime($row2['created_date'])) . ' </td></tr>';
	echo '</table>';
}
mysqli_close($dbc);
}

 

[kickstart]

Hi

 

I would just use

 

$query2 = "SELECT * FROM asciiart ".(($select_category == 'All') ? '' : "WHERE asciiart_category = '$select_category'");

 

That way the query copes with both.

 

All the best

 

Keith