-1

Here is a similar structure of a table that I am working on.

|--ID--|--user_id--|--meta_key---|--meta_value--|
|--1 --|--444444---|--first_name-|------NULL----|
|--2 --|--444444---|--last_name--|------NULL----|
|--3 --|--444444---|--phonenum---|------NULL----|
|--4 --|--333333---|--first_name-|------NULL----|
|--5 --|--333333---|--first_name-|------NULL----|
|--6 --|--333333---|--phonenum---|------NULL----|
|--7 --|--222222---|--first_name-|------NULL----|
|--8 --|--222222---|--first_name-|------NULL----|
|--9 --|--222222---|--phonenum---|------NULL----|

I am trying to UPDATE all the info for a given user. Let's say I want to update the first_name, last_name, and phonenum for the user with user_id 333333 in a single query using mysqli.

This is not a wordpress database, so please do not include native wordpress functions in the answer.

  • Can you always guarantee that the fields are the same and in the same order - i.e. first_name, last_name and phonenum are always the same, always adjacent and there only ever will be 3 records in the table for a given user_id. By "adjacent" I mean that the IDs will be consecutive? – Vérace Feb 5 '15 at 1:48
  • @Vérace no, unfortunately there is no guarantee that will happen. – brandon-estrella-dev Feb 5 '15 at 2:36
  • Can one at least assume that the number and name of fields will remain the same - if not in sequence with ID? – Vérace Feb 5 '15 at 3:01
  • You have found one of many reasons why a key-value schema sucks. – Rick James Feb 6 '15 at 6:17
0

This is not a complete answer, but it won't fit in a comment, so please excuse me in advance. Maybe you'll find the approach of help - I'm stuck!

I was working on something along these lines - but it depends on the adjancy that I refererred to earlier on - maybe the selects could be varied to get the right data?

CREATE TABLE My_Table
(
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  meta_key VARCHAR(15),
  meta_value VARCHAR(15)
);


INSERT INTO My_Table (user_id, meta_key) VALUES(44444, 'John');
INSERT INTO My_Table (user_id, meta_key) VALUES(44444, 'Doe');
INSERT INTO My_Table (user_id, meta_key) VALUES(44444, '12345');
INSERT INTO My_Table (user_id, meta_key) VALUES(33333, 'Mary');
INSERT INTO My_Table (user_id, meta_key) VALUES(33333, 'Sixpack');
INSERT INTO My_Table (user_id, meta_key) VALUES(33333, '54321');
INSERT INTO My_Table (user_id, meta_key) VALUES(22222, 'Marcel');
INSERT INTO My_Table (user_id, meta_key) VALUES(22222, 'Toulemonde');
INSERT INTO My_Table (user_id, meta_key) VALUES(22222, '00-33-9999');


== Works - gives first_name, last_name, phonenum - but as one column, not one row.


SET @user_id := 22222;
SELECT @ID := ID FROM My_Table WHERE user_id = @user_id;
SELECT * FROM
(  
  SELECT meta_key FROM My_Table WHERE ID = (@ID -2)
  UNION 
  SELECT meta_key FROM My_Table WHERE ID = (@ID -1)  
  UNION
  SELECT meta_key FROM My_Table WHERE ID = (@ID)  
) tab;


+------------+
| meta_key   |
+------------+
| Marcel     |
| Toulemonde |
| 00-33-9999 |
+------------+
3 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.