Filtering Combobox PHP+MSSQL Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
suki-purewal
Forum Newbie
Posts: 2
Joined: Mon Apr 17, 2006 6:54 am

Filtering Combobox PHP+MSSQL Problem

Post by suki-purewal »

Helo, this is my first post........im a total n00b and im self taught and im having this realy niggly problem......hope someone should point out the obvoius that im not seeing :)

firsly il post the code and error msg in im keep getting when trying to display records based on a combobox selection from a previous page: -

heres the code that (theoretically) should retun all equipment details based on the location selected on the previous page-

Code: Select all

if(array_key_exists("cmdSearch", $_POST) )

{

open_db();

$query = "SELECT Equipment, Description, Location, Area, Bookable FROM Equipment WHERE Location = ".$_POST["cmbLocation"]."";

$result = mssql_query($query) or die ("error in connection");
there alot more code than this but the error msg is in regards to the WHERE Location = ".$_POST["cmbLocation"].""; bit

this is the eror msg i keep getting
Warning: mssql_query(): message: Line 1: Incorrect syntax near '='. (severity 15) in d:\inetpub\wwwroot\admin\manageequipment.php on line 54

Warning: mssql_query(): Query failed in d:\inetpub\wwwroot\admin\manageequipment.php on line 54
error in connection
here the code for the combobox on the previous page - cmbLocation

Code: Select all

<select name="cmbLocation" id="cmbLocation">
<?php

	open_db();
	
	$query = "SELECT COUNT (Equip_ID), Location FROM Equipment GROUP BY Location ORDER BY Location ASC";
	$result = mssql_query($query) or die ("error in connection");
	
	while($Equipment = mssql_fetch_assoc($result))
	
		{
		echo("<option value=".$Equipment["Equip_ID"].">".$Equipment["Location"]."</option>");
		}
		
	mssql_close();

?>
</select>
can anyone see what im doing wrong cos ive done the identical thing on another section and it works just dandy.

any/all advice is appreciated and thank you in advance :D
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Filtering Combobox PHP+MSSQL Problem

Post by timvw »

suki-purewal wrote: $query = "SELECT Equipment, Description, Location, Area, Bookable FROM Equipment WHERE Location = ".$_POST["cmbLocation"]."";
Assuming that Location is a CHAR type field, you should place values between quotes:

Code: Select all

SELECT foo FROM bar WHERE Location = 'my value'
Personally i find it easier/faster to write it like:

Code: Select all

$query = "SELECT foo FROM bar WHERE Location ='{$sql['cmbLocation']}'";
Btw, don't forget to validate user input before you use it in a query
suki-purewal
Forum Newbie
Posts: 2
Joined: Mon Apr 17, 2006 6:54 am

Post by suki-purewal »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


helo timvw

firstly thank you for the speedy reponse............i adjusted to the code as to your suggestion but now i  get the following msg 

[quote]Notice: Undefined variable: sql in d:\inetpub\wwwroot\admin\manageequipment.php on line 52[/quote]


the whole thing hnow looks like this

Code: Select all

<?php 
 
if(array_key_exists("cmdSearch", $_POST) )

{

open_db();

//$sql = ($_POST["cmbLocation"]) [color=red]my poor attemp to define $sql variable to fix the error msg[/color]
 
$query = "SELECT Equipment, Description, Location, Area, Bookable FROM Equipment WHERE Location = '{$sql['cmbLocation']}'";

$result = mssql_query($query) or die ("error in connection");
	
	if(mssql_num_rows($result) > 0) 
	{
	
	while($Equip = mssql_fetch_assoc($result))
		{
			echo("<tr><td>".$Equip["Equipment"]."&nbsp;</td>");
			echo("<td>".$Equip["Description"]."&nbsp;</td>");
			echo("<td>".$Equip["Location"]."&nbsp;</td>");
			echo("<td>".$Equip["Area"]."&nbsp;</td>");
			if($Equip["Bookable"] == "1") {$bookable = "Yes";} else {$bookable = "No";}
			echo("<td>".$bookable."&nbsp;</td>");
			echo("<td><a href='EditEquipInfo.php?ref=".$Equip["Equip_ID"]."'>Edit</a></td></tr>");
			
			
			
		}
	
	}
	else
		{
			echo("<td colspan='5' scope='col'>Sorry there are no results matching your search</td>");
		}
		
}
?>



oh and Location is a NVARCHAR in MSSQL


my apologies for sounding useless and thank in advance for your help and advice :lol:


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Yes, you should try to understand what my code does, not copy it blindfully :p

(i used $sql instead of $_POST.. and i only place data ready for use in a query in that array... With MSSQL that would probably go like:)

Code: Select all

$sql = array();
if (isset($_POST['whatever'])) {
  $sql['whatever'] = addslashes($_POST['whatever']);
}
Post Reply