1

I just took this code from $wpdb Codex page, and modified my existing Insert query:

$table = $wpdb->prefix . 'user_req';

$post_id = explode('_', $_POST['post_id']);
$user_id = $_POST['user_id'];
$wpdb->insert( 
        $table,
        array(
            'user_id' => $user_id,
            'post_id' => $post_id[1]
        ),
        array( 
            '%d',
            '%d'
        )
    );

into:

$wpdb->query( $wpdb->prepare( 
            "
                INSERT INTO {$wpdb->prefix}user_req
                ( user_id, post_id )
                VALUES ( %d, %d )
                ON DUPLICATE KEY UPDATE
                user_id = VALUES(user_id), post_id = VALUES(post_id)
            ", 
            $user_id, 
            $post_id[1]
        ) );

Because I need to prevent the duplicate entries entering into the database. But still the duplicate entries are entering into the db while the insert query is functioning properly. To add the ON DUPLICATE KEY UPDATE portion, I followed the s_ha_dum's Answer on another thread.

What am I doing wrong?

  • 1
    Do you actually have a duplicate key here? What is the structure of user_req and what are the keys and indexes? – Otto Apr 20 '14 at 7:30
  • @Otto: May be you directed me on the actual matter. Actually I want to prevent entering duplicate post_id. – Mayeenul Islam Apr 20 '14 at 7:45
0

It's my fault. Got the solution after @Otto's comment:

Do you actually have a duplicate key here? What is the structure of user_req and what are the keys and indexes?

Here's how my SQL query should be:

INSERT INTO {$wpdb->prefix}user_req
                ( user_id, post_id )
                VALUES ( %d, %d )
                WHERE NOT EXISTS (
                  SELECT * FROM
                  {$wpdb->prefix}user_req.user_id = user_id
                  AND
                  {$wpdb->prefix}user_req.post_id = post_id
                )

Fixed now. Thanks to Otto.

UPDATE

Though I said, it's solved, but actually, it's not. The code somehow was not working and not even inserting data (Details here). So with the knowledge I had from the others, and with assistance of my colleague Mr. Ariful Haque, I did it with two different but interlocking queries:

    $alreadyGot = $wpdb->get_results(
                        "SELECT
                        COUNT(*) AS TOTALCOUNT
                        FROM {$table}
                        WHERE ( user_id = $user_id AND post_id = $post_id[1] )
                            AND ( order_id = '' )"
                    );

    $count = $alreadyGot[0]->TOTALCOUNT; //if there's any duplicate, it'd return 1

    // if the count return 1, do nothing, but else insert the data
    if( $count > 0 ) {
        //do nothing
    } else {
        $wpdb->insert( 
            $table,
            array(
                'user_id' => $user_id,
                'post_id' => $post_id[1]
            )
        );
    }
  • so on duplicate key actually won't work with wpdb? For instance I have a query "INSERT INTO my_locations (location_id,address,lat,lng) VALUES (%d,%s,%f,%f) ON DUPLICATE KEY UPDATE address = %s, lat = %f, lng = %f"; and when using prepare() it returns false. Will I really have to rewrite the query somehow? – trainoasis Jan 25 '16 at 8:32
  • We better ask a different question, if this one doesn't solve your issue. – Mayeenul Islam Jan 25 '16 at 8:46

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.