Null value in a $_GET string

poijoy · #1 (**permalink**) 07-22-09, 01:19

Hi guys
I'm hoping there's someone out there who can help! I'm trying to run a report and so far it's going ok apart from one thing. There are 7 options which could be searched under and I've added them all to one query. This is great if you want to return a specific result but not so great if you want to return a group or results based on 2 of the search options. The code I'm using is:

PHP Code:


			
$StartDate=$_GET['StartDate'];
$EndDate=$_GET['EndDate'];
$Analyst=$_GET['Analyst'];
$Assist=$_GET['Assist'];
$Incident=$_GET['Incident'];
$Process=$_GET['Process'];
$Service=$_GET['Service'];

$query = "SELECT Date, Analyst, Assist, Incident, Process, Service
FROM data
where Date between ('$StartDate') and ('$EndDate')
and Analyst = ('$Analyst')
and Assist = ('$Assist')
and Incident = ('$Incident')
and Process = ('$Process')
and Service = ('$Service')
Order by date";
$result = mysql_query($query) or die(mysql_error());

echo "<table width='600' border='10' cellspacing='2' cellpadding='5' align='center'><tr>";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "<th>".mysql_field_name($result, $i)."</th>";
}
echo "</tr>";
while($row = mysql_fetch_array($result)){
echo "<tr>";
for($i = 0; $i < mysql_num_fields($result); $i++){
echo "<td>". $row[$i] ."</td>";
}
echo "</tr>";
}

echo "</table>";

The search does work but I would like for any of my $_GET strings which don't have a value to return a wildcard so as to allow the user some flexibility when choosing search parameters.

The end result I'm after is for a user to select whichever range from whichever column they want and get results based on that criteria only. I would also like the users to be able to search by multiple columns if the mood takes them, all within on HTML form.

I'm sure this is plainly obvious but I've never user MYSQL or PHP before so please excuse me if the code is complete mess!

poijoy · #2 (**permalink**) 07-22-09, 01:53

Hmmm, just hada bit of a daft idea which didn't seem to work, looks like this:

PHP Code:


			
<?php
//call connect function
include 'connect.php';

//define strings
$StartDate=$_GET['StartDate'];
$EndDate=$_GET['EndDate'];
$Analyst=$_GET['Analyst'];
$Assist=$_GET['Assist'];
$Incident=$_GET['Incident'];
$Process=$_GET['Process'];
$Service=$_GET['Service'];

//Deal with Null Strings
$nullchange = "SELECT *, IFNULL(Incident, '%'), IFNULL(Analyst, '%'), IFNULL(Assist, '%'), IFNULL(Process, '%'), IFNULL(Service, '%')
from data";

//run $nullchange
mysql_query($nullchange) or die(mysql_error());

//define query
$query = "SELECT Date, Incident, Analyst, Assist, Process, Service
FROM data
where Date between ('$StartDate')  and ('$EndDate')
and Incident LIKE ('$Incident')
and Process LIKE ('$Process')
and Service LIKE ('$Service')
and Assist LIKE ('$Assist')
and Analyst LIKE ('$Analyst')
Order by date";

//run query and error reporting
$result = mysql_query($query) or die(mysql_error());


//return results in table
echo "<table width='600' border='10' cellspacing='2' cellpadding='5' align='center'><tr>";
for($i = 0; $i < mysql_num_fields($result); $i++){
    echo "<th>".mysql_field_name($result, $i)."</th>";
}
echo "</tr>";
while($row = mysql_fetch_array($result)){
    echo "<tr>";
    for($i = 0; $i < mysql_num_fields($result); $i++){
        echo "<td>". $row[$i] ."</td>";
    }
    echo "</tr>";
}

echo "</table>";
?>

If someone would be kind enough to let me know if I'm heading in the right direction it would be much appreciated!

healdem · #3 (**permalink**) 07-22-09, 02:33

PHP Code:


			
$query = "SELECT Date, Analyst, Assist, Incident, Process, Service

FROM data

where Date between ('$StartDate') and ('$EndDate')";

//go through each parameter

//check its been set, and if so its length is more than 1 character

//if so then append that as part of the query 

if (isset($_GET['Analyst'] == true and strlen($_GET['Analyst'])>1)

{ $query .= " and Analyst = ('".$_GET['Analyst']."'")

//and so on

$query .= "Order by date";

you could use wild card character is you wish, but it could seriously impact query performance

poijoy · #4 (**permalink**) 07-22-09, 04:05

Quote:

Originally Posted by healdem

PHP Code:


			
$query = "SELECT Date, Analyst, Assist, Incident, Process, Service

FROM data

where Date between ('$StartDate') and ('$EndDate')";

//go through each parameter

//check its been set, and if so its length is more than 1 character

//if so then append that as part of the query 

if (isset($_GET['Analyst'] == true and strlen($_GET['Analyst'])>1)

{ $query .= " and Analyst = ('".$_GET['Analyst']."'")

//and so on

$query .= "Order by date";

you could use wild card character is you wish, but it could seriously impact query performance

That's great, thanks for the help

I'm getting a parsing error on running the command though, I decided to just test it with the Analyst column before going through the whole lot using this:

PHP Code:


			
//define query

$query = "SELECT Date, Analyst, Assist, Incident, Process, Service

FROM data

where Date between ('$StartDate') and ('$EndDate')";

//go through each parameter

//check its been set, and if so its length is more than 1 character

//if so then append that as part of the query 

if (isset($_GET['Analyst']) == true and strlen($_GET['Analyst'])>1)

($query .= " and Analyst = ('".$_GET['Analyst']."%")

//and so on

$query .= "Order by date";

//run query and error reporting

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

I'm getting an parse error returned at:

PHP Code:


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

Do I need to give the queries unique names, rather than calling them all $query, and then run them in order or is that just a completely noobish thing to ask

As you can probably tell, I'm completely new to any form or coding beyond HTML, it's a touch alien to me

healdem · #5 (**permalink**) 07-22-09, 04:50

so what is the mysql error
at first glance Id guess its going to be a missing closing bracket
incidentally you don't need a to use brackets unless the terms are to be treated as equal precendece.

eg
where x = a and (y=b or z=c)

you need to develop some debugging skills
one of which is actually examining what the system tells you rather than what you think it is doing
so examine the SQL that is being sent to the sql engine and see if looks valid
examine the error message(s)

this site isn't really focussed on teaching, there are far better resources for learnign out there, notably W3Schools Online Web Tutorials and PHP: Hypertext Preprocessor is an invaluable resource for php

I'm sure most people here will try to help, but there may be a better way of learning

healdem · #6 (**permalink**) 07-22-09, 04:56

btw just noticed you are using a column called date.. I'm pretty certain date is a reserved word in MySQL, if so using it can cause all manner of problems.

as it turns out date isn't, but just for reference MySQL :: MySQL 5.1 Reference Manual :: 8.3 Reserved Words