Internet Marketing Forum

For Webmasters and Entrepreneurs

Welcome, Guest. Please login or register.
Did you miss your activation email?
May 21, 2012, 02:54:12 PM

In my experience, there is only one motivation, and that is desire. No reasons or principle contain it or stand against it.

- Jane Smiley

Home

Privacy

Internet Marketing Forum > Online Business Discussions > Website Design and Maintenance > Database

Insert unique record in mysql ?

0 Members and 3 Guests are viewing this topic.

Pages: [1]

Author

Topic: Insert unique record in mysql ? (Read 2222 times)

topProducer

Junior Member

Joined: Sep 2007
Posts: 61

$View topProducer\s Profile$
topProducer is from United States

United States

Sep 03, 2007, 12:20:07 PM

How do I insert in the mysql a unique value for email.

my table is schema of this

CREATE TABLE `users` (
user_id int(11)NOT NULL auto_increment,
first_name char(20) NOT NULL,
last_name char(20) NOT NULL,
sex char(1),
email varchar(128) NOT NULL,
PRIMARY KEY (user_id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I want use php to insert to mysql but im wanting unique on the email field. by hand i work to remove duplicate each day and is long work some day.

how can I do this ?

AndyColemants

Beginner

Joined: Jul 2007
Posts: 28

$View AndyColemants\s Profile$

United States

Sep 17, 2007, 05:14:28 AM

You can make the primary key on the email field or make a unique index and any inserts will fail.

BuyEstablishedWebsites

Galaxian

Senior Member

Joined: Oct 2007
Posts: 286

The internet is my playground.

$View Galaxian\s Profile$
Gender: Male

United States

Oct 15, 2007, 02:21:53 PM

thanks Andy. I make an index on the email field with a unique constraint but how do I supress a SQL error message if there is an INSERT that is not unique ?

topProducer

Junior Member

Joined: Sep 2007
Posts: 61

$View topProducer\s Profile$
topProducer is from United States

United States

Oct 15, 2007, 02:29:27 PM

ugh, I hate o be so stpid but how do you make a unique index ?

Code4Gold

Administrator

Joined: Jan 2006
Posts: 6215

Administrator

$View Code4Gold\s Profile$

United States

Oct 15, 2007, 08:03:32 PM

Quote from: Galaxian on Oct 15, 2007, 02:21:53 PM

thanks Andy. I make an index on the email field with a unique constraint but how do I supress a SQL error message if there is an INSERT that is not unique ?

Use the INSERT IGNORE statement to supress error messages. For example

INSERT IGNORE INTO my_table (name, email) values ('Fred', 'fred @mysite.com')

If you need to know if it was inserted, use the mysql_insert_id() to get the insert id, if it's equal to zero, nothing was inserted. For example :

Code:

$sql = "INSERT IGNORE INTO my_table (name, email) values ('Fred', 'fred@mysite.com')";
$result = mysql_query( $sql );
$insert_id = mysql_insert_id();

if ( $insert_id != 0 ){
echo "successfully inserted";
}else{
echo "errror with insert"
}

Quote from: topProducer on Oct 15, 2007, 02:29:27 PM

ugh, I hate o be so stpid but how do you make a unique index ?

Use this schema instead of the one you have in the first post

Code:

CREATE TABLE `users` (
user_id int(11)NOT NULL auto_increment,
first_name char(20) NOT NULL,
last_name char(20) NOT NULL,
sex char(1),
email varchar(128) NOT NULL,
PRIMARY KEY (`user_id`,`email`),
UNIQUE KEY `idx_email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Latest Blog Post : 8 Tips for Creating a Marketing Buzz

Best Affiliate Programs Ξ SocialNews Ξ Blogger Forum Ξ Wealth Watching

vikramrandhawa

Junior Member

Joined: Sep 2011
Posts: 71

$View vikramrandhawa\s Profile$
vikramrandhawa is from India

India

Sep 16, 2011, 05:26:05 AM

Just check from the Database that email is already available on your Databse with the Coding..Just Compare the database records with the new record of client..if the record is available already then you should display the message email is alreaDY exists..

stock market tips,Bank nifty tips,option call | web design company,web hosting company

Pages: [1]