Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Multi field search problem (MYSQL) (ASP)

c# mysql search

This topic has been archived. This means that you cannot reply to this topic.
1 reply to this topic

#1 Aaron.H

Aaron.H

    CC Regular

  • New Member
  • PipPipPip
  • 31 posts

Posted 23 July 2015 - 10:16 AM

Hello all,

 

I've been helping my friend with a website that they paid someone to do, but ended up running off with their money. So basically, the issue is a muli field search.

 

What the search is supposed to do is: Take all fields into consideration and generate a MYSQL query based on it and display the row that meet the criteria.

 

But what it seems to be doing is only using one of the fields and searching with that. So, if you enter a City and a Country, it will automatically just search the country and instead of narrowing down the search with the city.

I tried replacing the strQuery += "OR 

with and strQuery += "AND

 

But still no luck. I'm not familiar with C# or MYSQL, I'm just trying to use my knowledge of other languages. Can anyone identify the problem, maybe it's a syntax issue opposed to a logical issue?

 

 

I hope that makes sense.

 

Another issue is that the names of the Therapist names are showing up First Name, Last Name opposed to Last Name, First Name. I tried reversing the order of the "OrderBy and 'order by' statements' but no luck.

 

Here is the link to the application: http://www.shiatsuas...dTherapist.aspx

 

And here is the source

´╗┐using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EntitySTAO;
using System.Data;
namespace STAOweb
{
    public partial class FindTherapist : System.Web.UI.Page
    {
        STAOEntities iE = new STAOEntities();
        string strQuery = "";
        string name, city, state, country, postalcode;
        bool housecalls,checkall;
        protected void Page_Load(object sender, EventArgs e)
        {
            Page.MaintainScrollPositionOnPostBack = true;
            
            if (!IsPostBack)
            {
               // BindMember();
                if (!IsPostBack)
                {
                    checkall = false;
                    if (Request["search"] != null && Request["search"] != "")
                    {
                        if (Request["search"] != "all")
                        {
                            string name = Request["search"].ToString().Trim();

                            var omember = iE.Members.Where(db => (String.Concat(db.UserFirstName, " ", db.UserLastName).Contains

(name)) && (db.UserStatus == true)).ToList();
                            searchform.Visible = false;
                            spanText.Visible = true;
                            lblCount.Text = omember.Count.ToString();
                            rptSearchresult.DataSource = omember.OrderBy(member => member.UserFirstName + 

member.UserLastName).ToList();
                            rptSearchresult.DataBind();
                        }
                        else
                        {
                            searchform.Visible = false;
                            searchmember();
                        }
                    }
                }
            }
        }
        protected void btnSubmit_Click(object sender, EventArgs e)
        {

         //   searchmember();

        }
        protected void lbViewDetails_Click(object sender, EventArgs e)
        {

            LinkButton lnk = (LinkButton)sender;
            Member omember;
            if (lnk != null)
            {
                int memberid = int.Parse(lnk.CommandArgument);
                omember = iE.Members.Where(db => db.Id == memberid).FirstOrDefault();
                lbldname.Text = omember.UserFirstName + " " + omember.UserLastName;
                lbldCST.Text = omember.CST_Number;

                ///Primary Address///
                ///
                lblPCName.Text = omember.Company1;
                lblPAddress.Text = omember.Address1;
                lblPCity.Text = omember.City1;
                lblPState.Text = omember.State1;
                lblPPhone.Text = omember.HomePhone1;
                lblPEmail.Text = omember.Email1;
                if (omember.Email1 != "")
                {
                    ancemail.HRef = "mailto:" + omember.Email1;
                }

                if(omember.Housecalls??false)
                {
                    lblHouseCall.Text="Yes" + omember.HousecallsText;
                }
                else
                {
                    lblHouseCall.Text = "No";
                }
                
                ///Secondary Address//
                ///
                if (omember.AdditionalCompany??false)
                {
                    divSec.Visible = true;
                }
                    lblBusCompany1.Text = omember.Bus1_Company_Name;
                    lblBusAddress.Text = omember.Bus1_Address1;
                    lblBusCity.Text = omember.Bus1_City;
                    lblBusState.Text = omember.Bus1_Prov_State;
                    lblBusinessPhone.Text = omember.Bus1_phone;
                    lblBusinessEmail.Text = omember.Bus1_Email;
                    if (omember.Bus1_Email != "")
                    {
                        ancemail.HRef = "mailto:" + omember.Bus1_Email;
                    }
                

                ScriptManager.RegisterStartupScript(this, Page.GetType(), "sri", "sri = true;", true);
            }
        }

