1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

SQL syntax error

Discussion in 'Community' started by SoilentRed, Nov 6, 2019 at 11:33 PM.

  1. SoilentRed

    SoilentRed Caaan do!

    Level: Community
    I'm trying to insert a bunch of data on a loop. Almost there. my vardump on $row shows all the details. but for some reason I'm getting hit with this error:

    Code (Text):

    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 'doe,'test 022','2019-11-28 00:00:00',NIA,'test 022','777',71341' at line 3
     
    what's interesting is that based on the error, it looks like it's cutting off some of the data, for example, it only spits out half a phone number.... and the first name isn't in there...

    Code (Text):

    $myDb = JFactory::getDbo();
    $myQuery = $myDb->getQuery( true );

    $selection = '{sms___which_groups_raw}';
    $sels = explode( ",", $selection );
    $bad_symbols = array( ",", ".", "-", " " );
    // $list = array();

    // start building the query ..
    $myQuery->insert( 'txt' );
    $myQuery->columns(
      array(
        $myDb->quoteName( 'date_time' ),
        $myDb->quoteName( 'f_name' ),
        $myDb->quoteName( 'l_name' ),
        $myDb->quoteName( 'full_name' ),
        $myDb->quoteName( 'message' ),
        $myDb->quoteName( 'scheduled_datetime' ),
        $myDb->quoteName( 'company' ),
        $myDb->quoteName( 'subject' ),
        $myDb->quoteName( 'agent_id' ),
        $myDb->quoteName( 'mobile' )
      )
    );
    foreach ( $sels as $sel ) {
      $insel = ( int )$sel;
      $myDb->setQuery( 'SELECT cell_phone, userid, name, first_name, last_name, organization FROM #__acymailing_subscriber JOIN #__acymailing_listsub ON #__acymailing_subscriber.subid = #__acymailing_listsub.subid WHERE #__acymailing_listsub.listid = ' . $insel . '' );
      $rows = $myDb->loadObjectList();
      foreach ( $rows as $row ) {
        $cellphone = str_replace( $bad_symbols, "", $row->cell_phone );
        if ( empty( $cellphone ) ) {;
        } else {

          $myQuery->values(
            implode( ',',
              array(
                $myDb->quote( '{sms___date_time}' ),
                $row->first_name,
                $row->last_name,
                $row->name,
                $myDb->quote( '{sms___message}' ),
                $myDb->quote( '{sms___scheduled_datetime}' ),
                $row->organization,
                $myDb->quote( '{sms___subject}' ),
                $myDb->quote( '{sms___nia_agent_id}' ),
                $cellphone
              )
            )
          );
        }
      }
    }
      // run the query
      $myDb->setQuery( $myQuery );
      // uncomment this line if you need to debug
      // var_dump($myQuery);exit;
      $myDb->execute();
     
    And here's a vardump on $myQuery

    Code (Text):

    object(JDatabaseQueryMysqli)#2437 (25) { ["offset":protected]=> int(0) ["limit":protected]=> int(0) ["db":protected]=> object(JDatabaseDriverMysqli)#22 (23) { ["name"]=> string(6) "mysqli" ["serverType"]=> string(5) "mysql" ["connection":protected]=> object(mysqli)#23 (19) { ["affected_rows"]=> int(5) ["client_info"]=> string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: 3591daad22de08524295e1bd073aceeff11e6579 $" ["client_version"]=> int(50012) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["field_count"]=> int(6) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(15) "5.6.36-82.1-log" ["server_version"]=> int(50636) ["stat"]=> string(147) "Uptime: 179545 Threads: 2 Questions: 9498177 Slow queries: 125 Opens: 27955 Flush tables: 1 Open tables: 2048 Queries per second avg: 52.901" ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(195069) ["warning_count"]=> int(0) } ["nameQuote":protected]=> string(1) "`" ["nullDate":protected]=> string(19) "0000-00-00 00:00:00" ["_database":"JDatabaseDriver":private]=> string(13) "networki_j3db" ["count":protected]=> int(27) ["cursor":protected]=> NULL ["debug":protected]=> bool(false) ["limit":protected]=> int(0) ["log":protected]=> array(0) { } ["timings":protected]=> array(0) { } ["callStacks":protected]=> array(0) { } ["offset":protected]=> int(0) ["options":protected]=> array(9) { ["driver"]=> string(6) "mysqli" ["host"]=> string(9) "localhost" ["user"]=> string(13) "networki_j3du" ["password"]=> string(18) "I_=,+)@hbX)zW!3AA}" ["database"]=> string(13) "networki_j3db" ["prefix"]=> string(6) "b8r9o_" ["select"]=> bool(true) ["port"]=> int(3306) ["socket"]=> NULL } ["sql":protected]=> *RECURSION* ["tablePrefix":protected]=> string(6) "b8r9o_" ["utf":protected]=> bool(true) ["utf8mb4":protected]=> bool(true) ["errorNum":protected]=> int(0) ["errorMsg":protected]=> string(0) "" ["transactionDepth":protected]=> int(0) ["disconnectHandlers":protected]=> array(0) { } } ["sql":protected]=> NULL ["type":protected]=> string(6) "insert" ["element":protected]=> NULL ["select":protected]=> NULL ["delete":protected]=> NULL ["update":protected]=> NULL ["insert":protected]=> object(JDatabaseQueryElement)#2440 (3) { ["name":protected]=> string(11) "INSERT INTO" ["elements":protected]=> array(1) { [0]=> string(3) "txt" } ["glue":protected]=> string(1) "," } ["from":protected]=> NULL ["join":protected]=> NULL ["set":protected]=> NULL ["where":protected]=> NULL ["group":protected]=> NULL ["having":protected]=> NULL ["columns":protected]=> object(JDatabaseQueryElement)#2439 (3) { ["name":protected]=> string(2) "()" ["elements":protected]=> array(10) { [0]=> string(11) "`date_time`" [1]=> string(8) "`f_name`" [2]=> string(8) "`l_name`" [3]=> string(11) "`full_name`" [4]=> string(9) "`message`" [5]=> string(20) "`scheduled_datetime`" [6]=> string(9) "`company`" [7]=> string(9) "`subject`" [8]=> string(10) "`agent_id`" [9]=> string(8) "`mobile`" } ["glue":protected]=> string(1) "," } ["values":protected]=> object(JDatabaseQueryElement)#2441 (3) { ["name":protected]=> string(2) "()" ["elements":protected]=> array(3) { [0]=> string(126) "'2019-11-06 17:22:05',scott,talisman,scott talisman,'test 023','2019-11-28 00:00:00',NIA,'test 023','777',7135555555" [1]=> string(113) "'2019-11-06 17:22:05',,,(Graphic Designer) Roni Doe,'test 023','2019-11-28 00:00:00',,'test 023','777',7135555555" [2]=> string(107) "'2019-11-06 17:22:05',Dan,Houston,Administrator,'test 023','2019-11-28 00:00:00',,'test 023','777',7135555555" } ["glue":protected]=> string(3) "),(" } ["order":protected]=> NULL ["autoIncrementField":protected]=> bool(false) ["call":protected]=> NULL ["exec":protected]=> NULL ["union":protected]=> NULL ["unionAll":protected]=> NULL ["selectRowNumber":protected]=> NULL }
     
    As always, your thoughts and insights are valued. Go Fabrik!
     
  2. lousyfool

    lousyfool Active Member

    Level: Community
    Please look again at error message and var_dump: certain strings are not quoted.

    BTW, it says "near" in the error message. So, yes, in the error message you may see only part of the culprit, but you'll know what it is.
     
  3. startpoint

    startpoint Active Member

    Level: Community
    Try to remove ; from the end of this line:
    PHP:
    if ( empty( $cellphone ) ) {;
     
  4. lousyfool

    lousyfool Active Member

    Level: Community
    Good point, too!

    But since the variable isn't empty, there's no error thrown on this.
    In fact, the error as reported here comes from certain values not quoted in this block
    Code (Text):
              array(
                $myDb->quote( '{sms___date_time}' ),
                $row->first_name,
                $row->last_name,
                $row->name,
                $myDb->quote( '{sms___message}' ),
                $myDb->quote( '{sms___scheduled_datetime}' ),
                $row->organization,
                $myDb->quote( '{sms___subject}' ),
                $myDb->quote( '{sms___nia_agent_id}' ),
                $cellphone
              )
     
    as already indicated.
     
  5. startpoint

    startpoint Active Member

    Level: Community
    Try to create variables for all your placeholders before array and var_dump-ing each to view your data.
    I prefer to $myDB->quote() variables instead placeholders.
     
  6. startpoint

    startpoint Active Member

    Level: Community
    Create a new query object
    PHP:
    $myQuery = $myDb->getQuery( true );
    after
    PHP:
    $rows = $myDb->loadObjectList();
     
  7. troester

    troester Well-Known Member Staff Member

    Level: Community
    Do $myDb->quote on all your columns
     

Share This Page