SQL syntax error

[SoilentRed]

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:

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...

$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

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!

 

[lousyfool]

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.

 

[startpoint]

Try to remove ; from the end of this line:

if ( empty( $cellphone ) ) {;

 

[lousyfool]

Try to remove ; from the end of this line

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

          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.

 

[startpoint]

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.

 

[startpoint]

Create a new query object

$myQuery = $myDb->getQuery( true );

after

$rows = $myDb->loadObjectList();

 

[troester]

'2019-11-28 00:00:00',NIA,'test 022',

Do $myDb->quote on all your columns