<h1>Search API – <code>Search</code> Class</h1>
<p>
    The <strong>Search</strong> class gives you complete control over the search panel,
    advanced search, and filtering on your generated List pages.  It mirrors
    PHPRunner's <code>SearchClause</code> object while adding a fluent interface,
    JSON support, and automatic integration with the Fields module's saved
    search settings.
    Every method works on a specific table and can be used inside page events
    (<code>AfterTableInit</code>, <code>BeforeDisplay</code>) or in any custom code.
</p>

<h2>Contents</h2>
<ul>
    <li><a href="#get-instance">Getting a Search Instance</a></li>
    <li><a href="#field-values">Setting &amp; Getting Field Values</a></li>
    <li><a href="#search-options">Search Options</a></li>
    <li><a href="#between">BETWEEN Searches (Ranges)</a></li>
    <li><a href="#all-fields">All Fields Search</a></li>
    <li><a href="#custom-sql">Custom Search SQL</a></li>
    <li><a href="#where-clause">Building the WHERE Clause</a></li>
    <li><a href="#fluent">Fluent Chaining</a></li>
    <li><a href="#json">JSON Export &amp; Import</a></li>
    <li><a href="#reset">Resetting Search</a></li>
    <li><a href="#integration">Integration with Fields Module</a></li>
    <li><a href="#real-world">Real‑World Examples</a></li>
</ul>

<!-- =========================================== -->
<h2 id="get-instance">Getting a Search Instance</h2>
<p>Start by obtaining a <code>Search</code> object for the table you want to filter.</p>
<pre><code>use PpPhp\Core\Search;

$srch = Search::forTable('orders');
// All subsequent methods operate on the 'orders' table.</code></pre>

<!-- =========================================== -->
<h2 id="field-values">Setting &amp; Getting Field Values</h2>
<p>When you set a field value, the field is automatically added to the search panel.</p>

<h3>Example 1 – Basic value set</h3>
<pre><code>$srch->setFieldValue('status', 'shipped');
echo $srch->getFieldValue('status');  // "shipped"</code></pre>

<h3>Example 2 – Get a value that was never set</h3>
<pre><code>$value = $srch->getFieldValue('customer_name');  // null</code></pre>

<h3>Example 3 – Overwriting a value</h3>
<pre><code>$srch->setFieldValue('status', 'shipped');
$srch->setFieldValue('status', 'pending');
echo $srch->getFieldValue('status');  // "pending"</code></pre>

<!-- =========================================== -->
<h2 id="search-options">Search Options</h2>
<p>
    Choose how the value is matched against the database.
    Supported options: <strong>contains</strong>, <strong>equals</strong>,
    <strong>starts</strong>, <strong>greater</strong>, <strong>less</strong>, <strong>between</strong>.
    The default is <strong>contains</strong>.
</p>

<h3>Example 1 – Exact match</h3>
<pre><code>$srch->setFieldValue('status', 'shipped')
     ->setSearchOption('status', 'equals');
// SQL: WHERE status = 'shipped'</code></pre>

<h3>Example 2 – Substring search</h3>
<pre><code>$srch->setFieldValue('name', 'john')
     ->setSearchOption('name', 'contains');
// SQL: WHERE name LIKE '%john%'</code></pre>

<h3>Example 3 – Starts with</h3>
<pre><code>$srch->setFieldValue('name', 'A')
     ->setSearchOption('name', 'starts');
// SQL: WHERE name LIKE 'A%'</code></pre>

<h3>Example 4 – Greater than</h3>
<pre><code>$srch->setFieldValue('amount', '1000')
     ->setSearchOption('amount', 'greater');
// SQL: WHERE amount > '1000'</code></pre>

<h3>Example 5 – Reading the current option</h3>
<pre><code>$option = $srch->getSearchOption('status');  // e.g. "equals"</code></pre>

<!-- =========================================== -->
<h2 id="between">BETWEEN Searches (Ranges)</h2>
<p>Use <code>setSecondFieldValue()</code> together with <code>between</code> for range queries.</p>
<pre><code>$srch->setFieldValue('price', '100')
     ->setSecondFieldValue('price', '500')
     ->setSearchOption('price', 'between');
// SQL: WHERE price BETWEEN '100' AND '500'</code></pre>

<!-- =========================================== -->
<h2 id="all-fields">All Fields Search</h2>
<p>
    Search across all searchable fields at once.  The "All fields" value is
    combined with <strong>OR</strong> across every field that is marked as
    searchable in the Fields module.
</p>
<pre><code>$srch->setAllFieldsValue('john');
$all = $srch->getAllFieldsValue();   // "john"

// The WHERE clause will contain:
// (field1 LIKE '%john%' OR field2 LIKE '%john%' OR ...)</code></pre>

<!-- =========================================== -->
<h2 id="custom-sql">Custom Search SQL</h2>
<p>
    Replace the generated condition with raw SQL.  Use this when you need
    logic that cannot be expressed through the standard options.
</p>
<pre><code>$srch->setSearchSQL('status', "status IN ('shipped','pending') OR status IS NULL");
// SQL: WHERE (status IN ('shipped','pending') OR status IS NULL)</code></pre>

