3 Replies - 592 Views - Last Post: 02 July 2015 - 09:55 AM

#1 Relaxed_Bear   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 03-January 14

Retrieve data from parent table ( not the ID )?

Posted 02 July 2015 - 07:38 AM

Hi everyone,

I have a database ( mySQL ) with 2 tables: user_main and language. 'user_main' has a column called 'Lang_ID'. This has been connected with 'Lang_ID' from the other table. It basically looks like this:

Posted Image

The user can login into a webpage. On his profile page, his name and language should be displayed using PHP. I've been able to do this with the following code:

session_start();
				
$_SESSION['User_Name'] = $Show['User_Name']; // User 1
$_SESSION['User_Fam'] = $Show['User_Fam']; // Fam 1
$_SESSION['User_Lang'] = $Show['Lang_ID']; // 1 ( English )

// User information stored in session variables. 



And to display it the profile page:

<?php
      echo "<p>" .$_SESSION['User_Name']. "</p>"; 
?>

<?php
      echo "<p>" .$_SESSION['User_Lang']. "</p>"; 
?>



Result:

User 1
1 ( not English )

My question: how can I display languages from 'language' instead of Lang_ID of 'user_main'?

I've been trying countless like $_Show['Languages'] without succes. I've been searching on the internet but didn't succeed in finding an answer to my needs. All help is appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Retrieve data from parent table ( not the ID )?

#2 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Retrieve data from parent table ( not the ID )?

Posted 02 July 2015 - 07:42 AM

what does the query look like your using to pull the data. All you should have to do is change that to add a join condition to the language table.

like so:

SELECT <Select list>
FROM <Table1>
JOIN <Table2> 
    ON <Table1>.<Join Column> = <Table2>.<Join Column>



You would then add to your $Show array something for the language description.

Also, it looks like $Show might translate 1 to 1 with your database columns. You said you've tried $_Show['Languages'] but have you attempted $Show['Language']

This post has been edited by rgfirefly24: 02 July 2015 - 07:44 AM

Was This Post Helpful? 1
  • +
  • -

#3 Relaxed_Bear   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 03-January 14

Re: Retrieve data from parent table ( not the ID )?

Posted 02 July 2015 - 08:25 AM

View Postrgfirefly24, on 02 July 2015 - 07:42 AM, said:

what does the query look like your using to pull the data. All you should have to do is change that to add a join condition to the language table.

like so:

SELECT <Select list>
FROM <Table1>
JOIN <Table2> 
    ON <Table1>.<Join Column> = <Table2>.<Join Column>



You would then add to your $Show array something for the language description.

Also, it looks like $Show might translate 1 to 1 with your database columns. You said you've tried $_Show['Languages'] but have you attempted $Show['Language']


I'll start this comment by saying thank you :bigsmile:. I didn't knew much about joins. After reading about it, I've solved it.



I began with this query ( fetched with $Show ):

$Get = mysql_query("SELECT * FROM user_main WHERE User_Nick = '".$User_Nick."' AND User_Pass = '".$User_Pass."'");



This is the query I've used to check if the user may login or not. 'language' wasn't included in this table. I've added a second query using join:

$Lang = mysql_query("SELECT * FROM user_main INNER JOIN language ON user_main.Lang_ID=language.Lang_Id");



Now both tables are targeted. I've fetched this query and I displayed it with $Show_2['language'].

Full code:

$Show = mysql_fetch_assoc($Get);
$Show_2 = mysql_fetch_assoc($Color);
				
session_start();
				
$_SESSION['User_Name'] = $Show['User_Name'];
$_SESSION['User_Fam'] = $Show['User_Fam'];
$_SESSION['User_Lang'] = $Show_2['Language'];



Again, thank you ^^/>.
Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Retrieve data from parent table ( not the ID )?

Posted 02 July 2015 - 09:55 AM

No problem, glad you got it working. A couple points in the code you just posted that will help you out in the long run. Don't concatenate your SQL Statements. Use Prepared Statements. If you concat your statements like that it would be very easy for someone to inject something malicious into your code like so:

SELECT * FROM user_main WHERE User_Nick = '".$User_Nick."' AND User_Pass = '".$User_Pass."'"

//If I entered the following string in the UserName field:  1'; DROP TABLE user_main; -- . Then I would effectively destroy your entire system as MySQL will interpret your query as multiple commands like so:

SELECT * FROM user_mail WHERE User_Nick = '1'; DROP TABLE user_mail; -- AND User_Pass = '<inserted password>'




With prepared statements you would not have to worry about the SQL Injection. Also, Don't use SELECT *. Explicitly state what columns you need. That way it cuts down on unneeded columns being returned.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1