Using form to filter SQL database results

[ncncnc]

Hi,

 

I'm currently learning PHP through tutorials and I am working on a webpage that filters statistics (crime records) from an SQL table and filters them by an option of years that the user has picked from a dropdown box.

 

$desiredyear = $_POST['year'] is the year picked from the drop down box on a different page.

 

I did not encounter any problems simply displaying the entire table until I tried to filter the crimes by year. I keep getting this error message.

 

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\1.php on line 40

 

Here is the error line.

 

while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 

 

And here is my code so far

 

 <?php 

$server = 'SQL2008';
$connectionInfo = array( "Database"=>"rde_400792");
$conn = sqlsrv_connect($server,$connectionInfo);

$desiredyear = $_POST['year'];


$describeQuery='select year, force, homicide from crime where year = $desiredyear ';
$results = sqlsrv_query($conn, $describeQuery);

echo '<table border="1" BORDERCOLOR=Black>';
echo '<tr><th bgcolor = "LightBlue">Year</th><th bgcolor = "LightBlue" >Force</th><th  bgcolor = "LightBlue">Homicide</th></tr>';


while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 

{           echo '<tr>';
   echo '<td >' .$row['year'].'</td>'; 
   echo '<td>' .$row['force'].'</td>'; 
   echo '<td>' .$row['homicide'].'</td>'; 
   echo '</tr>';
} 

echo '</table>';
sqlsrv_close($conn); 

 

Please can somebody help me fix this? It's driving me mad and I've exhausted my limited knowledge on the subject. I just don't understand what I'm doing wrong.

[litebearer]

what type/format is the year field in your database?

[DavidAM]

I'm not familiar with using SQL Server with PHP, but that error usually indicates that the query failed:

 

$describeQuery='select year, force, homicide from crime where year = $desiredyear ';

 

1) PHP will not interpret variables inside a string that is delimited with single-quotes. This is likely the problem. Use double-quotes around the string or use concatenation.

 

2) Is "year" a reserved word in SqlServer? (It is not in mySql.)

 

3) Is the YEAR column an integer (it should be). If it is not, you are going to need quotes around the value.

 

[ncncnc]

Thanks a lot for the replies guys.

 

Year is a four-byte signed integer. This is what I was told to make it in the tutorial.

 

I'm not familiar with using SQL Server with PHP, but that error usually indicates that the query failed:

 

$describeQuery='select year, force, homicide from crime where year = $desiredyear ';

 

1) PHP will not interpret variables inside a string that is delimited with single-quotes. This is likely the problem. Use double-quotes around the string or use concatenation.

 

2) Is "year" a reserved word in SqlServer? (It is not in mySql.)

 

3) Is the YEAR column an integer (it should be). If it is not, you are going to need quotes around the value.

 

 

Do you mean to make it look like this?

$describeQuery="select year, force, homicide from crime where year = $desiredyear ";

 

I'm unable to test this now because I'm at work, but could it really be that simple?

[DavidAM]

Most likely, that is the problem

 

$year = 2012;
$sql = "select year, force, homicide from crime where year = $year ";
echo $sql;

$sql  = 'select year, force, homicide from crime where year = $year ';
echo $sql

 

output would be

select year, force, homicide from crime where year = 2012

select year, force, homicide from crime where year = $year

The second query is invalid because "$year" is not a valid number to compare against the year column; and, since it is a string, the server will either look for a column named "$year" or complain about an illegal character (or something).

[ncncnc]

Most likely, that is the problem

 

$year = 2012;
$sql = "select year, force, homicide from crime where year = $year ";
echo $sql;

$sql  = 'select year, force, homicide from crime where year = $year ';
echo $sql

 

output would be

select year, force, homicide from crime where year = 2012

select year, force, homicide from crime where year = $year

The second query is invalid because "$year" is not a valid number to compare against the year column; and, since it is a string, the server will either look for a column named "$year" or complain about an illegal character (or something).

 

Thanks for explaining that, I had no idea.

I'll give it a go tomorrow and let you know how it goes.

 

Can you see any other reason why this wouldn't work?