If key exists, how to concat another column?

[ChenXiu]

Visitor's Session ID and a Random Number gets inserted into mySQL.

If the page is re-visited with the same Session ID, I want the new Random Number appended to the existing random number already in mySQL.

This non-working example is the best I can do. What will make this work?

CREATE TABLE mytable ( // my Table structure
  Session_id varchar(255),
  Random_Number varchar(6),
  id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  KEY Session_id (Session_id)
)

$sessionid = session_id();
$Random_Number = rand(111111,999999);
$query = $db->prepare("INSERT INTO mytable (Session_id,Random_Number) VALUES (?, ?) ON DUPLICATE KEY CONCAT(Random_Number, VALUES(?))");
$query->bind_param("sss",$sessionid,$Random_Number,$Random_Number);
$query->execute();
$query->close();

I assume if I can make this "one-liner" method work, this method will be faster and more efficient than having to do 2 queries whereby the existance of Session_id is ascertained first, and then subsequently creating a new row if it doesn't exist, or, concating if already exists...

If there is a TOTALLY BETTER way of doing this, I am "all ears" (I learned this expression last week "all ears," means I am eager for better knowledge).

Thank you.

Edited May 24, 2021 by ChenXiu

[Barand]

There are some problems with your approach.

• For ON DUPLICATE KEY to work, the session_id column would also have to be defined as UNIQUE
• Your random_number column is defined a VARCHAR(6), so if you concatenate another 6 characters, where can they go?

Then

INSERT INTO mytable (Session_id,Random_Number) 
VALUES (?, ?)
ON DUPLICATE KEY UPDATE
    Random_Number = CONCAT(Random_number, VALUES(Random_Number) )

 

[ChenXiu]

Thank you, good point!
Two good points actually!
So, varchar is fixed now.
The question is whether a future Session ID from a new visitor would ever be the same.
In my case, probably yes it will happen...

[Barand]

2 hours ago, ChenXiu said:

Two good points actually!

I make it 3 if include the correction to your SQL syntax

[ChenXiu]

3 it is!
When I run this query on my table (which currently has only 1 row), mySQL answers back "Query OK, 2 rows affected"....
Why does it say "2 rows" affected when my table has only 1 row?
(Just like my counting "2 good points" when you really made "3 good points," it looks like mySQL is bad at counting, too 😀)

[Barand]

On this page it clearly states

Quote

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

 

[ChenXiu]

Thank you, I will learn about CLIENT_FOUND_ROWS now.
Also, rather than:
$something = $db->prepare('insert into table (column) values (?)');
Why do some coders wrap their prepared statements in "if" like this:
if (  $dog = $db->prepare('insert into table (column) values (?)')   )   {

Is it just a polite formality, like saying "please?"
Give me a newspaper
vs
If you would be so kind ( give me a newspaper )

Or does it help suppress errors? I've tried it both ways 1000 times and it doesn't appear to matter...