error with sub-select

error with sub-select

stevencmonstevencmon Posts: 25Questions: 9Answers: 2

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

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2
    <?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();
    ~                                                                     
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
        >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) {
    
  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2
    edited September 2018
    <?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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

    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

  • stevencmonstevencmon Posts: 25Questions: 9Answers: 2

    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
    
This discussion has been closed.