[SOLVED] Database Table Search and Column Sorting (Ascending)

Malevolence · December 30, 2007

Hi Guys,

This is my current script:

<html>
<head>
<title>RuneScapez.com :: Items Database</title>
<link rel="stylesheet" type="text/css" href="./idbstyle.css">
</head>

<body>

<!-- I want the Search to appear here -->

<?php
$result = mysql_query("SELECT * FROM items ORDER BY itemname ASC");

echo "<table border='1'>
<tr>
<th>Item Name</th>
<th>Item Type</th>
<th>Street Price</th>
<th>High Alch Reward</th>
<th>General Store Price</th>
<th>Members Item?</th>
<th>Stackable?</th>
<th>Quest?</th>
<th>Tradable?</th>
<th>Examine</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>";
echo "<a href='itempage.php?itemid=".$row['itemid']."'>".$row['itemname']."</a>";
  echo "</td>";
  echo "<td>" . $row['itemtype'] . "</td>";
  echo "<td>" . $row['streetprice'] . "</td>";
  echo "<td>" . $row['highalch'] . "</td>";
  echo "<td>" . $row['lowalch'] . "</td>";
  echo "<td>" . $row['memberitem'] . "</td>";
  echo "<td>" . $row['stackable'] . "</td>";
  echo "<td>" . $row['quest'] . "</td>";
  echo "<td>" . $row['tradable'] . "</td>";
  echo "<td>" . $row['examine'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
  </font></p>

</body>
</html>

What I want is to have a search above the table which allows me to search through records and show the results in the same layout as the table but only those results... I'm not sure how this is done. I want the search to search through the following fields:

itemname, itemtype, examine, notes

And the 2nd thing I want is links in the <th> bits that allow the user to SORT in ascending order of the column title they clicked. E.g. If they clicked 'Item Type', it would sort the items in the list in order of ascending by item type. I would also like what they click to be stored in a cookie so that when they come back to the page, it still is in order of what they chose (until they clear their cookies). I want this sort to not resort to having to switch to another page, but based on the URL for example, if they choose to sort by Item Type (itemtype) then the Url would appear as:

http://www.runescapez.com/itemsdb.php?sortBy=itemtype

That would then be saved in the cookies so that when the user goes to

http://www.runescapez.com/itemsdb.php, it is already sorted by itemtype

I don't know if this is possible; perhaps you know a better approach???

Note: I don't want all the different sorts to be a different .php page, I want it to be 1 page that can change due to what the user clicks/ has in their cookies.

If these questions are answered then I will be so happy!

Not to mention that I can get started on the appearence of the thing rather than the code!

a HUGE thanks in advance,

Dan.

Malevolence · December 31, 2007

Bump :S

mr_mind · December 31, 2007

This is what i came up with

<html>
<head>
	<title>RuneScapez.com :: Items Database</title>
	<link rel="stylesheet" type="text/css" href="./idbstyle.css">
</head>
<body>
	<?php
		// Start the form and point it to itself
		print '<form action=' . $_SERVER['PHP_SELF'] . ' method=get>';
		// Show the search box, and if anything has been searched show that
		print '<input type=text name=q value="' . $_GET['q'] . '" />';
		if(isset($_GET['o'])) {
			// If we have something to order the results by we want to carry that to the next page
			print '<input type=hidden name=o value="' . $_GET['o'] . '" />';
		}
		print '</form>';
		// Escape the search to make it database safe
		$search_item = mysql_real_escape_string($_GET['q']);
		// Start the sql statement, notice how i use the WHERE and LIKE statements
		$search_sql = "SELECT * FROM items WHERE itemname LIKE '" . $search_item . "' OR itemtype LIKE '" . $search_item . "' OR examine LIKE '" . $search_item . "' OR notes LIKE '" . $search_item . "'";
		if(isset($_GET['o'])) {
			// If we get what we want to order by we need to go with that
			switch($_GET['o']) {
				case 1:
					// If we want to order by the first column then do so and on and on
					$search_sql .= " ORDER BY itemname";
				break;
				case 2:
					$search_sql .= " ORDER BY itemtype";
				break;
				case 3:
					$search_sql .= " ORDER BY streetprice";
				break;
				case 4:
					$search_sql .= " ORDER BY highalch";
				break;
				case 5:
					$search_sql .= " ORDER BY lowalch";
				break;
				case 6:
					$search_sql .= " ORDER BY memberitem";
				break;
				case 7:
					$search_sql .= " ORDER BY stackable";
				break;
				case 8:
					$search_sql .= " ORDER BY quest";
				break;
				case 9:
					$search_sql .= " ORDER BY tradable";
				break;
				case 10:
					$search_sql .= " ORDER BY examine";
				break;
			}
		}
		else {
			// We default to ording by the itemid
			$search_sql .= " ORDER BY itemid";
		}
		// Finally we run the query
		$search_query = mysql_query($search_sql);
		if(isset($_GET['q'])) {
			// We need to format the URL to include the search or this wouldnt work
			$url = $_SERVER['PHP_SELF'] . '?q=' . urlencode($_GET['q']) . '&';
		}
		else {
			$url = $_SERVER['PHP_SELF'] . '?';
		}
		print '<table border=1>';
		print '<tr>';
		print '<th><a href=' . $url . 'o=1> ';
		if($_GET['o']==1) {
			// if we want to order by this section print a star before it
			print '* ';
		}
		print 'Item Name </a></th>';
		print '<th><a href=' . $url . 'o=2> Item Type </a></th>';
		if($_GET['o']==2) {
			print '* ';
		}
		print '<th><a href=' . $url . 'o=3> ';
		if($_GET['o']==3) {
			print '* ';
		}
		print 'Street Price </a></th>';
		print '<th><a href=' . $url . 'o=4> ';
		if($_GET['o']==4) {
			print '* ';
		}
		print 'High Alch Reward </a></th>';
		print '<th><a href=' . $url . 'o=5> ';
		if($_GET['o']==5) {
			print '* ';
		}
		print 'General Store Price </a></th>';
		print '<th><a href=' . $url . 'o=6> ';
		if($_GET['o']==6) {
			print '* ';
		}
		print 'Members Item? </a></th>';
		print '<th><a href=' . $url . 'o=7> ';
		if($_GET['o']==7) {
			print '* ';
		}
		print 'Stackable? </a></th>';
		print '<th><a href=' . $url . 'o=8> ';
		if($_GET['o']== {
			print '* ';
		}
		print 'Quest? </a></th>';
		print '<th><a href=' . $url . 'o=9> ';
		if($_GET['o']==9) {
			print '* ';
		}
		print 'Tradable? </a></th>';
		print '<th><a href=' . $url . 'o=10> ';
		if($_GET['o']==10) {
			print '* ';
		}
		print 'Examine </a></th>';
		print '</tr>';
		// Print the results
		while($search_array = mysql_fetch_array($search_query)) {
			print '<tr>';
			print '<td><a href=itempage.php?itemid=' . $search_array['itemid'] . '>' . $search_array['itemname'] . '</td>';
			print '<td>' . $search_array['itemtype'] . '</td>';
			print '<td>' . $search_array['streetprice'] . '</td>';
			print '<td>' . $search_array['highalch'] . '</td>';
			print '<td>' . $search_array['lowalch'] . '</td>';
			print '<td>' . $search_array['memberitem'] . '</td>';
			print '<td>' . $search_array['stackable'] . '</td>';
			print '<td>' . $search_array['quest'] . '</td>';
			print '<td>' . $search_array['tradable'] . '</td>';
			print '<td>' . $search_array['examine'] . '</td>';
			print '</tr>';
		}
		print '</table>';
	?>
</body>
</html>

Malevolence · December 31, 2007

I love learning PHP... tHanks for this. I'll try it out and give you my feedback.

Thanks again,

Malev.

mr_mind · December 31, 2007

your welcome

Malevolence · December 31, 2007

It works nearly perfectly!

The only problem is that it's only showing 1 item on page-load.

Here you have a look: http://www.runescapez.com/idb.php

I'll deeply look into the code a bit, but could you help me out?

I want * items to appear in the list until someone searches. Then it narrows down.... Is that what you coded it to do?

Thanks,

Dan.

mr_mind · December 31, 2007

no sorry, i forgot to default on that one. try this

<html>
<head>
	<title>RuneScapez.com :: Items Database</title>
	<link rel="stylesheet" type="text/css" href="./idbstyle.css">
</head>
<body>
	<?php
		// Start the form and point it to itself
		print '<form action=' . $_SERVER['PHP_SELF'] . ' method=get>';
		// Show the search box, and if anything has been searched show that
		print '<input type=text name=q value="' . $_GET['q'] . '" />';
		if(isset($_GET['o'])) {
			// If we have something to order the results by we want to carry that to the next page
			print '<input type=hidden name=o value="' . $_GET['o'] . '" />';
		}
		print '</form>';
		// Check to see if a search has been made
		if(isset($_GET['q'])) {
			// If so escape the search to make it database safe
			$search_item = mysql_real_escape_string($_GET['q']);
			// Start the sql statement, notice how i use the WHERE and LIKE statements
			$search_sql = "SELECT * FROM items WHERE itemname LIKE '" . $search_item . "' OR itemtype LIKE '" . $search_item . "' OR examine LIKE '" . $search_item . "' OR notes LIKE '" . $search_item . "'";
			if(isset($_GET['o'])) {
				// If we get what we want to order by we need to go with that
				switch($_GET['o']) {
					case 1:
						// If we want to order by the first column then do so and on and on
						$search_sql .= " ORDER BY itemname";
					break;
					case 2:
						$search_sql .= " ORDER BY itemtype";
					break;
					case 3:
						$search_sql .= " ORDER BY streetprice";
					break;
					case 4:
						$search_sql .= " ORDER BY highalch";
					break;
					case 5:
						$search_sql .= " ORDER BY lowalch";
					break;
					case 6:
						$search_sql .= " ORDER BY memberitem";
					break;
					case 7:
						$search_sql .= " ORDER BY stackable";
					break;
					case 8:
						$search_sql .= " ORDER BY quest";
					break;
					case 9:
						$search_sql .= " ORDER BY tradable";
					break;
					case 10:
						$search_sql .= " ORDER BY examine";
					break;
				}
			}
			else {
				// We default to ording by the itemid
				$search_sql .= " ORDER BY itemid";
			}
		}
		else {
			// If not just select all of the info
			$search_sql = "SELECT * FROM items";
		}
		// Finally we run the query
		$search_query = mysql_query($search_sql);
		if(isset($_GET['q'])) {
			// We need to format the URL to include the search or this wouldnt work
			$url = $_SERVER['PHP_SELF'] . '?q=' . urlencode($_GET['q']) . '&';
		}
		else {
			$url = $_SERVER['PHP_SELF'] . '?';
		}
		print '<table border=1>';
		print '<tr>';
		print '<th><a href=' . $url . 'o=1> ';
		if($_GET['o']==1) {
			// if we want to order by this section print a star before it
			print '* ';
		}
		print 'Item Name </a></th>';
		print '<th><a href=' . $url . 'o=2> Item Type </a></th>';
		if($_GET['o']==2) {
			print '* ';
		}
		print '<th><a href=' . $url . 'o=3> ';
		if($_GET['o']==3) {
			print '* ';
		}
		print 'Street Price </a></th>';
		print '<th><a href=' . $url . 'o=4> ';
		if($_GET['o']==4) {
			print '* ';
		}
		print 'High Alch Reward </a></th>';
		print '<th><a href=' . $url . 'o=5> ';
		if($_GET['o']==5) {
			print '* ';
		}
		print 'General Store Price </a></th>';
		print '<th><a href=' . $url . 'o=6> ';
		if($_GET['o']==6) {
			print '* ';
		}
		print 'Members Item? </a></th>';
		print '<th><a href=' . $url . 'o=7> ';
		if($_GET['o']==7) {
			print '* ';
		}
		print 'Stackable? </a></th>';
		print '<th><a href=' . $url . 'o=8> ';
		if($_GET['o']== {
			print '* ';
		}
		print 'Quest? </a></th>';
		print '<th><a href=' . $url . 'o=9> ';
		if($_GET['o']==9) {
			print '* ';
		}
		print 'Tradable? </a></th>';
		print '<th><a href=' . $url . 'o=10> ';
		if($_GET['o']==10) {
			print '* ';
		}
		print 'Examine </a></th>';
		print '</tr>';
		// Print the results
		while($search_array = mysql_fetch_array($search_query)) {
			print '<tr>';
			print '<td><a href=itempage.php?itemid=' . $search_array['itemid'] . '>' . $search_array['itemname'] . '</td>';
			print '<td>' . $search_array['itemtype'] . '</td>';
			print '<td>' . $search_array['streetprice'] . '</td>';
			print '<td>' . $search_array['highalch'] . '</td>';
			print '<td>' . $search_array['lowalch'] . '</td>';
			print '<td>' . $search_array['memberitem'] . '</td>';
			print '<td>' . $search_array['stackable'] . '</td>';
			print '<td>' . $search_array['quest'] . '</td>';
			print '<td>' . $search_array['tradable'] . '</td>';
			print '<td>' . $search_array['examine'] . '</td>';
			print '</tr>';
		}
		print '</table>';
	?>
</body>
</html>

Malevolence · December 31, 2007

I also forgot to say; when I search, you have to type the item's EXACT name in full, it isn't 'Catch-all'.

Is there a way of doing this? I saw do did 'Like' and 'While' mysql queries but they don't seem to work....

Could I perhaps involve wildcards or is this barking up the wrong tree??

I'll try out your recent script update first

Thanks.

Malevolence · December 31, 2007

http://www.runescapez.com/idb.php - try searching for a Blue party hat but only type blue party.

mr_mind · December 31, 2007

change

$search_sql = "SELECT * FROM items WHERE itemname LIKE '" . $search_item . "' OR itemtype LIKE '" . $search_item . "' OR examine LIKE '" . $search_item . "%' OR notes LIKE '" . $search_item . "'";

to

$search_sql = "SELECT * FROM items WHERE itemname LIKE '%" . $search_item . "%' OR itemtype LIKE '%" . $search_item . "%' OR examine LIKE '%" . $search_item . "%' OR notes LIKE '%" . $search_item . "%'";

mr_mind · December 31, 2007

I am not sure if this is case sensitive or not and it will only search against things being before it or after it.

hence if you hade

Sharp Cheddar

and typed

cheddar

it may come back though i am not sure

but if you type

Cheddar

it will come back

mmarif4u · December 31, 2007

It will sort out every thing because you use

like %$search%

Which will search all things including lower case or upper case.

Malevolence · December 31, 2007

Excellent!

The final problem and then I think that's everything is that when you click the <th> columns, I wanted it to resort the currently showing data in that order based on the heading; What your code does, is that it searches IN the selected column for data.....

The title is 2Q's (2 seperate questions).....

Sorry, I'm really new to PHP (Spent a while learning it but not enough for a script like this).

Perhaps put whatever you've got currently in the <th>'s in a dropdown next to the search box and then what I want the <th>'es to do is when you click them, it sorts the data in ascending order of that column.

Thanks,

Malev.

mr_mind · December 31, 2007

All the table headers do when you click on them is sort the searched data by that column. If you want to search within the selected cloumn you are going to have to change some things

Malevolence · December 31, 2007

Ahh, you DID do that. I know why it won't work.

For some reason, the sorting works when the URL looks like this:

http://www.runescapez.com/idb.php?q=

but not when it's like this:

http://www.runescapez.com/idb.php

Can you think of a fix?

mmarif4u · December 31, 2007

In your script 'GET' is used so when you sort by some header,it sent data to 'URL' from there it 'GET' the data and sort it.

mr_mind · December 31, 2007

yeah sorry try this

<html>
<head>
	<title>RuneScapez.com :: Items Database</title>
	<link rel="stylesheet" type="text/css" href="./idbstyle.css">
</head>
<body>
	<?php
		// Start the form and point it to itself
		print '<form action=' . $_SERVER['PHP_SELF'] . ' method=get>';
		// Show the search box, and if anything has been searched show that
		print '<input type=text name=q value="' . $_GET['q'] . '" />';
		if(isset($_GET['o'])) {
			// If we have something to order the results by we want to carry that to the next page
			print '<input type=hidden name=o value="' . $_GET['o'] . '" />';
		}
		print '</form>';
		// Check to see if a search has been made
		if(isset($_GET['q'])) {
			// If so escape the search to make it database safe
			$search_item = mysql_real_escape_string($_GET['q']);
			// Start the sql statement, notice how i use the WHERE and LIKE statements
			$search_sql = "SELECT * FROM items WHERE itemname LIKE '%" . $search_item . "%' OR itemtype LIKE '%" . $search_item . "%' OR examine LIKE '%" . $search_item . "%' OR notes LIKE '%" . $search_item . "%'";
		}
		else {
			// If not just select all of the info
			$search_sql = "SELECT * FROM items";
		}
		if(isset($_GET['o'])) {
			// If we get what we want to order by we need to go with that
			switch($_GET['o']) {
				case 1:
					// If we want to order by the first column then do so and on and on
					$search_sql .= " ORDER BY itemname";
				break;
				case 2:
					$search_sql .= " ORDER BY itemtype";
				break;
				case 3:
					$search_sql .= " ORDER BY streetprice";
				break;
				case 4:
					$search_sql .= " ORDER BY highalch";
				break;
				case 5:
					$search_sql .= " ORDER BY lowalch";
				break;
				case 6:
					$search_sql .= " ORDER BY memberitem";
				break;
				case 7:
					$search_sql .= " ORDER BY stackable";
				break;
				case 8:
					$search_sql .= " ORDER BY quest";
				break;
				case 9:
					$search_sql .= " ORDER BY tradable";
				break;
				case 10:
					$search_sql .= " ORDER BY examine";
				break;
			}
		}
		else {
			// We default to ording by the itemid
			$search_sql .= " ORDER BY itemid";
		}
		// Finally we run the query
		$search_query = mysql_query($search_sql);
		if(isset($_GET['q'])) {
			// We need to format the URL to include the search or this wouldnt work
			$url = $_SERVER['PHP_SELF'] . '?q=' . urlencode($_GET['q']) . '&';
		}
		else {
			$url = $_SERVER['PHP_SELF'] . '?';
		}
		print '<table border=1>';
		print '<tr>';
		print '<th><a href=' . $url . 'o=1> ';
		if($_GET['o']==1) {
			// if we want to order by this section print a star before it
			print '* ';
		}
		print 'Item Name </a></th>';
		print '<th><a href=' . $url . 'o=2> Item Type </a></th>';
		if($_GET['o']==2) {
			print '* ';
		}
		print '<th><a href=' . $url . 'o=3> ';
		if($_GET['o']==3) {
			print '* ';
		}
		print 'Street Price </a></th>';
		print '<th><a href=' . $url . 'o=4> ';
		if($_GET['o']==4) {
			print '* ';
		}
		print 'High Alch Reward </a></th>';
		print '<th><a href=' . $url . 'o=5> ';
		if($_GET['o']==5) {
			print '* ';
		}
		print 'General Store Price </a></th>';
		print '<th><a href=' . $url . 'o=6> ';
		if($_GET['o']==6) {
			print '* ';
		}
		print 'Members Item? </a></th>';
		print '<th><a href=' . $url . 'o=7> ';
		if($_GET['o']==7) {
			print '* ';
		}
		print 'Stackable? </a></th>';
		print '<th><a href=' . $url . 'o=8> ';
		if($_GET['o']== {
			print '* ';
		}
		print 'Quest? </a></th>';
		print '<th><a href=' . $url . 'o=9> ';
		if($_GET['o']==9) {
			print '* ';
		}
		print 'Tradable? </a></th>';
		print '<th><a href=' . $url . 'o=10> ';
		if($_GET['o']==10) {
			print '* ';
		}
		print 'Examine </a></th>';
		print '</tr>';
		// Print the results
		while($search_array = mysql_fetch_array($search_query)) {
			print '<tr>';
			print '<td><a href=itempage.php?itemid=' . $search_array['itemid'] . '>' . $search_array['itemname'] . '</td>';
			print '<td>' . $search_array['itemtype'] . '</td>';
			print '<td>' . $search_array['streetprice'] . '</td>';
			print '<td>' . $search_array['highalch'] . '</td>';
			print '<td>' . $search_array['lowalch'] . '</td>';
			print '<td>' . $search_array['memberitem'] . '</td>';
			print '<td>' . $search_array['stackable'] . '</td>';
			print '<td>' . $search_array['quest'] . '</td>';
			print '<td>' . $search_array['tradable'] . '</td>';
			print '<td>' . $search_array['examine'] . '</td>';
			print '</tr>';
		}
		print '</table>';
	?>
</body>
</html>

mr_mind · December 31, 2007

In your script 'GET' is used so when you sort by some header,it sent data to 'URL' from there it 'GET' the data and sort it.

and the point of mentioning this is? he asked for it to be sorted through the url and it is beneficial to do it this way so that the link can be later used

Malevolence · December 31, 2007

Yes, it ALL works now. Perhaps to save confusion I should add a 'Search' button rather than the user having to press enter.

But that's all I needed! Thanks!

Regards,

Dan.

mr_mind · December 31, 2007

your welcome

p.s.

I was going to add in a submit button but i tried that on my server and it put that into the url (being that it was part of the form) and o it is fine if you do that, the url is just a little bit bigger than if not

Malevolence · December 31, 2007

Is that just a simple

Or do I need to change the input name""?

Malevolence · December 31, 2007

I find it quite funny how the Read amount of this post is in the 80's as soon as you helped me. Everyone must be after a script like this?

Thanks Again.

mr_mind · December 31, 2007

yeah thats all you need to do

Sign In

[SOLVED] Database Table Search and Column Sorting (Ascending)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information