ncncnc Posted March 23, 2012 Share Posted March 23, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/ Share on other sites More sharing options...
litebearer Posted March 23, 2012 Share Posted March 23, 2012 what type/format is the year field in your database? Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/#findComment-1330560 Share on other sites More sharing options...
DavidAM Posted March 23, 2012 Share Posted March 23, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/#findComment-1330561 Share on other sites More sharing options...
ncncnc Posted March 23, 2012 Author Share Posted March 23, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/#findComment-1330564 Share on other sites More sharing options...
DavidAM Posted March 23, 2012 Share Posted March 23, 2012 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). Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/#findComment-1330568 Share on other sites More sharing options...
ncncnc Posted March 23, 2012 Author Share Posted March 23, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/259576-using-form-to-filter-sql-database-results/#findComment-1330570 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.