0

I am trying to insert some strings containing hyphens and apostrophes as values into a MySQL table. I assign the string value:

$venue_name = addslashes(get_the_title($id));

When I echo the value, I get:

4B’s Brewery

and

Ace Hardware - Bailey

My SQL statements looks like this:

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name,
 website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description,
 doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES
 (817, 7, 'Active', '4B’s Brewery', 'http://4bsbrewery.com/', '215 W Main St',
 '', 'Cedaredge', 'Colorado', '81413', '970-856-7762', '', '', 0, 0, '', '', '',
 0, '168', 89, 0)

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name,
 website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(915, 7, 'Active', 'Ace Hardware – Bailey', '', 'Moore Lumber & Hardware', '',
 'Bailey', 'Colorado', '', '720-924-3999', '', '', 0, 0, '', '', '', 0, '168',
 89, 0)

I am using

dbDelta($sql);

to update the database.

But I am getting database errors like this:

    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 ''4B&#8217' at line 4]

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name, 
website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(817, 7, 'Active', '4B&#8217



    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 ''Ace Hardware &#8211' at line 4]

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name, 
website, address_1, address_2, city, state, postal_code, phone, fax, email, 
logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(915, 7, 'Active', 'Ace Hardware &#8211

The special characters ' and - are terminating the strings. I have tried addslashes(), str_replace('-', '-", $venue_name), and mysql_real_escape_string(), with no success. How do I prepare the strings for acceptance as MySQL VARCHAR table column values?

1

dbDelta is really meant for creating databases or tables, not inserting records.

I'd suggest using $wpdb->insert(), which will escape the values for you:

$wpdb->insert( 
    $wpdb->prefix . 'w2bw2c_venue', 
    array(
        'market_id'  => 7,
        'venue_name' => get_the_title( $id ),
    ),
    array(
        '%d',
        '%s',
    )
);

I've just used the name field you were having trouble with in my example, obviously you'll want to fill out the rest. I included market_id to show the correct usage of the second $format array, ensuring that market_id is parsed as an integer.

  • Obviously insert() is the way to go here. If OP wants to write his/her own query, it us also possible to use prepared statements via $wpdb – kero Jul 24 '17 at 8:03
  • Thanks. That worked, although the special characters are now embedded in the field as ’ and – – Russell Eubanks Jul 24 '17 at 14:46
  • I assume these characters will display properly when the field is output. – Russell Eubanks Jul 24 '17 at 14:47
  • You'll probably need to unescape them before updating. You can use functions like html_entity_decode to do this. – Jacob Peattie Jul 25 '17 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.