[SOLVED] PHP & MSSQL: Error converting data type varchar to int

[stitch23]

Hi all!

I am having a bit of trouble with executing a stored procedure in MSSQL using PHP. Hopefully you can make sense of this, I am using PHP 5.2.6 and Microsoft SQL Server 2005 (Developer Edition), I have been having success using the two together until I tried to execute a stored procedure.

 

To illustrate it better I will give you my code, firstly the Stored Procedure in MSSQL:

CREATE PROCEDURE [dbo].[updateStatus] @userID int, @statusID int, @eta nchar(10), @notes nvarchar(MAX)
AS

BEGIN
UPDATE dbo.tblUsers
SET statusID = @statusID, eta = @eta, notes = @notes WHERE userID = @userID
END

 

Secondly, the PHP code:

$stmt = mssql_init('dbo.updateStatus');
mssql_bind($stmt, '@userID', 1,	int);
mssql_bind($stmt, '@statusID', 5, int);
mssql_bind($stmt, '@eta', '1:00pm');
mssql_bind($stmt, '@notes', 'test notes');

mssql_execute($stmt);

 

And thirdly, the error:

Fatal error: Only variables can be passed by reference in C:\xampp\htdocs\OfficeStatusSQL\updateStatus.php on line 5

 

Funnily enough if I use the code:

mssql_query('dbo.updateStatus @userID=1, @statusID=1, @eta=3, @notes=3');

It works fine but I am unable to use any variables in it so it is pretty much useless.

 

Please be aware that I am certainly no expert on PHP so you will have to make your answers pretty idiot-proof.

 

Thanks!

- Ben

 

[stitch23]

Just a quick update, I have managed to get it to work using:

$uid = "3";
$sid = "2";
$eta = "TestETA";
$notes = "Test";

mssql_query('dbo.updateStatus @userID = '.$uid.', @statusID = '.$sid.', @eta = '.$eta.', @notes = '.$notes.'');

 

But I can not use any spaces or special characters in the variables, for example - if I changed $eta = "TestETA" to $eta = "Test ETA" (note the space) I get the error:

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near 'ETA'. (severity 15) in C:\xampp\htdocs\OfficeStatusSQL\updateStatus.php on line 16

 

I'm not sure if this method is the right way of updating a MSSQL Database.

 

Can anyone tell me which method I should be using and how to fix the problems I am having with either?

[redarrow]

Trie trim

 

<?php

$eta = "Test ETA";

$eta=trim(mysql_real_ecsape_string($_POST['eta']));

?>

[stitch23]

Finally got it working after mucking around all day.

 

$uid = "3";
$sid = "2";
$eta = "'TEST ETA'";
$notes = "'Test Blah blah blah!'";

mssql_query('dbo.updateStatus @userID = '.$uid.', @statusID = '.$sid.', @eta = N'.$eta.', @notes = N'.$notes.'');

 

Works great.