Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have edited my question, I'm now getting the error about checking my MySQL settings. I changed the $sql_dvalues variable to an array with the values instead of comma spaced values and now it is saying the following:

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 3]

INSERT INTO wp_efpd_gmaps ( time, name, address, description, title ) VALUES ( '2011-10-30 08:48:32', 'test', 'tedst', 'tedst', 'tedst' ),

The reason I'm using $wpdb->prepare() is because I read this page: http://codex.wordpress.org/Class_Reference/wpdb under the headline Protect Queries Against SQL Injection Attacks but maybe I read wrong and don't need to use that? I'm not sure, some advice would be appreciated.


Older notes & code:

I have set up a way to add stuff into a WP database a little simpler and without having to know SQL, and I'm running into a problem. The query code outputs fine (see code below, die($str);) but whenever I try to actually test it by adding some data it gives me the following error:

WordPress database error: [Query was empty]

It was working at one point but not inserting any data, and giving me a different error telling me to check my MySQL settings or something on what to use after ", I don't really remember what that said but I want to fix this problem first.

Here's my function in my class called Efpd2

public function efpd_prepare_db($data=array(),$table_name) {
    global $wpdb;
    $table_name = $wpdb->prefix.$table_name;

    $datenow = date('Y\-m\-d H\:i\:s');

    $sql_values = 'time, ';
    $sql_cvalues = '%s, ';
    $sql_dvalues = array($datenow);
    $int=0; $dcount = count($data);
    foreach($data as $dat){
        $int++;
        $sql_values .= $dat['name'];
        if($int!=$dcount){
            $sql_values .= ", ";
        }
        $sql_cvalue .= $dat['type'];
        switch($dat['type']){
            case 'txt': $sql_cvalues .= '%s'; break;
            case 'num': $sql_cvalues .= '%d'; break;
            case 'float': $sql_cvalues .= '%f'; break;
            default: die('Error writing to database, wrong value type. Use \'num\', \'float\' or \'txt\'.');
        }
        if($int!=$dcount){
            $sql_cvalues .= ", ";
        }
        array_push($sql_dvalues,$dat['value']);
    }

    $str = " 
    INSERT INTO $table_name
    ( $sql_values )
    VALUES ( $sql_cvalues ),
    $sql_dvalues
    ";
    // For debugging, use this:  die($str);
    //die($str);

    $wpdb->show_errors();
    $wpdb->query( $wpdb->prepare( 
        "
            INSERT INTO $table_name
            ( $sql_values )
            VALUES ( $sql_cvalues ),
        ",
        $sql_dvalues
    ) );
}

And the code I'm using to add the data:

function add_sample_data(){     
    $efpd = Efpd2::efpd_prepare_db(
    array(
        0 => array(
            'name' => 'name', // Name of the variable inside the table
            'value' => 'test', // Value to add
            'type' => 'txt' // Type of data to be added ('num' = %d, 'txt' = %s, 'float' = %f). See $wpdb->prepare() documentation on codex.wordpress.org
        ),
        1 => array(
            'name' => 'address',
            'value' => 'tedst',
            'type' => 'txt'
        ),
        2 => array(
            'name' => 'description',
            'value' => 'tedst',
            'type' => 'txt'
        ),
        3 => array(
            'name' => 'title',
            'value' => 'tedst',
            'type' => 'txt'
        )
    ),
    'efpd_gmaps'); // Table to add data to
    unset($efpd);
}
add_action('init','add_sample_data');

And this is what is sent out when using die($str); in Efpd2::efpd_prepare_db():

INSERT INTO wp_efpd_gmaps ( time, name, address, description, title ) VALUES ( %s, %s, %s, %s, %s ), '2011-10-30 07:27:53', 'test', 'tedst', 'tedst', 'tedst'

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You are aware that $wpdb->insert does exactly this?

$wpdb->insert(
    'table', // Table name
    array(
        'column' => 'foo',
        'field' => 1337,
    ), // Columns
    array(
        '%s',
        '%d',
    ) // Explicit formatting
 );

Right?

share|improve this answer
    
I was actually focusing more on the text under the headline Protect Queries Against SQL Injection Attacks on this page: codex.wordpress.org/Class_Reference/wpdb but maybe I read wrong and don't need to use that? I'm not sure, some advice would be appreciated. –  Jared Oct 30 '11 at 8:39
1  
$wpdb->insert escapes everything itself. You don't need to use ->prepare unless you use ->query which is manually constructed by you and needs to be escaped. –  EarnestoDev Oct 30 '11 at 9:01
    
Alright, thank you. :) Seems to work fine, I just wasn't 100% sure what approach to take and I guess I took the wrong one first! –  Jared Oct 30 '11 at 10:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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