php sql query help [EDIT]

[roseman5544]

Hey Guys.....I would really appreciate all if you guys could help me on this..

i am developing a work order aka business ticketing system where i have to search the tickets based on the given criteria of: ticket no

or department name or project name or the status of the ticket or severity of the ticket...

i have written the query as

 

 

 

$qry = "SELECT * FROM tickets WHERE ((ticket_id='$var1') OR ('$var1' IS NULL))

      and ((dept_id='$var2') OR ('$var2' IS NULL)) and ((proj_id='$var3') OR ('$var3' IS NULL))

      and ((status='$var4') oR ('$var4' IS NULL)) and ((severity='$var5') oR ('$var5' IS NULL))";

 

 

 

1. if i have a value for var1==15 inserted and the values of var2 to var5 are null, the whole query gives zero rows

2 If I have values for var1 till var4 but one null for the var5 still query result is zeros

 

I need to make the condition work only where value is not null otherwise condition should be avoided

 

Is there any way - please write the query for me - EDIT - I am working in PHP

[fugix]

insert

mysql_query($sql) or die(mysql_error());

[xyph]

Urgent eh?

[Maq]

roseman5544, DO NOT double post, I delete your other thread.  Also don't mark as urgent, please look at the rules:

http://www.phpfreaks.com/page/rules-and-terms-of-service

[DavidAM]

If var1 through var5 are coming from an HTML form, they will most likely NOT be NULL. They are probably empty strings. NULL is a special value and is NOT the same as an empty string. Using the code provided:

$qry = "SELECT * FROM tickets WHERE ((ticket_id='$var1') OR ('$var1' IS NULL))
       and ((dept_id='$var2') OR ('$var2' IS NULL)) and ((proj_id='$var3') OR ('$var3' IS NULL))
       and ((status='$var4') oR ('$var4' IS NULL)) and ((severity='$var5') oR ('$var5' IS NULL))";

 

and the first example:

1. if i have a value for var1==15 inserted and the values of var2 to var5 are null, the whole query gives zero rows

 

the resulting SQL will (most likely) be:

 

SELECT * FROM tickets WHERE ((ticket_id='15') OR ('15' IS NULL))
       and ((dept_id='') OR ('' IS NULL)) and ((proj_id='') OR ('' IS NULL))
       and ((status='') oR ('' IS NULL)) and ((severity='') oR ('' IS NULL))

 

Which will generally return an empty resultset (because '' is NOT NULL). By looking at the resulting SQL (as suggested by fugix), it is pretty clear what needs to be done: change all of the "IS NULL" conditions to "= '' ".

 

If var2 through var5 are, in fact, NULL, you will get the same SQL. If they are, instead, the string "NULL", then you have to remove the single quotes from around the $var# variables in the php code. However, this will result in errors when any of the values are not NULL.

 

The usual way to resolve this dilemma, is to build the query dynamically, including only the non-empty query parameters. Something along the lines of:

 

$where = '';
$qry = "SELECT * FROM tickets ";
if (!empty($var1)) $where = (empty($where) ? "WHERE " : "OR ") . "ticket_id = '$var1' ";
if (!empty($var2)) $where = (empty($where) ? "WHERE " : "OR ") . "dept_id = '$var2' ";
// and so forth ...
$qry .= $where;

 

[roseman5544]

Hi...firstly thanks a lot for your reply....this query has been executed by me....it works for individual search but when u select two criterias or more....i.e if we select $var1 and $var2 which have a match in the database.....the query reports an error...

My executed query is:

$where = '';

$qry = "SELECT * FROM tickets ";

if (!empty($var1)) $where = (empty($where) ? "WHERE " : "OR ") . "ticket_id = '$var1' ";

if (!empty($var2)) $where = (empty($where) ? "WHERE " : "OR ") . "dept_id = '$var2' ";

if (!empty($var3)) $where = (empty($where) ? "WHERE " : "OR ") . "proj_id = '$var3' ";

