Cannot figure out how to add WHERE with LIKE clause

jawz302

I can make a simple like equals work as in the following:

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'system' )
    ->field(
        Field::inst( 'system.name' ),
        Field::inst( 'system.ip' ),
        Field::inst( 'system.description' ),
        Field::inst( 'system.os_name' ),
        Field::inst( 'system.model' ),
        Field::inst( 'system.serial' ),
        Field::inst( 'system.switch_system_id' ),
        Field::inst( 'system.switch_port' ),
        Field::inst( 'system.id' )
            ->options( Options::inst()
                ->table( 'software' )
                ->value( 'system_id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'software.name' )
    )
    ->leftJoin( 'software', 'software.system_id', '=', 'system.id' )

    ->where( 'system.domain', 'eagle.com' )
        ->where( 'system.status', 'production' )
        ->where( 'software.current', 'y' )
        ->where( 'software.name', 'Microsoft Office 2000 SR-1 Professional' )

But instead of retrieving everything from the "software" table in the "name" column that has exactly "Microsoft Office 2000 SR-1 Professional" as the record I want to retrieve all records in the "software" table in column "name" that have the phrase "Microsoft Office" in them. I am trying to use - ->where( 'software.name', 'Microsoft Office 2000 SR-1 Professional', 'LIKE' ) or something to this affect and cannot figure out how the syntax should be. I would appreciate any help you can give.
Thank you in advance.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

tangerine

I think WHERE ... LIKE looks like this:

               ->where( 'fieldname','%target_string%','LIKE' )

allan

Yes - exactly what @tangerine says! You need the wildcard (%) to allow matching inside a string and the third parameter for ->where() to be LIKE since the default is =.

Allan

jawz302

I found that and tried it out but received - DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1. Not sure if this is caused due to joining the tables, which makes the data to large which causes database to exceed memory allocated or what. I get the same error if I don't use ->where( 'software.name', 'Microsoft Office 2000 SR-1 Professional' ) to reduce the amount of data it is dealing with. May be the LIKE is still accessing all the data and causing the memory to overload?

jawz302

Well I moved the LIKE statement to a different line and that is the correct syntax -
->where( 'fieldname','%target_string%','LIKE' )
So I guess my question should be how do I reconfigure the LEFT JOIN between the two tables and all the WHERE clauses as to not exceed memory usage.

allan

So if you include ->where( 'fieldname','%target_string%','LIKE' ) on a field which is left joined you get a memory error? Can you show me your full code and also the PHP error message page?

Allan

jawz302

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 41 bytes) in C:\xampplite\htdocs\spreadsheets\php\Editor\Options.php on line 252

<?php
 
/*
 * Example PHP implementation used for date time examples
 */
 
// DataTables PHP library
include( "/DataTables.php" );
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
 
 
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'system' )
    ->field(
        Field::inst( 'system.name' ),
        Field::inst( 'system.ip' ),
        Field::inst( 'system.description' ),
        Field::inst( 'system.os_name' ),
        Field::inst( 'system.model' ),
        Field::inst( 'system.serial' ),
        Field::inst( 'system.switch_system_id' ),
        Field::inst( 'system.switch_port' ),
        Field::inst( 'system.id' )
            ->options( Options::inst()
                ->table( 'software' )
                ->value( 'system_id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'software.name' )
    )
    ->leftJoin( 'software', 'software.system_id', '=', 'system.id' )

    ->where( 'system.domain', 'eagle.com' )
        ->where( 'system.status', 'production' )
        ->where( 'software.current', 'y' )
        ->where( 'software.name', '%Microsoft Office%', 'LIKE' )
        
    ->process( $_POST )
    ->json();

Definitely exceeding memory size. I am just not sure if there is a way to reorder this or go about doing it another way that will work.

jawz302

Allan,

If I add this line to the top of my code it runs:
ini_set('memory_limit', '1024M');

Thank you guys for all your help.

allan

How large is the data set? I'm surprised that it is filling the memory limit there.

Thanks,
Allan