close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Custom SQL Query

Thread began 6/29/2022 5:08 am by s.joiner74419001 | Last modified 7/01/2022 9:25 am by Ray Borduin | 47 views | 5 replies |

s.joiner74419001

Custom SQL Query

Hi Ray.

I need help with an SQL query if you can.

So in our admin system I have an html table that shows a list of accounts from a database table ( Accounts Table ) . I’ve removed name, email and other columns that aren’t necessary for this.
Each of these accounts has a members list that is stored in another table ( Members table ). These tables have a relational value which is the Account_id primary key in the Accounts table and the Account_ID foreign key in the members table.

The members table has a ‘Fee’ column which can have a value of Paid or Unpaid.

So this is what I want to do…

When ALL values of the Fee column for a related account has a value of paid, I want to highlight the row, in the html table showing the accounts list, in a different colour.
An example using the sample tables would be account_id 3

I’m assuming I need to use COUNT in the query. I’m just not sure how to build this and then reference it to highlight each row In the html table.

I hope this makes sense.

Thanks in advance.

Sign in to reply to this post

Ray BorduinWebAssist

I think something like:

SELECT Accounts.*, Count(Members.Account_ID) AS unpaid FROM Accounts LEFT OUTER JOIN Members ON Members.Account_ID = Accounts.Account_ID AND Fee <> 'paid' GRUOP BY Accounts.Account_ID

Then you can use an IF statement to check if the value of "unpaid" in the recordset is equal to zero or not to change the color of the column.

Sign in to reply to this post
Did this help? Tips are appreciated...

s.joiner74419001

OK so this is creating a new recordset using COUNT function and GROUP BY statement.

But the problem is the html table showing the account list is using another recordset and a repeat region, so I think I’m right in thinking that binding data from the new recordset wont work.

How can I marry this together.

I’ve attached the page. SO the recordset that populates the list is rsActiveApps. The recordset that I have replicated your suggestion is rsAllPaid.

The table where I need to highlight the rows is under <h1 class="page-head">Admin - Live Applications</h1> line 383

Attached Files
live-applications.php
Sign in to reply to this post

Ray BorduinWebAssist

You could either integrate the COUNT and GROUP BY statements into your existing recordsets, or you could add a new recordset inside of the repeat region and get the count for each row separately by adding an additional WHERE clause to only get the data for that row.

Sign in to reply to this post
Did this help? Tips are appreciated...

s.joiner74419001

Having problems with this Ray..

I've tried numerous manual builds of the query but every time get SQL syntax errors.

So I then used the WA query builder and even that gives a syntax error.

the attached screen shot shows 2 queries ( recordsets ) rsActiveApps which is the original setup for the list. And rsActive which is the new one built from the query builder that doesn't work.

Also attached is page with these on.

Where am i going wrong?

Thanks in advance!

Attached Files
live-applications.php
Sign in to reply to this post

Ray BorduinWebAssist

I'll need to debug this in a premier support ticket. I'm not that familiar with your database structure to determine what might be wrong with your sql. I'd try copy and pasting the sql statement into phpMyAdmin and see if it gives you a better error you can use to find the problem.

Sign in to reply to this post
Did this help? Tips are appreciated...

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...