Results 1 to 5 of 5

Thread: HARDCORE QUESTION: php/mssql/stored procedures problem

  1. #1
    dgr
    dgr is offline
    Senior Member
    Join Date
    Jul 2003
    Posts
    621
    Thanks
    0
    Thanked
    0 times in 0 posts

    HARDCORE QUESTION: php/mssql/stored procedures problem

    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.




    PHP Code:
     
    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'], SQLCHARfalsefalse20); 
                    
                   
    $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
    dothan 745 @ 2.4ghz | 2gb Corsair XMS (2-3-3-6) | dual raptors (raid0) | ATI 9700pro | CM201 | dual lg 1810

  2. #2
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,782
    Thanks
    23
    Thanked
    42 times in 25 posts
    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?
    Simon


  3. #3
    dgr
    dgr is offline
    Senior Member
    Join Date
    Jul 2003
    Posts
    621
    Thanks
    0
    Thanked
    0 times in 0 posts
    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
    dothan 745 @ 2.4ghz | 2gb Corsair XMS (2-3-3-6) | dual raptors (raid0) | ATI 9700pro | CM201 | dual lg 1810

  4. #4
    Goat Boy
    Join Date
    Jul 2003
    Location
    Alexandra Park, London
    Posts
    2,428
    Thanks
    0
    Thanked
    0 times in 0 posts
    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...
    "All our beliefs are being challenged now, and rightfully so, they're stupid." - Bill Hicks

  5. #5
    dgr
    dgr is offline
    Senior Member
    Join Date
    Jul 2003
    Posts
    621
    Thanks
    0
    Thanked
    0 times in 0 posts
    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!
    dothan 745 @ 2.4ghz | 2gb Corsair XMS (2-3-3-6) | dual raptors (raid0) | ATI 9700pro | CM201 | dual lg 1810

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •