Forum Moderators: coopster
I have some questions regarding an advanced search on mysql database
First i will show you my form :
<form action="result.php" method="post">
Country: <input type="text" name="country" /> <br />
Test Stage:
<select name="env" /><br />
<option>PP</option>
<option>Prod</option>
<option>QA</option>
</select > <br />
BackBone: <input type="text" name="backbone" /><br />
Srv: <input type="text" name="srv" /> <br />
Database: <input type="text" name="db" /><br />
<input type="submit" /> <br />
</form>
I know all the steps to process the form, but my questions is, that it is possible to make something shorter than make a if clause for my cases example below:
$coutnry = $_POST['country']
$env = $_POST['env']
etc...
if ($country == "") {
execute query
}
if ($env =="" and $env == ""){
execute query2
}
etc
(this is just an example, there are mysql_real_escape_string $variables )
As you can see I have a lot of combinations of search and i don't want to make so much if&else statements!
One of solutions that i thought it could work is an auto update search that should work like that:
If choose country "ROMANIA" auto populate Test Stage, after choosing Test Stage, auto populate BackBone! the auto populate should work only for those three !
Also, i should show you how is my database structured :
there is a table for each country, each country has the following columns :
id ¦ country_code ¦ teststage ¦ backbone ¦ srv ¦ db ¦
for each country with the same testage, backbone and srv i can have more DB, or for the same testage i can have more srv's with 2 or 3 db's
for example :
RO ¦ PP ¦ MMC ¦ localhost ¦ MMCDB1
RO ¦ PP ¦ MMC ¦ localhost ¦ MMCDB2
RO ¦ PP ¦ CFM ¦ localhost1¦ CFMDB1
RO ¦ QA ¦ MMSST¦ localhost2¦ MMSSTDB1
RO ¦ QA ¦ MMSST¦ localhost2¦ MMSSTDB2
RO ¦ QA ¦ MMSST¦ localhost3¦ MMSSTDB3
RO ¦ QA ¦ MMSST¦ localhost3¦ MMSSTDB3
The search should work only with 1 field to all fields completed
So if you have other suggestions how can i make the search for each case without querying everytime the database, please don't hesitate to teach me! Also please give me some help with auto-populate search form!
Thanks in advance
SORRY FOR MY BAD ENGLISH, AND HOPE YOU UNDERSTAND WHAT ARE MY NEEDS!
how can I make that ? actually how can I include a variable in a query only if it is not empty ?
I really need help ...
Thanks in advance
Not
<select name="env" /><br />
<option>PP</option>
(which is invalid html, no break inside select)
but
<select name="env" id="env">
<option value="">Select</option>
<option value="PP">PP</option>
I'd make those numeric values, as it will make searches faster. But anyway . . .
Step two: map your form fields to your database fields. Never, ever, ever make them the same.
var $searchFields = Array (
'country' => 'ctr',
'env' => 'environment',
'backbone' => 'bk',
'srv' => 'server',
'db' => 'database'
);
In the above, form fields are the keys, database field names are the values.
Step three: compose a where. Note how "and" is only added if needed:
$where=NULL;
foreach ($searchFields as $key=>$value) {
if (isset($_POST[$key]) and ($_POST[$key] != '')) {
if ($where) { $where .= ' and'; }
$where .= " $value ='" . $_POST[$key] . "'";
// or like . . . all depends
}
}
So now you may or may not have a where. It will only be populated by actual values in the form. In the above, note the careful placement of SPACES in both the where and the "and". They are important.
Step four: build your select.
$select = "select * from table";
if ($where) { $select .= " where $where;" }
$select .= " $limitString";
Not that if nothing is entered, it will display all results:
select * from table $limitString;
I'll leave it to you to figure out how to compose a limit string as it involves lots of things, like pagination links, whatnot. But this will lead you in the right direction . . .
[edited by: rocknbil at 10:16 pm (utc) on Dec. 22, 2009]
$where=array();
foreach ($searchFields as $key=>$value) {
if (isset($_POST[$key]) and ($_POST[$key] != '')) {
$where[]= "$value='" . $_POST[$key] . "'";
// or like . . . all depends
}
}if(count($where)>1) {
$where=implode(' and ',$where);
}
elseif(isset($where[0])) {
$where=$where[0];
}
else {
$where='';
}
It might not matter much in this specific case, but one of the big differences is how many times the if($where) is evaluated. I don't check in the loop, but rather check how many times $where's been set outside of the loop, so there are 3 checks in my code, but they are outside of the loop, while with rocknbil's there is a check for every iteration of the loop... The difference may be small most of the time, but on a loop with 1000 iterations run for every page load it may add up.
You might also notice I've changed the spacing pointed out previously, because the ' and ' is spaced in my example, rather than the construction of the individual statements...
Like I said, just a nuance to different styles of coding you might like to see and really think any difference may come down to which is faster concatenation / accumulation or setting an array piece and imploding, and I don't feel like benchmarking, so they could be exactly the same, or rocknbil's could be faster, or mine could, or... it could just be a difference in style.
<added>
Of course now I'm curious as to which is actually faster... lol :)
</added>
Thanks again, and sorry for my bad english
you make an concatenation between what ?
As far as I know with implode you add "and" before each value of $where!
also, i don't understand what "elseif" does and my big question is, how should the select look ?
Sorry for asking so poor questions, but I really want to understand your entire code!
Thanks in advance, and sorry for my bad english!
require_once("includes/db.php");
$conn = new Mysqlconn();
$conn->connect_db();
$conn->select_db();
$country = trim($_POST['country']);
$env = trim($_POST['env']) ;
$db = trim($_POST['db']) ;
$bk = trim($_POST['backbone']) ;
$srv = trim($_POST['srv']) ;
$sf = array (
$country => 'ctr',
$env => 'environment',
$bk => 'backbone',
$db => 'database',
$srv => 'server');
$where=NULL;
foreach ($sf as $key=>$value) {
if ($_POST[$key] != '') {
if ($where) { $where .= ' and'; }
$where .= " $value ='" . $_POST[$key] . "'";
}
}
If I print_r my array it will show all the values that i insert in my form, but everytime it prints out the last value (in my case 'server') even if in the form I didn't insert a value, i tried to change with other value( put database instead of server as last value in my array) but the result was same! It prints me out all the fields that have values + the last one!
Here is the final code for the search :
require_once("includes/db.php");
$conn = new Mysqlconn();
$conn->connect_db();
$conn->select_db();
$country = trim($_POST['country']);
$env = trim($_POST['env']) ;
$db = trim($_POST['db']) ;
$bk = trim($_POST['backbone']) ;
$srv = trim($_POST['srv']) ;
$sf = array (
$env => 'environment',
$bk => 'backbone',
$db => 'datab',
$srv => 'server');
$where=NULL;
foreach ($sf as $key=>$value) {
if (isset($key) && ($key != "")) { //here changed from Post to variable. and also i changed != '' to != ""
if ($where) { $where .= ' and'; }
$where .= " $value ='" . $key . "'";
}
}
$select = "select * from ".$country."";
if ($where !='')
{ $select .= " where $where"; }
echo $select;
$result = mysql_query($select);
while ($row = mysql_fetch_array($result)) {
echo"<br />"; echo $row['ctr'];
echo "<br />"; echo $row['environment'];
echo "<br />"; echo $row['backbone'];
echo "<br />"; echo $row['datab'];
echo "<br />"; echo $row['server'];
}
In my original, this was to map form fields to database fields.
var $searchFields = Array (
'country' => 'ctr',
'env' => 'environment',
'backbone' => 'bk',
'srv' => 'server',
'db' => 'database'
);
So if someone has "country" entered, the where would do this:
$where .= " $value ='" . $_POST[$key] . "'";
which would equate to
... ctr='US'
presuming, of course, the database field for "country" is named "ctr."
What you did in your versions is replace those keys with actual post values, which may or may not be a problem (explanation below.)
$country = trim($_POST['country']);
$env = trim($_POST['env']) ;
$db = trim($_POST['db']) ;
$bk = trim($_POST['backbone']) ;
$srv = trim($_POST['srv']) ;
$sf = array (
$country => 'ctr',
$env => 'environment',
$bk => 'backbone',
$db => 'database',
$srv => 'server');
So now you have the actual values in the form as "keys." Using my "US" example above,
US => 'ctr'
Which still works, you're still mapping the submitted value to the database field - but what happens when all the other form fields are empty? Your array (country re-added for example) would like like this:
$sf = array (
'US' => 'ctr',
'' => 'environment',
'' => 'backbone',
'' => 'datab',
'' => 'server');
See that? All your keys are blank. So if you try
echo $sf[$env];
What do you get? Hard to say, as there are four keys with that value.
Try this, is should still work. I am gathering the table itself is named by country - make appropriate changes for that part . . .
$conn = new Mysqlconn();
$conn->connect_db();
$conn->select_db();
$sf = array (
'country' => 'country', // don't you have a country field?
'env' => 'environment',
'bk' => 'backbone',
'db' => 'datab',
'srv' => 'server');
// remembering that FORM FIELDS are the keys on the left, DATABASE FIELDS are the values on the right
$where=NULL;
foreach ($sf as $key=>$value) {
if (isset($_POST[$key]) && ($_POST[$key] != "")) {
if ($where) { $where .= ' and'; }
$where .= " $value ='" . trim(mysql_real_escape_string($_POST[$key])) . "'";
}
}
$select = "select * from table";
if ($where !='')
{ $select .= " where $where"; }
echo $select;
$result = mysql_query($select);
while ($row = mysql_fetch_array($result)) {
echo"<br />"; echo $row['ctr'];
echo "<br />"; echo $row['environment'];
echo "<br />"; echo $row['backbone'];
echo "<br />"; echo $row['datab'];
echo "<br />"; echo $row['server'];
}
Thanks in advance, and sorry for my bad english!
I have a thing that i want to get rid of : After submit the form, in result.php (where is processed the form) when i refresh the page it shows me that he will resubmit the form, is there any posibility how get rid of that ?
