Jump to content
#StayAtHome ×
mactron

Check if person_id already exist in the table

Recommended Posts

 Hi! I wrote a small function that check if person_id already exist in the table called persons_web_tbl. If the person_id exist I get a message otherwise I add twitter/facebook profile of that person. I'm just trying to prevent duplicated entries this way.  I will get person_id  from table person_tbl, where are all people saved. The function actually works, I'm just curious if I'm on the right path. Thanks!

$person_id = 11;
class test extends dbh{

public function personExist($person_id) {

$sql = "SELECT person_id FROM persons_web_tbl WHERE person_id = ?";
 
	$stmt = $this->conn->prepare($sql);
	$stmt->execute([$person_id]);
	$row = $stmt->fetchColumn();
			
		if ($row > 0){

			echo "found";

		}else {

			$person_web_twitter = "add something";

			$sql = "INSERT INTO persons_web_tbl (person_web_twitter, person_id) VALUES (?, ?)";
			$stmt = $this->conn->prepare($sql);
			$stmt->execute([$person_web_twitter, $person_id]);
			
		}

	 }
}



$person = new test ();
$person = $person->personExist($person_id);

 

Edited by mactron

Share this post


Link to post
Share on other sites

Where is $person_web_twitter coming from?  I don't see it getting any value here.

Plus - you probably don't want to burden your class methods with direct output like your echo of a message.  You should design the method to return a value and then use a different method to do your insert.

Share this post


Link to post
Share on other sites

From form ...

I have two person tables, both get data from forms.  I just added $person_web_twitter = "add something" and echo "found"; to test the class..

table 1 -> table person_tbl as an index of all people. 

table 2 -> persons_web_tbl where I will store people's links such as websites, twitter & facebook profiles.

 

So, that's wrong approach?

Edited by mactron

Share this post


Link to post
Share on other sites
58 minutes ago, mactron said:

I'm just trying to prevent duplicated entries this way.

That is not the way to handle it.

  1. Set a unique constraint on the DB column(s)
  2. Attempt the insert and catch the duplicate error if any.
  • Thanks 1

Share this post


Link to post
Share on other sites

In that case your "persons_web_table" needs to allow multiple person_id"

 

EG

person_id  |  platform  
-----------+------------
    1      |  Facebook
    1      |  Twitter
    1      |  Instgram
    2      |  Facebook
    2      |  Twitter
    3      |  Twitter 

 

Edited by Barand

Share this post


Link to post
Share on other sites

persons_web_tbl will look something like that

person_web_id | person_web_linkedin | person_web_twitter | person_web_facebook | person_web_website | person_id

 

Edited by mactron

Share this post


Link to post
Share on other sites
1 minute ago, mactron said:

person_web_id | person_web_linkedin | person_web_twitter | person_web_facebook | person_web_website | person_id

That is not the way to do it. DB tables are not spreadsheets

Share this post


Link to post
Share on other sites

But for future reference, as benanamen said, don't waste time checking first. 

  1. Ensure that person_id is either the primary key or is defined as a UNIQUE key. Then it is impossible to insert a duplicate and will throw an error/exception.
  2. Insert the new record and check for a duplicate key error. If there is one, output error message or "found", otherwise it worked and job done.

Example

CREATE TABLE `user_demo` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(20) DEFAULT NULL,
  `lname` varchar(20) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `username` (`username`)
)

DATA:
+--------+-------+----------+----------+
| userid | fname | lname    | username |
+--------+-------+----------+----------+
|      1 | Laura | Norder   | norderl  |
|      2 | Tom   | DiCanari | tomd     |
|      3 | Harry | Potter   | harryp   |
+--------+-------+----------+----------+

 

Attempting to insert another user with same username gives a 1062 Duplicate key error

mysql> INSERT INTO user_demo (fname, lname, username)
    -> VALUES ('Harry', 'Palmer', 'harryp');
ERROR 1062 (23000): Duplicate entry 'harryp' for key 'username'

The processing would be

try {
   insert new record
}
catch (exception e) {
   if error number = 1062
       echo "Duplicate!"
   end if
}

 

Share this post


Link to post
Share on other sites
persons_tbl

person_id  |  person_name | person_surname | person_description | person_slug
-----------+--------------+-------------------+--------------------+-------------
    1      |  John		  |	Doe 	 	   | bla bla bla  | john-doe
    1      |  Jane		  |	Doe            | bla bla bla        | jane-doe
    2      |  Greg		  |	Stue           | bla bla bla        | greg-stue
-----------+--------------+----------------+--------------------+-------------

persons_web_tbl

person_web_id  |  person_web_linkedin | person_web_twitter | person_web_facebook | person_web_website | person_id
---------------+----------------------+--------------------+---------------------+--------------------+---------
    1          |  linkedin.com/john   |	twitter.com/john | facebook.com/john    	| www.johndoe.com | 1
    1          |  linkedin.com/jane   |	twitter.com/jane | facebook.com/jane   		| www.janedoe.com | 2
    2          |  linkedin.com/greg   |	twitter.com/greg | facebook.com/greg    	| NULL			  | 3

 

Edited by mactron

Share this post


Link to post
Share on other sites
4 minutes ago, Barand said:

But for future reference, as benanamen said, don't waste time checking first. 

  1. Ensure that person_id is either the primary key or is defined as a UNIQUE key. Then it is impossible to insert a duplicate and will throw an error/exception.
  2. Insert the new record and check for a duplicate key error. If there is one, output error message or "found", otherwise it worked and job done.

Example


CREATE TABLE `user_demo` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(20) DEFAULT NULL,
  `lname` varchar(20) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `username` (`username`)
)

DATA:
+--------+-------+----------+----------+
| userid | fname | lname    | username |
+--------+-------+----------+----------+
|      1 | Laura | Norder   | norderl  |
|      2 | Tom   | DiCanari | tomd     |
|      3 | Harry | Potter   | harryp   |
+--------+-------+----------+----------+

 

Attempting to insert another user with same username gives a 1062 Duplicate key error


mysql> INSERT INTO user_demo (fname, lname, username)
    -> VALUES ('Harry', 'Palmer', 'harryp');
ERROR 1062 (23000): Duplicate entry 'harryp' for key 'username'

The processing would be


try {
   insert new record
}
catch (exception e) {
   if error number = 1062
       echo "Duplicate!"
   end if
}

 

I will take a look. Thanks!

Share this post


Link to post
Share on other sites

The person_id should be unique in person_tbl.

I showed you in my earlier reply how the persons_web_tbl should be.

Share this post


Link to post
Share on other sites

In fact this guy had the same problem a couple of days ago

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.