SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast Sebioni's Avatar
    Join Date
    Jun 2001
    Location
    France
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP - SELECT statement based on dynamic criteria

    HELP PLEASE!

    I am totally lost and I am starting to doubt whether php can create what I am trying to achieve here. I am sure there is an easy way to do this but I can't seem to find it.

    I am creating an intranet system whereby the users can generate some reports from a database of the company deals. So far so good.
    I have created the database and it is basically one big table with the date, seller, buyer, type of deals, etc....

    What they want to do is to select a few criteria (i.e. seller, buyer, date, product sold,currency,etc..), and do a search of the database based on a specific selected combination of criteria (drop_down menus or text fields).

    The outcome is a report they can analyse and use to track deals and improve their knowledge of the business.

    Now, this is where the problem starts. I can easily use a SELECT statement to pull the data from the DB as long as the criteria chosen by the user are always the SAME. What I don't seem to even get my head around is how I could deal with the same thing if they want to create the report on the fly.

    Let's say they get to the form and want to create a report on Sales by a specific seller and a specific type of product. How would I organise my SELECT statement?

    I could test their selection using a few IF statements but they want to have about 8 criteria to choose from which allows for hundreds of different combinations of criterias. I can't even start thinking about creating an if statement for each possibility and that wouldn't be good programming....

    Anyway, the code I am using so far to pull the data from the database is as follows (but that only covers the possibility of the user choosing the currency, the buyer and the seller criteria(I am testing whether seller was chosen here) this is the general idea of the problem, I am sure you are starting to understand):

    PHP Code:
    $sql "SELECT *
        FROM 
    $table_name
        where currency = '
    $currency' and seller = '$seller' and buyer = '$buyer'
        "
    ;
    } else {
    $sql "SELECT *
        FROM 
    $table_name
        where currency = '
    $currency' and buyer = '$buyer'
        "
    ;
    }

    $result = @mysql_query($sql,$connection);

    while (
    $row mysql_fetch_array($result)) {
        
    $id $row['id'];
        
    $volume $row['volume'];
        
    $unit_price $row['unit_price'];
        
    $po_nb $row['po_nb'];
        
    $date $row['date'];
        
    $commission $row['commission'];
        
    $currency $row['currency'];
        
    $seller $row['seller'];
        
    $buyer $row['buyer'];

        
    $display_block .= "
        <tr><td>
    $date</td><td>$seller</td><td>$buyer</td><td>$volume</td><td>$varietal</td><td>$commission</td></tr>
        "
    ;


    If someone could help me on this, I would be ever so grateful, they are good clients and I don't want to dissapoint them. I am desperate...

    Many thanks in advance.
    Seb

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you get the selected options from a select mutliple field?

    Be sure to use <name>[] as the name. (the [] are important.

    on the page where you want to build your select statement you could use:

    PHP Code:
    <?php
    $select 
    implode(","$HTTP_POST_VARS['nameoftheselectfield']);

    echo 
    $select;
    ?>
    Using the above code should list all selected columns seperated with a comma.

  3. #3
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, maybe I didn't understand fully what you wanted to do at first.

    If you need to use something like WHERE customer_id = 5 or I believe that there is no other choice but to use if or switch though and this is indeed a bit complicated.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so, for you're report screen the user would see, i assume that you have <select> drop downs for each column that you want the user to be able to specify? or single line edits or something?

    basically, in your form each <select> will have a unique name, and the action of the form will then setup the SQL query you want and execute it.

    the selects on the form would be populated from the database so that the seller one contains all unique seller names in your db, the product would contain all unique products, etc...for each drop down you need.

    when you submit the form with all the drop downs selected, you can have a function that will keep building the query if something was selected from each drop down. i am thinking something like this..
    PHP Code:
    $anything_appended false;

    $sql "SELECT * FROM $table_name";

    //if a seller is selected, append it to the query
    if(isset($_POST['seller'])) {
         if(!
    $anything_appended) {
              
    $sql .= ' WHERE ';
              
    $anything_appended true;
         else {
              
    $sql .= ' AND ';
         }

         
    $sql .= "seller = '" $_POST['seller'];
    }
        
    //if a product is selected, append it to the query
    if(isset($_POST['product'])) {
         if(!
    $anything_appended) {
              
    $sql .= ' WHERE ';
              
    $anything_appended true;
         else {
              
    $sql .= ' AND ';
         }

         
    $sql .= "product = '" $_POST['product'];

    and so on...
    i think something along these lines would work.

  5. #5
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can create a select statement dynamically by that I mean if you have a form with twenty fields, when the form is submitted you check to see if the field had been filled out, checked etc. then create your query accordingly.

    Code:
    $sqlquery = "SELECT * FROM table where 1=1 AND (page !='')";
    if($first !="") { $sqlquery .= " AND firstname like '%$first%'"; }
      if($last !=""){ $sqlquery .= " AND lastname like '%$last%'";    }
    if($year!="") { $sqlquery .= " AND year='$year'";   }
      if($state!='')  { $sqlquery .= " AND state like '%$state%'";  }
     if($country !=""){ $sqlquery .= " AND (country like '%$country%')";}
    
    $sqlquery .= "ORDER BY firstname,username";
    I hope this gives you an idea of how you can do it.

  6. #6
    SitePoint Enthusiast Sebioni's Avatar
    Join Date
    Jun 2001
    Location
    France
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys!!!

    This is really useful! I am now starting to see the light at the end of the tunnel...

    I am currently testing all your suggestions and I will probably mix and match to come up with the most suitable code for my purposes. I really appreciate the time and the ideas.

    I have one small question for zoordaan though. I understand the 'where 1 = 1' bit but what does the '(page !='')' do ?

    Cheers!
    Seb

  7. #7
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have one small question for zoordaan though. I understand the 'where 1 = 1' bit but what does the '(page !='')' do ?
    You don't need that. I took that code out of a script I have and didn't modify it extensively before posting. In my particular query I'm just using page as as a constant - i.e. if the person doesn't have a homepage I don't want them displayed in the search engine results until they do. If you have a search criteria that you know is always going to be the same then you can just stick it there otherwise just take it out.

  8. #8
    SitePoint Enthusiast Sebioni's Avatar
    Join Date
    Jun 2001
    Location
    France
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might become useful further down the line in this project.

    Thanks for all you help.
    Seb


Bookmarks

Posting Permissions

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