PHP - how to search a single column specified in a drop down menu

Question

I have a SQL database which is implemented onto a HTML webpage. I want the user to be able to type in a value into a searchbox and search a column of the table specified in a drop down menu.

For example: there are 3 columns on the table - 'first name', 'second name' and 'surname'. The user can choose one of these column names in a drop down menu and then type into a searchbox what they want to search for in the column specified. Let's say they choose the column 'surname' and want to search for 'Johnson' in that column.

The results should show all surnames with 'Johnson' in them and show the rest of the row for each found. My database is a bit different and larger than this but it applies the same. My current code can be found below:

<head>
    <link rel="stylesheet" type="text/css" href="stylesheet.css" />
    <meta charset="utf-8" />

    <title>The 24 Hour Race</title>
    <div id="title">
        <a href="index.php"><img src="images/title.png"></img></a>
    </div>
    <p id="subtitle">For the toughest of the tough</p>
</head>
<body>
    <div id="nav">
        <form method="post" action="index.php?go"  id="searchform">
            <br>
            <h3>Search in:</h3><select name="field">
            <option value="rider_no.">Rider No.</option>
            <option value="category">Category</option>
            <option value="name">Name</option>
            <option value="team_name">Team Name</option>
            <option value="nationality">Nationality</option>
            <option value="year_participated">Year Participated</option>
            <option value="total_laps">Total Laps</option>
            <option value="fastest_lap">Fastest Lap</option>
            </select>
            <input type="text" name="key">
            <input type="submit" name="submit" value="Search"><br>
        </form>
        <br>
        <h3>Sort by years:</h3>            
        <ul>
            <li><a href="2007.php">2007</a></li>
            <li><a href="2008.php">2008</a></li>
            <li><a href="2009.php">2009</a></li>
            <li><a href="2010.php">2010</a></li>
            <li><a href="2011.php">2011</a></li>
            <li><a href="2012.php">2012</a></li>
            <li><a href="2013.php">2013</a></li>
            <li><a href="2014.php">2014</a></li>
            <li><a href="2015.php">2015</a></li>
        </ul>
    </div>
    <div id="form">            
        <table id="t01" align="right" cellspacing="2">
    <thead>
        <tr>
            <td bgcolor="#ccc">Rider No.</td>
            <td bgcolor="#ccc">Category</td>
            <td bgcolor="#ccc">Name</td>
            <td bgcolor="#ccc">Team Name</td>
            <td bgcolor="#ccc">Nationality</td>
            <td bgcolor="#ccc">Year Participated</td>
            <td bgcolor="#ccc">Total Laps</td>
            <td bgcolor="#ccc">Fastest Lap</td>
        </tr>
    </thead>
    <tbody>
        <?php
        $connect = mysql_connect("localhost","root", "Highland5");
        if (!$connect) {
            die(mysql_error());
        }
        mysql_select_db("the24hourrace");
        if(isset($_POST[field]) and isset($_POST[key])) {
            $results = mysql_query("SELECT ".[field]." FROM riders WHERE ".[field]." = ".[key], $connect);
        } else {
            $results = mysql_query("SELECT * FROM riders", $connect); 
        }

        while($row = mysql_fetch_array($results)) {
   ?>
            <tr>
                <td><?php echo $row['rider_no']?></td>                    
                <td><?php echo $row['category']?></td>
                <td><?php echo $row['name']?></td>
                <td><?php echo $row['team_name']?></td>
                <td><?php echo $row['nationality']?></td>
                <td><?php echo $row['year_participated']?></td>
                <td><?php echo $row['total_laps']?></td>
                <td><?php echo $row['fastest_lap']?></td>
            </tr>
        <?php
        }
        ?>
     </tbody>
        </table>
    </div>
</body>

The important bits which I am asking about include div id="nav" and the form below it:

<form method="post" action="index.php?go"  id="searchform">
            <br>
            <h3>Search in:</h3><select name="field">
            <option value="rider_no.">Rider No.</option>
            <option value="category">Category</option>
            <option value="name">Name</option>
            <option value="team_name">Team Name</option>
            <option value="nationality">Nationality</option>
            <option value="year_participated">Year Participated</option>
            <option value="total_laps">Total Laps</option>
            <option value="fastest_lap">Fastest Lap</option>
            </select>
            <input type="text" name="key">
            <input type="submit" name="submit" value="Search"><br>
        </form>

And the PHP section:

