PHP and Microsoft SQL Server 2008, using a keyword passed from a form

PHP
1

Hey guys, I am trying to learn how to create a simple search form that can search my Microsoft SQL server database by a keyword. I am having sever difficulty tracking down any examples that use the SQL server syntax, I think I have managed to complete the code but just cant figure out how to use a variable that holds the users entered keyword in the actual query. Here is the code I have for the PHP:

<?php



$term = $_POST['term'];

$tsql = "select * from personnel where SURNAME like '%"$term"%'";

$stmt = sqlsrv_query( $conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

if( $stmt === false)
{
     echo "Error in query preparation/execution.\
";
     die( print_r( sqlsrv_errors(), true));
}

while ($row = sqlsrv_fetch_array($tsql)){
    echo 'NUMBER: '.$row['SNUM'];
    echo '<br/> Jobtitle: '.$row['JOBTITLE'];
    echo '<br/> Last Name: '.$row['SURNAME'];
    echo '<br/> Sex: '.$row['SEX'];
    echo '<br/><br/>';
    }
?>

I cant figure out the keyword wildcard code for the variable term, it is driving me crazy how everything on-line seems to be MySQL related. Can anyone tell me how to properly use a variable to run a search on my database, I can get this to load with no errors just a blank page so I feel I almost there. I really appreciate any help on this!!

Many thanks!!!

2 

$tsql = "select * from personnel where SURNAME like '%" . $term . "%'";

Its a PHP error, you just needed to use . to concat the string.

ps that $term should be escaped to avoid risk having your db emptied or worse.

You can also add a line of debug, to echo out the sql statement prior to going to the db, if that helps understand what is going on – it is sometimes faster than looking in your logfiles.


$tsql = "select * from personnel where SURNAME like '%" . $term . "%'";

// uncomment the following line if you have further problems
// echo $tsql;
3

Thanks for your reply its appreciated. I have changed the code to match your suggestion, I also put in the echo because I am still getting blank pages, with the echo I am now seeing this:

select * from personnel where SURNAME like ‘%brock%’

brock was the keyword I entered into the form. I have no idea why it is not returning the results, am I still doing something wrong? I have the database opened and my other queries are working fine, just this keyword search thing that is giving me issues.

Thanks!

4

I honestly do not know if LIKE is acceptable to sqlserver.

Copy the echoed text, and paste it directly into your database management too, does that find a result set?

If yes, then there is something wrong with your connection, of which I am unfamiliar… sorry.

How about you ?


// another line of debug
var_dump( $conn );
5

Thanks, I done what you said and I figured out how to run the query in the management tool, it worked, so then I realised it must be my output that was wrong, so I messed around with it and it eventually worked. Thanks a lot I didn’t even know I could test queries like that, that’s how new I am to this. Cheers for your help!!

6

it is :slight_smile:

7

While I am here guys, is there a good source of information that I can use to help me learn SQL and PHP for Microsoft SQL server 2008? I tried reading through all the documentation at Microsoft’s website but I feel its examples are too complex and all the simple things such as my original post are not easy to get help with. All the syntax for SQL server is different than the others and in many cases I cannot find examples on how to do things with PHP.

Many thanks for all your input!

8

Well, it might give you another slight learning curve but the tendency these days is towards using PDO (PHP Data Objects) to access your database.

It especially offers prepared statements which nicely tackle the sql injection risks I nodded towards in my first reply. There is a MSSQL driver available, and I’d urge you to use it if you can access the necessary extensions easily.

9

Thanks cups. So can I ask, can I just use the MSSQL driver in place of the SQLSRV driver I currently have installed on my computer? So the only difference is the commands that I will use? I didn’t know that I could use two different drivers for Microsoft’s SQL server 2008. It seems that your suggestion would be a better choice!

Thanks!

10

…erm, not really sure about which version of sqlserver the PDO driver connects to - you will have to consult the documentation for that.

Using PDO you can then follow along with discussions, questions and best practice here and elsewhere – all you should have to do is put in the correct driver when you initiate the object.

The idea being that PDO offers you database independence, so in theory, at some later stage you can switch drivers but leave your code the same.

This will only work if you write sql statements which do not use RDBMS-specific functions of course.


// this is the line that will be different for you:
 $PDO = new PDO("mysql:host=localhost;dbname=test", "root", "****");

Then follow along with any other PDO tutorial …

As I said though, your server will have to running PHP5+, have the PDO extension enabled and have the PDO mssql driver enabled before this will work (check the output of


<?php
phpinfo();

Manual is the best place to start, which I posted earlier.

PDO can be tricky to get your head around at first, esp if you have not used oop before, so come back with Qs if you get stuck.

11

Thanks a lot for al your advice cups, I shall look into this now and see what is available to me on my server! Thanks again fro all your help!!!

:slight_smile: