Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: Problem with retrieving information

    I am not*, I repeat *not great with PHP or SQL yet. However, I have got a database up to track runs for my limo service.. I am attempting now, to write a form that'll allow me to do a search of the records based on the run id number, driver, vehicle, or customer name. I've only found tutorials on how to display all the records in the database, without search criteria, whereas I want it to display the one(s) that adhere to whichever search field(s) are entered. Any help would be greatly appreciated, keep in mind i'm not good wiht php to sql, so very blatant/lamen instructions would be greatly appreciated!

    Thanks

    ex:
    Search for Driver: Joe Schmoe
    using : 22 passenger Excursion
    on : June 1st, 2006
    Results in Run such and such, displaying along with all it's fields
    Last edited by Drkvamp; 06-02-06 at 00:04.

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    What you need to look at is the WHERE clause in the SELECT statement to pick records with contents that match your search input.

    For your example (assuming you already have the fields from the form in variables and that everything is stored in one table) -
    PHP Code:
    $driver "....."// driver name to search for
    $vehicle "......"// vehicle name to search for
    $date "....."// date should be entered/stored in mysql format yyyy-mm-dd

    // sample query strings for mysql_query($query); statements...
    $query "SELECT * FROM your_table WHERE col_driver = '$driver'"// select all the records for a $driver

    $query "SELECT * FROM your_table WHERE col_vehicle = '$vehicle'"// select all the records for a $vehicle (any driver/ any date)

    $query "SELECT * FROM your_table WHERE col_driver = '$driver' AND col_vehicle = '$vehicle'"// select all the records for a $driver AND a $vehicle (any date)

    $query "SELECT * FROM your_table WHERE col_driver = '$driver' AND col_vehicle = '$vehicle' AND col_date = '$date'"// select all the records for a $driver AND a $vehicle AND a $date
    .
    .


  3. #3
    Join Date
    Jun 2006
    Posts
    3
    Ok... but then what? The farthest I am really comfortable with going on my own, is to set up the form in HTML

    Run # is named run_num
    Driver is named drive
    vehicle is named limo
    date is named run_date

    in the database it's "Run Number" "Driver" "Limo" and "Date" respectively

    Thanks for your patience...

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Here is the basic code that will query the database and printout the results in a HTML table -
    PHP Code:
    // I'll assume that you can connect and select the database here

    // This example will search for driver, limo, and date and give any runs found

    // Assume the following variables with values from the form. Also that the table name is "runs"
    $drive
    $limo
    $run_date

    // Perform SQL query
    $query "SELECT * FROM runs WHERE Driver = '$drive' AND Limo = '$limo' AND Date = '$run_date'";
    $result mysql_query($query) or die('Query failed: ' mysql_error());

    // Print results in HTML table
    echo "<table border=1>\n";
    echo 
    "\t<tr>\n";

    // get column headings
    $i 0;
    while (
    $i mysql_num_fields($result)) {
        
    $meta mysql_fetch_field($result$i);
        echo 
    "\t\t<td><b>".$meta->name."</b></td>\n";
        
    $i++;
    }
    echo 
    "\t</tr>\n";

    // display any rows returned from the SELECT query
    while ($line mysql_fetch_array($resultMYSQL_ASSOC)) {
        echo 
    "\t<tr>\n";
        foreach (
    $line as $col_value) {
            echo 
    "\t\t<td>$col_value</td>\n";
        }
        echo 
    "\t</tr>\n";
    }
    echo 
    "</table>\n"

  5. #5
    Join Date
    Jun 2006
    Posts
    3
    Alright, that example brought up the header information, but would not display any results with any of the 3 form fields selected.... or does that not allow for one of the fields to be blank?

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The syntax used in the query string that I gave requires all three to be present and match -
    Code:
    Driver = '$drive' AND Limo = '$limo' AND Date = '$run_date'
    If you want to leave out a term, you will need to add code to test and build the query string without the empty term(s).

    For example, if you want to have a blank date, the existing query would be -
    Code:
    SELECT * FROM runs WHERE Driver = '$drive' AND Limo = '$limo' AND Date = ''
    Which won't match anything. You would need -
    Code:
    SELECT * FROM runs WHERE Driver = '$drive' AND Limo = '$limo'

Posting Permissions

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