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

Filtering Combobox PHP+MSSQL Problem

Postby suki-purewal » Mon Apr 17, 2006 7:19 am

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-

Syntax: [ Download ] [ Hide ]
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

Syntax: [ Download ] [ Hide ]
<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
suki-purewal
Forum Newbie
 
Posts: 2
Joined: Mon Apr 17, 2006 6:54 am

Re: Filtering Combobox PHP+MSSQL Problem

Postby timvw » Mon Apr 17, 2006 7:25 am

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:

Syntax: [ Download ] [ Hide ]

SELECT foo FROM bar WHERE Location = 'my value'

 


Personally i find it easier/faster to write it like:

Syntax: [ Download ] [ Hide ]
$query = "SELECT foo FROM bar WHERE Location ='{$sql['cmbLocation']}'";


Btw, don't forget to validate user input before you use it in a query
timvw
DevNet Master
 
Posts: 4897
Joined: Tue Jan 20, 2004 12:11 am
Location: Leuven, Belgium

Postby suki-purewal » Mon Apr 17, 2006 7:55 am

feyd | Please use
Syntax: [ Download ] [ Hide ]
,
[syntax=php]and
Syntax: [ Download ] [ Hide ]
  1. tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: Posting Code in the Forums to learn how to do it too. 
  2.  
  3.  
  4.  
  5.  
  6.  
  7. helo timvw 
  8.  
  9.  
  10.  
  11. firstly thank you for the speedy reponse............i adjusted to the code as to your suggestion but now i get the following msg  
  12.  
  13.  
  14.  
  15. Notice: Undefined variable: sql in d:\inetpub\wwwroot\admin\manageequipment.php on line 52
     
  16.  
  17.  
  18.  
  19.  
  20.  
  21. the whole thing hnow looks like this 
Syntax: [ Download ] [ Hide ]
<?php

 

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



{



open_db();



//$sql = ($_POST["cmbLocation"]) <span style="color: red">my poor attemp to define $sql variable to fix the error msg</span>

 

$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
Syntax: [ Download ] [ Hide ]
,
[syntax=php]and
Syntax: [ Download ] [ Hide ]
  1. tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: Posting Code in the Forums to learn how to do it too. 
suki-purewal
Forum Newbie
 
Posts: 2
Joined: Mon Apr 17, 2006 6:54 am

Postby timvw » Mon Apr 17, 2006 9:48 am

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

Syntax: [ Download ] [ Hide ]
$sql = array();

if (isset($_POST['whatever'])) {

  $sql['whatever'] = addslashes($_POST['whatever']);

}
timvw
DevNet Master
 
Posts: 4897
Joined: Tue Jan 20, 2004 12:11 am
Location: Leuven, Belgium


Return to PHP - Code

Who is online

Users browsing this forum: No registered users and 17 guests