Page 1 of 1

Query to get respective user id from other table

Posted: Fri Dec 18, 2015 4:09 am
by ranjanroy130586
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 -----------------

Code: Select all

    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.

Re: Query to get respective user id from other table

Posted: Fri Dec 18, 2015 6:35 am
by Celauran

Code: Select all

INNER JOIN count ON count.user_id = users.id

Re: Query to get respective user id from other table

Posted: Fri Dec 18, 2015 6:59 am
by ranjanroy130586
Celauran wrote:

Code: Select all

INNER JOIN count ON count.user_id = users.id
[text]Can you please provide the complete code so i can have the solution. Thanks.[/text]

Re: Query to get respective user id from other table

Posted: Fri Dec 18, 2015 5:49 pm
by Christopher
ranjanroy130586 wrote:Can you please provide the complete code so i can have the solution. Thanks.
Check the documentation for FROM in your SQL documentation to see how to add a JOIN.