Sign in to follow this  
Followers 0
StungStang

Search question

35 posts in this topic

#1 ·  Posted (edited)

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 by StungStang

Share this post


Link to post
Share on other sites



Hi,

try to join them.

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

Mega


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

Always same error with this string :

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

Hi!

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Edited by SmOke_N
removed link, xeno already provided

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

@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!

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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)

[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

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.

Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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

Hi!

Share this post


Link to post
Share on other sites

StungStang,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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 by StungStang

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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 by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

@Kylomas

I hope that a db guru can help me :)

Hi

Share this post


Link to post
Share on other sites

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 ).


[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0