Display Entry when referencing 2 Tables

Question

Ok this is building on what I was previously working on in my last post:

<?php
$db = JFactory::getDBO();
$db->setQuery("SELECT `profile_value` FROM #__user_profiles WHERE `user_id`='customer_id' AND `profile_key`='profile.address1'");
$result = $db->loadResult();
echo $db->loadResult();
?>

So in this situation, I have to reference customer_id from a different table than the rest of this. How would I go about pointing just that one field that is located in this other table (rsticketspro_tickets)

Or am I going about this in the completely wrong way? I'm trying to place this code to pull an address from a users profile on a "View Ticket" screen. So the customer_id is what is referenced on that page I believe, so maybe its already a variable?

NEW CODE

<?php
$db = JFactory::getDBO();
$db->setQuery("SELECT `profile_value` FROM #__user_profiles WHERE `user_id`='rsticketspro_tickets.customer_id' AND `profile_key`='profile.address1'");
$result = $db->loadResult();
echo $db->loadResult();
?>

NEW NEW CODE

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('a.profile_value'))
  ->from($db->qn('#__user_profiles'), 'a')
  ->join('INNER', $db->quoteName('#__rsticketspro_tickets', 'b') . ' ON (' . $db->qn('a.user_id') . " = " . $db->q(b.customer_id) . ')')
->where($db->qn('a.profile_key') . " = " . $db->q('b.address1'));
$db->setQuery($query);
$result = $db->loadResult();

This might help, but I cant seem to see whats wrong in everything at the moment still.

TABLE: #__user_profiles

COLUMNS: user_id, profile_key, profile_value

PROFILE_KEY VALUE: profile.address1

TABLE: #__rsticketspro_tickets

COLUMNS: customer_id

Answer 1

You should review how to use JDatabase for queries, what you wrote is basically a RAW SQL statement and left off the join for the second table. Here is the correct structure with chaining (it should work if your table columns and variables are named correctly). In either case, see the references for example on using JDatabase.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('a.profile_value'))
  ->from($db->qn('#__user_profiles'), 'a')
  ->join('INNER', $db->quoteName('#__rsticketspro_tickets', 'b') . ' ON (' . $db->qn('a.user_id') . " = " . $db->q(b.customer_id) . ')')
  ->where($db->qn('a.user_id') . " = " . $db->q($customer_id));
$db->setQuery($query);
$result = $db->loadResult();

Assuming you already have the customer ID the join is totally unnecessary unless you need data from the second table.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('a.profile_value'))
  ->from($db->qn('#__user_profiles'), 'a')
  ->where($db->qn('a.user_id') . " = " . $db->q($item->customer_id));
$db->setQuery($query);
$result = $db->loadResult();

additional code needed to extract the address1 data from the $result

• See Selecting data using JDatabase
• See Inserting, Updating and Removing data using JDatabase
• See Joomla coding standards (which includes a small part on best practices for JDatabase queries in the PHP section)

Answer 2

OMG I think I might have something that works!!! Its kind of a combination of all of the stuff you guys helped me with:

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->qn('profile_value'))
      ->from($db->qn('#__user_profiles'))
      ->where($db->qn('user_id') . " = " . $db->q($user->id))
      ->where($db->qn('profile_key') . " = " . $db->q('profile.address1'));
$db->setQuery($query);

$result = $db->loadResult();
echo $db->loadResult();

Only problem is I'm putting this into the field with the user profiles, so ANY person that replies is going to get the address showing up instead of just the original customer_id which is what we really want, but all I was able to find in the other code was the $user->id reference. Also, it seems to be putting my result in quotes for some reason when displaying.