[RESOLVED] Sort MySQL results with multiple selection boxes

[SomeoneElse]

Hey all, I am knowledgable in PHP and MySQL, but im not very good at implementing them both together, now my question is, how would I be able to sort database results using multiple selections, and then displaying sorted results.

This is my current PHP and HTML code, to get data off the database, and displaying it in a table.

<?php

$rowsPerPage = 20;

$pageNum = 1;

if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;
$query = " SELECT * FROM players" .
         " LIMIT $offset, $rowsPerPage";

$result = mysql_query($query)
or die(mysql_error()); 

$num = mysql_num_rows($result);

?>

<table border="0" cellspacing="7" cellpadding="6" style="text-align: center;">
<tr>
<th>Value1</th>
<th>Value2</th>
<th>Value3</th>
<th>Value4</th>
<th>Value5</th>
<th>Value6</th>
<th>Value7</th>
<th>Value8</th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f2=mysql_result($result,$i,"value1");
$f3=mysql_result($result,$i,"value2");
$f4=mysql_result($result,$i,"value3");
$f5=mysql_result($result,$i,"value4");
$f6=mysql_result($result,$i,"value5");
$f7=mysql_result($result,$i,"value6");
$f8=mysql_result($result,$i,"value7");
$f9=mysql_result($result,$i,"value8");
?>

<tr>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
<td><?php echo $f6; ?></td>
<td><?php echo $f7; ?></td>
<td><?php echo $f8; ?></td>
<td><?php echo $f9; ?></td>
</tr>

<?php
$i++;
}
?>
</table>
</center>


   <div id="pages"> 
<?php
$query   = "SELECT COUNT(*) AS numrows FROM table1";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= "<b><u>$page</u></b>";
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> <br>";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;';
   $first = '&nbsp;';
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = "<br> <a href=\"$self?page=$page\">[Next]</a>";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;';
   $last = '&nbsp;';
}

echo $first . $prev . $nav . $next . $last;

mysql_close();

?>

and this is my selection form code, to sort by.

<form name="input" action="<?php $_SERVER['PHP_SELF']; ?>" method="get">
Value 1:
<SELECT NAME="value1">
<OPTION VALUE="Database values?">Database Values?
</SELECT>
 etc...

<input type="submit" value="Submit" />
</form> 

[SomeoneElse]

Anyone? really need help with this.

[niroshan]

Hi SomeoneElse,

What you need to do is when you post the form change the query accordingly. Like,

if(isset($_POST['submit']))
	$query = "SELECT * FROM players WHERE <field> = <dropdown value> LIMIT $offset, $rowsPerPage";
else
	$query = "SELECT * FROM players LIMIT $offset, $rowsPerPage";

Remember to change the both queries.

Hope this helps,

Regards,
Niroshan

[SomeoneElse]

So I would replace:

$query = " SELECT * FROM players" .
         " LIMIT $offset, $rowsPerPage";

with your piece of code, after changing the queries.

[niroshan]

yes 🙂... make sure you change <field> and <dropdown value> with proper values.

[SomeoneElse]

Im not sure if this is correct, really sorry about my newbie-ness in this subject.

if(isset($_POST['submit']))
    $query = "SELECT * FROM players WHERE position = position LIMIT $offset, $rowsPerPage";
else
    $query = "SELECT * FROM players LIMIT $offset, $rowsPerPage";
 

[niroshan]

Hi SomeoneElse,

Well that not quite right.

Are you familiar with $_POST ?

if(isset($_POST['submit']))
    $query = "SELECT * FROM players WHERE position = '".$_POST['value1']."' LIMIT $offset, $rowsPerPage";
else
    $query = "SELECT * FROM players LIMIT $offset, $rowsPerPage"; 

value1 = name of the drop down.

Regards,
Niroshan

[SomeoneElse]

Ah yes, sorry thanks for that, ive never really used $_POST function before thank you.

[SomeoneElse]

It does not seem to work. Here is my revised code:

<?php

$rowsPerPage = 20;

$pageNum = 1;

if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

if(isset($_POST['submit']))
    $query = "SELECT * FROM players WHERE position = '".$_POST['position']."' LIMIT $offset, $rowsPerPage";
else
    $query = "SELECT * FROM players LIMIT $offset, $rowsPerPage";

$result = mysql_query($query)

// $result = mysql_query("SELECT * FROM players")
or die(mysql_error()); 

$num = mysql_num_rows($result);

?>

<form name="input" action="<?php $_SERVER['PHP_SELF']; ?>" method="get">
Position:<br>
<SELECT NAME="position">
<OPTION VALUE="*">Any
<OPTION VALUE="ST">ST
</SELECT>
<input type="submit" value="Sort" />
</form>

[niroshan]

Hi SomeoneElse,

First of all sorry my mistake.. I didnt checked your form method. Its get not post.
So instead of using $POST you have to use $GET.

<SELECT NAME="position">
<OPTION VALUE="*">Any
<OPTION VALUE="ST">ST
</SELECT>

Please use proper html code. your missing closing tags.

<input type="submit" value="Sort" />

Your missing the name property. this should be,

<input type="submit" name="submit" value="Sort" />

You can use anything for name but for this example i have used submit.

here`s the revised code,

<?php

$rowsPerPage = 20;
$pageNum = 1;

if(isset($_GET['page']))
	$pageNum = $_GET['page'];

$offset = ($pageNum - 1) * $rowsPerPage;

if(isset($_GET['submit']))
    $query = "SELECT * FROM competitions WHERE compname = '".$_GET['position']."' LIMIT $offset, $rowsPerPage";
else
    $query = "SELECT * FROM competitions LIMIT $offset, $rowsPerPage";

$result = mysql_query($query) or die(mysql_error()); 
$num = mysql_num_rows($result);

while($row = mysql_fetch_array($result))
{
	print $row['compname'].'<br />';
}

?>

<form name="input" action="<?php $_SERVER['PHP_SELF']; ?>" method="GET">
  Position:<br>
  <select name="position">
    <option value="*">Any</option>
    <option value="compi1">compi1</option>
  </select>
  <input type="submit" name="submit" value="Sort" />
</form>

remember in php html elements are accessed by its name. so dont forget to specify the name.

Regards,
niroshan

[SomeoneElse]

Thank you very much, it seems to be working now!

One more question, If I want multiple drop down boxes, how would I go by doing that, by searching multiple options, for example:

<form name="input" action="<?php $_SERVER['PHP_SELF']; ?>" method="GET">
  <select name="position">
    <option value="*">Any</option>
    <option value="ST">ST</option>
  </select>

  <select name="national">
    <option value="*">Any</option>
    <option value="England">England</option>
  </select>
  <input type="submit" name="submit" value="Sort" />
</form>

[niroshan]

Hi SomeoneElse,

One thing I forgot to mention. In your drop down you have a option called any and the value as *. so you have to ignore the any condition as well.

if(isset($_GET['submit']))
{
	$conditions = array('position', 'national');
	$dbFields = array('compname', 'national');

$query = "SELECT * FROM competitions";
$options = '';

while(list($key, $val) = each($conditions))
{
	if(isset($_GET[$val]) && $_GET[$val] <> '*')
	{
		$options .= $options == '' ? ' WHERE '.$dbFields[$key]." = '".$val."'" : ' AND '.$dbFields[$key]." = '".$val."'";

	}
}

$query .= $options." LIMIT $offset, $rowsPerPage";
}
else
    $query = "SELECT * FROM competitions LIMIT $offset, $rowsPerPage";

Thanks,
best regards,
Niroshan

[SomeoneElse]

Its returning an empty result?

[niroshan]

Hi SomeoneElse,

Did you change the $dbFields array according to your table field names?

remember $conditions array list all the conditions and $dbFields list all the corresponding field names.

Please change the table name as well. I have used my test table in the query.

Thanks,
niroshan

[SomeoneElse]

I have changed it to this:

    $conditions = array('position', 'national');
    $dbFields = array('position', 'national');

What do you mean by conditions by the way?

[niroshan]

Hi SomeoneElse,

What do you mean by conditions by the way?

Conditions I mean all the filters you need in your query. (ex: position dropdown, national drop down ..etc)

can you please post your final code.🙂

EDIT: sorry i have done a small mistake... :o

it should be,

$options .= $options == '' ? ' WHERE '.$dbFields[$key]." = '".$_GET[$val]."'" : ' AND '.$dbFields[$key]." = '".$_GET[$val]."'";

sorry i think i need a sleep 😃

regards,
niroshan

[SomeoneElse]

Really thanks a lot for that, theres only one problem, the paging does not work. It is showing all the pages from the original time, it is not showing the pages involving the sort.

Here is the coding involving paging:

   <div id="pages"> 
<?php
$query   = "SELECT COUNT(*) AS numrows FROM players";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= "<b><u>$page</u></b>";
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> <br>";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;';
   $first = '&nbsp;';
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = "<br> <a href=\"$self?page=$page\">[Next]</a>";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;'; 
   $last = '&nbsp;'; 
}

echo $first . $prev . $nav . $next . $last;

mysql_close();

?>

[dagon]

you have to pass the vars in the url for the sort, just as you do for the page number

[SomeoneElse]

For a database query, how would I go to pass the vars in the url, so the sorting also goes through the paging code.

[dagon]

the same way you pas the page var in the url