Update a table that has already been executed PHP

Question

I have a text file that goes like this

1 wordsgohere
2 morewordsgohere
3 yougetthepoint

I want to assign one of the strings above to the user_id of that person. So say you are the third person to register, your user_id is 3 and your deposit_id would be 'yougetthepoint'.

    // write new users data into database
    $query_new_user_insert = $this->db_connection->prepare('INSERT INTO users (deposit_id, user_name, user_password_hash, user_email, user_activation_hash, user_registration_ip, user_registration_datetime) VALUES(:deposit_id, :user_name, :user_password_hash, :user_email, :user_activation_hash, :user_registration_ip, now())');
    $query_new_user_insert->bindValue(':deposit_id', 'placeholderid', PDO::PARAM_STR);
    $query_new_user_insert->bindValue(':user_name', $user_name, PDO::PARAM_STR);
    $query_new_user_insert->bindValue(':user_password_hash', $user_password_hash, PDO::PARAM_STR);
    $query_new_user_insert->bindValue(':user_email', $user_email, PDO::PARAM_STR);
    $query_new_user_insert->bindValue(':user_activation_hash', $user_activation_hash, PDO::PARAM_STR);
    $query_new_user_insert->bindValue(':user_registration_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);

    $query_new_user_insert->execute();

// id of new user
    $user_id = $this->db_connection->lastInsertId();


    // searches text file for address
    $lines_array = file("test.txt");

    foreach($lines_array as $line) {
    echo $line;
            if(strpos($line, $user_id) != false) {
                    list(, $new_str) = explode($user_id, $line);

            }
    }

This now puts the deposit_id needed into new_str. Now I need to update the deposit_id in the table with new_str.

Any help would be great.

Tried this and can't get it to update. I just wanted to see if I could update just the first users deposit_id.

        $query_new_user_update = $this->db_connection->prepare('UPDATE users set deposit_id :deposit_id where user_id =1');
        $query_new_user_update->bindValue(':deposit_id', 'doesthischange', PDO::PARAM_STR);
        $query_new_user_update->execute();

You figured out how to do an `INSERT` statement; `UPDATE` statements aren't all that different. You should be able to find examples through Google. — quickshiftin, Dec 21 '13 at 20:47
possible duplicate of [Update query with PDO and MySQL](http://stackoverflow.com/questions/18323065/update-query-with-pdo-and-mysql) — quickshiftin, Dec 21 '13 at 20:47
It would have been nicer if your text file was already in the database as a different table. In that case a subquery inside your first query would have sufficed. — nl-x, Dec 21 '13 at 20:48
you need to do the strpos with a whitespace behind the user_id. Otherwise users 10,11,12, etc will get the text of user 1 — nl-x, Dec 21 '13 at 20:52

nl-x · Accepted Answer · 2013-12-22T03:02:23.323

1

You need to add something like this at the bottom:

$query_new_user_update = $this->db_connection->prepare('UPDATE users set deposit_id = :deposit_id where user_id = last_insert_id()');
$query_new_user_update->bindValue(':deposit_id', $new_str, PDO::PARAM_STR);
$query_new_user_update->execute();

In this case last_insert_id() is a native mysql function that you can call in your SQL.

Edit:

And by the way, you may want to change

if(strpos($line, $user_id) != false) {
// bad: does not recognize strings that start with $user_id.
//      It only recognizes strings that contain $user_id when
//      the $user_id is not at the start of the line.

into

if(strpos($line, $user_id . ' ') === 0) {
// good: only recognizes string that start with
//       $user_id followed by a white space

!= false would not work well, because when a line starts with the user_id, str_pos will return 0, meaning the user_id is found at character position 0. But 0 == false, as it does loose comparison. So you need now to work with === that does strict comparison, to also check for matching types. 0 !== false , as 0 is an INT and false is a BOOL.

Also to avoid matching a user_id (EG 1) with any numbers inside the text on the right. Or with other user_ids that contain that digit. (EG 11, 12, 13, etc.) You also need to search for strings that start with the user_id, and to also check for the white space after the user_id.

edited Dec 22 '13 at 03:02

answered Dec 21 '13 at 20:55

nl-x

11,762
7
33
61

That's what I've been looking for. Except I can't get it to work. I tried changing it to see if it would just update user id 1's deposit_id with a random string and nothing. Will post the code in the post. – SolidCloudinc Dec 21 '13 at 21:21
@SolidCloudinc aay, In my 3 lines, second line still contains `query_new_user_insert` in stead of `query_new_user_update`. I'll edit it right now in my answer. Try it again now. – nl-x Dec 21 '13 at 21:29
Yeah I noticed that and changed it but it still doesn't update it. I also tried it with just trying to update user 1. However the new if statement works where as my old one did not. – SolidCloudinc Dec 21 '13 at 21:51
@SolidCloudinc what mysql type is Deposit_id anyway? Int? Varchar? Text? ... Make sure its not a numerical type (So not Int, Float, Decimal) – nl-x Dec 22 '13 at 02:38
@SolidCloudinc ow, I see it now. I was also missing a `=` between `deposit_id` and `:deposit_id`. But what you actually should do is learn how to spot and read out query error messages of failed queries. – nl-x Dec 22 '13 at 03:03
Where do you find the query error messages? I'm editing with notepad++ and my webpage gave no errors. I'm new to php. Thanks for your help. – SolidCloudinc Dec 22 '13 at 04:55

Update a table that has already been executed PHP

1 Answers1