Search a custom database on multiple fields

160 Posts

insomnix Reply #1, 14 years, 11 months ago

I don't know why I'm having such a hard time with this, it seams simple enough. I have a single database with 6,000 records. I don't want to display each record on a page, there just isn't enough information to justify that. I just want to display the information in a table and make it searchable. Right now I am using rowboat with getpage and it displays the information perfectly. The problem is I'm not sure how to add a form and use the where clause to search rowboat on multiple fields.

I am open to trying a different solution, but I've already tried AdvSearch and couldn't even get it to display all the data on initial page load, and I couldn't figure out how to search more than one field with SimpleSearch.

Any help would be greatly appreciated.

My schema, if it helps:

<?xml version="1.0" encoding="UTF-8"?>
<model package="players" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
    <object class="Player" table="players" extends="xPDOSimpleObject">
        <field key="pla_team" dbtype="varchar" precision="25" phptype="string" null="false" default=""/>
        <field key="pla_position" dbtype="varchar" precision="10" phptype="string" null="false" default=""/>
        <field key="pla_first_name" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
        <field key="pla_mid_name" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
        <field key="pla_last_name" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
        <field key="pla_age" dbtype="int" precision="3" attributes="unsigned" phptype="integer" null="true" default="0"/>
        <field key="pla_height" dbtype="varchar" precision="15" phptype="string" null="false" default=""/>
        <field key="pla_weight" dbtype="int" precision="3" attributes="unsigned" phptype="integer" null="true" default="0"/>
        <field key="pla_school" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
        <field key="pla_city" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
        <field key="pla_state" dbtype="varchar" precision="3" phptype="string" null="false" default=""/>
 
    </object>
</model>

24,544 Posts

BobRay Reply #2, 14 years, 11 months ago

You should be able to use the &where property with RowBoat:

http://rtfm.modx.com/display/ADDON/Rowboat.Rowboat

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

160 Posts

insomnix Reply #3, 14 years, 11 months ago

Yes, but how do I fill that &where clause dynamically with multiple search criteria? I'm still a little new at modx, so please forgive my ignorance.

Thanks

24,544 Posts

BobRay Reply #4, 14 years, 11 months ago

&where takes a JSON string that's a JSON encoded array. If all you need are AND clauses, you can do something like this:

<php
$a = array(
      'parent'=>'12',
      'published'=>'1',
      'deleted'=>'0',
      'hidemenu'='0',
);

$string = $modx->toJSON($a);

Then use that string in the where property. [ed. note: BobRay last edited this post 14 years, 11 months ago.]

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

160 Posts

insomnix Reply #5, 14 years, 11 months ago

So this is in a snippet, then how do I pass this to &where in my search page?

[[!getPage?
   &element=`Rowboat`
   &table=`players`
   &sortBy=`pla_year`
   &tpl=`player.row.tpl`
   &totalVar=`rowboat.total`
   &where=``
   &cache=`0`
   &limit=`50`
]]

Thanks for your patience.

24,544 Posts

BobRay Reply #6, 14 years, 11 months ago

If you're not familiar with JSON notation (and don't want to mess with it), you can make a utility snippet from the code I posted above, change the values to match your search, and return the JSON string from it for display. Then you can paste that string into the &where property.

It's also possible that RowBoat would respond properly to just passing the array in that value.

$where=`array('parent'=>'12,'published'=>'1','deleted'=>'0', 'hidemenu'='0');`

BTW, my code above was incorrect, I've fixed it.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

160 Posts

insomnix Reply #7, 14 years, 11 months ago

OK, thanks for the point to JSON. I was able to do multiple where search using this code.

&where=`{"pla_year":"1971","pla_magazine":"KO","pla_last_name:LIKE":"%ra%"}`

74 Posts

BearsFan Reply #8, 10 years, 5 months ago

Hi Bob, I tried using your code to generate a string, but when I put it into the $where clause, i'm not getting any results.

Im running a getPage/Rowboat setup, and i'm trying to filter as such:

$a = array(
array(
'City:=' => [[+filter]],
'OR:PostalCode:=' => [[+filter]]
),
array(
'AND:Beds:>=' => 1,
'AND:Baths:<=' => 1
)
);

is the code i used to generate this string, and then in my &where clause as such:

&where=`[{"City:=":[[+filter]],"OR:PostalCode:=":[[+filter]]},{"AND:Beds:>=":[[+beds]],"AND:Baths:>=":[[+baths]]}]`

where bed and bath have their own placeholders, and i have a third placeholder called filter that I need to run against both city and postal code (its an input field).

any idea what is wrong with this setup?

Anthony

Quote from: BobRay at Nov 20, 2011, 10:08 PM

If you're not familiar with JSON notation (and don't want to mess with it), you can make a utility snippet from the code I posted above, change the values to match your search, and return the JSON string from it for display. Then you can paste that string into the &where property.

It's also possible that RowBoat would respond properly to just passing the array in that value.
$where=`array('parent'=>'12,'published'=>'1','deleted'=>'0', 'hidemenu'='0');`
BTW, my code above was incorrect, I've fixed it.

24,544 Posts

BobRay Reply #9, 10 years, 5 months ago

You need quotes around the tags:

$a = array(
array(
    'City:=' => '[[+filter]]',
    'OR:PostalCode:=' => '[[+filter]]'
),
array(
    'AND:Beds:>=' => 1,
    'AND:Baths:<=' => 1
));

Since they're not integers, they need to be in quotes.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting