Domain status sql query

[chrismfz]

Hello, can someone with better SQL knowledge than me help me ?

 

We can recall .gr domains as registrars and we have a "grace" period

of 5 days. So we are automatically register for the client the .gr domain

he wants and we recalling it with an Addon Module we created.

 

So we can just enter the domain name (in a textbox) and click the recall

button. Domain is recalled and we have 5 more days till the client pays and

so on...

 

Now, because after of a number of orders we get confused, I want to do

a SQL query in the same Addon Module to show me which domains

have Status = Unpaid in the invoices. So I will get the list only

with domains which we didnt get paid yet.

 

First I tried this one when I was playing around:

$query = "SELECT domain,status from tbldomains WHERE registrar = 'eppgr';";

which gives me ALL .gr domains... But I need only them which is unpaid.

 

So I also tried this:

 

$query = "SELECT tbldomains.domain
from tbldomains
INNER JOIN tblinvoices
ON tbldomains.userid=tblinvoices.userid
WHERE tbldomains.registrar = 'eppgr' AND tblinvoices.status='Unpaid';";

and this:

 

$query = "select tbldomains.domain from tbldomains INNER JOIN tblinvoices ON
tbldomains.userid=tblinvoices.userid WHERE tbldomains.registrar = 'eppgr' AND tblinvoices.status='Unpaid'";

But I get again ALL domains back not unpaid...

What's wrong ? What I did wrong / mistake ?

 

I only need the domains which in their invoice are marked as unpaid... So we can keep recalling

them until we mark their invoiced paid...

 

Anyone ?

[chrismfz]

Anyone can help ? Still didnt find any solution

to see only the unpaid domains...

[othellotech]

you need something like ...

select * from tblinvoices where status="Unpaid" and id in (select invoiceid from tblinvoiceitems where type="Domain" and description like "%{$domain}%";

[chrismfz]

SELECT  * 
FROM tblinvoices
WHERE  STATUS  =  "Unpaid"
AND id
IN (
SELECT invoiceid
FROM tblinvoiceitems
WHERE  TYPE  =  "Domain"
AND description LIKE  "%gr%"
)

 

Thanks a lot! It work about ...50%

 

I get the Ids of the invoices which any *gr domain is Unpaid.

 

But how can I get the domain name too ?

 

I tried similar sql code and editing this one but I got either errors or no output at all