Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Aug 2012
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts

    sqlsrv_fetch_array() Error

    Hello everyone. I have a database (SQL server 2008 R2) that I finally got PHP5 installed on. I Can run basic simple querries against the DB without problem. I am now trying to allow a user to search the database by drop down menus and text fields to narrow a search down and am running into a few problems. Any advice would be greatly appreciated.

    Error Message:
    Warning: sqlsrv_query() expects at least 2 parameters, 1 given in C:\Inetpub\wwwroot\msag\index.php on line 34

    Here is the index.php File:
    Code:
    <center><h1><u> MSAG Database </u></h1>
    <br>			
    <br>
    		
    <?php include 'includes/menu.php'; ?>
    			
    <form method="post" action="index.php">
    <input type="hidden" name="submitted" value="true" />
    
    <label>Search Category:
    
    <select name="category">
    	<option value="StreetName">Street Name</option>
    </select>
    </label>
    
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<label>Search Criteria: <input type="text" name="criteria" /></label>
    
    <input type="submit" name="search" value="Search"/>
    
    </form>
    
    </center>
    <?php
    
    include 'includes/db/connect.php';
    
    if(isset($_POST['search']) || isset($_POST['submit']))
    {	 
    	$category = $_POST['category'];
    	$criteria = $_POST['criteria'];
    	
    	$query = "SELECT * FROM MSAG WHERE $category LIKE %$criteria% ORDER BY StreetName ASC";
    	$resource=sqlsrv_query($query);
    }	
    ?>
    <form action="" method="post">
    		<table align="center" border="1" cellpadding="2" cellspacing="2" width="100%" >
    		<tr align = 'center'>
    			<td><b>Street Name</b></td>
    		</tr>
    <?php
    while($result=sqlsrv_fetch_array($resource))
    { 
    	echo "
    <tr align = center>
    <td>".$result['StreetName']."</td>
    <td><a href=\"modify2.php?id=".$result['StreetID']."\"><input type=\"button\" name = \"details\" value = \"Details\" /></a></td>
    </tr>
    "; 		
    }
    ?>
    </table>
    </form>
    Ultimately I would like to be able to have several different drop boxes and text fields to search by. I thank you all in advance for your time and help.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Your missing the connection resource in the query call. It's required as the first parameter. Since you likely set it up in that connect include, we don't know the name for it, but if I had to guess it'd be $db or $con which is pretty typical.
    You may want to check as well for PDO drivers, it may be easier to use the PDO abstraction instead.

  • #3
    New Coder
    Join Date
    Aug 2012
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts
    This is my connect file.

    PHP Code:
    <?php
    date_default_timezone_set
    ('America/New_York');
    error_reporting(E_ALL); ini_set('display_errors''1');
    $connectionInfo = array("Database"=>"****","UID"=>"****","PWD"=>"******");
    $serverName "*******";
    $conn sqlsrv_connect($serverName$connectionInfo);
    if (
    $conn) {
        echo 
    "Connection established. <br / >";
    } else{
        echo 
    "Whhoooops not working. <br />";
        }
    ?>
    Last edited by willscarlet; 11-19-2012 at 05:28 PM.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yep, take $conn and give it as the first argument to the sqlsrv_query call.
    If that's real information, I'd suggest editing it out.

  • #5
    New Coder
    Join Date
    Aug 2012
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Ok, that cleared one error. Now it still shows one more error though:
    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\Inetpub\wwwroot\msag\index.php on line 43

    New code is:
    PHP Code:
    <center><h1><u> MSAG Database </u></h1>
    <br>            
    <br>
            
    <?php include 'includes/menu.php'?>
                
    <form method="post" action="index.php">
    <input type="hidden" name="submitted" value="true" />

    <label>Search Category:

    <select name="category">
        <option value="StreetName">Street Name</option>
    </select>
    </label>

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<label>Search Criteria: <input type="text" name="criteria" /></label>

    <input type="submit" name="search" value="Search"/>

    </form>

    </center>
    <?php

    include 'includes/db/connect.php';

    if(isset(
    $_POST['search']) || isset($_POST['submit']))
    {     
        
    $category $_POST['category'];
        
    $criteria $_POST['criteria'];
        
        
    $query "SELECT * FROM MSAG WHERE $category LIKE %$criteria% ORDER BY StreetName ASC";
        
    $resource=sqlsrv_query($conn$query);
    }    
    ?>
    <form action="" method="post">
            <table align="center" border="1" cellpadding="2" cellspacing="2" width="100%" >
            <tr align = 'center'>
                <td><b>Street Name</b></td>
            </tr>
    <?php
    while($result=sqlsrv_fetch_array($resource))

        echo 
    "
    <tr align = center>
    <td>"
    .$result['StreetName']."</td>
    <td><a href=\"modify2.php?id="
    .$result['StreetID']."\"><input type=\"button\" name = \"details\" value = \"Details\" /></a></td>
    </tr>
    "
    ;         
    }
    ?>
    </table>
    </form>

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    That indicates that your query has failed.
    Use this to find out why: $resource=sqlsrv_query($conn, $query) or die(print_r(sqlsrvr_errors(), true));.
    Interesting enough, the sqlsrv_query appears to accept either a string query or a bindable prepared type. Neat.

  • #7
    New Coder
    Join Date
    Aug 2012
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts
    okay, when I added your code, I got this print out when I hit submit:
    Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 156 [code] => 156 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'ORDER'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'ORDER'. ) )

    So I added '' around the Criteria variable:
    PHP Code:
    $query "SELECT * FROM MSAG WHERE $category LIKE %$criteria% ORDER BY StreetName ASC"
    after that, if I just hit sumbit on the page, it will output ever record in the database, however, if I try and fill in the search critera I get this error:
    PHP Code:
    NoticeUndefined variableresource in C:Inetpubwwwrootmsagindex.php on line 44 Warningsqlsrv_fetch_array() expects parameter 1 to be resourcenull given in C:Inetpubwwwrootmsagindex.php on line 44 
    I also get the above error when The page first loads as well.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Is whatever $catagory a string datatype? If so, $criteria also has to be a string, so you need to block it into single quotations.
    The notice is caused by being unable to construct the query since its blocked into an isset check on $_POST['search']. If you don't post the form, it will still attempt to execute the while, so when you first load the form it doesn't have a query to execute and triggers a notice that the variable $resource doesn't exist (and therefore cannot be fetched). Move the block with the fetch into the same block that queries.
    I'd also pull this off: || isset($_POST['submit']). You shouldn't bother searching for a submit button; IE (at least olderish versions) refuse to accept that a form with just that button is valid if you hit the enter key instead of clicking. Therefore it will not consider it successful when passing to the server, so even if it is submitted it will not provide it to the server (in violation of W3 successful field standard). So you should simply check for the fields you are interested in.

  • #9
    New Coder
    Join Date
    Aug 2012
    Posts
    65
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I am sorry sir. I thank you for all your help so far, but I am officially lost now. I tried moving around the while loop with the fetch array and it still would not work right but I believe I probably did it wrong. Oh, and the 'Category' is a dropdown down menu and the criteria is the value to search the category with:
    Code:
    <form method="post" action="index.php">
    <input type="hidden" name="submitted" value="true" />
    
    <label>Search Category:
    
    <select name="category">
    	<option value="StreetName">Street Name</option>
    </select>
    </label>
    
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<label>Search Criteria: <input type="text" name="criteria" /></label>
    
    <input type="submit" name="search" value="Search"/>
    
    </form>

    This is my code, thusfar:
    PHP Code:
    <center><h1><u> MSAG Database </u></h1>
    <br>            
    <br>
            
    <?php include 'includes/menu.php'?>
                
    <form method="post" action="index.php">
    <input type="hidden" name="submitted" value="true" />

    <label>Search Category:

    <select name="category">
        <option value="StreetName">Street Name</option>
    </select>
    </label>

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<label>Search Criteria: <input type="text" name="criteria" /></label>

    <input type="submit" name="search" value="Search"/>

    </form>

    </center>
    <?php

    include 'includes/db/connect.php';

    if(isset(
    $_POST['search']))
    {     
        
    $category $_POST['category'];
        
    $criteria $_POST['criteria'];
        
        
    $query "SELECT * FROM MSAG WHERE $category LIKE '%$criteria%' ORDER BY StreetName ASC";
        
    $resource=sqlsrv_query($conn$query) or die(print_r(sqlsrv_errors(), true));
    }    
    ?>
    <form action="" method="post">
            <table align="center" border="1" cellpadding="2" cellspacing="2" width="100%" >
            <tr align = 'center'>
                <td><b>Street Name</b></td>
                <td><b>Details</b></td>
            </tr>
    <?php
    while($result=sqlsrv_fetch_array($resource))

        echo 
    "
    <tr align = center>
    <td>"
    .$result['StreetName']."</td>
    <td><a href=\"modify2.php?id="
    .$result['StreetID']."\"><input type=\"button\" name = \"details\" value = \"Details\" /></a></td>
    </tr>
    "
    ;         
    }
    ?>
    </table>
    </form>


  •  

    Posting Permissions

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