filtering my search further

Question

I want to echo the results I want, how can I filter them further?

example, search x100 y100, currently get hundreds of results. I need to filter these results further so I only get those thats are marked hostile, pending or friendly

I have the following html form

<form action="xysearch.php" method="post">
     <label>X Coord
       <input type="text" name="x" />
      </label>
     <label>Y Coord
       <input type="text" name="y" />
      </label>
     <select name="term">
       <option value="Hostile">Hostile</option>
       <option value="Pending">Pending</option>
       <option value="Friendly">Friendly</option>
      </select>
     <input type="submit" value="Search" />
   </form>

what I need to add to the search query is a way to filter those results so only the option selected in the diplomacy dropdown are shown

My query thus far which is not working - I can get ALL the results, but not just the filtered ones.

<?php

$x = $_POST['x'];
$y = $_POST['y'];
$term = $_POST['term'];

mysql_connect ("localhost","host","pass")  or die (mysql_error());
mysql_select_db ("d_base");

 $res = mysql_query("SELECT * FROM my_table WHERE (x BETWEEN $x -75 AND $x +75) AND (y BETWEEN $y -75 AND $y +75) ");
 $res2 = mysql_query("SELECT dip FROM my_table WHERE dip IN '%$term%' ORDER BY '%$term%' DESC  ");

    echo "<table border='1' align='center' cellpadding='5'>";
    echo "<tr> <th>City Name</th> <th>X</th> <th>Y</th> <th>Diplomacy</th> </tr>";

// loop through results of database query, displaying them in the table

    while($row = mysql_fetch_array( $res, $res2 )) {

// echo out the contents of each row into a table

    echo '<td>' . $row['city'] . '</td>';
    echo '<td>' . $row['x'] . '</td>';
    echo '<td>' . $row['y'] . '</td>';
    echo '<td>' . $row['dip'] . '</td>';
    echo "</tr>";

// close table>

    echo "</table>";

    }

?>

I'm not too sure where i am going wrong as i can actually get results to echo, is just the filtering thats the issue.

Your script seems to be vulnerable to [SQL injection](http://en.wikipedia.org/wiki/SQL_injection). You should have a look at [How to prevent SQL injection in PHP?](http://stackoverflow.com/q/60174/53114) — Gumbo, Apr 07 '13 at 08:23
Hello, thank you for the advice, I understand the risk, however, the website that it is being for has only a few people with access to it. This access is protected by password. I'm sure as i learn more about php, i will adopt PDO Methods. again, thank for the warning and advice — Paul Hesketh, Apr 07 '13 at 08:28
If you don't want to go through PDO Methods, just use mysql_real_escape_string — sagibb, Apr 07 '13 at 08:32

score 0 · Answer 1 · answered Apr 07 '13 at 08:25

0

Use:

"SELECT dip FROM my_table WHERE dip IN '%".$term."%' ORDER BY '".$term."' DESC"

Or if that doesn't work:

"SELECT dip FROM my_table WHERE dip='".$term."' ORDER BY '".$term."' DESC"

and yes, SQL injection :) You are VERY vulnerable.

answered Apr 07 '13 at 08:25

Christian-G

2,371
4
27
47

sagibb · Accepted Answer · 2013-04-07T08:51:57.930

0

I took a look at the PHP definition of mysql_fetch_array and it seems to accept only one result set, so if I'm correct, you are only iterating through the first result set, which is the unfiltered one.

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

Why aren't you filtering the result in a single query? It does seem like you are fetching items from a single table (my_table), right?

Here is a working sqlfiddle:

$res = mysql_query("SELECT * FROM my_table WHERE (x BETWEEN $x -75 AND $x +75) AND (y BETWEEN $y -75 AND $y +75) AND dip REGEXP '$term' ORDER BY dip DESC");
while($row = mysql_fetch_array( $res )) {
...
}

Notice that you'll have concatenate the different terms by an '|' (or) operator.

edited Apr 07 '13 at 08:51

answered Apr 07 '13 at 08:29

sagibb

956
2
9
21

I have tried what you sent, (many thanks), it didn't work unfortunately. All results are from the same table but different columns. – Paul Hesketh Apr 07 '13 at 08:35
Can you please paste in the rendered query? i.e: the one with the real values rather than variables? I'll get you a SQL Fiddle in a minute. Also, I've edited the query, the ORDER BY part should use a column name rather than a value. – sagibb Apr 07 '13 at 08:37
I've changed the query a bit and added a working sqlfiddle, let me know if it helps. – sagibb Apr 07 '13 at 08:52

score 0 · Answer 3 · answered Apr 07 '13 at 09:11

A first glimpse of your code:

while($row = mysql_fetch_array( $res, $res2 )) {

isn't correct because $res2 SHOULD be a resulttype and not a result from a query.

The options are:

while($row = mysql_fetch_array( $res, MYSQL_ASSOC )) {
while($row = mysql_fetch_array( $res, MYSQL_NUM )) {
while($row = mysql_fetch_array( $res, MYSQL_BOTH )) {

I guess you're trying to achieve something like this:

$res = mysql_query("SELECT * FROM my_table WHERE (x BETWEEN $x -75 AND $x +75) AND  (y BETWEEN $y -75 AND $y +75) AND dip LIKE '%$term%'");

This would mean code like this:

<?php

$x = $_POST['x'];
$y = $_POST['y'];
$term = $_POST['term'];

mysql_connect ("localhost","host","pass")  or die (mysql_error());
mysql_select_db ("d_base");


 $res = mysql_query("SELECT * FROM my_table WHERE (x BETWEEN $x -75 AND $x +75) AND  (y BETWEEN $y -75 AND $y +75) AND dip LIKE '%$term%'");



    echo "<table border='1' align='center' cellpadding='5'>";
    echo "<tr> <th>City Name</th> <th>X</th> <th>Y</th> <th>Diplomacy</th> </tr>";

// loop through results of database query, displaying them in the table

    while($row = mysql_fetch_array( $res, MYSQL_ASSOC )) {

// echo out the contents of each row into a table

    echo '<td>' . $row['city'] . '</td>';
    echo '<td>' . $row['x'] . '</td>';
    echo '<td>' . $row['y'] . '</td>';
    echo '<td>' . $row['dip'] . '</td>';
    echo "</tr>";

// close table>

    echo "</table>";

    }

?>

For a better database, I think you SHOULD store the terms in a separate table and do some joining instead:

If you decide to do this, use a query in a way similar to this:

 $res = mysql_query("SELECT * FROM my_table mt LEFT JOIN terms t ON (mt.term_id = t.id) WHERE (x BETWEEN $x -75 AND $x +75) AND  (y BETWEEN $y -75 AND $y +75) AND dip LIKE '%$term%'");

Another tip is to use PDO. It really isn't harder then using mysql_* - functions. In a way it's easier to create better and more readable code.

filtering my search further

3 Answers3