Jump to content
Sign in to follow this  
Joboy2k

Searching entire 2D array

Recommended Posts

Hey there, I'm looking for help with something that may just be so simple that I'm just overlooking it.

 

Im making an inputbox that searches through an array and returns all results, I've tried _ArraySearch which only returns 1 result and I've also tried _ArrayFindAll which does search the entire array but only in 1 column. Is there a built in function that will search the entire array (all rows and columns) for the results. I know I can make loops to go through all the columns but these arrays could be massive and also as i'm using WM_COMMAND and  getting the search to update a listview live with every keystroke so would prefer not to have to do 15-20 loops to cover all columns after every letter typed.

 

I'm just hoping someone can point me in the right direction of something that will make this process a little simpler.

 

Thanks in advance

Jobo

Share this post


Link to post
Share on other sites

I don't really have a code for this issue yet because I don't know how to do it.

I have an array populated by a sqlite.db

 

$SQLArray[10000][20]

_ArrayFindAll only searches 1 column out of the 20

 

Is there a way to search every column with 1 command rather than

$Results = _ArrayFindAll($SQLArray, "SearchCriteria", 0, 0, 0, 1, 1)

$Results = _ArrayFindAll($SQLArray, "SearchCriteria", 0, 0, 0, 1, 2)

$Results = _ArrayFindAll($SQLArray, "SearchCriteria", 0, 0, 0, 1, 3)

$Results = _ArrayFindAll($SQLArray, "SearchCriteria", 0, 0, 0, 1, 4)

ETC

Edited by joboy2k

Share this post


Link to post
Share on other sites

If you're searching a given fixed data ("SearchCriteria") in both rows and columns that means that your database schema is enormously denormalized.  Most probably the real solution to your question is to adopt a normalized DB schema.

Tell us what your 20 columns contain and what are the relationship(s) between those 20 columns.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

It's a list of spare parts for repairs, so it has a list of the name of the parts, the make and model, notes and comments on the parts, prices, quantities, vendors, part numbers etc. The Columns do have fixed data, I was looking to just have a single search inputbox for searching the entire array. Rather than a specific search criteria Inputbox for each column.

 

Similar to being in excel, you would press CTRL+F type in what you're looking for and it will take you the result in any column, im looking to type into a single box and it filter out everything that doesn't match the search criteria to cut the list down.

 

Basically its completely unknown as to what the user will be searching for and I dont want them messing around selecting what type of data they need returned, I just want it to return everything that matches without it mattering what column its in. So if they type "Sony" I want it to find any mention of Sony, whether in the Make column of the Notes or the Comments or if they type "12" I want it to find any "Part Number", "Price" or "Quantity" etc

 

Edited by joboy2k

Share this post


Link to post
Share on other sites

That's a pretty fuzzy search!
An SQL database has almost nothing in common with a spreadsheet.  An SQL table is essentially a mathematical set, each row storing an element of this set and columns in a given row storing distinct attributes of this element.

For searching "12" everywhere (row/column) you need to store prices, quantities and everything else in string format.
Rather than grabing megabytes of data and searching in a 2D array --all in pedestrian applicative code-- perhaps you could just benefit of your DB engine features.  For instance, create a companion FTS (Full Text Search) table maintained by triggers.  Then when a user enters a search term, lookup the FTS table and just display what pops up from there.

If you don't want to go that route, you can still use the power of SQL instead of app code:
select * from mytable where col1 || col2 || col3 || ... || col20 like '%12%' --cast numeric data to text where needed

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

I think that’s going to have to be my only option. I do understand its a strange request, the only reason I didn’t want to go down the SQL search route was because I have multiple users accessing at the same time (I know this is not recommended but I have put measures in to try and stop and clashes while reading/writing to the DB). The search was going to be live, every single Character click would research the array and would be cutting the list down live as you’re typing using WM_Command which gets updated every single change. This is to risky to use in the database search as it multiplies the risk of clashes by crazy amounts(was trying to keep the access to the DB to a minimum).

 

Thank you for your help and I will redesign the program around the suggestions you made and see which one works out best. 

 

Share this post


Link to post
Share on other sites

I've no idea which DB engine you use, but read concurrency isn't going to be a problem.  For instance in SQLite the WAL mode allows one writer AND multiple readers concurrently without any issue.

Character by character search and refinment in a local array is maybe your best approach, given your requirements.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

On a somewhat large array like you have, I am not sure going char by char would be the best approach, as you would need to show the entire 2D array on screen...

I would probably go with something simpler and display a complete result at once, like this example :

#include <Constants.au3>
#include <Array.au3>
#include <Math.au3>

Opt ("MustDeclareVars", 1)

Local $aGlobal[100][20]
Local $aResult
Local $aFinalResult[UBound ($aGlobal,1)][UBound ($aGlobal,2)]
Local $sSearch = "ab12"

; generate random content (your SQL query here)
For $i = 0 to UBound ($aGlobal,1)-1
  For $j = 0 to UBound ($aGlobal,2)-1
    For $k = 0 to StringLen ($sSearch)-1
      $aGlobal[$i][$j] &= StringMid ($sSearch, Random (1, StringLen ($sSearch), 1), 1)
    Next
  Next
Next

; locate all matches
Local $iLine = 0, $iCol = 0
For $i = 0 to UBound ($aGlobal,2)-1
  $aResult = _ArrayFindAll ($aGlobal, $sSearch, 0, 0, 0, 0, $i)
  If Not IsArray ($aResult) Then ContinueLoop
  For $j = 0 to UBound ($aResult)-1
    $aFinalResult[$iLine][$j] = $aResult[$j] & "/" & $i
  Next
  $iCol = _Max ($iCol, UBound ($aResult))
  $iLine += 1
Next

; show result - instead of showing line and column number, you could use DB names
ReDim $aFinalResult[$iLine][$iCol]
_ArrayDisplay ($aFinalResult)

 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...