PHP filter mysql results

Question

I have the below php code. I want to filter the results based on a query i will give on url. For example if i give http:/www.example.com/?aff=someuser , display me only data that contains the user someuser .here is my code

<?php
require_once('visitors_connections.php');//the file with connection code and functions

if ($_GET['start'] == "") $start = 0;
else $start = $_GET['start'];
$limit = 15;

$additionalQuery = "SQL_CALC_FOUND_ROWS ";

mysql_select_db($database_visitors, $visitors);
$query_visitors = "(SELECT ".$additionalQuery." * FROM visitors_table WHERE";





if ($_POST['day']!="") {
$query_visitors .= " visitor_day = '".$_POST['day']."'";
} else {
$query_visitors .= " visitor_day = ".date("d")."";

if ($_POST['month']!="") {
$query_visitors .= " AND visitor_month = '".$_POST['month']."'";
} else {
$query_visitors .= " AND visitor_month = ".date("m")."";
}

if ($_POST['year']!="") {
$query_visitors .= " AND visitor_year = '".$_POST['year']."'";
} else {
$query_visitors .= " AND visitor_year = ".date("Y")."";
}}
$query_visitors .= " LIMIT $start,$limit)";
$insert_visitors = mysql_query($query_visitors, $visitors) or die(mysql_error());
$row_visitors = mysql_fetch_assoc($insert_visitors);
$totalRows_visitors = mysql_num_rows($insert_visitors);

$nbItems = mysql_result(mysql_query("Select FOUND_ROWS() AS nbr"),0,"nbr");
if ($nbItems>($start+$limit)) $final = $start+$limit;
else $final = $nbItems;

echo '<table style="width:100%; border:1px dashed #CCC" cellpadding="3">
      <form id="form1" name="form1" method="post" action="display_visits.php">
       <tr>
        <td>day 
        <select name="day" id="day">
          <option value="" selected="selected"></option>
          <option value="01">01</option>
          <option value="02">02</option>
          <option value="03">03</option>
          <option value="04">04</option>
          <option value="05">05</option>
          <option value="06">06</option>
          <option value="07">07</option>
          <option value="08">08</option>
          <option value="09">09</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
          <option value="13">13</option>
          <option value="14">14</option>
          <option value="15">15</option>
          <option value="16">16</option>
          <option value="17">17</option>
          <option value="18">18</option>
          <option value="19">19</option>
          <option value="20">20</option>
          <option value="21">21</option>
          <option value="22">22</option>
          <option value="23">23</option>
          <option value="24">24</option>
          <option value="25">25</option>
          <option value="26">26</option>
          <option value="27">27</option>
          <option value="28">28</option>
          <option value="29">29</option>
          <option value="30">30</option>
          <option value="31">31</option>
        </select></td>
        <td>Month 
        <select name="month" id="month">
          <option value="" selected="selected"></option>
          <option value="1">1</option>
          <option value="2">2</option>
          <option value="3">3</option>
          <option value="4">4</option>
          <option value="5">5</option>
          <option value="6">6</option>
          <option value="7">7</option>
          <option value="8">8</option>
          <option value="9">9</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
        </select></td>
        <td>Year 
        <select name="year" id="year">
          <option value="" selected="selected"></option>
          <option value="2013">2013</option>
        </select></td>
        <td><input type="submit" name="Submit" value="Submit" /></td>
        <td></td>
       </tr>';

echo '<tr>
        <td style="width:15%;border-bottom:1px solid #CCC">IP</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Browser</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Time</td>
        <td style="width:30%;border-bottom:1px solid #CCC">Refferer</td>
        <td style="width:25%;border-bottom:1px solid #CCC">Page</td>
       <td style="width:25%;border-bottom:1px solid #CCC">Affiliate</td>
       </tr>';

do {

echo '<tr onmouseout="this.style.backgroundColor=\'\'" 
      onmouseover="this.style.backgroundColor=\'#EAFFEA\'">
        <td>'.$row_visitors['visitor_ip'].'</td>
        <td>'.$row_visitors['visitor_browser'].'</td>
        <td>'.$row_visitors['visitor_hour'].':'.$row_visitors['visitor_minute'].'</td>
        <td>'.$row_visitors['visitor_refferer'].'</td>
        <td>'.$row_visitors['visitor_page'].'</td>
         <td>'.$row_visitors['visitor_affiliate'].'</td>
       </tr>';
} while ($row_visitors = mysql_fetch_assoc($insert_visitors));
paginate($start,$limit,$nbItems,"display_visits.php","");
?>

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — PeeHaa, Jan 17 '13 at 20:45
^^^^as true as it is, its getting a little tedious for regular S.O users to see it 10 times a day. — , Jan 17 '13 at 20:47
@Dagon I think as long as questions are posted using mysql_* it's relevant. — Jim, Jan 17 '13 at 20:49
so every comment about spelling,grammar,indenting and a few thousand other issues almost every post has. — , Jan 17 '13 at 20:54

score 1 · Accepted Answer · edited May 25 '19 at 14:34

1

In PHP the values placed in the URL:

http:/www.example.com/?aff=someuser

Are placed into a superglobal $_GET. This is an array and you can grab out the aff value by doing:

$_GET['aff']

In your case you can use this to update your query:

if ($_GET['aff']!="") {
    $aff = $_GET['aff'];
    $aff = sanitise($aff);
    $query_visitors .= " AND user = '".$aff."'";
}

Note that you will have to santise the value, mysql_real_escape_string is normally used to do this.

Finally the mysql_* methods are deprectaed and you really shouldn't use them. See here for information.

edited May 25 '19 at 14:34

Zoe

27,060
21
118
148

answered Jan 17 '13 at 20:45

Jim

22,354
6
52
80

tried that but i get this You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'admin' visitor_day = 18 AND visitor_month = 01 AND visitor_year = 2013 LIMIT ' at line 1 – Stauroula Xalkia Jan 17 '13 at 20:59
1

What is your actual query. Are you missing an "AND" somewhere? Is there an extra "AND" somewhere? – showdev Jan 17 '13 at 21:09
@StauroulaXalkia You need an `AND` between the user check and the visitor day check. – Jim Jan 17 '13 at 21:32
@Jim Where exactly? could you post an example? – Stauroula Xalkia Jan 17 '13 at 22:01

score 0 · Answer 2 · answered Jan 17 '13 at 20:43

0

Try this:

        <?php
        $url = "http:/www.example.com/?aff=someuser";
        parse_str(parse_url($url, PHP_URL_QUERY), $parts);
        var_dump( $parts ); // u will get array(1) { ["aff"]=> string(8) "someuser" }
        ?>

answered Jan 17 '13 at 20:43

Afsar

3,104
2
25
35

PHP filter mysql results

2 Answers2