[PHP] Weapon Search Script Issue

Hello, I am not a coder, as you may be able to tell by the code posted below, but what I am trying to accomplish is a simple script to pull up all the weapons in the database and be able to organize them by entry, name, item level, and required level. I have been able to pull all of the weapons from the item_templates database, by subclass and have them displayed in a table. The problem is now when you enter the minimum or maximum filters, and then try to sort, the query is messed up at it only shows items with a required level of 0. So I guess what I am asking is if there is someone who knows PHP that can help me fix this script…


<?php

mysql_connect ("localhost", "root","password")  or die (mysql_error());

mysql_select_db ("world");

$weapon = $_POST['weapon'];

$minlevel = $_POST['minlevel'];

$maxlevel = $_POST['maxlevel'];

$order = (isset($_GET['order'])) ? $_GET['order'] : 'entry';

$sorting = (isset($_GET['sorting'])) ? $_GET['sorting'] : 'desc';

switch($sorting) {

    case "asc":

	    $sort = 'desc';

	    break;

    case "desc":

	    $sort = 'asc';

	   break;

}


$sql = mysql_query("SELECT * FROM item_template WHERE class = 2 AND subclass = '$weapon' AND RequiredLevel >= '$minlevel' AND RequiredLevel <= '$maxlevel' ORDER BY $order"." $sort");

?>

<h2>Weapon Search</h2>

<form name="search" method="post" action"<?=$PHP_SELF?>">

<select name="weapon">

  <option value="0">One Handed Axe</option>

  <option value="1">Two Handed Axe</option>

  <option value="2">Bow</option>

  <option value="3">Gun</option>

  <option value="4">One Handed Mace</option>

  <option value="5">Two Handed Mace</option>

  <option value="6">Polearm</option>

  <option value="7">One Handed Sword</option>

  <option value="8">Two Handed Sword</option>

  <option value="10">Staff</option>

  <option value="13">Fist Weapon</option>

  <option value="15">Dagger</option>

  <option value="16">Thrown</option>

  <option value="17">Spear</option>

  <option value="18">Crossbow</option>

  <option value="19">Wand</option>

  <option value="20">Fishing Pole</option>

</select>

<input name="minlevel" type="text" value="0" size="4" maxlength="2" /> -

<input name="maxlevel" type="text" value="80" size="4" maxlength="2" />

<input type="submit" name="submit" value="Submit">

<br><br>

<font color=#000000>

<table align=center cellpadding="2" cellspacing="2"  border="2">

  <thead>

   <td><center><a href="weapon_search.php?order=entry&sorting=<?=$sort;?>">Entry</a></center></td>

   <td><center><a href="weapon_search.php?order=name&sorting=<?=$sort;?>">Name</a></center></td>

   <td><center><a href="weapon_search.php?order=ItemLevel&sorting=<?=$sort;?>">Item Level</a></center></td>

   <td><center><a href="weapon_search.php?order=RequiredLevel&sorting=<?=$sort;?>">Required Level</a></center></td>

	    </thead>

<tbody>

<?php

while ($row = mysql_fetch_array($sql)){

echo "

   <tr>

    <td><center>",$row['entry'],"</center></td>

    <td><center>",$row['name'],"</center></td>

    <td><center>",$row['ItemLevel'],"</center></td>

    <td><center>",$row['RequiredLevel'],"</center></td>

   </tr>";

    }


?>

Thanks in advance!

Works (level req filter does apply) fine on my desktop.

SERVER_SOFTWARE Apache/2.2.21 (Unix) DAV/2 mod_ssl/2.2.21 OpenSSL/1.0.0c PHP/5.3.8 mod_apreq2-20090110/2.7.1 mod_perl/2.0.5 Perl/v5.10.1 
Mysql Client API version mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

//EDIT

ahh now i see what you mean. Its happening cause you are loosing the post vars after clicking on a sort url. You need to store search conditions somehow, in sessions for eg.

