0

I am new to worpress database and stuck on checking duplicate entry of user email. I would like to convert the following sql query in $wpdb

  $sql = "INSERT INTO trip_rate (id, trip, rating, r_name, email, title, review) 
  SELECT  '', '$trip', '$rating', '$name', '$email', '$title', '$review' 
  WHERE NOT EXISTS (
  SELECT email FROM trip_rate WHERE email='$email' AND trip='$trip')";


if(mysql_affected_rows() > 0){
    echo 'Review saved';
}
else{
    echo 'You have already reviewed this trip!';
}

My new code to convert it into WordPress

 <?php
if( isset($_POST['submit']) ) {
$trip = $_POST['trip'];
$rating = $_POST['rating'];
$name = $_POST['name'];
$email = $_POST['email'];
$title = $_POST['title'];
$review = $_POST['review'];

global $wpdb;
$wpdb->dmtable = $wpdb->base_prefix . 'trip_rate';
$my_part_ID = (bool)$wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(r_email) FROM {$wpdb->dmtable}
                WHERE r_email = %d AND trip = %d LIMIT 1",
        $email, $trip
    )
);

if ($my_part_ID){
    echo"already reviewed";
}
else{
    global $wpdb;
    $wpdb->insert('sb_trip_rate',array('trip'=>$trip, 'rating'=>$rating, 'r_name'=>$name,'r_email'=>$email, 'title'=>$title, 'review'=>$review,),array('%s', '%s', '%s', '%s','%s','%s'));
    }

}
?>

It keeps on saving values to table in spite of email field being unique

  • Is this your database table? Might want consider making the email column unique. – Jacob Peattie Sep 12 '17 at 9:49
  • The code works perfectly in php... i am trying to convert it into wordpress sql statement to save in my custom table in database – dilip shrestha Sep 12 '17 at 9:54
  • If a columns needs to be unique, you should set it that way in the table. WordPress or not. – Jacob Peattie Sep 12 '17 at 9:55
  • @JacobPeattie can you please review the new code. Thanks – dilip shrestha Sep 12 '17 at 10:01
0

WHERE NOT IN is what you need.

$sql = "INSERT INTO trip_rate (id, trip, rating, r_name, email, title, review) SELECT '', '$trip', '$rating', '$name', '$email', '$title', '$review' WHERE (trip_rate.email) NOT IN ( SELECT email FROM trip_rate WHERE email='$email' AND trip='$trip')";

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.