Possibility of example SQL script for CB Query

Paladin Brewer
OFFLINE
Posts: 43
Thanks: 2
Karma: 0

7 years 11 months ago #287760 by Paladin Brewer

Possibility of example SQL script for CB Query was created by Paladin Brewer

Hi guys,

So I don't know if this is asking too much, but I want to do an SQL query with my photo gallery (I use Simgallery) so the Profiles can show users albums, pictures, and videos.

The actual number is not in the database, I need a counter. The tables look like this:

Code:

<table name="upa1d_simgallery_media">
<column name="mid">2</column>
<column name="owner">993</column>
<column name="type">photo</column>
<column name="typeid">1</column>
</table>
<table name="upa1d_simgallery_media">
<column name="mid">3</column>
<column name="owner">993</column>
<column name="type">photo</column>
<column name="typeid">2</column>
</table>
<table name="upa1d_simgallery_media">
<column name="mid">4</column>
<column name="owner">993</column>
<column name="type">photo</column>
<column name="typeid">3</column>
</table>
<table name="upa1d_simgallery_media">
<column name="mid">5</column>
<column name="owner">993</column>
<column name="type">photo</column>
<column name="typeid">4</column>
</table>
<table name="upa1d_simgallery_media">
<column name="mid">6</column>
<column name="owner">993</column>
<column name="type">photo</column>
<column name="typeid">5</column>
</table>

So basically I need some sort of SQL query that:
1. Accesses the database
2. Looks only for the info of whoever's profile we're on
3. Searches that specific "owner" for the type=photo
4. Counts them, and displays that number.

That make sense? My SQL is horrible so, any possible help here would be super appreciated.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48454
Thanks: 8280
Karma: 1443

7 years 11 months ago #287772 by krileon

Replied by krileon on topic Possibility of example SQL script for CB Query

That's a simple count select query. Example as follows based of your criteria.

Code:

SELECT COUNT(*) FROM `#__simgallery_media` WHERE `owner` = '[user_id]' AND `type` = 'photo'

See the below for some SQL learning resources.

www.w3schools.com/sql/default.asp
dev.mysql.com/doc/refman/5.6/en/entering-queries.html

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

The following user(s) said Thank You: Paladin Brewer

Please Log in or Create an account to join the conversation.

Paladin Brewer
OFFLINE
Posts: 43
Thanks: 2
Karma: 0

7 years 11 months ago - 7 years 11 months ago #287774 by Paladin Brewer

Replied by Paladin Brewer on topic Possibility of example SQL script for CB Query

Thanks...so I copied/pasted that as a query. The field is published and it is Internal DB, etc, everything else is default...but the field doesn't even show up? Is there something else I need to do for query fields?

EDIT: I also tried adding a semicolon to the end to see if that was the problem but still no field is showing up.

Last edit: 7 years 11 months ago by Paladin Brewer.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48454
Thanks: 8280
Karma: 1443

7 years 11 months ago - 7 years 11 months ago #287778 by krileon

Replied by krileon on topic Possibility of example SQL script for CB Query

What fieldtype are you using exactly? It should strictly be Query and not a Query Select nor should you be using the Query Validation parameters on a text field for example. See the below videos on how to use CB Query Field.

www.youtube.com/playlist?list=PLp0puRITgC7MM9iP9FooKB1qk8f5MLWVp

Also ensure CB Query Field plugin is published within CB > Plugin Management.

Last edit: 7 years 11 months ago by krileon.

Please Log in or Create an account to join the conversation.

Paladin Brewer
OFFLINE
Posts: 43
Thanks: 2
Karma: 0

7 years 11 months ago #287779 by Paladin Brewer

Replied by Paladin Brewer on topic Possibility of example SQL script for CB Query

Oops, I didn't have the plugin published, embarrassing. Working now, much thanks!

Please Log in or Create an account to join the conversation.

Paladin Brewer
OFFLINE
Posts: 43
Thanks: 2
Karma: 0

7 years 11 months ago #287800 by Paladin Brewer

Replied by Paladin Brewer on topic Possibility of example SQL script for CB Query

If you can help me with 1 more query, I'd appreciate it. I want to do another query to show what usergroup someone is in. The problem is it doesn't directly show the person's usergroup, I have to refer to a few tables:

Code:

<table name="upa1d_user_usergroup_map">
<column name="user_id">993</column>
<column name="group_id">8</column>
</table>

Code:

<table name="upa1d_usergroups">
<column name="id">8</column>
<column name="parent_id">1</column>
<column name="lft">20</column>
<column name="rgt">21</column>
<column name="title">Super Users</column>
</table>

So user 993 (that's me) is in group_id 8, and group id 8's title is "Super Users" which is what I want to display. I've tried it, but I break it everytime

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon