0

How can I INSERT the values of this function to only WHERE the value of the username column (located in the login table, contains the exact same value as the value of the column nickname (located in a table called active_users). 

    $stmt = $this->pdo->prepare('INSERT INTO login (device_token) VALUES (?)');
    $stmt->execute(array($token));

Additional info: when a user logs in, I set up the API to add the user's name to both username (in the login table) and nickname in the active_users table. Since these values will always be the same for their respective tables, I was hoping we could add some conditional logic to achieve this type of specificity. My goal is to take the device_token parameter and pass it to a different spot in the database.

Edit: Schema

active_users table

login table

enter image description here

enter image description here

Update AFNetworking command for login

NSMutableDictionary* params =[NSMutableDictionary dictionaryWithObjectsAndKeys:command, @"command", _nicknameTextField.text = fldUsername.text, @"username", hashedPassword, @"password", nil ];

Update: active_users INSERT statment

$stmt = $this->pdo->prepare('INSERT INTO active_users (user_Id, device_token, nickname, secret_code, ip_address) VALUES (?, ?, ?, ?, ?)');
$stmt->execute(array($userId, $token, $name, $code, $_SERVER['REMOTE_ADDR']));
Improve this question
5
  • 1
    I think you should show us the schema of your tables. Commented Jan 26, 2016 at 12:35
  • I am using multiple tables in my database. One handles the users login info and the other keeps track of that user as well as gives them a device_token. I want to update the device token column when the user logs in Commented Jan 26, 2016 at 12:38
  • 1
    I know what you're trying to do, you need to use a join (most likely a LEFT join) however, until I see your schema I cannot help you. A schema being your table definitions and the fields. Commented Jan 26, 2016 at 12:39
  • 1
    you should ad a foreign key to your second table and update the token while the user login by the foreign key Commented Jan 26, 2016 at 12:45
  • 1
    Thank you @ash; I've realized the best way to do it thanks to all of your input Commented Jan 26, 2016 at 13:40

2 Answers 2

Reset to default
3

If I understand you correctly. What you really want is an update and not an insert. You want to add the value of device_token to the row with nickname, correct?

You would do that with an update:

UPDATE login
SET device_token = @token
WHERE username = @nickname

If you're trying to update the record with an existing record from active_users table than:

UPDATE login AS L
JOIN active_users AU ON L.username = AU.nickname
SET L.device_token = AU.device_token

Though, looking at your tables. I'm wondering if you're repeating too much of the data. It seems like you can link login and active_users by user_id. Well, this would get off topic, so I'll just suggest double checking db design concepts and your needs.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Is there a way to join that statement with the active_users table so that it knows that thats where the nickname statement is?
@Program1 I edited my question. Was that what you were asking?
Yes, I was hoping that would work though for some reason it still skips a line so that even though the login device_token field gets updated, it still needs to be specifically associated with the current user. Picture update above
The thing is the user_id in the active_users table is dynamic and is always changing. I was hoping the fact that the values for nickname and username are the same would be sufficient. I updated my post: the last picture shows what happens when I sign up with a username of "Santiago" after using the JOIN statement. Notice how it just skips a line altogether
That's very odd. It should not create a new record if you are doing an update.
|
1

Using PDO it should be

UPDATE login
SET device_token = :token
WHERE username = :nickname

where the params array that will be bound looks like

$params = array("token" => $token, "nickname" => $nickname);

Full example:

$stmt = $this->pdo->prepare('UPDATE login SET device_token=:token WHERE nickname=:nickname');
$stmt->execute(array("token" => $token, "nickname" => $nickname));

Or you use the method of binding with ?

UPDATE login
SET device_token = ?
WHERE username = ?

where the parameter array is

$params = array($token, $nickname);

Full example:

$stmt = $this->pdo->prepare('UPDATE login SET device_token=? WHERE nickname=?');
$stmt->execute(array($token, $nickname));
Improve this answer

12 Comments

After changing your code to replace nickname with username, I signed up with the username electric and the device_token gets added to a new line still. I want it to be added to the same row with the electric username. Thoughts?
Sorry - of course it should be UPDATE active_users ... if you want to update the active_users table Prerequisite is, that the user with the nickname already exists in the active_users table Or should the token be set in the login table??
How do you set the information in the login table now? So how is the username set there?
Ok - so the login is done via AFNetworking or does it only pass the data to php and the php writes the data into the login table? If so, you can enrich the $_POST data with the token selected from the active_users table and then insert into the login table.
Yeah i think then it would be best/easiest to do like you said in your comment before.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.