Select from MySQL based on HTML form

Question

A HTML form contain 4 fields (first name, last name, mobile and attendid). This is a search form to find a record in the attend mysql table. All of these fields are optional with intention being that the more fields you enter in the form, you are narrowing down the search. I know that the issue is with the first SQL as it is not taking into account all the variables.

The second bit to confuse it in more... Where results are echoed in a table, the last field of the echoed table should contain data that is selected from the second SQL statement but this data is in another table.

Sorry if anything is vague but I have no idea how to approach this, been looking at it too long!

Thanks so much for you help!

<html>
<body>
<table>
<table border="1">
<tr><th>AttendeeID</th><th>Wristband</th><th>Firstname</th><th>Lastname</th><th>Telephone
</th><th>Mobile</th><th>Address 1</th><th>Address 2</th><th>Town</th><th>Postcode</th><th>
E-Mail</th><th>Medical Notes</th><th>Last Reader Tap</th></tr>

<?php

include "checkmysqlconnect.php";

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$mobile = $_POST['mobile'];
$attendid = $_POST['attendid'];
$search = $_POST['search'];

if ($search == "Search") {
    if ($firstname == '' AND $lastname == '' AND $attendid == '' AND $mobile == '') {
        header("Location: searchattendform.php?result=1");
        $error = true;
    }

    if ($error != true) {

$sql = "SELECT * FROM `attend` WHERE `firstname` = '".$firstname."' OR `lastname` = '".$lastname."' OR `attendid` = '".$attendid."' OR `mobile` = '".$mobile."'";
$query = mysql_query($sql);
$count = mysql_num_rows($query);

$sql1 = "SELECT `readerid` FROM `taps` WHERE `attendid` = '".$attendid."' ORDER BY `time` DESC LIMIT 1";
$query1 = mysql_query($sql1);

if ($count > 1) {
    echo "More than one matching attendee. Entering more details will help narrow down results.";
while($value = mysql_fetch_assoc($query));
while($value1 = mysql_fetch_assoc($query1)) {   
    echo "<tr><td>".$value['attendid']."</td><td>".$value['wristband']."</td><td>".$value['firstname'].
    "</td><td>".$value['lastname']."</td><td>".$value['telephone']."</td><td>".$value['mobile']."</td><td>".$value['address1'].
    "</td><td>".$value['address2']."</td><td>".$value['town']."</td><td>".$value['postcode']."</td><td>".$value['email'].
    "</td><td>".$value['medical']."</td><td>".$value1['readerid']."</td></tr>";
} } else {
    if ($count == 0) {
        header("Location: searchattendform.php?result=2");
} else {
    if ($count == 1) {
        ($value = mysql_fetch_assoc($query));
            echo "<tr><td>".$value['attendid']."</td><td>".$value['wristband']."</td><td>".$value['firstname'].
        "</td><td>".$value['lastname']."</td><td>".$value['telephone']."</td><td>".$value['mobile']."</td><td>".$value['address1'].
        "</td><td>".$value['address2']."</td><td>".$value['town']."</td><td>".$value['postcode']."</td><td>".$value['email'].
        "</td><td>".$value['medical']."</td><td>".$value1['readerid']."</td></tr>";
    } else {
        echo "There was an issue searching attendees. Please contact SOFia Admin.";
    } }
}
}
}

?>

</table>
</body>
</html>

stop using `mysql_*` functions and start using pdo or mysqli, as `mysql_*` is deprecated and has high potential of sql injection — davejal, Jan 27 '16 at 12:51
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jan 27 '16 at 13:25
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 27 '16 at 13:26

score 0 · Answer 1 · answered Jan 27 '16 at 12:51

0

Take a look at your outer loop while($value = mysql_fetch_assoc($query));.

Shouldn´t this not be while($value = mysql_fetch_assoc($query)){?

answered Jan 27 '16 at 12:51

Bonscho

133
7

don't forget not to use `mysql_*` functions anymore as they are deprecated, so don't suggest them either! – davejal Jan 27 '16 at 12:52
Damn right. You definitivly should escape user or form input to avoid SQL injections. Your really should! – Bonscho Jan 27 '16 at 12:57

Select from MySQL based on HTML form

1 Answers1