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' ] );
    }
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.

  • That worked! Thank you so much for your help. – user1701282 May 30 '18 at 16:26
1

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

->where($db->quoteName('geo.fid') . ' != 887');
  • 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 '18 at 5:21
  • @user1701282 - Try putting a semicolon at the end of it – Lodder May 23 '18 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 '18 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 '18 at 13:29
  • 1
    @mickmackusa - No worries, all done – Lodder May 25 '18 at 14:51
0

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 ' . );
  • 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 '18 at 12:59

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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