Mysql select query with multiple conditions
-
hi, am facing problem with the following; my table is having 6 columns. i want to select the data based on "from" and "to" dates,and also if i enter value in any one /two/three/.../all of the fields data should be displayed from the database. am using this:
if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
}
else if(R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "') AND tag_id='" + R_Tag.Text + "' ";
}
else if (R_Category.Text != "" && R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
} -
hi, am facing problem with the following; my table is having 6 columns. i want to select the data based on "from" and "to" dates,and also if i enter value in any one /two/three/.../all of the fields data should be displayed from the database. am using this:
if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == ""))
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";
}
else if(R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "') AND tag_id='" + R_Tag.Text + "' ";
}
else if (R_Category.Text != "" && R_Tag.Text != "")
{
query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')";
}Start by reading about SQL Injection[^], and then fixing your code to use parameterized queries. Your current approach will need 64 different queries to satisfy every possible combination. A simpler approach would look something like:
var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);queryBuilder.Append("AND (1 = 1");
if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = @RTag");
command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
queryBuilder.Append(" OR category_id = @RCategory");
command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
// Repeat for other fields...queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Start by reading about SQL Injection[^], and then fixing your code to use parameterized queries. Your current approach will need 64 different queries to satisfy every possible combination. A simpler approach would look something like:
var queryBuilder = new StringBuilder();
queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate");
command.Parameters.AddWithValue("@FromDate", fromDate);
command.Parameters.AddWithValue("@ToDate", toDate);queryBuilder.Append("AND (1 = 1");
if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = @RTag");
command.Parameters.AddWithValue("@RTag", R_Tag.Text);
}
if (!string.IsNullOrEmpty(R_Category.Text))
{
queryBuilder.Append(" OR category_id = @RCategory");
command.Parameters.AddWithValue("@RCategory", R_Category.Text);
}
// Repeat for other fields...queryBuilder.Append(")");
command.CommandText = queryBuilder.ToString();
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
thanks for reply. but am getting fatal error at da.Fill(ds). and :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 '(1 = 1) OR tag_id = 'E2002076990B019322902641')' at line 1 here is my code: in button click; i entered tag_id dynamically // Connect to Mysql String query; System.Data.DataTable dt = new System.Data.DataTable(); String conString = "Server=localhost;Database=asset;Uid=root;pwd=root"; MySqlConnection con = new MySqlConnection(conString); MySqlCommand command = new MySqlCommand(); command .Connection = new MySqlConnection(conString); command.CommandType = CommandType.Text; //command.CommandText =query; con.Open(); MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", con); string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd"); string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd"); query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "')"; if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == "")) { query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' "; command.CommandText = query; da = new MySqlDataAdapter(command); ds = new DataSet(); da.Fill(ds); //System.Data.DataTable dt = new System.Data.DataTable(); da.Fill(dt); } else { try { var queryBuilder = new StringBuilder(); queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate"); command.Parameters.AddWithValue("@FromDate", fromDate); command.Parameters.AddWithValue("@ToDate", toDate); queryBuilder.Append("AND (1 = 1"); if (!string.IsNullOrEmpty(R_Tag.Text)) {
-
thanks for reply. but am getting fatal error at da.Fill(ds). and :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 '(1 = 1) OR tag_id = 'E2002076990B019322902641')' at line 1 here is my code: in button click; i entered tag_id dynamically // Connect to Mysql String query; System.Data.DataTable dt = new System.Data.DataTable(); String conString = "Server=localhost;Database=asset;Uid=root;pwd=root"; MySqlConnection con = new MySqlConnection(conString); MySqlCommand command = new MySqlCommand(); command .Connection = new MySqlConnection(conString); command.CommandType = CommandType.Text; //command.CommandText =query; con.Open(); MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", con); string fromDate = dateTimePicker1.Value.ToString("yyyy-MM-dd"); string toDate = dateTimePicker2.Value.ToString("yyyy-MM-dd"); query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where (DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "')"; if ((R_Tag.Text == "") && (R_Category.Text == "") && (R_Product.Text == "") && (R_ProName.Text == "") && (R_Gate.Text == "")) { query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' "; command.CommandText = query; da = new MySqlDataAdapter(command); ds = new DataSet(); da.Fill(ds); //System.Data.DataTable dt = new System.Data.DataTable(); da.Fill(dt); } else { try { var queryBuilder = new StringBuilder(); queryBuilder.Append("select tag_id, category_id, product_id, product_name, gate_id, in_time, out_time, remarks from transaction where DATE(in_time) BETWEEN @FromDate And @ToDate"); command.Parameters.AddWithValue("@FromDate", fromDate); command.Parameters.AddWithValue("@ToDate", toDate); queryBuilder.Append("AND (1 = 1"); if (!string.IsNullOrEmpty(R_Tag.Text)) {
Member 10263519 wrote:
queryBuilder.Append("AND (1 = 1)");
You've put an extra closing bracket in that line. It needs to be:
queryBuilder.Append("AND (1 = 1");
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Member 10263519 wrote:
queryBuilder.Append("AND (1 = 1)");
You've put an extra closing bracket in that line. It needs to be:
queryBuilder.Append("AND (1 = 1");
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
eventhough am getting same 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 '(1=1OR tag_id = 'E2002076990B019322902641')' at line 1
-
eventhough am getting same 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 '(1=1OR tag_id = 'E2002076990B019322902641')' at line 1
I don't have a copy of MySQL to test, but why does the error message include a specific value rather than a parameter name? I would expect it to read
near '(1 = 1 OR tag_id = @RTag)'. There seems to be contradicting information on how to pass parameters to a MySQL query. You might want to try using?nameinstead of@nameto see if that makes any difference:if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = ?RTag");
command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}If it still doesn't work, try running the query in the MySQL Workbench to see if that gives you any more information.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I don't have a copy of MySQL to test, but why does the error message include a specific value rather than a parameter name? I would expect it to read
near '(1 = 1 OR tag_id = @RTag)'. There seems to be contradicting information on how to pass parameters to a MySQL query. You might want to try using?nameinstead of@nameto see if that makes any difference:if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = ?RTag");
command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}If it still doesn't work, try running the query in the MySQL Workbench to see if that gives you any more information.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
if i keep , ? instead of @. i got error as: Fatal error encountered during command execution .at da.Fill(ds) i have placed "Allow User Variables=True" in connection string.plz help me . how to run this query in workbench.
-
I don't have a copy of MySQL to test, but why does the error message include a specific value rather than a parameter name? I would expect it to read
near '(1 = 1 OR tag_id = @RTag)'. There seems to be contradicting information on how to pass parameters to a MySQL query. You might want to try using?nameinstead of@nameto see if that makes any difference:if (!string.IsNullOrEmpty(R_Tag.Text))
{
queryBuilder.Append(" OR tag_id = ?RTag");
command.Parameters.AddWithValue("?RTag", R_Tag.Text);
}If it still doesn't work, try running the query in the MySQL Workbench to see if that gives you any more information.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
thanks for the help:i solved it in another way: query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "'"; if(R_Tag.Text != "") { query=query+ " AND " + "tag_id=" + "'" + R_Tag.Text + "' "; } if (R_Category.Text != "") { query = query + " AND " + "category_id=" + "'" + R_Category.Text + "' "; //query = "select tag_id,category_id,product_id,product_name,gate_id,in_time,out_time,remarks from transaction where DATE(in_time)BETWEEN '" + fromDate + "'AND '" + toDate + "' AND (tag_id='" + R_Tag.Text + "' OR category_id='" + R_Category.Text + "' OR product_id='" + R_Product.Text + "'OR product_name='" + R_ProName.Text + "' OR gate_id='" + R_Gate.Text + "')"; } if (R_Product.Text != "") { query = query + " AND " + "product_id=" + "'" + R_Product.Text + "' "; } if (R_ProName.Text != "") { query = query + " AND " + "product_name=" + "'" + R_ProName.Text + "' "; } if (R_Gate.Text != "") { query = query + " AND " + "gate_id=" +"'" + R_Gate.Text + "' "; }
