Simple PHP / MySQL query

[johnsmith153]

I have a table like this:

 

primary-id / user-id / payment / charge

 

The 'payment' and 'charge' fields are decimal (5,2) datatypes and refer to a payment into the account and charge on the account.

 

So, for user 1, these records may exist:

 

1 / 1 / 20.00 /

93 / 1 / 10.00 /

159 / 1 / / 5.00

 

… so their account balance would be $25

 

Imagine the user's table as simply being Name / id / is_active

 

Dave / 1 / 1 (example above)

Jim / 2 / 0 (an inactive user)

 

I need to return any user where their account balance is more than $2 in the red.

 

I imagine it would be something like this:

 

SELECT user-details, actual-a/c-balance WHERE SUM(etc.)<-2

 

…but could do with a bit of help. Thanks.

[kickstart]

Hi

 

Try something like this

 

SELECT users.id, users.username, SUM(charge) - SUM(payment) AS CusBal
FROM users
INNER JOIN paycharge ON users.id = paycharge.userid
GROUP BY users.id, users.username
HAVING CusBal > 2

 

All the best

 

Keith

[johnsmith153]

Thanks for this, except I forgot one important thing (sorry):

 

The two tables are actually in different databases. They are on the same db server, but use a different user id / password to access them. I have all the usernames/passwords. Is this possible?

 

If not, what do I do as moving them isn't possible? Would I need to dump the entire accounts table and do a sort in PHP?

[fenway]

Why not create a single mysql account that has access to both?

[Muddy_Funster]

.... Would I need to dump the entire accounts table and do a sort in PHP?

 

Surely dumping the users table would be much quicker and easier to work with?

But, yeah, as fenway said - can't you just make a login with read access to both databases?  It's definately the option that makes most sense.

[johnsmith153]

Can't create a single login due to limitations of hosting provider. Each db must have a separate user login.

 

In the end the obvious thing is to use kickstart's query (but for the accounts table only) and as not too many records would be returned (I'm only looking to return accounts in the red), I can then run another query (on the 'users' db) to get the user name and any other info using whatever id's are returned from the search of the accounts db. A lot of the time there won't be anything returned at all from the 'accounts' db anyway, so no further query needed.

 

I've done this and it works great.

 

Thanks for all the help.

[kickstart]

Hi

 

I have never done this before, but it does seem it might be possible:-

 

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

 

All the best

 

Keith

[Muddy_Funster]

Hi

 

I have never done this before, but it does seem it might be possible:-

 

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

 

All the best

 

Keith

 

Nice find! I Look forward to the day when it supports MS-SQL connectins 

[fenway]

Federated is super-buggy -- but I guess for read-only it might be ok.