if (!empty($var4)) $where = (empty($where) ? "WHERE " : "OR ") . "status = '$var4' ";

if (!empty($var5)) $where = (empty($where) ? "WHERE " : "OR ") . "severity = '$var5' ";

$qry .= $where;

$result=mysql_query($qry) or die(mysql_error());

 

the error i am receiving on echoing the query for any two options selected(here i have selected 18 as my ticket no and project id is 4) is:

 

184You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR proj_id = '4'' at line 1

 

 

 

 

[DavidAM]

Oops! my mistake. We need to ADD each condition to the $where value. I changed each $where = to $where .=. Sorry about that.

 

$where = '';
$qry = "SELECT * FROM tickets ";
if (!empty($var1)) $where .= (empty($where) ? "WHERE " : "OR ") . "ticket_id = '$var1' ";
if (!empty($var2)) $where .= (empty($where) ? "WHERE " : "OR ") . "dept_id = '$var2' ";
if (!empty($var3)) $where .= (empty($where) ? "WHERE " : "OR ") . "proj_id = '$var3' ";
if (!empty($var4)) $where .= (empty($where) ? "WHERE " : "OR ") . "status = '$var4' ";
if (!empty($var5)) $where .= (empty($where) ? "WHERE " : "OR ") . "severity = '$var5' ";
$qry .= $where;
$result=mysql_query($qry) or die(mysql_error());

[roseman5544]

Hi...you r superb thaks a lot it worked...now i am trying to add another part in this that is of a session id....an employee session id....which would match with the actual creator of the tickets.....the query is as follows:

 

$var6=$_POST[$_SESSION["empId"]];

echo $var6;

$where = '';

$qry = "SELECT * FROM tickets";

if (!empty($var1)) $where .= (empty($where) ? "WHERE " : "AND ") . "ticket_id = '$var1' ";

if (!empty($var2)) $where .= (empty($where) ? "WHERE " : "AND ") . "dept_id = '$var2' ";

if (!empty($var3)) $where .= (empty($where) ? "WHERE " : "AND ") . "proj_id = '$var3' ";

if (!empty($var4)) $where .= (empty($where) ? "WHERE " : "AND ") . "status = '$var4' ";

if (!empty($var5)) $where .= (empty($where) ? "WHERE " : "AND ") . "severity = '$var5' ";

if (!empty($var6)) $where .= (empty($where) ? "WHERE " : "AND ") . "creator_id = '$var6'";

 

this doesnot execute.....can u please help me out.....

my problem is that the creator of the tickets and the currently logged in employee must be the same....

 

 

[mikesta707]

You seem to have a space in your column name. This is not very good practice because, as you can see, it starts creating errors when you try to use the column name in queries.

 

To get around this, you can try using backticks "`" (the symbol behind the 1 on your keyboard).

 

IE

if (!empty($var6)) $where .= (empty($where) ? "WHERE " : "AND ") . "`creator_id` = '$var6'";

 

Hope this helps

[roseman5544]

Hi....thanks a lot for your help...but my $var6 value is not coming withing the query:

$var6=$_POST[$_SESSION["empId"]];

echo $var6;

$where = '';

 

how do i fetch the session emoid into the $ variable.....

 

i have also tried

$var6=$_REQUEST[$_SESSION["empId"]];

 

All Help would be appreciated...

[mikesta707]

Oh sorry I must have missread your post. Firstly, in order to access session variables, you simply use the $_SESSION super global array. Kind of like what you did, but don't use it as a key to the $_POST array, just use it by itself. So

 

$var6 = $_SESSION["empId"]

 

will assign the value of the session variable "empId" to the variable $var6. This, however, is assuming that you are indeed setting this session somewhere in your website. Make sure to read up on sessions (that is a link to the w3schools.com tutorial on sessions. It has some older information but it is still mostly valid and useful). You also need to use the session_start() function in order to access sessions on your webpage. However, if you are using sessions, you should already know this

 

hope this helps