Member Avatar for haribo83

I am trying to create a set of league table in MySQL and PHP.

I can get the tables to show the top and bottom 5 values but the problem is when I have less than ten records in the system.

When this occurs the same people can appear in the top and bottom league tables.

Is there a way around this?

Thanks

  • 4 Contributors
  • 5 Replies
  • 3K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by drjohn
Member Avatar for haribo83

Is it possible to only run a sql query if there are over 10 records in the database?

Member Avatar for pritaeas

Not sure if you can do it in a single query. You could run a count query beforehand, or write a stored procedure which can do the check for you and return either result set.

Member Avatar for tyson.crouch

If you use the appropriate ORDER BY ... DESC/ASC you can get the top 5 and bottom 5 from the column you wish.
ALSO, mysql does not limit your query usage to having a minimum number of rows in a table

GETS TOP 5:

SELECT * FROM tableA
ORDER BY id DESC
LIMIT 0, 5

GETS BOTTOM 5

SELECT * FROM tableA
ORDER BY id ASC
LIMIT 0, 5
Edited by tyson.crouch because: n/a
Member Avatar for pritaeas

Of course, now I see. You can join these two with a UNION and use select distinct

SELECT DISTINCT * FROM
(
  SELECT * FROM tableA
  ORDER BY id ASC
  LIMIT 0, 5
UNION
  SELECT * FROM tableA
  ORDER BY id DESC
  LIMIT 0, 5
) dummy
ORDER BY id ASC
Member Avatar for drjohn

Run a query to count the number of records.

Then use an IF statement in your programming language (PHP?) to use one of two queries - one your original top five / bottom five union, the other is just a select with no limit.

This is not a database problem, it is a logic problem.

Edited by drjohn because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.