[SOLVED] How to query mysql database using like

Hi, nice community :slight_smile:
> fullNameQuery := r.FormValue(“full_name”)

	rows, err := db.Query("SELECT id, full_name, school FROM students WHERE full_name LIKE '%' ? '%' LIMIT       50", fullNameQuery) 

the above code doesn’t work, I tried everything.
and when querying using = instead of like doesn’t work either for any string based fields, just any digit based field retrieve right results.
my string based fields are utf8 non latin, so is that why? if so how to make it work?
I am using
github.com/go-sql-driver/mysql driver

hmm it is not about encoding, I tried with latin data and using
rows, err := db.Query("SELECT id, name FROM data WHERE name = ?", fullNameQuery)
it is not working why?

Have you already inspected the value of err returned by db.Query()? The error message usually points to the possible reasons for the error.

My guess is that the LIKE errors out:

 LIKE '%' ? '%' 

Try moving the wildcards outside the query string:

fullNameQuery :=  "%" + r.FormValue("full_name") + "%"
rows, err := db.Query("SELECT id, full_name, school FROM students WHERE full_name LIKE ? LIMIT 50", fullNameQuery)
1 Like

Thanks @christophberger please see my update.
and I’ll use ur advice :slight_smile:

edit: I used ur advice but it returns empty slice.

Looks like we need to investigate deeper.

  • What does err contain after the call?
  • What does fullNameQuery contain before the call? Does this value exist in the database?

there is no error, the program working just return empty slice.
I managed to make it work by omitting the quotation mark of the get parameter fullNameQuery.
is that normal?

Which quotation mark did you omit?

http://localhost:1234?full_name="John"
The quotation marks around "John"
when I send it like this it works :
http://localhost:1234?full_name=John
is this right?

Ok, so the quotation marks are part of the queryFullName string. Yes, these quotation marks can get in the way, The SQL query would then search for %"John"% instead of %John%.

If you don’t want to (or cannot) remove the quotes from the URL, you can use strings.Trim(fullNameQuery, "\"") to remove the quotation marks from fullNameQuery,

1 Like

@christophberger Thanks a lot, I can’t thank you enough :smile:

No problem, glad I could help! :slight_smile:

Sorry to bother you but I tried ur code :
strings.Trim(fullNameQuery, “”")
but it didn’t work, it still returning the string with quotation marks.

Does fullNameQuery already include the percent signs?

strings.Trim trims characters only from the start or the end of the string and leaves the rest of the string untouched.

You could trim the result of FormValue() directly, before putting the percent signs around the string; although the line becomes a bit long then:

fullNameQuery :=  "%" + strings.Trim(r.FormValue("full_name"), "\"") + "%"

If fullNameQuery then still includes quotation marks, inspect those quotation marks closer. Maybe they are not the usual straight quotes " but rather “curved” quotes (see this list, code points U+201C and U+201D). You can add these to the “cutset” (like so: string.Trim(stringToTrim, "\"“”") and Trim will then remove them as well.

Edit: here is the cutset again but enlarged, so that the difference between straight and curved quotes becomes more visible:

“”“”"

I don’t know what to say, I am really grateful :smile:
this worked beautifully.

Glad to hear it is solved!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.