PHP/MySQL Search Page

[Caraldur]

Hello everyone,

I'm need some help. I'm running PHP/MYSQL with IIS, I have managed to setup quite about of the site without a problem but now I've run into a snag. I'm trying to perform a search based on user input. There are 5 fields that the user can choose to search. They need to be able to enter something in one or all of these fields. I can do the single entry search with no problem but when I try to setup multiple variables for the recordset I keep getting errors. Can someone help me out.

Fred

 

[deecee]

how is your code looking on the results page? i would like to see your sql statement.

zzzzzz

 

[Caraldur]

I'm really new to all of this so I'm not even sure that this statement is even remotely correct or makes any sense.

SELECT *
FROM maint_logbook
WHERE maint_logbook.EquipmentName = machine
OR maint_logbook.Shift = cShift
OR maint_logbook.Tech1 = Technicain
OR maint_logbook.Tech2 = Technicain
OR maint_logbook.`Problem Area`= area
OR maint_logbook.`Date`= day

Like I said I'm a real newb when it comes to Dreamweaver, PHP, and MySQL. machine, cshift, technicain, area, and day are varibles I set in the advanced recordset binding menu. On search page I set those variables as URL parameters for the form where the User Input text files area.

 

[deecee]

your select statement would read like this

below is an example of a statement that I use regularly -- what it does is search 2 fields (price and category). User puts in low and high price and can choose a category if they want. The problem is if they do not want to chose a category then you have to filter only by price. The category must then be concatenated if it has a value

$lowprice_connProds = "1";
if (isset($HTTP_POST_VARS['LowPrice'])) {
  $lowprice_connProds = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['LowPrice'] : addslashes($HTTP_POST_VARS['LowPrice']);
}
$highprice_connProds = "1600";
if (isset($HTTP_POST_VARS['HighPrice'])) {
  $highprice_connProds = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['HighPrice'] : addslashes($HTTP_POST_VARS['HighPrice']);
}
$type_connProds = "0";
if (isset($HTTP_POST_VARS['ProductType'])) {
  $type_connProds = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['ProductType'] : addslashes($HTTP_POST_VARS['ProductType']);
}
mysql_select_db($database_connCorp, $connCorp);
$query_connProds = sprintf("SELECT * FROM prods WHERE (msrp BETWEEN '%s' AND '%s')", $lowprice_connProds,$highprice_connProds,$type_connProds);

<strong>//**This will concatenate if the user choses to filter by category too**//</strong>

if ($type_connProds > "0") 
{
    $query_connProds .= sprintf(" AND (cat_id = '%s')",$prod_products);
}
$connProds = mysql_query($query_connProds, $connCorp) or die(mysql_error());
$row_connProds = mysql_fetch_assoc($connProds);
$totalRows_connProds = mysql_num_rows($connProds);

So for simplicity sake your sql statement should be something like

SELECT * FROM table WHERE maint_logbook.EquipmentName = $_GET['machine'] AND maint_logbook.Tech1 = $_GET['Technician']....

Your variables should be case sensitive too. I am assuming that maint_logbook.EquipmentName is the field name.

zzzzzz

 

[Caraldur]

I hate to sound stupid but I can't get it to work. Here is the SQL statement that DreamWeaver generated for my query

$query_searchresults = sprintf("SELECT * FROM maint_logbook WHERE maint_logbook.EquipmentName =$_GET[ '%s'] AND maint_logbook.Shift = $_GET['%s'] AND maint_logbook.Tech1 = $_GET['%s'] AND maint_logbook.Tech2 = $_GET['%s'] AND maint_logbook.`ProblemArea`= $_GET['%s'] AND maint_logbook.`Date`= $_GET['%s']", $machine_searchresults,$cShift_searchresults,$Technicain_searchresults,$Technicain_searchresults,$area_searchresults,$day_searchresults);

This is really driving me nuts. I'v been stuck on this for a couple days now. If you have MSN and are willing to help a complete Noob through this I would really appreciate it and you would have my eternal gratitude.

Fred

 

[deecee]

first do you know the difference between GET and POST?

zzzzzz

 

[Caraldur]

Get puts the variables in the URL and Post doesn't. I think

 

[deecee]

get pulls the variable from a url and post is passed along via a form -- if you are passing these variables from a form the you use $_POST and if via url (ex.

http://www.mysite.com?id=100)

then use $_GET.

in your sql statement you wouldnt use $_GET[%s]

when you define your variables in DW you would put the name of the variable being passed and a default value for it. then DW will assign them properly (for the most part) in the script. It looks like you are going about it wrong in DW.

zzzzzz