Mysql Query

Databases
1

Hi,
Following the Mysql Sample Project, how to do querying the data into listbox, based from textfield’s criteria,

this code still showing an error ’ unkown column ‘’ in where clause.

[code]
If Not IsConnected Then
MsgBox(“Connect to the database first.”)
End If

Dim sql As String
sql = “SELECT * FROM Team WHERE name =” + TextField5.Text

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
data.IdxField(3).StringValue, data.IdxField(4).StringValue)

  data.MoveNext
Wend
data.Close

End If[/code]

anyone knows whats missing ?

thanks
Arief

2 
+  "'" + TextField5.Text + "'"

What follows next is a long conversation about why this simplistic approach will cause you trouble in the long term.
Read up about parameterised queries.

3

Dear Jeff,

Thanks for the code,
but its seem the code only showing the result while I do typing the criteria completely.
how to make it autocomplete.

how to make it like,
there are names, crows, crows parker, crows johan,

what I need is, while I am typing ‘cro’ in the textfield, the listbox filled with data contains name with ‘cro’

thanks
arief

4

Put your query in the textfield’s KeyDown event.

5

its solved, I change the query into,

sql = "SELECT * FROM Team WHERE name LIKE '%" + TextField5.Text + "%'"

Thanks
Arief

6

Now try to enter Max’s team into Textfield5 and see what happens. Then follow the advice given to you here about prepared statements.

7

Hi,

is there anyway to do querying data base from any columns ?

sql = "SELECT * FROM Team WHERE name LIKE '%"  + TextField5.Text + "%'"

I have 10 fields, and I want to querying data in all columns which is match.

I found it on the web, its easy on php.
https://datatables.net/

thanks
arief

8

Use Xojo to create a PREPARED STATEMENT using the database table schema data… this way you can have access to all the field names and build the PREPARED STATEMENT dynamically even if the table name or table schema changes

and the link you provided is JSQuery… not ANSI SQL

9

Hi Mr. S,

Some code sample would be great…

Thanks
Arief

10

https://forum.xojo.com/3161-input-sanitization

Just search for ‘prepared’ in the forum. Lots of examples around.