[RESOLVED] Search script with required and optional inputs

fdgloworm

I have read the posting guidelines, seen the video (great! 🙂 ), and have searched every key word I can think of for this, and still have not been able to find an answer. When I googled, I got some more info, but not a concrete answer on what will work and what won't, just that lots of script may be needed. Please forgive me if there is another thread elsewhere - I couldn't find it.

Here is the basis for my question: a pgsql database with columns for name, category, city, state and zip. I want a search form that will allow users to choose category from list; city, state and zip are all user definable. Category and state are required, but city and zip are optional. I want a query that will search the db and pull out names that match all submitted criteria. I am at work right now so I have not been able to test this, but I wanted to get any help I could. Here is what I have right now.

<?php
$dbconn=pg_connect("host=localhost port=5432 dbname=business");
if (!dbconn){
     echo "An error occurred. \n";
     exit;
}

$city1=(boolean)$city   // $city is variable from form - forces boolean
$zip1=(boolean)$zip    // $zip is variable from form - forces boolean

IF $city1=(!FALSE) AND $zip1=(!FALSE){
     $results[]=(SELECT table.name WHERE table.category=$category AND
                        table.city=$city AND
                        table.state=$state AND
                        table.zip=$zip;
}
ELSEIF $city1=(!FALSE) AND $zip1=(FALSE){
     $results[]=(SELECT table.name WHERE table.category=$category AND
                      table.city=$city AND
                      table.state=$state;
}
ELSEIF $city1=(FALSE) AND $zip1=(!FALSE){
     $results[]=(SELECT table.name WHERE table.category=$category AND
                       table.state=$state AND
                       table.zip=$zip;
}
ELSE {
     $results[]=(SELECT table.name WHERE table.category=$category AND
                       table.state=$state;
}

This is not the entire script - the other reason I can't test just yet is it's not completed. Suffice it to say $category, $city, $state, $zip all come from the form; $city1 and $zip1 are forced boolean copies of $city and $zip to make true/false decisions in the IF statement; $results is supposed to be an array with the output from the query for listing on a results page.

Is this completely wrong, or will it work?

fdgloworm

Kudose

I would do something like this:

<?php
  $cat = $_POST['category'];
  $state = $_POST['state'];
  $city = $_POST['city'];
  $zip = $_POST['zip'];

  //if required fields are false or NULL, die 
  if(!$cat || !$state || $city == NULL || $state == NULL)
    die('Category and state required.');

  //build standard SQL
  $sql = "SELECT * FROM tablename WHERE category LIKE '%$cat%' ";
  $sql .= "AND state LIKE '%$state%' ";

  //build added SQL if city is true
  if($city && $city !== NULL)
    $sql .= "AND city LIKE '%$city%' ";

  //build added SQL if zip is true  

  if($zip && $zip !== NULL)
    $sql .= "AND zip LIKE '%$zip%' ";

  //addtional clauses  

  $sql .= "ORDER BY category ASC";

  //run the query
  $query = mysql_query($sql) or die(mysql_error());

  //fetch the data
  $fetch = mysql_fetch_array($sql);

  //display results
  echo '<pre>';
  print_r($fetch);
  echo '</pre>';
?>

You'll need to add form validation and a few security tweaks, but thats the basics.

fdgloworm

Kudose wrote:

I would do something like this:

<?php
  $cat = $_POST['category'];
  $state = $_POST['state'];
  $city = $_POST['city'];
  $zip = $_POST['zip'];

Sorry - have this elsewhere on the page, just didn't put it in this blurb of script. As I mentioned, this isn't completed yet, it's on several sheets of paper, and I only put up the search query. 🙂

Kudose wrote:

  //if required fields are false or NULL, die 
  if(!$cat || !$state || $city == NULL || $state == NULL)
    die('Category and state required.');

This is what I was looking for!! I couldn't find anything like this in three different manuals. sigh Should have dug out my old college boolean algebra book for this one.

Kudose wrote:

//build standard SQL
  $sql = "SELECT * FROM tablename WHERE category LIKE '%$cat%' ";
  $sql .= "AND state LIKE '%$state%' ";

  //build added SQL if city is true
  if($city && $city !== NULL)
    $sql .= "AND city LIKE '%$city%' ";

  //build added SQL if zip is true  

  if($zip && $zip !== NULL)
    $sql .= "AND zip LIKE '%$zip%' ";

  //addtional clauses  

  $sql .= "ORDER BY category ASC";

And of course I learned about the .= by chance after I posted this. That makes much more sense, building the query as I go. Thank you!

Kudose wrote:

//run the query
  $query = mysql_query($sql) or die(mysql_error());

  //fetch the data
  $fetch = mysql_fetch_array($sql);

  //display results
  echo '<pre>';
  print_r($fetch);
  echo '</pre>';
?>

I am using pgsql - the mysql_query won't work. Can I just substitute pg_query? That is available, but I didn't think I needed it. And don't I need a db connection string?

Kudose wrote:
You'll need to add form validation and a few security tweaks, but thats the basics.

Yeah, that's on another sheet also. 🙂 Just haven't put it all together yet. Can't run tests on scripts here at work - only paper and pencil.

fdgloworm

Kudose

Yeah, just replace the mysql_query with pg_query and the likes. I always use MySQL so I typed it in by accident.

If you already connect to your database elsewhere in your script, you wont need to reconnect.

Kudose

Your welcome by the way. 🙂

Forgot that in my last post.

It's nice to see a new member on the boards, especially one that wants to learn; not just have work done for them.

fdgloworm

I have read all the newbie posts - and I've been dabbling in programming and computers all my life - so learning to write the code is my goal. Earning money by building a website is a perk!

That being said - I am going back and figuring out some of the syntax you used and trying to understand why it didn't occur to me - but my biggest question is would my original script have done what I wanted it to do? I am getting ready to code the page, and I'm pressed for time a little, but I also want to put my original in just to see if it would have worked - it's definitely not as neat as yours. 🙂 I am just curious about functionality and if my rudimentary thought processes were on the right track.

I am very content to be here, and very glad to have assistance when I can't figure things out, or if there might be a better mousetrap then the one I'm making. 🙂

fdgloworm

Kudose

I've never seen anyone code PHP in the fashion you did, so I cannot tell you if it would work.

Where did you pick it up from ... a book?

fdgloworm

Hehehe...believe it or not, it's all what I have picked up from years of writing different code - Basic, Fortran, etc - and a year of Boolean Algebra in College -- then applied all that to what I have read in online manuals. Self teaching. 🙂 I'll plug it in later and see if it would actually work.

Thank you again for your help. 🙂

fdgloworm

swatisonee

i'm fascinated by this whole post ! but may i ask a question ? What does

 $sql .= "AND state LIKE '%$state%' ";

this do ? Its the first time i've ever seen this kind of query .

Will this replace multiple dropdowns ?

ie. For every state chosen in a primary dropdown, a list of cities in that state have to be visible in a secondary dropdown and once someone chooses a city, then a third dropdown can list all the zipcodes of that city...

Have I understood the thread correctly ?

Thanks !

fdgloworm

swatisonee wrote:
i'm fascinated by this whole post ! but may i ask a question ? What does
 $sql .= "AND state LIKE '%$state%' "; 
this do ? Its the first time i've ever seen this kind of query .

Will this replace multiple dropdowns ?

ie. For every state chosen in a primary dropdown, a list of cities in that state have to be visible in a secondary dropdown and once someone chooses a city, then a third dropdown can list all the zipcodes of that city...

Have I understood the thread correctly ?

Thanks !

Whoa. Sorry, it's not that fantastic. 🙂 I'm not using drop-downs - yet. Getting ready to change a few over, but...

As far as I can tell, the LIKE function looks for a pattern included in a string. This helps if someone can't spell their state exactly right, or you want to allow postal codes and complete state names, etc. It's used for loose matching of input to data I believe.

Sorry - I am not sure if the LIKE syntax is what you were asking about or not. It might also be the .=, which is used to add that snippet of code to the current query if the check is true. This can be used to build your query based off of input from users...which is exactly what I was wanting. 🙂

swatisonee

i meant to ask about the .= only. I've used LIKE so no issues there. But I still cannot figure out how this will work.

Pl correct me if i am wrong - say, a user types in Texs instead of Texas. Does this sql search the table for all records that have Texas in them and then return the result ?

thanks.