help with search form

Anon

I have a form that appears like a spreadsheet (ie columns/rows) on the page. There are 6 columns. I can edit each row in-line - one at a time.

The fields in the mysql database are things like name, account, account_mgr, region, etc.

Now, it displays all rows all the time by default.

I'm looking for help with two things (I'm very new to PHP, MySql, so bear with me).

The first, is I want to call this form with parameters so it narrows the rows returned. For example, the script is called accounts.php3, and I want to show all accounts where the region is EMEA, so I want to add a parameter to the form and call it like this: accounts.php3?region=EMEA and then it would return only those matching that criteria.

If you can point me to a tutorial for help with this or provide some sample code, that would be great - basically looking for how to modify my php3 file to accomodate the above.

Second question is with regards to searching. I want to add a very simple search form to be called from this accounts.php3 and then return results in the accounts.php3 output format. So, take the user to a screen where they make search selection criteria decisions, then click on a search button and then return to the accounts.php3 script with only those rows matching the search criteria.

Any help is most appreciated.

thanks

rulian

well, in reguards to you limiting the results to EMEA, you are going to have to do that in your mysql query as opposed to your actual php coding, even though it's possible to do that, it's much easier if you simply do it through mysql.

For example, let's say your query is as now this:
mysql_query("select * from TABLE");
you may want to modify it to look like this:

mysql_query("select * from TABLE where region = '$region' ");

that way, when you go to accounts.php3?region=EMEA, it will do the search and return only the ones that match EMEA, therefore, limiting your results and saving you some php coding. For the searching, I would suggest doing something abou the same thing. Where you would use a form to gather the information the user wanted, such as the query, and where they wanna search, and code your mysql query to accomodate for it, such as this :
mysql_query("select $query from $table where region = '$region' ") That should leave your search pretty versitle (versital, i cant spell)
Hope that helps, let me know if you need anythign else.

Anon

After reading my post, there's something I should clarify. In my spreadsheet looking page, the column headers provide sort capability. So, when I pass in ?region=EMEA, then click on a column header, all records are return, because the region=EMEA is lost. How do I cleanly in the code check for all options that are set and not lose them? So if I've entered the page with ?region=EMEA, then click on sort, I get the page of EMEA accounts sorted. Ugh, that's hard to explain, hopefully this is clear, if not, let me know and I'll try to be clearer. 🙂

rulian

well, i might not still be clear on how you've got things going. But here goes.
Lets say your using a drop down menu, or a link, or however you wish to make the change and the value of EMEA is $PHP_SELF?region=emea, you would need to do one of two things for when the page refreshes:
A. You could perform the same function you did when you set up your initial page, let's say you made a query into mysql and you made a for loop to display the info, you could either modify the search in mysql to return only emea accounts WHERE region = '$region', $region of course being set to emea,

OR B.
Have different functions to display the different accounts for example:

function emea(){
$geinfo=mysql_queyr(select * from blah blah bha)
for (blah blah blah)
SET UP THE VIEW LIKE SO
}

so that you can make a switch
switch ($region) {
case "emea":
emea(); --- Calls the funciton you made
break;
case "AMAI(just as a second choice)":
amai(); - another function which you display
break;
default:
All();
break;
}

did this help a bit more, if not feel free to cotact me again.

Anon

Got it, thanks. I've got a search and the parameter passing working. Now for the last part of combining it all.

So I've entered into the page with accounts.php3?region=EMEA, now I want to search only those results OR I want to sort only those results.

currently, If I do a search, it searches all content (since that's what my query is), but how do I get it to know that I've clicked on search from within the ?region=EMEA view?

thanks for the help!

rulian

my guess would be set up your search query with a variable in it, so that you can define it and it can search for something different.

for example

if (isset($region)){ $query="select from table where region='$region'"; }
else { $query="select from table";}

$search=mysql_query($query);

you can do the same thing with the sorting, setting up different scenarios for different results by using a switch, or something of the sort, I dont really know what it takes to sort your table, but let's say your just getting information from a mysql table and displaying it, basically, the sorting and limiting of the results would still be in the query, so make a form to submit what they wanna sort, and adjust the query to it.

if (isset($region)){
switch ($region){
case "AMEA":
scenario1();
break;
case "whatever":
sceneario2();
$sort="select from table where region= '$region' order by cust_name";
break;
}
}
else {
$sort ="select from table";
display_default();
}