2

I want to use PHP to check if a value already exists in the database and prevent that value from being entered twice. If the value does not exist, then we insert. I have tried with a PHP if statement, but could not get it right. I get the message:

Syntax error or access violation: 1064 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 ':email' at line 1

Here is my insert script:

try
{
    $sql= 'INSERT INTO user_registration SET
           email= :email,
           date = CURDATE()';

    $s = $pdo->prepare($sql);

    $s->bindValue(':email', $_POST['email']);

    //$test=$s->execute();  
}
catch (PDOException $e) 
{
    echo "Problem with script".$db_name.$e->getMessage();
}

try 
{
    $sql=$pdo->query("SELECT email FROM user_registration WHERE email = :email");

    $stmt = $pdo->prepare($sql);

    $stmt->bindParam(':email', $_POST['email']);

    $stmt->execute();
}
catch (PDOException $e)
{
    echo "Problem exisyts".$e->getMessage();
} 

if ($stmt->rowCount > 0) {
    echo "Already exists";
    exit();
}
else
{
    $s->execute();
}
Blackwood
  • 4,504
  • 16
  • 32
  • 41
Doss Smart
  • 77
  • 1
  • 1
  • 7
  • 1
    Why not `select` the `column` then use `empty` on the value of that column. If `true` it is empty. Then you insert. Otherwise it is not empty. – Script47 Aug 11 '15 at 23:48
  • You want to do it in PHP and not at the DB level? – chris85 Aug 11 '15 at 23:51
  • @chris85 i was trying to figure how i should do it..if it is good to do at db level, will be more than happy to learn about... – Doss Smart Aug 11 '15 at 23:55
  • @DossSmart it depends what you prefer. Either one will work. I provided a suggestion which you can attempt, if you get stuck update your post and I'll be happy to help. – Script47 Aug 11 '15 at 23:57
  • @chris85 could you please translate the suggestion into code, so that i could see?? thanks – Doss Smart Aug 12 '15 at 00:02
  • 1
    At the DB leve change the `email` column so it has a unique constraint https://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html. Then in the PHP do an an insert ignore, https://dev.mysql.com/doc/refman/5.5/en/insert.html. `With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.` – chris85 Aug 12 '15 at 00:05
  • *"i have tried with php if statement"* - what `if` statement? a lot of ways to do this. You need to show us what you tried. – Funk Forty Niner Aug 12 '15 at 00:05
  • @chris85@Fred -ii- I have updated my code. So you guys could check and help with mistakes by giving tips and links. Thanks. – Doss Smart Aug 12 '15 at 00:32
  • Is this code you exact code? – chris85 Aug 12 '15 at 00:54
  • @chris85 yes, it is. is something wrong? been trying to do things by myself to understand php/mysql very well – Doss Smart Aug 12 '15 at 00:59
  • I thought the error message but I don't see a cause to that, are you not getting that message? Did you decide to not go unique constraint route? – chris85 Aug 12 '15 at 01:07
  • @chris85 unique contraint route?? where i should do that? in phpadmin? or in the code? – Doss Smart Aug 12 '15 at 01:16
  • Yes modify the structure of the table's column in phpmyadmin to go that route. http://stackoverflow.com/questions/10749382/alter-table-add-unique-key-results-in-an-error – chris85 Aug 12 '15 at 01:20
  • @chris85 Thanks it will do it now , but is my code correct? is there room for improvement in my code? – Doss Smart Aug 12 '15 at 01:31
  • You won't need the select if you use the `unique`. You will need to update the `insert` so it doesnt throw an error when it encounters duplicates. See my second comment, everything should be there. – chris85 Aug 12 '15 at 01:33
  • Yes, you also could do an `on duplicate` if you wanted it to update the date field.. http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update Are you having issues with that query? – chris85 Aug 12 '15 at 12:22
  • @chris85 No don't have problem with the INSERT iGNORE, it works very well,i even remove the select query but i am having trouble to create a suitable message to inform the user that the email already exists, Any thoughts or tips on how i should do it? thanks – Doss Smart Aug 12 '15 at 12:31
  • Oh, you won't know this with this approach. You could do an ajax request before the user submits that checks if the email address exists or not already. That would be using the select method you had before. – chris85 Aug 12 '15 at 12:33
  • @chris85 Ajax request?hmm..that is like beyond my little knowledge. don't know ajax yet. But it should be a way to do it in php, right? if there is, give me the route, i would try and find myself...thanks – Doss Smart Aug 12 '15 at 12:42
  • Use the code you have in this question, move the `select` before the `insert`. Ajax isn't that hard to learn and would be useful for you in the future, https://learn.jquery.com/ajax/. You also don't need to remove the unique constraint that will work as a safe guard incase your select fails for some reason.. – chris85 Aug 12 '15 at 12:45
  • @chris85 Thannkkk you very much...got it right..can't thank you enough from Ivory coast (western Africa)...Thanks – Doss Smart Aug 12 '15 at 15:41

0 Answers0