How do I get last 7 bet slips (BetSlipID) for the certain user

Question

my current code shows bet slips for last 7 days, but I need to show last 7 bet slips regardless of the date.

select 
  betsliphistory.MatchID,Team1.Name as HomeTeam ,
  Team2.Name as AwayTeam, BetSlipID , userid , TipID , 
  matches.ResultTipID ,betsliphistory.`Date`
from betsliphistory
inner join matches on betsliphistory.MatchID = matches.MatchID
inner join teams as Team1 on matches.HomeTeamID = Team1.TeamID
inner join teams as Team2 on matches.AwayTeamID = Team2.TeamID
where userid =".$user." 
  and betsliphistory.`Date` between CURDATE()-7 and CURDATE() 
order by BetSlipID , MatchID;

"last 7 bet slips regardless of the date." - if not by date, then what determines the last? — Ja͢ck, Nov 29 '12 at 12:21
The last will be the one with the highest betslipID. I solved the problem with Kant's help. Thanks anyway — Boza22, Nov 29 '12 at 12:32
I'm only asking because the other answer should provide the correct results while avoiding the sub query. In these cases, a sqlfiddle would really help to reduce confusion. — Ja͢ck, Nov 29 '12 at 12:36

score 2 · Answer 1 · edited May 23 '17 at 11:48

but I need to show last 7 bet slips regardless of the date.

Remove the date consition from the WHERE clause.

Use ORDER By BetSlipID DESC with LIMIT 7 for the top 7 BetSlipID, like so:

 ... -- Your current query
 WHERE userid = ".$user." 
 ORDER BetSlipID  DESC
 LIMIT 7;

Note that: Your code this way is vulnerable to SQL Injection. Use PDO or prepared statement instead. See this for more details:

PHP: Best way to prevent SQL injection?.

score 0 · Accepted Answer · edited Nov 29 '12 at 12:14

0

Try this::

 select betsliphistory.MatchID,Team1.Name as HomeTeam , 
                      Team2.Name as AwayTeam, BetSlipID , userid , TipID , matches.ResultTipID ,betsliphistory.`Date`
                      from betsliphistory
                      inner join matches on betsliphistory.MatchID = matches.MatchID
                      inner join teams as Team1 on matches.HomeTeamID = Team1.TeamID
                      inner join teams as Team2 on matches.AwayTeamID = Team2.TeamID
                      inner join (Select DISTINCT(BetSlipID) tempBetSlip from betsliphistory where userid =".$user."  order by `Date` limit 7) temp_betHistory on temp_betHistory.tempBetSlip = BetSlipID
                          where userid =".$user." order by `BetSlipID`

edited Nov 29 '12 at 12:14

Boza22

15
3

answered Nov 29 '12 at 09:32

Sashi Kant

13,277
9
44
71

I tried, but it gives me only the last 7 records ,I need all the records for the last 7 betslips.My English is not very good. Sorry about that – Boza22 Nov 29 '12 at 09:40
Query returns exactly what I need when I run it in the test database, but when I run it in database used by app, there is empty query. Tables in both databases are same. Thanks for help, i will check what is the problem – Boza22 Nov 29 '12 at 10:57

How do I get last 7 bet slips (BetSlipID) for the certain user

2 Answers2