I am trying to figure out how to get around all that sort PHP and try to sort the tables using javascript, having issues with that too, but usually i can figure things out

Trying out the tips on this site, but i cant get them working. I can get it to add the sort to the first row, but the other rows are not affected. I think it has something to do with how I have my table set up

http://www.kryogenix.org/code/browser/sorttable/

Fixed some typos and seems to work, there you go:

(dont forget to configure database connection)

[CODE]<?php
// call the session thingy to start if it doesnt do dat automatically
session_start();

/* configure me */
$dbuser = ‘root’;
$dbpass = ‘myrealpassword’;
$dbhost = ‘localhost’;
$dbname = ‘worlddb’;
$debug = 1; // set it to 0 later

/*
put the weapon types into array so we can output theem
with a loop and mark selected if needed
*/
$types = array(
0 => ‘One Handed Axe’,
1 => ‘Two Handed Axe’,
2 => ‘Bow’,
3 => ‘Gun’,
4 => ‘One Handed Mace’,
5 => ‘Two Handed Mace’,
6 => ‘Polearm’,
7 => ‘One Handed Sword’,
8 => ‘Two Handed Sword’,
10 => ‘Staff’,
13 => ‘Fist Weapon’,
15 => ‘Dagger’,
16 => ‘Thrown’,
17 => ‘Spear’,
18 => ‘Crossbow’,
19 => ‘Wand’,
20 => ‘Fishing Pole’
);

/*
print_weapons
set up a function to display wepon pick form
*/
function print_weapons($types) {
$type_id = array_keys($types); // this will be option value
$c = count($types); // count the types so we know when to break looping
for($i = 0; $i < $c; ++$i) {
echo ‘<option value="’.$type_id[$i].‘"’;
// check if it was selected
if($_SESSION[‘search’][‘weapon’] == $i) {
echo ’ selected=“selected”';
}
echo ‘>’.$types[$type_id[$i]].“”;
}
}

/*
search_condition_handler
this will grab all the user passed variables and put then into session so
we wont loose it at any moment (until user closes the browser )
*/
function search_condition_handler() {
//$_SESSION[‘search’] = array ();
// we’re gonna floor each key to make sure that injections are impossible
// its simple and effective in this case

    // user submitted a form, pass it to session array
    if(isset($_POST) && $_POST == true) {
        $_SESSION['search']['minlevel']    = floor($_POST['minlevel']);
        $_SESSION['search']['maxlevel']    = floor($_POST['maxlevel']);
        $_SESSION['search']['weapon']    = floor($_POST['weapon']);
        }
    // no form submission but still we need some values to construct the query later
    elseif(isset($_POST) && !isset($_SESSION['search'])) {
        $_SESSION['search']['minlevel'] = 0;
        $_SESSION['search']['maxlevel'] = 80;
        $_SESSION['search']['weapon'] = 0;
        }

    // same as above but this time for get variables (those passed by url)
    if(isset($_GET) && $_GET == true) {
        $_SESSION['search']['orderby']    = htmlspecialchars($_GET['order']);
        $_SESSION['search']['sorting']    = htmlspecialchars($_GET['sort']);
    }
    // nothing in the url? set it to defaults
    else {
        $_SESSION['search']['orderby']    = 'RequiredLevel';
        $_SESSION['search']['sorting']    = 'DESC';
        }

}

// prepare sorting switch
// build search conditions
search_condition_handler();
switch($_SESSION[‘search’][‘sorting’]) {
case ‘DESC’; $new_sorting = ‘ASC’; break;
case ‘ASC’; $new_sorting = ‘DESC’; break;
}

