Search question

StungStang · March 8, 2011

Hi to all, i've an sql like that :

;====================================================================================
;
;                                 First Table "America"
;
;====================================================================================

;   Name        |Streaming         | Site               | Genre       | State       |
;   ----------------------------------------------------|-------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         | California  |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          | Florida     |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        | New Y       |


;====================================================================================
;
;                                 Second Table "Europe"
;
;====================================================================================

;   Name        |Streaming         | Site               | Genre       | State       |
;   ----------------------------------------------------|-------------|-------------|
;   MyRadio1    |mms://myradio1.asx|www.myradio1.com    |Rock         | France      |
;   Myprefradio2|mms://mypref2.asx |www.mypref2.net     |Pop          | Germany     |
;   MyExample3  |http.//link3.mp3  |www.myexample3.com  |Dance        | Spain       |

For example i've two table : "America" and "Europe"...

Now i want search for example all the radio that start with "My" string...i only know how to search in one table...but how i can search on 2 table, or more?

This is my code for 1 Table search:

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>

Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg
$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Country", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)

GUISetState(@SW_SHOW)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\Database.db")

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Search
            $Valor = GUICtrlRead ($Input1)
            _Search($Valor)
    EndSwitch
WEnd


Func _Search ($Value)
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)
For $i = 1 to $iRows
$Line = $aResult[$i][0] & "|" & $aResult[$i][1] & "|" & $aResult[$i][2] & "|" & $aResult[$i][3] & "|" & $aResult[$i][4]
    ConsoleWrite($Line) ;For debug purpose
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
_GUICtrlListView_EndUpdate($ListView)
EndFunc

How you can see now im able only to search in the table "America"...but if i want search in the table "America" and "Europe"?

I try with this string :

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America AND Europe WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)

But dont work

How i can do that?

Hi!

EDIT:

I try with this string :

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America,Europe WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)

But it give me this error :

Error: ambiguous column name: Name

How to fix?

Hi!

Edited March 8, 2011 by StungStang

Xenobiologist · March 8, 2011

Hi,

try to join them.

http://www.shokhirev.com/nikolai/abc/sql/joins.html

Mega

StungStang · March 8, 2011

Always same error with this string :

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America CROSS JOIN Europe WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)

Hi!

SmOke_N · March 8, 2011

Check out "inner join" in the link that Xenobiologist provided

Edited March 8, 2011 by SmOke_N
removed link, xeno already provided

StungStang · March 8, 2011

Same error with this string :

$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America INNER JOIN Europe WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)

If you would try my script this is the database generator :

$DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)
           ;Create Table America
           _SQLite_Exec (-1, "CREATE TABLE America (Name,Streaming,Site,Genre,State);")
           ;Add value to table America
           _SQLite_Exec (-1, "INSERT INTO America VALUES ('MyRadio','mms://myradio.asx','www.myradio.com','Rock','California');")
           _SQLite_Exec (-1, "INSERT INTO America VALUES ('Myprefradio','mms://mypref.asx','www.mypref.net','Pop','Florida');")
           _SQLite_Exec (-1, "INSERT INTO America VALUES ('MyExample','http.//link.mp3','www.myexample.com','Dance','New Y');")
           ;Create Table Europe
           _SQLite_Exec (-1, "CREATE TABLE Europe (Name,Streaming,Site,Genre,State);")
           ;Add value to table Europe
           _SQLite_Exec (-1, "INSERT INTO Europe VALUES ('MyRadio1','mms://myradio1.asx','www.myradio1.com','Rock','France');")
           _SQLite_Exec (-1, "INSERT INTO Europe VALUES ('Myprefradio2','mms://mypref2.asx','www.mypref2.net','Pop','Germany');")
           _SQLite_Exec (-1, "INSERT INTO Europe VALUES ('MyExample3','http.//link3.mp3','www.myexample3.com','Dance','Spain');")

Or you can download it here.

Hi!

Xenobiologist · March 8, 2011

Hi,

is the something you want to achieve?

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit -1
EndIf

;Example Table
;   Name        | Age
;   -----------------------
;   Alice       | 43
;   Bob         | 28
;   Cindy       | 21

If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Alice','43');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Bob','28');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Cindy','21');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())

If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons2 (Name, Age);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons2 VALUES ('Mega','43');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons2 VALUES ('Man','28');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons2 VALUES ('Gina','21');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())

$iRval = _SQLite_GetTable2d(-1, "SELECT i.Name AS cName, i.Age AS cAge FROM persons as i INNER JOIN persons2 as s USING (Age) where s.Name like 'M%';", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)
;~
;~   Name  Alice  Bob  Cindy
;~   Age   43   28   21sfsff

Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()

Mega

StungStang · March 8, 2011

@Xeno

I want simple do a partial search from 2 or more table...

For example with this two table :

;====================================================================================
;
;                                 First Table "America"
;
;====================================================================================

