Add values from a table

Asked

Viewed 57 times

0

I have the following query:

$qrysel = "select * from won_auctions w left join registration r on w.userid=r.id where payment_date!='0000-00-00 00:00:00'";

This query will show a list of users of the table won_auctions and take the user name in the table Registration.

The idea is that it checks how many records each user has in the table won_auctions during the current month, and the total.

The date can be checked by the column payment_date, and the user by the column userid, table won_auctions

In the case: 

$total_arrematados = xxx;
$total_este_mes = xxx;

I created a table and thought about using while to show the results, only I need it to show only one line per user, not multiple lines for the same user.

I also need to sort to display those that have more records for the first, for the current month.

Structure won_auctions:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auction_id` int(11) NOT NULL DEFAULT '0',
  `accept_denied` varchar(100) NOT NULL DEFAULT '',
  `userid` int(11) NOT NULL DEFAULT '0',
  `won_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `accept_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `payment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `invoice_flag` enum('0','1') NOT NULL DEFAULT '0',
  `situacaodescr` varchar(50) NOT NULL,
  `dataenvio` datetime NOT NULL,
  `rastreamento` varchar(100) NOT NULL DEFAULT '',
  `lances_creditados` enum('SIM','NAO','REMOVIDO','') NOT NULL DEFAULT '',
  `transacao_ps` varchar(35) NOT NULL DEFAULT '',
  `cancelado` enum('SIM','NAO') NOT NULL DEFAULT 'NAO',
  `cancelado_motivo` varchar(1000) NOT NULL DEFAULT '',

Structure Registration:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL DEFAULT '',
  `firstname` varchar(100) NOT NULL DEFAULT '',
  `lastname` varchar(100) NOT NULL DEFAULT '',
php mysql sql

  • and where is the structure of the tables? Which fields are needed ? use * with aggregation functions will get complicated. If possible, access Sqlfiddle and assemble the database with some information to make it easier for those who try to help you

  • Sorry for the lack of information, I thought it was not necessary. I updated the question.

  • @Victor In fact he will add yes, will take all the values and add to tell me how many records there are in total and for the current month.

  • solved what I needed ?

  • It worked out thank you :D

1 answer

1


Following your logic, to count how many won_actions user had in the current month:

SELECT
 r.id,
 r.username,
 count(w.id) as total
FROM won_auctions w
INNER JOIN registration r on r.id = w.userid
where month(w.payment_date) = month(now()) and year(w.payment_date) = year(now())
group by r.id, r.username;

To count how many won_actions user had in total:

SELECT
 r.id,
 r.username,
 count(w.id) as total
FROM won_auctions w
INNER JOIN registration r on r.id = w.userid
group by r.id, r.username;

However, I would do using a sub-select, since it needs two columns that count different things, and select part of the user table:

select
r.id,
r.username,
(select count(w.id) from won_auctions w where w.userid=r.id and month(w.payment_date) = month(now()) and year(w.payment_date) = year(now())) as total_mes,
(select count(w.id) from won_auctions w where w.userid=r.id) as total
from registration r

I put in Sqlfiddle: http://sqlfiddle.com/#! 9/263d8/5 (No data, just validating the syntax.)

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.