PHP Script to display info from another table for UserID

[rpacdn]

Update: Can someone please take a look at this issue and provide a solution. Let me know if you need additional information.
===========
A simple question for a PHP/MySQL expert!

I am using a third party newsletter module/plugin for Site Registration, The plugin integrates the User registration to the Joomla! User registration table as well. Thus some of the User information such as the UserID, email etc will be stored in the Joomla! User table, and the rest of the information such as their address, lastname, firstname, phone number etc will be stored in the table that belong to the Mail plugin.

Can someone help me retrieve the additional information saved in the third party tables for the UserID of the Visitor who is logged in to site?

I have tried the following code. Everything upto the echo command for the Language works, because I am getting that information from the Joomla! Users table.

Code: Select all

$db = JFactory::getDbo();
$user = JFactory::getUser();
$language = $user->getParam('language', 'the default');
echo "<p>Your name is {$user->name}, your email is {$user->email}, and your username is {$user->username}, and your userID is {$user->id}</p>";
echo "<p>Your language is set to {$language}.</p>";

Now I need to do the following:

• 1. retrieve the submission id from the "#__thirdpartysubmissions" table for the logged in user
  2. then retrieve that User's information (lastname, firstname, phonenumber) from the "#__thirdparty_submission_values" table based on the submission id from step 1.

The following code was suggested as a starting point, but I am not too familiar with PHP/MySQL to put it all together.

Code: Select all

$db = JFactory::getDbo();
$db->setQuery("SELECT `column_name` FROM `#__table_name` WHERE `column_name`='value' LIMIT 1");
return $db->loadResult();

Can someone please help!

By the way, do I always need the #__ infront of the table name, when I am using them in a query string?

Thank you.

[helln]

Hey rpacdn!

MySQL Joins is the keyword, for your first question:
http://dev.mysql.com/doc/refman/5.7/en/join.html
Example:

Code: Select all

SELECT `#__thirdpartysubmissions`.`id`, `#__thirdparty_submission_values`.*
FROM `#__thirdpartysubmissions`
RIGHT JOIN `#__thirdparty_submission_values` ON `#__thirdpartysubmissions`.`id` = `#__thirdparty_submission_values`.`submission_id`

By the way, do I always need the #__ infront of the table name, when I am using them in a query string?

The "#__" part of the tables is used as a placeholder, for the table prefix like "jos3_users". That way your code will always be executable, in any Joomla installation. (you can set/change the table prefix, while installing or later in the config file)

Regards
Nik