// construct the query, note that were not using SELECT * anymore, we dont need all of the
// columns here
$sql = “SELECT entry, name, ItemLevel, RequiredLevel FROM item_template “;
$sql .= “WHERE class = ‘2’ AND subclass = '”.$_SESSION[‘search’][‘weapon’].”’ “;
$sql .= “AND RequiredLevel >= '”.$_SESSION[‘search’][‘minlevel’].”’ AND RequiredLevel <= '”.$_SESSION[‘search’][‘maxlevel’].“'”;
$sql .= "ORDER BY “.$_SESSION[‘search’][‘orderby’].” ".$_SESSION[‘search’][‘sorting’];
if($debug = 1) {
echo ‘

’;
echo ‘

conditions

’;
print_r($_SESSION);
echo ‘
’;
echo ‘

sql query

’;
echo $sql;
echo ‘
’;
}
?>

Weapon Search

<?php print_weapons($types); ?> -

<?php // connect to mysql $db = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass); // push the statement $q = $db->query($sql); if($q == true) { // execute the query $result = $q->fetchAll(PDO::FETCH_ASSOC); // see if we got anything back if(count($result) < 1) // no results echo 'sorry, nothing to show'; // display what we got else { foreach($result as $row) { echo ""; } } } // query did not execute (eg mysql syntax error etc) else echo 'failed to execute search'; ?>[/php][/CODE]
Entry Name Item Level Required Level
",$row['entry']," ",$row['name']," ",$row['ItemLevel']," ",$row['RequiredLevel'],"

If you don’t want to itch yourself with sessions switch your form’s method to GET and add the missing parameters to your link. Also most sortables lists are made in javascript.

@rkm, thank you for looking into this. I have a couple problems with your script though that I cant seem to solve.

First: The tables still do not sort. I see that you have placed this code:


// prepare sorting switch

// build search conditions

	    search_condition_handler();

	    switch($_SESSION['search']['sorting']) {

			    case 'DESC'; $new_sorting = 'ASC'; break;

			    case 'ASC'; $new_sorting = 'DESC'; break;

	    }

But I dont see where it is triggered

Second: If I chose any weapon above two handed sword, the select field does on keep the value. I assume this is because the weapons go in numerical order until you get to staff, where it jumps from subclass 8 to 10


$types = array(

	 0 => 'One Handed Axe',

	 1 => 'Two Handed Axe',

	 2 => 'Bow',

	 3 => 'Gun',

	 4 => 'One Handed Mace',

	 5 => 'Two Handed Mace',

	 6 => 'Polearm',

	 7 => 'One Handed Sword',

	 8 => 'Two Handed Sword',

	 10 => 'Staff',

	 13 => 'Fist Weapon',

	 15 => 'Dagger',

	 16 => 'Thrown',

	 17 => 'Spear',

	 18 => 'Crossbow',

	 19 => 'Wand',

	 20 => 'Fishing Pole'

);

Thank you for your time!

Please use prepared statements instead of an SQL string, it’s still possible to do SQL injection the way it is right now. The function htmlspecialchars is not a catch all for SQL injection vectors so it’s still quite possible that $_SESSION[‘search’][‘orderby’] and $_SESSION[‘search’][‘sorting’] may cause havok. Instead either use a prepared SQL Statement, it takes minimal effort or validate all the possible values for those two strings.

@up;

You’re right, no idea why i came out with htmlspecial chars instead of addslashes, must have been tired.

As for the prepared statements, i think that OP should handle it.

But I dont see where it is triggered

it’s triggered by switch, if it was set to ascending, will be descending after you click the link.

Second: If I chose any weapon above two handed sword, the select field does on keep the value. I assume this is because the weapons go in numerical order until you get to staff, where it jumps from subclass 8 to 10

change the line:

        if($_SESSION['search']['weapon'] == $i) {

to:

        if($_SESSION['search']['weapon'] == $type_id[$i]) {

Using addslashes is worse than htmlspecialchars. Use prepared statements.

As for print_weapons, use foreach

I think he means that $new_sorting is not used after that switch case, it seems to me it should be used in SQL query. That said again for security reasons I’d shy away from normal SQL query, instead use a prepared statement as it reduces the ability to do SQL injections to near nil if used with some sort of data validation.