        protected void searchmember()
        {
            try
            {
                spanText.Visible = true;
                strQuery = "select m.* from Members m";
                bool blnflag = false;
                if (!string.IsNullOrEmpty(txtName.Text))
                {
                    if (blnflag)
                    {
                       // strQuery += " OR m.UserFirstName  like  '%" + txtName.Text.ToString().Trim() + "%'  OR m.UserLastName  

like  '%" + txtName.Text.ToString().Trim() + "%'";
                        strQuery += " OR m.UserFirstName + ' ' +   m.UserLastName  like  '%" + txtName.Text.ToString().Trim() + 

"%'";
                    }

                    else
                    {
                        strQuery += " WHERE m.UserFirstName + ' ' +   m.UserLastName  like  '%" + txtName.Text.ToString().Trim() + 

"%'";
                    }
                    blnflag = true;
                }

                if (!string.IsNullOrEmpty(txtCity.Text))
                {
                    if (blnflag)
                    {
                        strQuery += " OR m.City1  like  '%" + txtCity.Text.ToString().Trim() + "%' ";
                    }

                    else
                    {
                        strQuery += " WHERE m.City1  like  '%" + txtCity.Text.ToString().Trim() + "%' ";
                    }
                    blnflag = true;
                    //city = txtCity.Text.Trim();
                }

                if (!string.IsNullOrEmpty(txtState.Text))
                {
                    if (blnflag)
                    {
                        strQuery += " OR m.State1  like  '%" + txtState.Text.ToString().Trim() + "%' ";
                    }

                    else
                    {
                        strQuery += " WHERE m.State1  like  '%" + txtState.Text.ToString().Trim() + "%' ";
                    }
                    blnflag = true;
                    state = txtState.Text.Trim();
                }

                if (!string.IsNullOrEmpty(txtCountry.Text))
                {
                    if (blnflag)
                    {
                        strQuery += " OR m.Country1  like  '%" + txtCountry.Text.ToString().Trim() + "%' ";
                    }

                    else
                    {
                        strQuery += " WHERE m.Country1  like  '%" + txtCountry.Text.ToString().Trim() + "%' ";
                    }
                    blnflag = true;
                    country = txtCountry.Text.Trim();
                }

                if (!string.IsNullOrEmpty(txtPosatal.Text))
                {
                    if (blnflag)
                    {
                        strQuery += " OR m.PostalCode1  like  '%" + txtPosatal.Text.ToString().Trim() + "%' ";
                    }

                    else
                    {
                        strQuery += " WHERE m.PostalCode1  like  '%" + txtPosatal.Text.ToString().Trim() + "%' ";
                    }
                    blnflag = true;
                    postalcode = txtPosatal.Text.Trim();
                }

                if (chkHousecalls.Checked)
                {
                    checkall = true;
                    if (blnflag)
                    {
                        strQuery += " OR m.Housecalls =1";
                    }

                    else
                    {
                        strQuery += " WHERE m.Housecalls=1 ";
                    }
                    blnflag = true;
                }
                else if (checkall == true)
                {
                    if (blnflag)
                    {
                        strQuery += " OR m.Housecalls =0";
                    }

                    else
                    {
                        strQuery += " WHERE m.Housecalls=0 ";
                    }
                    blnflag = true;
                }

                if (blnflag)
                {
                    strQuery += " AND (m.UserType ='user') AND (m.UserStatus = 1)";
                }

                else
                {
                    strQuery += " WHERE (m.UserType ='user') AND (m.UserStatus = 1)";
                }

                
                strQuery += " order by m.UserFirstName, m.UserLastName " ;
                SearchDAL objSearch = new SearchDAL();
                DataTable searchresult = objSearch.fn_GetSearchResult(strQuery);
                lblCount.Text = searchresult.Rows.Count.ToString();
                rptSearchresult.DataSource = searchresult;
                rptSearchresult.DataBind();
            }
            catch (Exception ex)
            {
            }
        
        }
        protected void rptSearch_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                HiddenField hdfId = (HiddenField)e.Item.FindControl("hdfId");
                Label lblNear1 = (Label)e.Item.FindControl("lblNear1");
                Label lblNear2 = (Label)e.Item.FindControl("lblNear2");
                Label lblCity1 = (Label)e.Item.FindControl("lblCity1");
                if (hdfId != null)
                {
                    int id= int.Parse(hdfId.Value);
                    Member omember= iE.Members.Where(db=>db.Id ==id).FirstOrDefault();
                    if(omember.Nearest_Intersection_1_of_2 != null && omember.Nearest_Intersection_1_of_2 !="")
                    {
                        lblNear1.Text = omember.Nearest_Intersection_1_of_2;
                    }
                    if (omember.Nearest_Intersection_1_of_2 != null && omember.Nearest_Intersection_1_of_2 != "" && 

omember.Nearest_Intersection_2_of_2 != null && omember.Nearest_Intersection_2_of_2 != "")
                    {
                        lblNear2.Text = " & " + omember.Nearest_Intersection_2_of_2;
                    }
                    else if (omember.Nearest_Intersection_2_of_2 != null && omember.Nearest_Intersection_2_of_2 != "")
                    {
                        lblNear2.Text =  omember.Nearest_Intersection_2_of_2;
                    }
                    if (omember.City1 != null && omember.City1 != "")
                    {
                        lblCity1.Text = omember.City1;
                    }
                }
            }
        }
        protected void BindMember()
        {
            try
            {
                //search = "";
                var objUser = iE.Members.Where(p => ((p.UserType.ToLower() == "user") && (p.UserFirstName.Contains("")))).ToList();

                if (objUser != null)
                {
                    rptSearchresult.DataSource = objUser;
                    rptSearchresult.DataBind();
                }
            }
            catch (Exception ex)
            {
            }
        }
    }
}



#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 23 July 2015 - 12:24 PM

The SQL you want to generate will be similar to this:

 

SELECT * FROM LOCATION WHERE COUNTRY='United States' AND CITY='New York';

 

You can chain in as many conditions as you want.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download