Please assist with php/mysql code

Beginners - Learning PHP
#1

Hi,

Been trying working with database but been stuck. I want to use php to check in a value already exist in db and prevent that value to enter twice, if the value does not exist, then we insert.

i have tried with php if statement; but could not get it right… Could help please how i should. 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();

}

Thanks

#2

What you want to do is set a unique index on the table column. That will prevent duplicates at the database level. When a duplicate value is attempted to be entered you capture the error and do something with it, usually displaying a custom error message.

I suggest you download my PDO Bumpstart Database from the link in my signature.

#3

Could you take a lot at my code please and help out? Thanks

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

[php]
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();
}
[/php]

#4

[php]INSERT INTO user_registration SET
email= :email,
date = CURDATE()[/php]

What is the table structure? If you do not have all of the column names, I believe this type of insert fails. Use Rubio’s advice for the unique index. The next issue I see is, the script looks out of place. Why are you just inserting an email? Would you not want names or other information as well? As in a full registration form that would be submitted?

If this is just to update, you would have the person’s id from the database, so I am confused about just having their email and the date.

Using Rubio’s approach:

[php]
try {

$sql =<<<SQL
INSERT INTO user_registration
(email, date)
VALUES
( :email, CURDATE())
SQL;

$s = prepare( $sql );

$s->execute( array( ‘:email’ => $_POST[‘email’] ) );

} catch( PDOException $e ) {

if ( $e->getCode() == ‘23000’ )
echo “

Duplicate Entry

”;
else
echo $e->getMessage();

}

[/php]

#5

Thanks for your advise, i have updated everything based on your advise, but one more issue. The code does not add records when the email already exits, But having trouble to create a suitable message to inform the user.

here the db design:

id - primary key
name - varchar
email - varchar, unique index
persoID - varchar
date

here the code:

include ‘connection_db.php’;

if (isset($_POST[‘submit’])) {

$name = $_POST[‘name’];

$email = $_POST[‘email’];

$persoID = $_POST[‘persoID’];

try
{

$sql=‘INSERT IGNORE INTO personal_info SET
name = :name,
email = :email,
persoID = :persoID,
date = CURDATE()’;

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

$s->bindValue(’:name’, $_POST[‘name’]);

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

$s->bindValue(’:persoID’, $_POST[‘persoID’]);

$s->execute();

} catch (PDOException $e)

{
echo “No data insert”.$e->getMessage();

}
}

Everything works, but don’t know how to create a suitable message to inform the user that the email already exits.

#6

I would start with informing them they are already in the system,possibly asking if they want to update the information. But only after they havevpassed authentication, twice.

#7

If you want all the information to update or be refreshed and the email already exists you can use the “ON DUPLICATE KEY UPDATE” syntax.

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Or you can do it the long way “Test to see if the email exists, then Insert or Update based on the test results”

#8

Oh no, no, goodness no! If you do that you will allow person #2 to change/update the information for person #1.

#9

I don’t follow Kevin, it really depends on how you layout your tables. If I had a User table with just (user_id and Password) then another table with (User Contact Information). Then if the user wanted to add/update contact information it will work fine with id, email being the unique key.

#10
db design

id - primary key
name - varchar
email - varchar, unique index
persoID - varchar
date

#11

It’s just a bad DB design.

If you want to work with that design - any person can sign up with someone elses email address, preventing the real owner from signing up.

So many validation checks will have to be done “Opt in link to validate the email is owned by that person” - etc. So you need another column that tells you if the email has been validated.

#12

I was getting at that in the above post, i just didn’t want to do a walk thru of how to authenticate that way.

#13

I was going on the assumption of a newbie with a single table adding a new record. Of course, ultimately you would want to validate the email address by sending out a confirmation.

Sponsor our Newsletter | Privacy Policy | Terms of Service