HARDCORE QUESTION: php/mssql/stored procedures problem

[dgr]

hello

its a fairly difficult question, please help if you can!!!!

problem - trying to make a php script that executes a stored procedure (sp) on a MSSQL (not mysql) database.

the problem is that i can only refer to each column in the recordset by its number not its name.

heres the php function below. The stored procedure is called sp_authorise.

 
function f_authorise_user($database, $link){ //this function returns the user type value of the current user logged in. 
     $user_type = "0"; 
     If($_SESSION["current_user"] !== ""){ //current user is logged in 
          $query = mssql_init("sp_authorise", $link); 
               // Bind the parameters 
               mssql_bind($query, "@username", $_SESSION['current_user'], SQLCHAR, false, false, 20); 
                
               $result = mssql_execute($query); 
               while($row = mssql_fetch_row($result)){ 
                    $user_type = $row[3]; 
               } 
     } 
     return $user_type; //return user type. if 0, this means  no user logged in or user inactive. other values refer to a logged in user of varying type. 
} 

heres the sp:

CREATE PROCEDURE sp_authorise @username CHAR(20)
AS
SELECT * from [user] WHERE use_username=@username
GO

I can only refer to the column "use_type" in the database table by its recordset number, 3.

This will be a problem in the larger queries where i will have 200 or more columns (hence the need for SPs)


if anyone can help, very appreciated

- dgr

[Shad]

Can't see a specific problem there, but you should avoid using 'SELECT * FROM...' where possible. Reference each field and perhaps give it a new name for the recordset. Also, a point in passing; you should give your tables (and fields of course) more meaningful and unique names than just 'user'. As you obviously know it's a reserved word and needs [] around it.

Is it just the one field that you can't use by its name?

[dgr]

cheers for the info.

I ddin't realse that SELECT * differed from SELECT 1,2,3...

Will bear that in mind.

Each field is named by a 3 character suffix for the table + a unique name.

And the [user] question, I only realised that after starting to use SQL server.. I'm not from a microsoft background!!!

dgr

[DaBeeeenster]

Can you get it working by performing the same query but not within a stored procedure? I.e. just grab a recordset direct from the php script? Check that that works first, I'd suggest...

[dgr]

the answer:

i used

while($row = mssql_fetch_row($result)){

i should have used:

while($row = mssql_fetch_recordset($result)){


DaBeeeenster@
stored procedures are faster, more secure (no need to give user table access), and easier to code (no mixed SQL and script).

if you meant for dev, then good idea. but on a live system, bad idea!