error with sub-select

stevencmon

I've got following code but my sub-select doesn't seem to be well liked.. (response follows code):

`// 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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'all_portmaps' )
->fields(
Field::inst( 'id' )
->set( false ),
Field::inst( 'site_id' ),
Field::inst( 'device_name' ),
Field::inst( 'device_port' )
->validator( Validate::numeric() )
->setFormatter( Format::ifEmpty(null) ),
Field::inst( 'switch' ),
Field::inst( 'port' )
->validator( Validate::numeric() )
->setFormatter( Format::ifEmpty(null) ),
Field::inst( 'vlan' ),
Field::inst( 'cable_type' ),
Field::inst( 'connector_type' ),
Field::inst( 'patch_panel' ),
Field::inst( 'patch_panel_port' )
->validator( Validate::numeric() )
->setFormatter( Format::ifEmpty(null) )
)
->where( 'site_id', $_SESSION['site_id'] )
->where( "switch", "(SELECT device_name FROM installed_equipment WHERE site_id = " . $_SESSION['site_id'] . " AND equipment_type LIKE '%switch%')", "IN", false )
->debug( true )
->process( $_POST )
->json();
`
And the response was:

fieldErrors []
error An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(SELECT device_name FROM installed_equipment WHERE site_id = 761 AND equipment_' at line 1
data []
ipOpts []
cancelled []
debug […]
0 {…}
query SELECT id as 'id', site_id as 'site_id', device_name as 'device_name', device_port as 'device_port', switch as 'switch', port as 'port', vlan as 'vlan', cable_type as 'cable_type', connector_type as 'connector_type', patch_panel as 'patch_panel', patch_panel_port as 'patch_panel_port' FROM all_portmaps WHERE site_id = :where_0 AND switch IN :where_1
bindings […]
0
name :where_0
value 761
type null
1
name :where_1
value (SELECT device_name FROM installed_equipment WHERE site_id = 761 AND equipment_type like '%switch%')
type null

allan

No - Editor won't like that as its where() method is a little different from the Query->where() method. Editor doesn't have an option to not bind the value given. You need to use a closure:

->where( function ($q) {
  $q->where( "switch", "(SELECT device_name FROM installed_equipment WHERE site_id = " . $_SESSION['site_id'] . " AND equipment_type LIKE '%switch%')", "IN", false )
} )

Regards,
Allan

stevencmon

That get me a "Uncaught Error: Call to undefined function where() " in my log file

allan

Can you show me your full PHP with that change please? That looks correct to me, but something is obviously going a bit weird).

Allan

stevencmon

<?php

session_start();

if ( ! isset( $_SESSION['site_id'] ) ) {
  print json_encode( 'NO SESSION' );
  exit;
}

// DataTables PHP library and database connection
include( $_SERVER['DOCUMENT_ROOT'] . "/php/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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'all_portmaps' )
  ->fields(
      Field::inst( 'id' )
        ->set( false ),
      Field::inst( 'site_id' ),
      Field::inst( 'device_name' ),
      Field::inst( 'device_port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) ),
      Field::inst( 'switch' ),
      Field::inst( 'port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) ),
      Field::inst( 'vlan' ),
      Field::inst( 'cable_type' ),
      Field::inst( 'connector_type' ),
      Field::inst( 'patch_panel' ),
      Field::inst( 'patch_panel_port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) )
    )
    >where( function ($q) {
      $q->where( "switch", "(SELECT device_name FROM installed_equipment WHERE site_id = " . $_SESSION['site_id'] . " AND equipment_type LIKE '%switch%')", "IN", false );
    } )
    ->where( 'site_id', $_SESSION['site_id'] )
    ->process( $_POST )
    ->json();
~                                                                     

tangerine

    >where( function ($q) {

If that is your actual code, rather than a pasting error in your post, then you should fix it.

->where( function ($q) {

stevencmon

<?php

session_start();

if ( ! isset( $_SESSION['site_id'] ) ) {
  print json_encode( 'NO SESSION' );
  exit;
}

// DataTables PHP library and database connection
include( $_SERVER['DOCUMENT_ROOT'] . "/php/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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'all_portmaps' )
  ->fields(
      Field::inst( 'id' )
        ->set( false ),
      Field::inst( 'site_id' ),
      Field::inst( 'device_name' ),
      Field::inst( 'device_port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) ),
      Field::inst( 'switch' ),
      Field::inst( 'port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) ),
      Field::inst( 'vlan' ),
      Field::inst( 'cable_type' ),
      Field::inst( 'connector_type' ),
      Field::inst( 'patch_panel' ),
      Field::inst( 'patch_panel_port' )
        ->validator( Validate::numeric() )
        ->setFormatter( Format::ifEmpty(null) )
    )
    ->where( function ($q) {
      $q->where( "switch", "(SELECT device_name FROM installed_equipment WHERE site_id = " . $_SESSION['site_id'] . " AND equipment_type LIKE '%switch%')", "IN", false );
    } )
    ->where( 'site_id', $_SESSION['site_id'] )
    ->process( $_POST )
    ->json();

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

allan

That code (corrected) results in:

That get me a "Uncaught Error: Call to undefined function where() " in my log file

?

Does it say if the issue is line 45, 46 or 48? All of them look like they should be okay!

Allan

stevencmon

45

[Fri Aug 31 14:56:04.591783 2018] [:error] [pid 8342] [client 73.106.250.204:59168] PHP Fatal error: Uncaught Error: Call to undefined function where() in /var/www/yyyy.xxxx.com/public_html/asbuilt/lib/table.portmap.php:45\nStack trace:\n#0 {main}\n thrown in /var/www/blueprintrf.pcthree.com/public_html/asbuilt/lib/table.portmap.php on line 45, referer: https://yyyy.xxxx.com/asbuilt/live/live.php

allan

What version of PHP are you using? I've just tried copying and pasting the code from above and it runs as expected (throwing an error about the database table not existing - no syntax errors).

Allan

stevencmon

PHP 7.0.30-0ubuntu0.16.04.1 (cli) ( NTS ) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies with Zend OPcache v7.0.30-0u

stevencmon

give same results when installed on a debian 8 server

PHP 5.6.30-0+deb8u1 (cli) (built: Feb  8 2017 08:50:21) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies