PHP parameterized query SQL server

PHP
1

I am trying to write my first parameterized query using PHP against a 2014 SQL server database. I have done this in vb.net many times and know how to do this, but i’m not getting it to work.

I started by going to google and searching for working code examples. Once I found one, I modified it for my website. It works… as in it’s printing out all the proper messages to the screen, but the data in the database is not updating.

//connect to the database.
$serverName = "Localhost";
$connectionInfo = array( "Database"=>"pEvents", "UID"=>"PUser", "PWD"=>"P@$$" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

/* Set up the parameterized query. */  
$tsql = "UPDATE tblevents SET tblevents.Photo = ?, tblevents.Title = ?, tblevents.Unlist = ?, tblevents.Sort = ?, tblevents.Description = ? WHERE (((tblevents.EventID)=?));";  

/* Set parameter values. */  
$params = array($_POST['eventID'], $_POST['photo'], $_POST['title'], $_POST['unlist'], $_POST['sort'], $_POST['description']);  

/* Prepare and execute the query. */  
$stmt = sqlsrv_query($conn, $tsql, $params);  
if ($stmt) {  
    echo "Event Updated!\n";  
} else {  
    echo "Event NOT Updated!\n";  
    die(print_r(sqlsrv_errors(), true));  
}  

/* Free statement and connection resources. */  
sqlsrv_free_stmt($stmt);  
sqlsrv_close($conn);
2

It’s easier to read your code if it is formatted properly. I’ve done it this time, but the next time you wish to post code, just place three backticks (`) on the line before the code and three backticks on the line after the code. Or you could highlight the code and select the </> icon from the icon list above the post edit area.

3

thanks for doing that for me.

4

I don’t think it would, but I guess it’s possible those extra parentheses are making the WHERE not be truthy.

My money is on the use of question mark placeholders and the order of the parameters passed to it (with named parameters the names are important but not the order, with question mark placeholders there are no names, but order is important). So the put-together query would be

UPDATE tblevents 
SET 
 tblevents.Photo = $_POST['eventID'] 
, tblevents.Title = $_POST['photo'] 
, tblevents.Unlist = $_POST['title'] 
, tblevents.Sort = $_POST['unlist'] 
, tblevents.Description = $_POST['sort'] 
WHERE tblevents.EventID = $_POST['description']

That doesn’t look right to me and I doubt an id would equal a description.

5

That makes sense. I can’t believe I overlooked the obvious. If I wanted to use named parameters, how would I do that?

6

You can use whatever names make sense to you. I try to give names that are descriptive enough that I can know what they are with a glance. eg.

... tblEvents.Photo = :photo, ...
in the query. Then in the parameters array use that name as the key. eg.
... ':photo' => $_POST['photo'], ...

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.