axessJosh
asked on
MySQL Query with Several options (variables)
I am building what I perceive as a fairly complex query. I currently have an ideas database where each listing is defined by 8 criteria. Each possible criteria is listed in its own table, and those table IDs match the IDs in my main table with all the ideas.
I have built a search form using dynamic lists pulling the names of each criterion possible. What I am trying to achieve is a Query where any or all of the criterion can be selected.
I know i can pull it off with IF and SQL statements for each possible scenario, however this could get very long.
I need some help and direction on how to use individual or multiple criteria in my search.
Thanks in advance.
I have built a search form using dynamic lists pulling the names of each criterion possible. What I am trying to achieve is a Query where any or all of the criterion can be selected.
I know i can pull it off with IF and SQL statements for each possible scenario, however this could get very long.
I need some help and direction on how to use individual or multiple criteria in my search.
Thanks in advance.
Last Comment
could you post the layouts of the main table at least?
i get the feeling that currently you have the main table containing 8 criteria columns
what you more probably should have is an ideascriteria link table with an entry per criteria
that means you can use exists subqueries to select the ideas required...
i get the feeling that currently you have the main table containing 8 criteria columns
what you more probably should have is an ideascriteria link table with an entry per criteria
that means you can use exists subqueries to select the ideas required...
ASKER
Sure, my main table is setup as follows and each listed criteria has an ID and info field in it corresponding table.
TblBiz
bizID
bCosts
bTime
bLegitimacy
bContact
bemployees
btraining
bchange
bseason
I have a form setup with pull down menus for the user to select based on which criteria they are interested in. From there, I'd like for the user to be able to select any or all of the criteria available and for it to query the DB based on that. What I can't figure out is how to do it when I really can't use AND and OR also won't work. I really need a hybrid of both.
TblBiz
bizID
bCosts
bTime
bLegitimacy
bContact
bemployees
btraining
bchange
bseason
I have a form setup with pull down menus for the user to select based on which criteria they are interested in. From there, I'd like for the user to be able to select any or all of the criteria available and for it to query the DB based on that. What I can't figure out is how to do it when I really can't use AND and OR also won't work. I really need a hybrid of both.
not sure i understand now...
can you give an example of what you expect them to be selecting?
i.e. do you mean report
when costs between 20,000 and 50,000
and/or time between 90 and 150 days?
and notraining
or will the searches be "text" based
?
can you give an example of what you expect them to be selecting?
i.e. do you mean report
when costs between 20,000 and 50,000
and/or time between 90 and 150 days?
and notraining
or will the searches be "text" based
?
ASKER
The first part is exactly correct.
Each pull down has ranges as you have mentioned, and I'd like the user to select the range they are desiring.
Make sense?
Each pull down has ranges as you have mentioned, and I'd like the user to select the range they are desiring.
Make sense?
ASKER
Here is what I have built thus far. I am working on a tool that checks for various conditions, then builds the MySQL statement from those variables. My code is here:
I have only completed three conditions so far. I want to check to see that I am on the right track.
I also tried to run the query, which successfully queried the DB, but gave me the error below:
an error occured:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.bCosts WHERE tblbiz.bCosts = 3' at line 1
I have the code above in an include file and it is executed on my page with the following:
<?php
// declare all my variables
$cost = $_POST['costs'];
$time = $_POST['time'];
$leg = $_POST['leg'];
$people = $_POST['people'];
$employee = $_POST['employee'];
$training = $_POST['training'];
$change = $_POST['change'];
$season = $_POST['season'];
// set all the conditions to send to mySQL
$filter = "";
if ($cost != 0) {
$filter .= "tblbiz.bCosts = " .$cost. "'";
$sel = "tblbiz.bCosts";
}
// if $costs is selected check to see if $time has a value
if ($time != 0) {
if(isset($cost)&&($cost != 0)){
$sel .= "tblbiz.bTime";
$filter .= "AND";
}
$filter .= "tblbiz.bTime = '".$time."'";
}
// if $costs, $time is selected check to see if $leg is selected (business legitmacy)
if ($leg != 0) {
if(isset($cost)&&($cost != 0)||isset($time)&&($time != 0)) {
$sel .= "tblbiz.bLeg";
$filter .= "AND";
}
$filter .= "tblbiz.bLeg = '".$leg."'";
}
$sql = "SELECT tblbiz.bname $sel WHERE $filter";
mysql_query($sql)
or die('an error occured:' .mysql_error());
$count = mysql_num_rows($sql);
I have only completed three conditions so far. I want to check to see that I am on the right track.
I also tried to run the query, which successfully queried the DB, but gave me the error below:
an error occured:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.bCosts WHERE tblbiz.bCosts = 3' at line 1
I have the code above in an include file and it is executed on my page with the following:
<?php
// declare all my variables
$cost = $_POST['costs'];
$time = $_POST['time'];
$leg = $_POST['leg'];
$people = $_POST['people'];
$employee = $_POST['employee'];
$training = $_POST['training'];
$change = $_POST['change'];
$season = $_POST['season'];
// set all the conditions to send to mySQL
$filter = "";
if ($cost != 0) {
$filter .= "tblbiz.bCosts = " .$cost. "'";
$sel = "tblbiz.bCosts";
}
// if $costs is selected check to see if $time has a value
if ($time != 0) {
if(isset($cost)&&($cost != 0)){
$sel .= "tblbiz.bTime";
$filter .= "AND";
}
$filter .= "tblbiz.bTime = '".$time."'";
}
// if $costs, $time is selected check to see if $leg is selected (business legitmacy)
if ($leg != 0) {
if(isset($cost)&&($cost != 0)||isset($time)&&($time != 0)) {
$sel .= "tblbiz.bLeg";
$filter .= "AND";
}
$filter .= "tblbiz.bLeg = '".$leg."'";
}
$sql = "SELECT tblbiz.bname $sel WHERE $filter";
mysql_query($sql)
or die('an error occured:' .mysql_error());
$count = mysql_num_rows($sql);
<?php include_once ('sql.php'); ?>
<?php echo $row['tblbiz.bName']; ?>
ASKER
Also, in the above examples, the form variables will be submitted as integers.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
here is what i created.
I added my version of Dreamweaver code for the recordset behavior but i am not getting the results to show correctly. It actually gives me an undefined variable error.
if i were to do away with the DW RS code, What would my code be to show the results?
<?php
// refine database query
// declare all my variables
if (isset ($_POST['time'])){
$time = $_POST['time'];}
if (isset ($_POST['leg'])){
$leg = $_POST['leg'];}
if (isset ($_POST['people'])){
$people = $_POST['people'];}
if (isset ($_POST['employee'])){
$employee = $_POST['employee'];}
if (isset ($_POST['training'])){
$training = $_POST['training'];}
if (isset ($_POST['change'])){
$change = $_POST['change'];}
if (isset ($_POST['season'])){
$season = $_POST['season'];}
// build the query
if (isset($_POST['button'])){
$filter = "";
if ($time != 0) {
$filter .= " AND tblbiz.bTime = " .$time. "'";
}
if ($leg != 0) {
$filter .= " AND tblbiz.bLeg = " .$leg. "'";
}
if ($people != 0) {
$filter .= " AND tblbiz.bCon = " .$people. "'";
}
if ($employee != 0) {
$filter .= " AND tblbiz.bEmp = " .$employee. "'";
}
if ($training != 0) {
$filter .= " AND tblbiz.bTra = " .$training. "'";
}
if ($change != 0) {
$filter .= "AND tblbiz.bCha = " .$change. "'";
}
if ($season != 0) {
$filter .= "AND tblbiz.bSea = " .$season. "'";
}
mysql_select_db($database_con_search, $con_search);
$query_rs_refsel = sprintf("SELECT tblbiz.bName WHERE 1=1 $filter",
GetSQLValueString($time, "int"),
GetSQLValueString($leg, "int"),
GetSQLValueString($people, "int"),
GetSQLValueString($employee, "int"),
GetSQLValueString($training, "int"),
GetSQLValueString($change, "int"),
GetSQLValueString($season, "int"));
$rs_refsel = mysql_query($query_rs_refsel, $con_search) or die(mysql_error());
$row_rs_refsel = mysql_fetch_assoc($rs_refsel);
$totalRows_rs_refsel = mysql_num_rows($rs_refsel);
}
?>
I added my version of Dreamweaver code for the recordset behavior but i am not getting the results to show correctly. It actually gives me an undefined variable error.
if i were to do away with the DW RS code, What would my code be to show the results?
>It actually gives me an undefined variable error.
on which line?
anyhow, when you do this:
so, you shall do:
and similarly for all the other variables.
on which line?
anyhow, when you do this:
if (isset ($_POST['time'])){
$time = $_POST['time'];}
so, you shall do:
$time = 0;
if (isset ($_POST['time'])){
$time = $_POST['time'];}
and similarly for all the other variables.
ASKER
That satisfied the undefined variables.
now i get the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 tblbiz.bCosts = 2' AND tblbiz.bTime = 1'' at line 1
now i get the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 tblbiz.bCosts = 2' AND tblbiz.bTime = 1'' at line 1
visibly the code you posted above does not show where you put bCosts = xxx into the $filter...
and exactly there you missed to put the AND like for the other parts ...
and exactly there you missed to put the AND like for the other parts ...
ASKER
I added it to the $filter root variable after i realized it was not there.
<?php if (isset($_POST['Submit'])){
// refine database query
// declare all my variables
$costs = $_GET['costs'];
$time = 0;
if (isset ($_POST['time'])){
$time = $_POST['time'];}
$leg = 0;
if (isset ($_GET['leg'])){
$leg = $_GET['leg'];}
$people = 0;
if (isset ($_POST['people'])){
$people = $_POST['people'];}
$employee = 0;
if (isset ($_POST['employee'])){
$employee = $_POST['employee'];}
$training = 0;
if (isset ($_POST['training'])){
$training = $_POST['training'];}
$change = 0;
if (isset ($_POST['change'])){
$change = $_POST['change'];}
$season = 0;
if (isset ($_POST['season'])){
$season = $_POST['season'];}
// build the query
$filter = "tblbiz.bCosts = " .$costs. "'";
if ($time != 0) {
$filter .= " AND tblbiz.bTime = " .$time. "'";
}
if ($leg != 0) {
$filter .= " AND tblbiz.bLeg = " .$leg. "'";
}
if ($people != 0) {
$filter .= " AND tblbiz.bCon = " .$people. "'";
}
if ($employee != 0) {
$filter .= " AND tblbiz.bEmp = " .$employee. "'";
}
if ($training != 0) {
$filter .= " AND tblbiz.bTra = " .$training. "'";
}
if ($change != 0) {
$filter .= "AND tblbiz.bCha = " .$change. "'";
}
if ($season != 0) {
$filter .= "AND tblbiz.bSea = " .$season. "'";
}
mysql_select_db($database_con_search, $con_search);
$query_rs_refsel = sprintf("SELECT tblbiz.bName WHERE 1=1 $filter",
GetSQLValueString($time, "int"),
GetSQLValueString($leg, "int"),
GetSQLValueString($people, "int"),
GetSQLValueString($employee, "int"),
GetSQLValueString($training, "int"),
GetSQLValueString($change, "int"),
GetSQLValueString($season, "int"));
$rs_refsel = mysql_query($query_rs_refsel, $con_search) or die(mysql_error());
$row_rs_refsel = mysql_fetch_assoc($rs_refsel);
$totalRows_rs_refsel = mysql_num_rows($rs_refsel);
ASKER
Figured it out.
had to add "FROM tblbiz" to the SQL statement.
had to add "FROM tblbiz" to the SQL statement.
Databases
Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.
62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
HTH,
Kent