get data from 2 tables!

[donnan]

Hi guys,

I am trying to retrieve the user-id based a users username. Community Builder doesn't have a username column in the #_comprofiler table. Then I wish to retrieve the members paypal info from the cb_paypal field in #_comprofiler

Once I have the user-id I can then use the information in the #_comprofiler table.

Here is what I have tried.

Code: Select all

$sql = "SELECT jospk_users.username, jospk_users.id, jospk_comprofiler.user_id, jospk_comprofiler.cb_paypal FROM `jospk_users`,`jospk_comprofiler` WHERE jospk_users.username = $g AND jospk_users.id = jospk_comprofiler.user_id";

$result = $conn->query($sql);

	if ($result->num_rows > 0) {
    	// output data of each row
    		while($row = $result->fetch_assoc()) {

echo "<br>Id = ".$row[id]." Username = ".$row[username]." Paypal is:".$row[cb_paypal];

		} //end while

[SharkyKZ]

Join on user ID:

Code: Select all

$username = 'YourUsername';

$db = JFactory::getDbo();
$query = $db->getQuery(true)
	->select(array('u.id', 'u.username', 'c.cb_paypal'))
	->from($db->quoteName('#__users', 'u'))
	->join('INNER', $db->quoteName('#__comprofiler', 'c') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('c.user_id') . ')')
	->where($db->quoteName('u.username') . ' = ' . $db->quote($username));
$db->setQuery($query);
$results = $db->loadObjectList();

foreach ($results as $result)
{
	echo '<br>Id = '. $result->id . ' Username = ' . $result->username . ' Paypal is: ' . $result->cb_paypal;
}

[donnan]

Hi SharkyKz,

Thanks so much for your reply. Unfortunately, I don't get any return from the code. Just a blank page.

I changed your code to try direct access to the tables with this, but it also leaves a blank page.

Code: Select all

	$db = JFactory::getDbo();
	$query = $db->getQuery(true)
   	->select(array('u.id', 'u.username', 'c.cb_paypal'))
   	->from($db->quoteName('jospk_users', 'u'))
   	->join('INNER', $db->quoteName('jospk_comprofiler', 'c') . ' ON (' . $db->quoteName('u.id') . ' = ' . $db->quoteName('c.user_id') . ')')
   	->where($db->quoteName('u.username') . ' = ' . $db->quote('Marbiz'));
	$db->setQuery($query);
	$results = $db->loadObjectList();

	foreach ($results as $result)
		{
   		echo '<br>Id = '. $result->id . ' Username = ' .$result->username . ' Paypal is: ' .$result->cb_paypal;
		}

Any ideas?

[donnan]

Thanks again SharkyKZ, you are the best!

I was able to solve the problem using the code I posted in my last reply plus I added this code to the top of the php page and it worked like a dream.

Code: Select all

if (!defined('_JEXEC'))
{
    // Initialize Joomla framework
    define('_JEXEC', 1);
}

// Load system defines
if (file_exists(dirname(__FILE__) . '/defines.php'))
{
    require_once (dirname(__FILE__) . '/defines.php');
}
if (!defined('JPATH_BASE'))
{
    define('JPATH_BASE', dirname(__FILE__));
}
if (!defined('_JDEFINES'))
{
    require_once JPATH_BASE . '/includes/defines.php';
}

// Get the framework.
require_once (JPATH_BASE . '/includes/framework.php');

[toivo]

A bit of error checking would be useful to include. Otherwise, if something goes wrong, users see only a blank page. For example:

Code: Select all

	try
	{
		$results = $db->loadObjectList();
	}
	catch (RuntimeException $e)
	{
		$this->setError(JText::sprintf('COM_USERS_DATABASE_ERROR', $e->getMessage()), 500);
		return false;
	}

[donnan]

thanks toivo, very handy little add on in deed.