$wpdb->prepare with ON DUPLICATE KEY UPDATE

Question

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?

Answer 1

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]
            )
        );
    }