$wpdb->insert – Truncated Value

$table = $wpdb->prefix.stores;
$data = array ('retailer' => $row[1], 'store_number' => $id, 'store_name' => $row[3], 'address' => $row[4], 'city' => $row[5], 'state' => $row[6], 'zip' => $row[7], 'm_vendor_id' => $row[8], 'lw_branch_name' => $row[9], 'lw_branch_page_link' => $row[10], 'lw_branch_street' => $row[11], 'lw_branch_city' => $row[12], 'lw_branch_state' => $row[13], 'lw_branch_zip' => $row[14], 'inside_sales_phone' => $row[15], 'inside_sales_contact' => $row[16], 'email' => $row[17], 'outside_sales_contact' => '', 'outside_sales_phone' => '', 'pcid' => $row[18]);
$format = array ('%s','%d','%s','%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%d');
$wpdb->insert($table,$data,$format);

I should also note that I imported the CSV file directly into the database and had no issues with the csv file but when I use the $wpdb->insert($table,$data,$format); method the phone format gets messed up. I have dashes in some of the url values with no issue but those dam phone numbers just won’t work.

If I had to guess, the issue is in the section of your code that is reading the CSV file. Have you verified that the $row[15] value contains the full phone number?

If you are asking me if the CSV file is correct in terms of the data containing the full phone number it is.

If you are asking something else please elaborate as I do very much appreciate the help solving this.

• This reply was modified 4 years, 4 months ago by kokoruz.

I’m asking you to check your code that reads in the CSV file. My suspicion is that the phone number is being read incorrectly. That’s why I asked whether you verified that the $row[15] value in the code you posted contains the full phone number.

the code for that is below

$wpdb->insert('4R51claX_stores', array ('inside_sales_phone' => $row[15]), array ('%s'));

is there something else I should be doing? I appreciate the response.

You posted the code doing the insert. That’s not what I requested. Please check your code before that point, when the CSV file is read and $row[15] is created. Does $row[15] contain the correct value to be inserted? I suspect it does not, and that would be a problem with your code that is reading the CSV file.

this is where the csv file is read
$file = fopen(__DIR__ . ‘/stores.csv’, ‘r’);

The code does not actually create the table. The table was created in phpMyAdmin. This code only inserts the data.

In phpMyAdmin the space that imports $row[15] is set to
Name: inside_sales_phone
Type: varchar(16)
Collation: utf8_general_ci
Null: Yes
Default: NULL

I sure hope I am answering your questions correctly. Thanks again for your response.

The code I was interested in seeing is how $row[15] is created. As I had said three times previously, I suspect your problem is that $row[15] is being read incorrectly and does not contain the full phone number.

It doesn’t look like we’re getting anywhere, so I’ll let others try to assist. I wish you luck in locating the problem.

I’m hoping to give this one more short with you. If what I have posted does not help to answer the question then I thank you for sticking with me for this long.

