sqlsrv_fetch_array() Error

[willscarlet]

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:

<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.

[Fou-Lu]

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.

[willscarlet]

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 />";
    }
?>

[Fou-Lu]

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.

[willscarlet]

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>

[Fou-Lu]

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.

[willscarlet]

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:

Notice: Undefined variable: resource in C:Inetpubwwwrootmsagindex.php on line 44 Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, null given in C:Inetpubwwwrootmsagindex.php on line 44 


I also get the above error when The page first loads as well.

[Fou-Lu]

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.

[willscarlet]

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:

<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>