Adding values from a table

Question

Adding values from a table

Navigation

#1 by (1 votes)

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 won_auctions table and get the user name in the registration table.

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

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

In case:

$total_arrematados = xxx;
$total_este_mes = xxx;

I created a table and thought about using the while to show the results, but I need it to show only one row per user, not several rows for the same user.

I also need to sort to display the ones with the most records first, for the current month.

won_auctions structure:

  '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 '',

registration structure:

  '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

asked by anonymous 27.11.2017 / 12:22

1 answer

Is there a way to connect a cordova app directly to Mysql? Use findViewById in all scopes or use a variable?

score 1 · Accepted Answer

Following your logic, to count how many won_actions the 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 the 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 it using a sub-select, since it needs two columns that count different things, and the 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 it in SQLFiddle: link (No data, just validating the syntax.)