PHP MSSQL Query Search

Question

I have been batting this around for a while and can not get the variables working for a search. Can connect fine and return results defined as a proper mssql_query and also am fine order by on variables etc but trying to get a text search implemented is not returning results not sure why.

<?php

$link = mssql_connect('SERV13\\RALSQL12', 'RA4joomla', 'Fenestron1');

if (!$link || !mssql_select_db('RALNHV', $link)) {
    die('Unable to connect or select database!');
}else{
echo"";
}

if(isset($_REQUEST['submit'])){
    $firstname=$_POST['FirstName'];
    $surname=$_POST['Surname'];
    $query = 'SELECT * FROM lEmployee WHERE FirstName LIKE '%".$firstname."%' OR Surname LIKE '%".$surname."%'';
    $q=mssql_query($sql);
}
else{ 
    $query = 'SELECT * FROM lEmployee';
    $q = mssql_query($query);
}
?>
<form method="post">
    <table width="200" border="1">
  <tr>
    <td>Name</td>
    <td><input type="text" name="firstname" value="<?php echo $firstname;?>" /></td>
    <td>Email</td>
    <td><input type="text" name="surname" value="<?php echo $surname;?>" /></td>
    <td><input type="submit" name="submit" value=" Find " /></td>
  </tr>
</table>
</form>
<?php
// Check if there were any records
echo "<table class='table'>"; 
echo "<tr>";  
echo "<th><a href='?orderBy=FirstName'>FirstName</a></th><th><a href='?orderBy=Surname'>Surname</a></th><th><a href='?orderBy=EmployeeNo'>Trigram</a></th><th>Office Phone</th><th>Mobile</th><th><a href='?orderBy=EmployeeJobTitle'>Job Title</a></th><th><a href='?orderBy=Name'>Base</a></th>";
echo "</tr>";

    while ($row = mssql_fetch_array($query)) {
        echo "<tr>";  
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' .  iconv("CP1252", "UTF-8", $row['FirstName']) . '</a>' . "</td>";  
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . iconv("CP1252", "UTF-8", $row['Surname']) . '</a>' . "</td>";
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . $row['EmployeeNo'] . '</a>' . "</td>";  
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . $row['Phone'] . '</a>' . "</td>";
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . $row['Mobile'] . '</a>' . "</td>";
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . $row['EmployeeJobTitle'] . '</a>' . "</td>";
            echo "<td>" . '<a href="user-profile-id?id=' . $row['ID'] . '" class="modal">' . $row['Name'] . '</a>' . "</td>";

        echo "</tr>"; 
    }
echo "</table>";
?>

If I were you, I'd read up on SQL injection before you go any further. — podiluska
– podiluska, Commented Oct 15, 2013 at 17:57
I agree with podiluska. This makes your database completely open to everyone. You are directly including what the user enters in your query. I could easily delete your entire database. — JP_
– JP_, Commented Oct 15, 2013 at 18:02
2 things - (1) Is the SQL query being formed properly? If you echo($query) you may find a format issue. (2) If not are the $_POST variables picked up OK? Your input box names may refer to variables sent in the post but are reference in the $_POST['FirstName'] etc. The SQL injection stuff very critical too and the above comment v. valid...Nick. — nickL
– nickL, Commented Oct 15, 2013 at 18:04
It is an intranet site so no issue on the sql injection all internal. — Andy Robinson
– Andy Robinson, Commented Oct 16, 2013 at 4:29

saqibahmad · Accepted Answer · 2014-12-11 17:00:47Z

1

I am 100% agree with nickL you have some formating issue in your query try to replace your search query by this:

    $firstname=$_POST['firstname'];
    $surname=$_POST['surname'];
    $query = "SELECT * FROM lEmployee WHERE FirstName LIKE '%".$firstname."%' OR Surname LIKE '%".$surname."%'";
    $q=mssql_query($sql);

php is a case sensitive language your post variables name are wrong replace the code and try again, if not succeeded try echo $query and run it in query browser in sql server.

hope this will fix the issue.

answered Dec 11, 2014 at 17:00

saqibahmad

9721 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MSSQL Query Search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related