Select From Where beginners pain [solved]

[metro]

Hi Guys

I've now taken the plunge and started my first DB program, a reverse search Phone Book. I am a complete beginner with RB and despite searching posts on this board and reading here www.w3schools.com/sql/sql_where.asp
i do not understand what I'm doing wrong here.

' clean.db3
' TABLE =phone
' FIELDS=initials,name,phone_no, suburb
'CODE copied from runbasic.wikispaces.com/No+Frills+SQL+Demo
thanks mike_ukmid

[readFromTable]

input "Enter phone number to find: ";indata$

html "<div align=""center""><br />"
sqliteconnect #mySQLdb, "clean.db3"

' query$ = "select * FROM phone WHERE phone_no= '93909551';" ' THIS WORKS
' query$ = "select * FROM phone WHERE phone_no= 'indata$'" ' DOES NOT WORK
' query$ = "select * FROM phone WHERE phone_no="+ indata$ ' ***DOES NOW***
'query$ = "select * FROM phone WHERE suburb= 'ARMADALE'" 'DOES NOT WORK
query$ = "select * FROM phone WHERE suburb="+ indata$ ' ***DOES NOT WORK***
#mySQLdb execute(query$)

cssClass "table", "{ width: 400px; text-align:left;border-style:solid;border-width:1px;border-color:#000000;}"
cssClass "tr", "{ background: #FFFACD; padding: 3px }"
cssClass "td", "{width:45%;}"
render #mySQLdb
html "</div>"
#mySQLdb disconnect()
wait
I get the following error:

Runtime Error in program 'untitled': #mySQLdb execute(query$)
no such column: ARMADALE

thanks in advance

metro

[Carl Gundel - admin]

Try removing the commented out lines of code. I think you've run into a bug in th parser related to double quotes in comments.

-Carl

[metro]

Thanks Carl

I changed code to:
' clean.db3
' TABLE =phone
' initials,name,phone_no, suburb

[readFromTable]

line input "Enter phone number to find: ";indata$
html "<div align=""center""><br />"

sqliteconnect #mySQLdb, "clean.db3"

query$ = "select * FROM phone WHERE name = "+indata$
' if I replace the phone_no field with the suburb or name field it does not work
#mySQLdb execute(query$)

cssClass "table", "{ width: 400px; text-align:left;border-style:solid;border-width:1px;border-color:#000000;}"
cssClass "tr", "{ background: #FFFACD; padding: 3px }"
cssClass "td", "{width:45%;}"

render #mySQLdb
html "</div>"
#mySQLdb disconnect()
wait
I decided to search by name and used a known name in the database "KING A&J" the result is an error

Runtime Error in program 'untitled': #mySQLdb execute(query$)
near "A": syntax error and if I change the name to be found to "KING B&J" the error message shows to be NEAR "B"

update:
change search to
query$ = "select * FROM phone WHERE name ='KING A&J'"
(so no input indata$)
all I get is the report (table) header but no data
initials name phone_no suburb

also tried the following
query$ = "select * FROM phone WHERE name like '%KING'order by suburb" 'DOES NOT WORK
query$ = "select * FROM phone WHERE name like '%KING%'order by suburb" ' WORKS WHY???

any clues???

thanks

metro

[davos1]

maybe the ' and " ...
try something like
name$="bart"
a$="select * from users where name='";name$;"' "
print a$
use the ' to indicate that there is something
use the " to exit the string
then enter the rb variable like name$

then if you wish you could print the string to check the syntax

[metro]

Thank davos1

tried your suggestion but modified slightly to print the output to screen.

FIRST:
query$ = "select * FROM phone WHERE name='";indata$;"'"
print query$
result of print query$ = select * FROM phone WHERE name='KING'

SECOND:
query$ = "select * FROM phone WHERE name =" + "'" + indata$ + "'"
print query$
result of print query$ = select * FROM phone WHERE name ='KING'

so I think either method gives the correct format for "select"
but for some reason nothing is found, even though I have entered a record with the name "KING".
I then tried these:

1/: query$ = "select * FROM phone WHERE name like" + "'%" + indata$ + "%'" +"ordered by suburb"

print query$=select * FROM phone WHERE name like'%KING%'ordered by suburb

SO FOR 1/: I get a syntax error Runtime Error in program 'untitled': #mySQLdb execute(query$)
near "ordered": syntax error

2/:query$ = "select * FROM phone WHERE name like '%KING%'order by suburb"

print query$=select * FROM phone WHERE name like '%KING%'order by suburb

the printing of query$ gives the same out put but only the second option gives a result of a few hundred records

there must be a tutorial some where to cover this but i don't seem to be able to find it.
only this www.w3schools.com/sql/sql_where.asp

so my observation is using = finds nothing
using LIKE with wildcard sometimes gives a result


just about ready to give up

metro

[kokenge]

I tried a few of your commands.
They all seem to be ok

Was to lazy to create a DB on disk so did it in memory.
I also used rowid as the key to the data.
But you can copy this into your RB and try it.
Hope this helps.

' create in memory DB
sqliteconnect #mySQLdb, ":memory:"
sql$ = "CREATE TABLE phone(name,phoneNum)"
#mySQLdb execute(sql$)

' put some names and numbers in the db
sql$	= "
INSERT INTO phone values ('John',   '01782556677');
INSERT INTO phone values ('Mary',   '01248763132');
INSERT INTO phone values ('King',   '20394810322');
INSERT INTO phone values ('Bill',     '23243423417');
INSERT INTO phone values ('Metro',  '93909551');
INSERT INTO phone values ('Davos1', '75243423417');
INSERT INTO phone values ('George', '97243423417');"
print sql$
i = 1
WHILE word$(sql$,i,";") <> ""
  sql1$	= word$(sql$,i,";")
  print "sql1:";sql1$
  #mySQLdb execute(sql1$)
  i = i + 1
WEND

' print the DB data in name sequence
sql$ = "SELECT *,rowid FROM phone ORDER BY name"
#mySQLdb execute(sql$)
html "<TABLE BORDER=1>"
WHILE   #mySQLdb hasanswer()
	#row 		= #mySQLdb #nextrow()
	rowid		= #row rowid()
	name$		= #row name$()
	phoneNum$	= #row phoneNum$()
	html "<TR><TD>";rowid
	html "<TD>";name$
	html "<TD>";phoneNum$
WEND
html "</table>"

sql$ = "SELECT * FROM phone WHERE phoneNum = '93909551'"
#mySQLdb execute(sql$)
#row = #mySQLdb #nextrow()
print "FOR sql:";sql$;" Result:";#row name$()

indata$ = "20394810322"
sql$ = "SELECT * FROM phone WHERE phoneNum = '";indata$;"'"
#mySQLdb execute(sql$)
#row = #mySQLdb #nextrow()
print "FOR sql:";sql$;" Result:";#row name$()

sql$ = "SELECT * FROM phone WHERE phoneNum = '" + indata$ + "'"
#mySQLdb execute(sql$)
#row = #mySQLdb #nextrow()
print "FOR sql:";sql$;" Result:";#row name$()

sql$ = "SELECT * FROM phone WHERE name = 'Metro'"
#mySQLdb execute(sql$)
#row = #mySQLdb #nextrow()
print "FOR sql:";sql$;" Result:";#row name$()

indata$ = "King"
query$ = "SELECT * FROM phone WHERE name = '" + indata$ + "'"
#mySQLdb execute(sql$)
#row = #mySQLdb #nextrow()
print "FOR sql:";sql$;" Result:";#row name$()