<?php
        $connect = mysql_connect("localhost","root", "Highland5");
        if (!$connect) {
            die(mysql_error());
        }
        mysql_select_db("the24hourrace");
        if(isset($_POST[field]) and isset($_POST[key])) {
            $results = mysql_query("SELECT ".[field]." FROM riders WHERE ".[field]." = ".[key], $connect);
        } else {
            $results = mysql_query("SELECT * FROM riders", $connect); 
        }

        while($row = mysql_fetch_array($results)) {
   ?>
            <tr>
                <td><?php echo $row['rider_no']?></td>                    
                <td><?php echo $row['category']?></td>
                <td><?php echo $row['name']?></td>
                <td><?php echo $row['team_name']?></td>
                <td><?php echo $row['nationality']?></td>
                <td><?php echo $row['year_participated']?></td>
                <td><?php echo $row['total_laps']?></td>
                <td><?php echo $row['fastest_lap']?></td>
            </tr>
        <?php
        }
        ?>

If you have any questions about my code or what I am asking then please do not hesitate to ask! I found this a bit tricky to explain :)

so what you want to do is pretty much is once they enter the details int o1 of the fields and hit a button you want it to display the results, or do you want it do be displayed as they type it? — rackemup420, Mar 18 '16 at 14:44
@rackemup420 i want it to refresh the page and display it with the new results after they hit 'submit' — Angurz, Mar 18 '16 at 14:48
`$results = mysql_query("SELECT ".[field]." FROM riders WHERE ".[field]." = ".[key], $connect);` should be `$results = mysql_query("SELECT ".$_POST['field']." FROM riders WHERE ".$_POST['field']." = ".$_POST['key'], $connect);` — rackemup420, Mar 18 '16 at 14:56
yeah I realised my question was more of a code dump after I submitted it... anyway after trying to search a column it displays no results. I had $_POST in front of [field] and [key] before hand but had the same result then. If I remove WHERE ".[field]." = ".[key] from the first IF statement then the table will display the specified column with all values (nothing will need typed into the search bar for this to happen) and the rest of each row will be empty. — Angurz, Mar 18 '16 at 14:58
because you are missing the '' around `field` & `key` check my comment above i fixed it up — rackemup420, Mar 18 '16 at 14:59
ah i see what you were saying now @rackemup420. the code is still producing no values after searching still though :/ — Angurz, Mar 18 '16 at 15:01
var_dump your post info and make sure it is getting passed in — rackemup420, Mar 18 '16 at 15:02
i am setting it all up on my machine to test your code. I will post an answer in a few minutes. — rackemup420, Mar 18 '16 at 15:13

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

For future reference, please research before you post a question and also provide your error code.

PHP is an Object orientated programming language, this is the best method to loop through each of your Columns.

<?php foreach($connect->query($sql) as $r): ?>
      <td> <?php echo $r['column']; ?> </td>
<?php endforeach; ?>

Also when you're referencing global variables you cannot catch it the way you're trying to catch it.

GET Request -

echo $_GET['param'];
echo $_GET[$variable];

POST Request -

echo $_POST['param'];
echo $_POST[$variable];

This meaning, if you're not storing the parameter inside a variable, you must declare it like a string.

Please also note, you're vulnerable to SQLi Injections because you're not binding the fields securely.

But if you're not worried about that then you can do this:

$field = $_POST['field'];
$key = $_POST['key'];
$sql = "SELECT * FROM riders WHERE '$field'='$key'";

edited May 23 '17 at 12:31

Community

1
1

answered Mar 18 '16 at 14:45

Jaquarh

6,493
7
34
86

1

` ` should be ` ` you fogot the $ before `r['column']` – rackemup420 Mar 18 '16 at 14:47
edit it for me if you like (+rep) ;) but yeah, probably typing fast and forgot to put it. – Jaquarh Mar 18 '16 at 14:48
it wont let me edit it.. it says `Seems like nothing has changed` when I changed it lol – rackemup420 Mar 18 '16 at 14:49
Not a problem, I edited then x) Nice spot though @rackemup420 – Jaquarh Mar 18 '16 at 14:50
2

You also had endif; at the end of the foreach! – TommyBs Mar 18 '16 at 14:51
Oh god, its been a long day - apologies @TommyBs – Jaquarh Mar 18 '16 at 14:52

rackemup420 · Accepted Answer · 2016-03-18T15:42:50.577

So I went ahead and pretty much cleaned it all up and rewrote it using http://php.net/manual/en/pdo.prepared-statements.php which is much more secure. I tested it and its working. Also this isn't the MOST secure way but its more secure than the code you originally posted. You can google around and find out more methods to secure your data even more.

