#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0

    PHP MSSQL - sqlsrv stored procedure - again


    I am trying to get a handle on the interaction between MSSQL (sqlsrv) and PHP. I got one page to work fine, but when I tried to duplicate the code, I was unable to get the new page to show results - no errors, but no results.

    So, I thought I would post the code and see if anyone could help me out and tell me what I am doing wrong... btw, the stored procedure executes as expected in MSSQL.

    PHP Code:
    /* Define the Transact-SQL query. Use question marks (?) in place of  the parameters to be passed to the stored procedure */ 

    $tsql_callSP "{call category_search( ?, ?, ?, ?, ?, ?, ?, ?, ? )}";  

    /* Define the parameter array. By default, the first parameter isan INPUT parameter. The second parameter is specified as an OUTPUT parameter. To ensure data type integrity, output parameters 

    should be initialized before calling the stored procedure, or the desired PHPTYPE should be specified in the $params array.*/ 

    $opt_out_eap $_SESSION['id_eap'];
    $opt_out_company $_SESSION['id_company'];
    $content_languages_list $_SESSION['id_language'];
    $assignments_list $id_category;
    $publish 'public';
    $id_eap $_SESSION['id_eap'];
    $id_element $id_element;
    $title '';
    $id_cr '';

    $params = array(
    array(
    $opt_out_eap,SQLSRV_PARAM_IN),
    array(
    $opt_out_company,SQLSRV_PARAM_IN),
    array(
    $content_languages_list,SQLSRV_PARAM_IN),
    array(
    $assignments_list,SQLSRV_PARAM_IN),
    array(
    $publish,SQLSRV_PARAM_IN),
    array(
    $id_eap,SQLSRV_PARAM_IN),
    array(
    $id_element,SQLSRV_PARAM_IN),
    array(
    $titleSQLSRV_PARAM_OUT),
    array(
    $id_crSQLSRV_PARAM_OUT)              
    );  

    /* Execute the query. */ 
    $stmtTerms sqlsrv_query$conn$tsql_callSP$params); 

    if( 
    $stmtTerms === false ) {      
    echo 
    "Error in executing statement Terms.\n";      
    die( 
    print_rsqlsrv_errors(), true)); }  


    /* Display the value of the output parameters. */  
    while ($obj=sqlsrv_fetch_array($stmtTerms)) {      
        
    // SET PARAMETERS - SET TERMS     
        
    $title$obj->title;
        
    $id_cr=$obj->id_cr;
        echo 
    'yes'; echo $title;
    print_rsqlsrv_errors(), true);

    My stored procedure executes without problem...

    Code:
    	
    -- Add the parameters for the stored procedure here @opt_out_eap varchar(MAX) = '', 
    @opt_out_company varchar(MAX) = '', 
    @content_languages_list varchar(MAX) = '', 
    @assignments_list varchar(MAX) = '', 
    @publish varchar(10) = 'public', 
    @id_eap varchar (10) = '', 
    @id_element varchar (10) = '', 
    @title nvarchar (500) OUTPUT,
    @id_cr varchar (10) OUTPUT 
     
    AS 
    BEGIN 	
    -- SET NOCOUNT ON added to prevent extra result sets from 	-- interfering with SELECT statements. 	
    SET NOCOUNT ON;      
    
    -- Insert statements for procedure here 			
    
    SELECT TOP 5 title, id_cr, id_element, COUNT(id_cr) OVER () AS total 	 			
    FROM [lml].[dbo].[content_data_active] 			WHERE   			
    (	opt_out_eap NOT LIKE '%' + @opt_out_eap + '%'    		AND opt_out_company NOT LIKE '%' + @opt_out_company + '%'    				
    AND content_languages_list LIKE '%' + @content_languages_list + '%' 				AND assignments_list LIKE '%' + @assignments_list + '%' 				
    AND publish = @publish 				
    AND id_element = @id_element) 
    ORDER BY title

    Comments on this post

    • Jacques1 disagrees : Please stop closing your threads. Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,397
    Rep Power
    594
    That probably means there were no records that met the search criteria. You can test it like this:
    PHP Code:
    /* Execute the query. */ 
    $stmtTerms sqlsrv_query$conn$tsql_callSP$params); 

    if( 
    $stmtTerms === false ) {      
    echo 
    "Error in executing statement Terms.\n";      
    die( 
    print_rsqlsrv_errors(), true)); }
    if (
    sqlsrv_num_rows($stmtTerms)==0) {
       die(
    "No records found.");

    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    15
    Rep Power
    0

    Thank you for the code...


    Thank you so much for the code, I was able to see that one of my variables was inserting the wrong value! Now it works...

    I have one more problem, but I will post as a different thread and close this one... thank you again!!!


    Originally Posted by gw1500se
    That probably means there were no records that met the search criteria. You can test it like this:
    PHP Code:
    /* Execute the query. */ 
    $stmtTerms sqlsrv_query$conn$tsql_callSP$params); 

    if( 
    $stmtTerms === false ) {      
    echo 
    "Error in executing statement Terms.\n";      
    die( 
    print_rsqlsrv_errors(), true)); }
    if (
    sqlsrv_num_rows($stmtTerms)==0) {
       die(
    "No records found.");


IMN logo majestic logo threadwatch logo seochat tools logo