;   Name        |Streaming         | Site               | Genre       | State       |
;   ----------------------------------------------------|-------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         | California  |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          | Florida     |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        | New Y       |


;====================================================================================
;
;                                 Second Table "Europe"
;
;====================================================================================

;   Name        |Streaming         | Site               | Genre       | State       |
;   ----------------------------------------------------|-------------|-------------|
;   MyRadio1    |mms://myradio1.asx|www.myradio1.com    |Rock         | France      |
;   Myprefradio2|mms://mypref2.asx |www.mypref2.net     |Pop          | Germany     |
;   MyExample3  |http.//link3.mp3  |www.myexample3.com  |Dance        | Spain       |

I want to search all the radio station that contain in their name string "radio".

I want just simply search the "name" string in all table that i've in the database

This is my example code :

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>

Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg
$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Country", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)

GUISetState(@SW_SHOW)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\Database.db")

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Search
            $Valor = GUICtrlRead ($Input1)
            _Search($Valor)
    EndSwitch
WEnd


Func _Search ($Value)
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM America WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)
For $i = 1 to $iRows
$Line = $aResult[$i][0] & "|" & $aResult[$i][1] & "|" & $aResult[$i][2] & "|" & $aResult[$i][3] & "|" & $aResult[$i][4]
    ConsoleWrite($Line) ;For debug purpose
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
_GUICtrlListView_EndUpdate($ListView)
EndFunc

But it work only for the table "America", but i want to search the name on "America" table and "Europe" table

Sorry for my english, but i think that you can undertand what i've write (i hope =P, my english is very bad)

Hi!

Xenobiologist · March 9, 2011

Hi,

I think what you need is a FULL OUTER JOIN which you want to filter. (You want every line of every table and then filter)

This is currently not possible in sqlite.

You need do solve the problem with two Select statement, I guess.

Mega

SmOke_N · March 9, 2011

What does this query get you?

Local $s_query = "SELECT * FROM America WHERE Name LIKE '%Radio%' "
$s_query &= "UNION SELECT * FROM Europe WHERE Name LIKE '%Radio%';"
_SQLite_GetTable2d(-1, $s_query, $aResult, $iRows, $iColumns)

Xenobiologist · March 9, 2011

What does this query get you?

Local $s_query = "SELECT * FROM America WHERE Name LIKE '%Radio%' "
$s_query &= "UNION SELECT * FROM Europe WHERE Name LIKE '%Radio%';"
_SQLite_GetTable2d(-1, $s_query, $aResult, $iRows, $iColumns)

Yes, that works. But then you still got two selects.

StungStang · March 9, 2011

Yes, that work...the databse is in progress...i have more than 10 table...

With this trick is too long to write a query

It's an alternative to do that?

Hi!

Xenobiologist · March 9, 2011

The length of a query shouldn't be a problem.

You need to explain in more words what you want to achieve. Maybe there is a better way out there.

StungStang · March 9, 2011

Just simple...i want to store the radio station information...for example...

I've 5 table, each table for one continent (Europe, America, Asia, Africa, Oceania)

In each table i store the radio info (Name,Streaming Link, Web Site, Genre, Country)

This is the scheme of database...

You have a better soluction to do that?

Hi!

Xenobiologist · March 9, 2011

So there always at least two ways for a Database model

A operative system like

B optimized for reporting

It doesn't matter what kind of model you have, you can always get the data out of it as you want it. It is only a matter of speed and comfort.

So, if you need to get all rows of 5 tables where a column has a value x then, of course you can do that via SQL.

StungStang · March 9, 2011

It's right my sql structure? Or you have a better way to do that? What is the best in term of speed?

Hi!

kylomas · March 9, 2011

StungStang,

$.02 worth - make the country a field and use 1 table only..

kylomas

StungStang · March 9, 2011

For you it's best in term of speed?...My first problem is the speed

If i create a field for country i've a lot of redundant data...for example if i've to add 2.000 American radio station, i've to write 2.000 times the field "Country" with the value America. It's not good for me in term of speed...or not?

hi!

Edited March 9, 2011 by StungStang

kylomas · March 9, 2011

StungStang,

You are correct. If speed is the primary concern then the data should be normalized to whatever extent possible.

Given that, the complexity of the SQL STMT should not matter.

kylomas

Edit: I am not a DBA type but it seems to me to be a balancing act dictated by whatever the app requires. You should wait for additional responses. There are a couple really good DBA types on this board.

Edited March 9, 2011 by kylomas

StungStang · March 9, 2011

@Kylomas

I hope that a db guru can help me

Hi

SmOke_N · March 9, 2011

Your first mistake if you're concerned with speed is using _SQLite_GetTable2d; Much slower than Querying data, Fetching Data, and Finalizing Query.

Your table could be optimized quite a bit, but really, you should go off and read on DB structuring if your concern is speed.

The union's I've presented, should be just as fast as if it were all one line; be constructive, and create something that creates the query dynamically in autoit code if you have many tables ( not always a good thing ).

Search question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members