New Code:

define('DB_HOSTNAME', 'localhost');
define('DB_DATABASE', 'the24hourrace');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');

error_reporting(E_ALL);
ini_set('display_errors', 1);

function dataQuery($query, $params) {
$queryType = explode(' ', $query);

// establish database connection
try {
    $dbh = new PDO('mysql:host='.DB_HOSTNAME.';dbname='.DB_DATABASE, DB_USERNAME, DB_PASSWORD);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    echo $e->getMessage();
    $errorCode = $e->getCode();
}

// run query
try {
    $queryResults = $dbh->prepare($query);
    $queryResults->execute($params);
    if($queryResults != null && 'SELECT' == $queryType[0]) {
        $results = $queryResults->fetchAll(PDO::FETCH_ASSOC);
        return $results;
    }
    $queryResults = null; // first of the two steps to properly close
    $dbh = null; // second step to close the connection
}
catch(PDOException $e) {
    $errorMsg = $e->getMessage();
    echo $errorMsg;
}
}

?>
<head>
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
<meta charset="utf-8" />

<title>The 24 Hour Race</title>
<div id="title">
    <a href="index.php"><img src="images/title.png"></img></a>
</div>
<p id="subtitle">For the toughest of the tough</p>
</head>
<body>
<div id="nav">
    <form method="post" action="index.php"  id="searchform">
        <br>
        <h3>Search in:</h3><select name="field">
        <option value="rider_no">Rider No.</option>
        <option value="category">Category</option>
        <option value="name">Name</option>
        <option value="team_name">Team Name</option>
        <option value="nationality">Nationality</option>
        <option value="year_participated">Year Participated</option>
        <option value="total_laps">Total Laps</option>
        <option value="fastest_lap">Fastest Lap</option>
        </select>
        <input type="text" name="key" required>
        <input type="submit" name="submit" value="Search"><br>
    </form>
    <br>
    <h3>Sort by years:</h3>            
    <ul>
        <li><a href="2007.php">2007</a></li>
        <li><a href="2008.php">2008</a></li>
        <li><a href="2009.php">2009</a></li>
        <li><a href="2010.php">2010</a></li>
        <li><a href="2011.php">2011</a></li>
        <li><a href="2012.php">2012</a></li>
        <li><a href="2013.php">2013</a></li>
        <li><a href="2014.php">2014</a></li>
        <li><a href="2015.php">2015</a></li>
    </ul>
</div>
<div id="form">            
    <table id="t01" align="right" cellspacing="2">
<thead>
    <tr>
        <td bgcolor="#ccc">Rider No.</td>
        <td bgcolor="#ccc">Category</td>
        <td bgcolor="#ccc">Name</td>
        <td bgcolor="#ccc">Team Name</td>
        <td bgcolor="#ccc">Nationality</td>
        <td bgcolor="#ccc">Year Participated</td>
        <td bgcolor="#ccc">Total Laps</td>
        <td bgcolor="#ccc">Fastest Lap</td>
    </tr>
</thead>
<tbody>
    <?php
    if (isset($_POST['submit'])) {
        $field = $_POST['field'];
        $key = $_POST['key'];
        $query = 'SELECT * FROM `riders` WHERE `'.$field.'` = ?';
        $params = array($key);
    }else{
        $query = 'SELECT * FROM `riders`';
        $params = array();
    }
    $results = dataQuery($query,$params);

    foreach($results as $row) {
   ?>
        <tr>
            <td><?php echo $row['rider_no']?></td>                    
            <td><?php echo $row['category']?></td>
            <td><?php echo $row['name']?></td>
            <td><?php echo $row['team_name']?></td>
            <td><?php echo $row['nationality']?></td>
            <td><?php echo $row['year_participated']?></td>
            <td><?php echo $row['total_laps']?></td>
            <td><?php echo $row['fastest_lap']?></td>
        </tr>
    <?php
    }
    ?>
 </tbody>
    </table>
</div>
</body>

Man, this is amazing. I probably should've mentioned this is for school so I wasn't needing anything too secure (if anything at all) but this looks great! I'll try messing around with your version as I don't want to just copy/paste haha - I'll get back to you on Monday after I fiddle with this code :) thank you! — Angurz, Mar 19 '16 at 23:27
sorry It's wednesday and I'm only just replying - I'll try out the code as soon as possible as other things have popped up — Angurz, Mar 23 '16 at 14:01

PHP - how to search a single column specified in a drop down menu

2 Answers2