<div class="alert alert-warning">
    <i class="bi bi-exclamation-triangle"></i> <strong>Security note:</strong>
    The SQL you pass is <strong>not escaped</strong>.  Only use this method with
    trusted, hard‑coded strings – never with raw user input.
</div>

<!-- =========================================== -->
<h2 id="where-clause">Building the WHERE Clause</h2>
<p>
    Call <code>buildWhereClause()</code> to get a ready‑to‑use SQL snippet
    that you can append to your main query.
</p>
<pre><code>$where = $srch->buildWhereClause();
// Returns: " WHERE status = 'shipped' AND name LIKE '%john%'"
//   … or an empty string if no conditions are set.

$rows = DB::Query("SELECT * FROM orders {$where} ORDER BY id DESC");
while ($row = $rows->fetchAssoc()) {
    echo $row['order_number'];
}</code></pre>

<!-- =========================================== -->
<h2 id="fluent">Fluent Chaining</h2>
<p>All setter methods return <code>$this</code>, so you can chain them for compact code.</p>
<pre><code>$where = Search::forTable('orders')
    ->setFieldValue('status', 'shipped')
    ->setSearchOption('status', 'equals')
    ->setFieldValue('total', '100')
    ->setSecondFieldValue('total', '500')
    ->setSearchOption('total', 'between')
    ->buildWhereClause();</code></pre>

<!-- =========================================== -->
<h2 id="json">JSON Export &amp; Import</h2>
<p>
    You can serialise the entire search state to JSON – perfect for AJAX
    endpoints that save or restore user searches.
</p>
<pre><code>// Export
$json = json_encode($srch->toArray());
file_put_contents('saved_search.json', $json);

// Import
$srch = Search::fromJson(file_get_contents('saved_search.json'));
$where = $srch->buildWhereClause();</code></pre>

<p>You can also import from an array directly:</p>
<pre><code>$srch = Search::fromArray([
    'table'  => 'orders',
    'fields' => ['status' => 'shipped'],
    'options'=> ['status' => 'equals'],
]);
echo $srch->buildWhereClause();  // " WHERE status = 'shipped'"</code></pre>

<!-- =========================================== -->
<h2 id="reset">Resetting Search</h2>
<pre><code>$srch->resetSearch();
echo $srch->isSearchStarted();   // false
echo $srch->buildWhereClause();  // "" (empty)</code></pre>

<!-- =========================================== -->
<h2 id="integration">Integration with Fields Module</h2>
<p>
    The <strong>Fields</strong> module lets you configure per‑field search settings
    (searchable, default option, etc.).  The <code>Search</code> API reads these
    settings automatically when the Build button generates List pages, ensuring
    the search panel matches what you designed visually.
</p>
<pre><code>// In a generated List page, the Search API already knows:
//   - which fields appear on the search panel
//   - the default search option for each field
//   - whether "All fields" search is enabled
$srch = Search::forTable('customers');
// $srch->getSearchableFields() returns only fields marked as searchable
// in the Fields module.</code></pre>

<!-- =========================================== -->
<h2 id="real-world">Real‑World Examples</h2>

<h3>Example 1 – Simple filter on a List page</h3>
<pre><code>// In the List page's BeforeDisplay event
$srch = Search::forTable('products');
$srch->setFieldValue('category', 'Electronics');
$srch->setSearchOption('category', 'equals');

$where = $srch->buildWhereClause();
$products = DB::Query("SELECT * FROM products {$where}");</code></pre>

<h3>Example 2 – Advanced search with multiple fields</h3>
<pre><code>$srch = Search::forTable('customers');
$srch->setFieldValue('last_name', 'Smith')
     ->setSearchOption('last_name', 'starts');
$srch->setFieldValue('age', '18')
     ->setSearchOption('age', 'greater');
$srch->setFieldValue('status', 'active')
     ->setSearchOption('status', 'equals');

$where = $srch->buildWhereClause();
// WHERE last_name LIKE 'Smith%' AND age > '18' AND status = 'active'</code></pre>

<h3>Example 3 – Save and restore a user's search</h3>
<pre><code>// Save (e.g., on page unload via AJAX)
$_SESSION['last_search'] = json_encode($srch->toArray());

// Restore (on next page load)
if (!empty($_SESSION['last_search'])) {
    $srch = Search::fromJson($_SESSION['last_search']);
    $where = $srch->buildWhereClause();
}</code></pre>

<h3>Example 4 – Use BETWEEN for date range</h3>
<pre><code>$srch = Search::forTable('orders');
$srch->setFieldValue('order_date', '2025-01-01')
     ->setSecondFieldValue('order_date', '2025-12-31')
     ->setSearchOption('order_date', 'between');
// WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'</code></pre>

<hr>
<h3>🔗 Related Help Topics</h3>
<p>
    <a href="DB" class="btn btn-sm btn-outline-primary me-1">Database API</a>
    <a href="Security" class="btn btn-sm btn-outline-primary me-1">Security API</a>
    <a href="Helpers" class="btn btn-sm btn-outline-primary">Helpers</a>
</p>