Query to get respective user id from other table

Question

I'd really appreciate some help. I realise this sort of thing is asked, but I can't find a similar enough question to solve my problem.

We have above two tables in our database named Account and Count. What we need is to compare username & password from account table so that user can login & then need to get his respective user_id from count table.

Account table contains id, username and password . Count table contains user_id and point.

Account: 
ID  |  username  |    password   |   email
--------------------------------------------                   
1   |  sample1   |    ***        |   abc@gmail.com         

Count:
user_id  |   friends   |   point
---------------------------------                
1        |    99       |    80

Now the problem is we need the above solution with our current code below .....

LOGIN -----------------
if($formAction == "login") {
    //VERIFY IF THE USERNAME ALREADY EXIST
    $SQL = "SELECT * FROM Accounts WHERE username = '" . $username . "'";

    //GIVE THE RESULT
    $result_id = @mysql_query($SQL) or die("DATABASE ERROR!");
    $total = mysql_num_rows($result_id);

    if($total) {
        $datas = @mysql_fetch_array($result_id);                
        if(!strcmp($password, $datas["password"])) {
            if($emailActivation == "true") {
                click on his activation link                        
                if($datas["activated"] == 1) {                          
                        echo "IMCONNECTED";
                    } 
                else {
                        echo "ACCOUNT_NOT_ACTIVATED";
                    }
                } 
                else if($emailActivation == "false") {                      
                    echo "IMCONNECTED";
                }
            } 
            else {  
                echo "WRONG_PASSWORD";
            }
        } 
        else {              
            echo "User not found!";
        }       
    } //END OF LOGIN ACTION

So could anyone help me out in getting the solution as how to get user_id from count table via passing a query in the above login php code.

The code on my post allow me to access all the details about the user after login such as email from the Accounts table, but what i want is to pass a query for 'count' table in such a way so that i can access information about that particular user id such a 'friends' or 'point' from count table.

Can anyone please help me out. I don't know much php so i need complete code.

Please consider using prepared statements. Your code is vunerable to SQL-Injection. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — eol, Dec 15 '15 at 11:14
Thanks for raising the issue but i know it and not a problem. I just looking for the solution . — Ranjan Roy, Dec 15 '15 at 11:25
@RanjanRoy That's not an answer, please kindly [edit] your question and add information. — Praveen Kumar Purushothaman, Dec 15 '15 at 11:37

score 0 · Answer 1 · answered Dec 15 '15 at 11:54

0

    if(!strcmp($password, $datas["password"])) {

        $res = mysql_query("SELECT * FROM `Count` WHERE `user_id`='".$datas['id']."';") or die(mysql_error());
        $row = mysql_fetch_assoc($res);

        # here you have it, do what you need
        $user_id = $row['user_id'];

answered Dec 15 '15 at 11:54

jmp

2,456
3
30
47

Where should i put your given code. please let me know. – Ranjan Roy Dec 16 '15 at 09:56
Please provide me complete code if possible. Thanks. – Ranjan Roy Dec 17 '15 at 12:06

Query to get respective user id from other table

1 Answers1