Inserting into data into external DB using WPDB

Question

I'm trying to work with an external db in Wordpress for the first time and can't seem to get it to insert the data into the DB. Here's what I have:

    <?php
    global $wpdb;
    $url = home_url();

        require_once($url.'/viper/confff.php');  //contains variable to fill in below
            $newdb = new wpdb($username, $password, $db_name, $host);
            $newdb->show_errors();
     ?>

$numrows = $newdb->get_results("SELECT * FROM chi_clients WHERE AccountNum='$acctNum'");

      if($numrows!=0)
      {
        $row =  $newdb->get_results("SELECT * FROM chi_clients WHERE AccountNum='$acctNum'",ARRAY_A);    
                $name= $row['lastName'] . ", " . $row['firstName'];
        $phone= $row['phone'];
        $phonearea = substr($phone,0,3);
        $phonemain = substr($phone,3,10);
        $phonenumber = $phonearea ."-". $phonemain;
        $email= $row['email'];

        $newdb ->insert (
                    chi_orders, array(
                        'name' => '$name',
                        'AccountNum' => '$acctNum',
                        'email' => '$email',
                        'OrderDesc' => '$orderDesc',
                        'product' => '$service',
                        'quantity' => '$quantity',
                        'notes' => '$notes',
                        'status' => 'Order Received',
                        'dateIne' => '$todayDate',
                        'dateOut' => '$newDate',
                        'advert' => '$advert',
                        'phone' => '$phonenumber'
                    )
                   );
        }


        else {
           echo'<span style="color: #000; font-family: Verdana; font-weight: bold; font-size: 12px;">Incorrect Account Number!</span>';
                 }

I know the data is being posted to page as I have the variables echoed out to display on the page. However it's not connecting to the Database as I'm not getting the information from the get_results SELECT statement and it's not inserting anything into database. For my $host- I'm using 'localhost' which is what I've always used for this particular database. And I know that the username, pass, and database name are correct.

any ideas? thanks.

Answer 1

You are using $wpdb incorrectly Take a look at these lines:

$row =  $newdb->get_results("SELECT * FROM chi_clients WHERE accountNum='$acctNum'",ARRAY_A);    
$name= $row['lastName'] . ", " . $row['firstName'];

$wpdb->get_results is going to return an array of "rows". It is going to multidimensional. You are treating it like it has returned a one-dimensional array. That is, this (completely different data but illustrates the array to expect):

array(10) {
  [0]=>
  array(23) {
    ["ID"]=>
    string(1) "1"
    ["post_author"]=>
    string(1) "1"
    ["post_date"]=>
    string(19) "2013-03-26 18:37:57"
    ["post_date_gmt"]=>
    string(19) "2013-03-26 18:37:57"
    ["post_content"]=>
    string(429) "[testsc]
    ...

You need to loop over that result set to get to the individual rows, or do something like this:

$name= $row[0]['lastName'] . ", " . $row[0]['firstName'];

That is true even if the result set is one row. If you know the result set is only one row you can use $wpdb->get_row instead and that will give you a one dimensional array.

Secondly, variables do not expand inside single quotes so all of your attempts to use variables in this way-- 'name' => '$name',-- simple won't work. You have literally set name to $name, not to the data held in the $name variable.

Third, the first parameter of $wpdb ->insert is a string representing the table name. You have used what PHP will interpret to be a constant, and almost certainly an undefined one. You need quotes around chi_orders.

Those are the issues that I spot. If correcting those doesn't fix it, it will move you forward quite a bit.