Welcome to the MetaMod and Chameleon Support Forums.

Before posting, please check out the FAQs.

helpme

 

Need extra help with your Joomla site? Consider paid Joomla support by the developer of Chameleon and MetaMod.

 

Recipe for database query help please

Recipe for database query help please

I know very little about SQL so I am hoping someone here can help me with this recipe. I need to do a few things:

1. Check a user's login status
2. Check column named "publish" in the table named "vendor_node" to see if it is marked with a "1". *Some user won't have an entry at all so I wasn't sure if I could query only for the number 1.
3. Return Module A if the "publish" under vendor_node for the user is 1
4. Return Module B if the user has no vendor_node row or if the user is not logged into the site.

I found something similar for another component but I wasn't sure what to change:

Code:

$userid = (int)$user->id;

if ($userid > 0) {
  $query = "SELECT userid FROM #__tplancers_lancer WHERE userid = $userid";
  $db->setQuery( $query, 0, 1 );
  $found = $db->loadResult();
  if ($found) return XXX; /* replace XXX with the module ID you want to include */
  /* optional line: */
  else return YYY; /* replace YYY with module ID to be used if user is logged in
                              but NOT a freelancer */
}
/* optional line: */
else return ZZZ; /* replace ZZZ with module ID to be used if user is not logged in at all */

Could someone help me with this?

Thank you.
Moni

thegrindlab
Beginner Modder
ranks
useravatar
Offline
7 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Hi Moni,

you didn't mention whether the table "vendor_node" has a column for the user id, so I'm going to assume it does, and it's named "userid".

if ($user->id == 0) return BBB; // short circuit if not logged in
$query = "select publish from vendor_node where userid = " . (int)$user->id
  . " and publish = 1";
$db->setQuery( $query, 0, 1 );
$found = $db->loadResult();
if ($found) return AAA;
return BBB;
// Replace AAA with module id to use when user has published entry
// in the vendor_node table.
// Replace BBB with module id to use if user is not logged in, or
// user does not have published entry in vendor_node table.

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3328 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Thank you! This got me very close but I realized there is no cross-reference in this table to the joomla user's id. I found it on another table. This is what I found in vendor_node:

UID = store user
VENDID = user's vendor number


However, I had to go to table: members_node to find:

ID = member's joomla userid
UID = store user


Any advice on how I have metamod check those additional factors from the members_node table?

Thanks again.
-Moni

thegrindlab
Beginner Modder
ranks
useravatar
Offline
7 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

ok, this might work. I'm using the exact column names you sent.


if ($user->id == 0) return BBB; // short circuit if not logged in
$query = "SELECT v.publish FROM vendor_node v, members_node m "
  . " WHERE v.UID = m.UID "
  . " AND m.ID = " . (int)$user->id
  . " AND v.publish = 1";
$db->setQuery( $query, 0, 1 );
$found = $db->loadResult();
if ($found) return AAA;
return BBB;
// Replace AAA with module id to use when user has published entry
// in the vendor_node table.
// Replace BBB with module id to use if user is not logged in, or
// user does not have published entry in vendor_node table.


Cheers,
Stephen

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3328 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Thanks again, Stephen. It is not working quite yet but I'll look to see if I missed something in the database.

-Moni

thegrindlab
Beginner Modder
ranks
useravatar
Offline
7 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

My apologies. The code you provided actually works. I neglected to add my database prefix to everything. Thank you very much!

Lastly, are you the developer of Metamod? Just wondering. I don't need the pro version but I plan to register it next week if only to support the software.

Thanks again!
-Moni

thegrindlab
Beginner Modder
ranks
useravatar
Offline
7 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Hi Moni,

I'm glad this is working for you. Yes, I'm the developer.

Best regards,
Stephen

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3328 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Well, didn't need the pro version on Metamod (as the free version did everything I needed) but I just licensed Chameleon. Saved me A LOT of work! Amazing software! Reviewing both on the Joomla Extensions Directory now.

Much appreciation!
-Moni

thegrindlab
Beginner Modder
ranks
useravatar
Offline
7 Posts
User info in posts
Administrator has disabled public posting

Re: Recipe for database query help please

Glad it helped - thanks in advance for the reviews, they're always much appreciated.

Cheers,
Stephen

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3328 Posts
User info in posts
Administrator has disabled public posting

Board Info

Board Stats:
 
Total Topics:
1679
Total Polls:
6
Total Posts:
5933
Posts this week:
3
User Info:
 
Total Users:
4386
Newest User:
verlywl7
Members Online:
1
Guests Online:
538

Online: 
verlywl7

Forum Legend:

 Topic
 New
 Locked
 Sticky
 Active
 New/Active
 New/Locked
 New Sticky
 Locked/Active
 Active/Sticky
 Sticky/Locked
 Sticky/Active/Locked