Check if person_id already exist in the table

[mactron]

 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 February 15, 2020 by mactron

[ginerjm]

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.

[mactron]

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 February 15, 2020 by mactron

[benanamen]

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.

[Barand]

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 February 15, 2020 by Barand

[mactron]

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 February 15, 2020 by mactron

[Barand]

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

[Barand]

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
}

 

[mactron]

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 February 16, 2020 by mactron

[mactron]

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!

[Barand]

The person_id should be unique in person_tbl.

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

[mactron]

It Is

int(11) AUTO_INCREMENT

 

[Barand]

Just bad typing then?

[mactron]

Yes

[Barand]

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