0

Hello I have te following PDO insert into database function, how I can check if there is such a recored inside the database already to give error that the user is already registred with this information ? My table is organized like that 

id | curso_id | user_id 
1  | 12       | 43
2  | 5        | 56

so if a combination of curso_id = 12 and user_id = 43 is inside the database do not register second one of this kind, give error. 

$userid = $_SESSION['userID'];  
$cursoid = $_GET["id"];

try {

          $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); //our new PDO Object
          $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
          $stmt = $con->prepare( "Insert Into subscriptions ( curso_id, user_id ) Values ('$cursoid', '$userid')" );
          $stmt->execute();

       } catch (PDOException $e) {
              echo "I'm sorry there is a problem with your operation.."; $e->getMessage(); //catch and show the error
       }

$con = null;
header( 'Location: cursos.php' ) ;
}

Thanks for the help in advance

John Siniger
  • 875
  • 2
  • 16
  • 39
  • Just a side comment,you are not using prepared statements. – Mihai Oct 05 '13 at 23:11
  • $userid is coming from the loged user in the session. and $cursoid is coming from another database sqlquery which is showing the articles in the database – John Siniger Oct 05 '13 at 23:15
  • the idea is to compate the both columns and if there is a match on what you are inserting do give error, To not put duplicated records. $userid it will be always > than 0 because on every insert you are inserting something on the both columns. – John Siniger Oct 06 '13 at 00:04

2 Answers2

0

Proactively, look before you leap. Execute a select before inserting, which will show you if there's already a relevant record.

Reactively, add a unique(curso_id,user_id) constraint to your table. That would protect your data when everything else fails...

geomagas
  • 3,230
  • 1
  • 17
  • 27
0

Set a UNIQUE index : unique( curso_id, user_id ) constraint .
And then you can use query something like this with ON DUPLICATE KEY UPDATE clause :

INSERT INTO subscriptions
    ( curso_id, user_id )
    VALUES
    ('12', '43')
    ON DUPLICATE KEY UPDATE user_id = user_id;

If the affected-rows is 1 , a new row is inserted .
If the affected-rows is 2 , an existing row is updated .

Based on the affected-rows value , you can choose to display appropriate message to the user .
...

INSERT ... ON DUPLICATE KEY UPDATE
REPLACE
Insert to table or update if exists (MySQL)

Community
  • 1
  • 1
Uours
  • 2,517
  • 1
  • 16
  • 21