' find all names that has the letter "i" in it somewhere
print "Name has i in it"
sql$ = "SELECT *,rowid FROM phone where name like '%i%' ORDER BY name"
#mySQLdb execute(sql$)
html "<TABLE BORDER=1>"
WHILE   #mySQLdb hasanswer()
	#row 		= #mySQLdb #nextrow()
	rowid		= #row rowid()
	name$		= #row name$()
	phoneNum$	= #row phoneNum$()
	html "<TR><TD>";rowid
	html "<TD>";name$
	html "<TD>";phoneNum$
WEND
html "</table>"
' find all names that end in "n" 
print "Names ending with n"
sql$ = "SELECT *,rowid FROM phone where name like '%n' ORDER BY name"
#mySQLdb execute(sql$)
html "<TABLE BORDER=1>"
WHILE   #mySQLdb hasanswer()
	#row 		= #mySQLdb #nextrow()
	rowid		= #row rowid()
	name$		= #row name$()
	phoneNum$	= #row phoneNum$()
	html "<TR><TD>";rowid
	html "<TD>";name$
	html "<TD>";phoneNum$
WEND
html "</table>"

wait

[metro]

thanks Kokenge

I really appreciate you taking the time

Not sure what to make of this but even taking your code and making minor adjustments to some names I still get errors or no record found.

At first I thought it may have been the leading blank I found in the name field and suburb field. But I deleted the table and read in the 480,000 records again and TRIM$()'d the leading space of each field
still no joy.

I wondered if my naming of the DB with .db3 extension is causing problems but then why would a query on the 'phone_no' succeed?

Can indexes create problems? I have a primary index on phone_no and wondered if that's why a query works with phone_no

thanks again anyway I will keep at it, but maybe after creating another DB with less records.

regards

metro

[kokenge]

Sounds good Metro.


I still get errors or no record found.

What does your DB and the SQL command look like?


I thought it may have been the leading blank I found in the name field and suburb field.

Leading blanks would not be good..


I wondered if my naming of the DB with .db3 extension is causing problems

You can call it any valid file name you like.


Can indexes create problems? I have a primary index on phone_no and wondered if that's why a query works with phone_no

For large files indexes are good.
Personally I stay away from using the Primary index. It makes too many assumptions I don't like. Basically the primary index will create a number if it's null. Basically it's exactly the same number as the "rowid". So why bother.
I use the unique index instead.

Another thing you may want to consider. Remember RB cannot use the under score as in phone_no. OK in the DB but when you pull data from the DB you cannot use that name. Better to be consistent and call it something like phoneNo. That way your RB and DB names will match.. sup to you however..

If you want you can post your DB and the sql command you are having problems with, and I'll have a look-see.

Hope this helps..

[metro]

Thanks again kokenge
mystery solved

At first I thought it may have been the leading blank I found in the name field and suburb field. But I deleted the table and read in the 480,000 records again and TRIM$()'d the leading space of each field
still no joy.

seems it helps if you copy the newly created database into your working directory

I really appreciate the time you spent helping, the code you posted has very informative for a novice like me.

all the best

metro

[kokenge]

Sounds Great..

A couple of projects that you may want to consider.
They can cut your development time considerably, and you will be able to keep your hair a lot longer.

Go here and check them out..
runbasic.wikispaces.com/Projects

1. SQLite Database manager.
This helps you create a database and indexes. The query lets you enter sql commands and test them before you put them into code. Actually once you run your query, it gives you the option of generate the RB code for your query.

It gives backups of the schema and data. You can export your database and it's data. You can load the data back by pasting the exported data into the query.
In your case you will probably want to create a table of province codes. This will make your data more accurate because of the table relationships. (a relational DB)

2. The RBgen program.
Once you have your DB defined and their relationships, this will generate the RB code to maintain the database tables. It lets you do add/change/delete, sorts, drill down, and export to spread sheets.

Again it's up to you but it will make your DB life a lot easier.

Hope this helps..
Dan

[metro]

Thanks Dan

your a legend

regards

Laurie