0

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

  • Ok so again I'm working on it and identified kind of how to do it but I must have some issues with syntax maybe. New code listed above. – Brandon Apr 30 '16 at 0:54
  • You need a JOIN I think. You need data from multiple tables combined together? – David Hayes Apr 30 '16 at 1:05
  • No - I think the issue is that I need to find the variable for customer_id - in the other example I had, this was something like ".$item->id." - but I cant seem to find the equivalent for customer_id. – Brandon Apr 30 '16 at 1:08
  • New new code looks a lot closer. Is it throwing an error? – David Hayes Apr 30 '16 at 14:55
  • Yea - i think that the tables are wrong somehow. i'll edit the original post to show the tables format a little better somehow. – Brandon Apr 30 '16 at 18:53
3

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

  • This is what I got when trying that: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM #__user_profiles INNER JOIN #__rsticketspro_tickets AS b ON (Ar' at line 2 SQL=SELECT FROM #__user_profiles INNER JOIN #__rsticketspro_tickets AS b ON (Array = 'bcustomer_id') WHERE Array = 'baddress1' – Brandon Apr 30 '16 at 5:27
  • I don't have your table names and column names so I can't fix that issue. All I posted was to show you the correct format for the structure, use the links provided with your specific data to implement it for your use case. There just isn't enough information in what you provided for me to provide fully functioning code. (plus you should read the references I provided so you know what to do the next time) – Walt Sorensen Apr 30 '16 at 5:32
  • Also Double check what you copied, there was a quote hanging out at the end of the ->where() statement. A misplaced quote, or a missing or extra bracket can break a SQL query. – Walt Sorensen Apr 30 '16 at 5:36
  • 1
    Ya I saw the extra quote and that's when I got the syntax error message. I'll play with it more in a little while and read the material too! :) – Brandon Apr 30 '16 at 5:39
  • Just noticed there was some wrong and missing tick marks. I have corrected that now. – Walt Sorensen Apr 30 '16 at 15:06
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.