Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Null value in a $_GET string

    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!
    Last edited by poijoy; 07-22-09 at 05:06.

  2. #2
    Join Date
    Jul 2009
    Posts
    4
    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!
    Last edited by poijoy; 07-22-09 at 05:04.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2009
    Posts
    4
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2009
    Posts
    4
    Quote Originally Posted by healdem
    you need to develop some debugging skills
    heh, too true! Guess it's time to hit the books

    Thanks for the pointers though, it's been really helpful

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •