Searching entire 2D array

[Joboy2k]

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

[Nine]

Show your code so you can help us to help you |

[Joboy2k]

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 July 4, 2019 by joboy2k

[jchd]

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.

[Joboy2k]

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 July 4, 2019 by joboy2k

[jchd]

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

 

[Joboy2k]

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. 

 

[jchd]

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.

[Nine]

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)