0

At first I have to enter two Accountnames in the website.

My tablename is 'account'.

The rows are id, username and recruiter.
The lines are
7, Janis, 0
and
4, testlol, 0.

I want to 'connect' two users. In the first line - the line of Janis - have to change his 'recruiter' into the value of the other user.

So the result should looks like:

7 | Janis | 4
and
4 | testlol | 7

...

In my php script I wrote 

<?php
$servername = "localhost";
$username = "name";
$password = "test";
$dbname = "db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql1 = "UPDATE account SET recruiter=??? WHERE username=\"" . $_POST["account1"] . "\"";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

What do I have to write instead of the ??? for updating it with the other user's id?

It's from this form:

<form action="handler.php" method="post" >
    <p>Account Nr.1:</p><input class="input" type="text" name="account1" value="">
    <p>Account Nr.2:</p><input class="input" type="text" name="account2" value="">
    <p></p><input class="button" type="submit" name="submit" value="Bestätigen"> 
</form>
Janis
  • 13
  • 4
  • Who is `the other user`? Shouldn't that just be the `???`? Don't pass user input directly to your query, separate it with prepared statements. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – chris85 Jul 04 '15 at 02:16
  • sorry, but I don't understand... I only type two Accaountnames in my website. In the form they have the names 'account1' and 'account2'. Helpful? – Janis Jul 04 '15 at 02:18
  • How is the `recruiter` determined? From a form value, a db value? – chris85 Jul 04 '15 at 02:22
  • So the `recruiter` is account2? Just `$_POST["account2"]`, if that is correct. – chris85 Jul 04 '15 at 02:25
  • My problem is, I want to change the 'recruiter' into the 'id' of the other user. So i have to call up the id with the username in my update script. I think so... – Janis Jul 04 '15 at 02:28
  • Run a subquery in the update to pull the id. – chris85 Jul 04 '15 at 02:30
  • Yeah .. but .. how do I do this? – Janis Jul 04 '15 at 02:31
  • I don't know your tables or columns how does `userid` correlate to `$_POST["account2"]`? Adding something like `(select userid from table where column = $_POST["account2"])` I think would do it. But again please **don't** do it this way, look at prepared statements. – chris85 Jul 04 '15 at 02:32
  • but I can't write it that way in an update command... – Janis Jul 04 '15 at 02:34
  • Yes, you can. Follow my exact syntax. – chris85 Jul 04 '15 at 02:34
  • I could write UPDATE account SET recruiter=( SELECT id FROM account WHERE username = $_POST["account2"]) WHERE username=$_POST["account1"]; – Janis Jul 04 '15 at 02:36
  • Yes, can you not? Is userid not unique (1 id per table)? But again donttttt do it that way. – chris85 Jul 04 '15 at 02:37
  • Yes, but it does not work... They said: "Error updating record: You can't specify target table 'account' for update in FROM clause" – Janis Jul 04 '15 at 02:39
  • my code is: "UPDATE account SET recruiter=(SELECT id FROM account WHERE username=\"" . $_POST["account2"] . "\") WHERE username=\"" . $_POST["account1"] . "\""; – Janis Jul 04 '15 at 02:39
  • DIdn't know it was the same table. See this thread. http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – chris85 Jul 04 '15 at 02:42
  • I've had written the same code they said "update account set recruiter = (select id from account where username =\"" . $_POST["account2"] . "\") where username =\"" . $_POST["account1"] . "\""; , but the error is still there – Janis Jul 04 '15 at 02:48
  • What, did you miss last comment? – chris85 Jul 04 '15 at 02:50
  • the thread can't help me. I tried it the same way they said... I'm in a fix... :'D – Janis Jul 04 '15 at 02:52

1 Answers1

0

This should be what you're looking for below. Although, it's hard to tell from you post what exactly you want. If I'm following correctly, you want to switch the recruiter value to other recruiter? If so, this should work: 

$sql1 = "UPDATE account SET recruiter = $_POST["account2"] WHERE username = $_POST["account1"]";
Sam B.
  • 283
  • 4
  • 17
  • I don't want to set the recruiter to the name of the other user. So ... there, where you written "account2" have to be the "SELECT id FROM account WHERE username=$_POST["account2"]".. But I can't insert it this way – Janis Jul 04 '15 at 18:15
  • What do you want to update or switch out? The id, username or recruiter? – Sam B. Jul 04 '15 at 18:22
  • recruiter have to be updated. The new vale have to be the id of the the other user's entered name – Janis Jul 04 '15 at 18:37
  • So you want the id's to be switched between the two users? – Sam B. Jul 04 '15 at 18:41
  • No, the id's have to stay the same. Only the row 'recruiter' have to become the value of the 'id' of the other user. So the recruiter of Janis have to change to 4 - the id of testlol The recruiter of testlol have to change to 7 - the id of Janis – Janis Jul 04 '15 at 18:56
  • This is kind of confusing. Why are you trying to have the recruiter of one of them switch to the other's id number and vice versa? Can you explain what you are trying to do so I can get a better understanding to help you? – Sam B. Jul 06 '15 at 04:42