<?php
// Import stores to db from csv file stores.csv
function importStores() {
    $file = fopen(__DIR__ . '/stores.csv', 'r');

    $ok = $exit = $fail = 0;
    $id_stored = [];
    $id_duplicated = [];

    while (($row = fgetcsv($file)) !== FALSE) {
        $id = $row[2]; // storeNumber
        global $wpdb;

        if (!isset($id_stored[$id])) {
            $id_stored[$id] = $id;
        } else {
            $id_duplicated[] = $id;
        }

        $table = $wpdb->prefix.stores;	
        $existed = $wpdb->get_row("SELECT <code>id</code> FROM <code>4R51claX_stores</code> WHERE <code>store_number</code> = " . $id);

if (!$existed) {
$showErrors = $wpdb->show_errors();
		
$data = array ('retailer' => $row[1], 'store_number' => $id, 'store_name' => $row[3], 'address' => $row[4], 'city' => $row[5], 'state' => $row[6], 'zip' => $row[7], 'm_vendor_id' => $row[8], 'lw_branch_name' => $row[9], 'lw_branch_page_link' => $row[10], 'lw_branch_street' => $row[11], 'lw_branch_city' => $row[12], 'lw_branch_state' => $row[13], 'lw_branch_zip' => $row[14], 'inside_sales_phone' => $row[15], 'inside_sales_contact' => $row[16], 'email' => $row[17], 'outside_sales_contact' => '', 'outside_sales_phone' => '', 'pcid' => $row[18]);

$format = array ('%s','%d','%s','%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%d','%s','%s','%s','%d');
			
$inserted = $wpdb->insert($table,$data,$format);

if ($inserted) { echo '<p>OK Insert: ' . $id . ' - #' . $wpdb->insert_id . ' (' . ( ++$ok) . ')</p>';}
else {echo $showErrors; echo '<p>Fail Insert: ' . $id . ' (' . ( ++$fail) . ')</p>';}

} else {echo '<p>Existed Insert: ' . $id . ' (' . ( ++$exit) . ')</p>';}

flush(); ob_flush();
}
    fclose($file);

    echo '<p>DONE</p>';
    echo '<p>Imported: ' . count($id_stored) . '</p>';
    echo '<p>Duplicated: ' . count($id_duplicated) . '</p>';
    die;
}
if (isset($_GET['import_stores'])) {
    importStores();
}

• This reply was modified 4 years, 4 months ago by kokoruz.

Let’s just check the values for $row[15] Add the following just before the $wpdb->get_row() call:
error_log("Phone value from fgetcsv: {$row[15]}");
Extract just a few rows from the CSV file into a new file to use a a test source. There will be a log entry for every row and we don’t really need that many entries. After doing a test import with this code added, check your server’s error log file to verify if the entire phone value was logged or not.

If that checks out, what is the actual field type for the table’s ‘inside_sales_phone’ column? Verify through the phpMyAdmin app. Look at the table’s “Structure” tab.

I did just as you outlined. In my log files I have the following

mod_fcgid: stderr: Phone value from fgetcsv: 423-282-5106

as well as the following. Perhaps that is the issue?

mod_fcgid: stderr: PHP Warning: Use of undefined constant stores – assumed ‘stores’ (this will throw an Error in a future version of PHP) on line 20 ($table = $wpdb->prefix.stores;)

Loo forward to your feedback.

Yeah, that’s a minor issue. I think you want
$table = $wpdb->prefix .'stores';

I don’t think it’ll solve the problem though since PHP apparently correctly guessed what was intended. At least we now know the phone was read from the table correctly. Did you check the destination field type in phpMyAdmin? I suspect it may be a numeric type instead of a string type. I’d normally use varchar for aplphanumeric data.

The quote marks resolved that issue.

I have taken a screen grab to show the type is set to varchar(16)

https://drive.google.com/file/d/1naAj7ckZub45yJX76yVIT5c_JRsTzz88/view?usp=sharing

I did notice I am getting the following log warning

mod_fcgid: stderr: WordPress database 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 ” at line 1 for query SELECT * FROM 4R51claX_stores WHERE store_number = made by require(‘wp-blog-header.php’), require_once(‘wp-includes/template-loader.php’), include(‘/themes/stockscatter/page-home.php’)

Thank You

There’s no close quote at the end. Where you assign get_row() return to $results the query in part has WHERE <code>store_number</code> = " . $id);
(the <code> tags are due to how the forum’s parser handles backticks. The actual backticks in your source code are fine as is)
Depending on what the data type of the store_number column is, you want either
= $id); for integers or = ' . $id . '); for strings (though as long as $id values never have spaces the quotes aren’t really required even for strings)

The error means the subsequent insert code will always happen whether a record exists or not so fixing this does not resolve the truncated phone value. Seeing that $row[15] has the right value and the related column is varchar, I’m at a loss to explain how the inserted value gets truncated.