Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

How to get the id that came BEFORE the provided id

Hi,
I have some code that supplies an $id and an $account_id for a record in a table

I need to be able to query using those what the PREVIOUS id is that is associated with that account_id.

So in other words, what was the last id for a record that was created before the one that is provided under that account _id.

Its not simply one less than the current id (LIMIT 1, 1) because there may be others in between that are associated to other accounts.

Make any sense?
0
tjyoung
Asked:
tjyoung
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in short:
select max(id) from yourtable where account_id = xxx and id < yyy
0
 
Ray PaseurCommented:
It might make more sense if we could see the CREATE TABLE statements.  I think angelIII has a right answer, but without seeing the important details it's not clear what columns are AUTO_INCREMENT, etc.  Please see http://sscce.org

My guess might be:

SELECT id, account_id FROM myTable WHERE account_id='$id' ORDER BY id DESC LIMIT 2

Then use MySQL_Data_Seek($resource,1)
0
 
tjyoungAuthor Commented:
Hi, giving it a try now. The id is auto increment so I'll check that solution. Seems to make sense. max($id) must grab the highest $id available that is less than the supplied.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tjyoungAuthor Commented:
HI, this is the query I'm using. It works but it returns the lowest available id for some reason instead of the next highest below the current id:

$result = mysql_query("SELECT id, MAX(id) FROM contests WHERE account_id = '$account_id' AND id < '$contest_id' ORDER BY id DESC LIMIT 1");

Screenshot of table is attached...

I'm passing $contest_id = 317 and get back 314 (any number I pass gets back 314)
screen.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please remove ORDER BY id DESC LIMIT 1 from the subquery ....
as you use MAX() the LIMIT will "kill" that part.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in clear:
$result = mysql_query("SELECT c.* FROM contests c
WHERE c.account_id = '$account_id' AND id = ( select max(t.id) from contests t where t.account_id = '$account_id' and t.id < '$contest_id' ) "); 

Open in new window

0
 
tjyoungAuthor Commented:
I don't really understand it yet.. but works great.
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now