Jump to content

If key exists, how to concat another column?


ChenXiu
 Share

Recommended Posts

Posted (edited)

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 by ChenXiu
Link to comment
Share on other sites

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) )

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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 😀)

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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...

 

 

Link to comment
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.

 Share

×
×
  • 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.