4

I need to modify the database query to not select a certain ID in the database so the items with that ID do not show in the results. However, I cannot figure out how to do this. below I added a WHERE statement but I don't know if this is the right way to do this or if it is even created correctly.

->where ('geo.fid' != 887)

Any help would be much appreciated.

protected function loadEntries( &$data, $conditions )
    {
        $db = SPFactory::db();
        $table = $db->join( array(
                array( 'table' => 'spdb_field_geo', 'as' => 'geo', 'key' => 'sid' ),
                array( 'table' => 'spdb_object', 'as' => 'spo', 'key' => 'id' )
        ) );
        if ( Sobi::My( 'id' ) ) {
            $this->userPermissionsQuery( $conditions, 'spo.' );
        }
        else {
            $conditions = array_merge( $conditions, array( 'spo.state' => '1', '@VALID' => $db->valid( 'spo.validUntil', 'spo.validSince' ) ) );
        }
        $data[ 'entries' ] = SPFactory::db()
                ->select( array( 'geo.sid', 'geo.latitude', 'geo.longitude' ), $table, $conditions )

                ->where ('geo.fid' != 887) // this is what I am trying to add but it breaks the code.

                ->loadAssocList();
        Sobi::Trigger( 'SPGeoMap', 'AfterGetEntries', array( &$data[ 'entries' ] ) );
        $data[ 'count' ] = count( $data[ 'entries' ] );
    }
Improve this question

3 Answers 3

Reset to default
0

Based on this portion of code from https://github.com/Sigsiu-NET/SobiPro/blob/master/Site/lib/base/config.php :

enter image description here

I'll recommend this possible solution:

if (Sobi::My('id')) {
    $this->userPermissionsQuery($conditions, 'spo.');
} else {
    $conditions = array_merge($conditions, array('spo.state' => '1', '@VALID' => $db->valid('spo.validUntil', 'spo.validSince')));
}
$conditions = array_merge($conditions, array('!geo.fid' => '887'));

$data['entries'] = SPFactory::db()
         ->select(array('geo.sid', 'geo.latitude', 'geo.longitude'), $table, $conditions)
         ->loadAssocList();

It seems your earlier-used numeric values are single-quote wrapped, but the sourced code snippet does not bother with quoting integer values -- so you may be able to leave the value unquoted.

I say all this with absolutely no personal experience with this product; I am merely joining you on this researching quest. Do let us know how this pans out.

Improve this answer
1
  • That worked! Thank you so much for your help.
    – user1701282
    May 30, 2018 at 16:26
0

You need to make sure != is wrapped in the quotes. Also escape the column name, like so:

->where($db->quoteName('geo.fid') . ' != 887');
Improve this answer
6
  • Thank you for corrections. However whenever I add the WHERE statement it breaks the code. This code loads markers onto a google map. Perhaps I should not be trying to do this with a WHERE statement. I have 14 geolocation fields that load onto the map - but there is one, fid 887, that I don't want to load onto the map. Any suggestions would be much appreciated.
    – user1701282
    May 23, 2018 at 5:21
  • @user1701282 - Try putting a semicolon at the end of it
    – Lodder
    May 23, 2018 at 8:49
  • @Lodder Where is the manual on SPFactory? That select() method looks wrong, but maybe I'm looking up the wrong docs. Is it a non-Joomla thing? Does the where condition belong up in the $conditions declaration?
    – mickmackusa
    May 25, 2018 at 12:45
  • 1
    @mickmackusa - I can only assume SPFactory is a mimic of Joomla's JFactory from the SobiPro extension or something. The select clause does look incorrect completely. Perhaps The approach is supported with SPFactory::db()....not sure.
    – Lodder
    May 25, 2018 at 13:29
  • 1
    @mickmackusa - No worries, all done
    – Lodder
    May 25, 2018 at 14:51
-1

I have success with the following format 

->select($db->quoteName(array('geo.sid', 'geo.latitude', 'geo.longitude')), $table, $conditions );

->where($db->quoteName('geo.fid') . ' <> 887 ' . );
Improve this answer
1
  • According to the OP and a snippet that I dug up, this product does not expect the same format as Joomla query methods. This select() method's 3rd parameter expects an associative array and, oddly, the NOT distinction is declared by prepending the key with !. So it seems your suggestion will not work.
    – mickmackusa
    Jun 6, 2018 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.