Solved

An MySQL order problem...

Posted on 2004-10-19
10
200 Views
Last Modified: 2008-02-01
Hi,

I have a table like below:

----------------------
| shipment-1 |  9 |
| shipment-2 | 12 |
| shipment-3 | 28 |
| shipment-4 | 14 |
| shipment-5 | 10 |
| shipment-6 | 15 |
| shipment-7 | 21 |
| shipment-8 | 17 |
----------------------

and I want to select 5 row but my real selected row must be in the middle (must be third) and the 2 below and 2 up rows must be numerical close to the middle row... Like in the example below:

--------------------------
| 2 | shipment-7 | 21 |
| 3 | shipment-8 | 17 |
| 4 | shipment-6 | 15 |
| 5 | shipment-4 | 14 |
| 6 | shipment-2 | 12 |
--------------------------

How can I achieve this with a MySQL query? I think first I have to select my real shipment like SELECT * FROM db WHERE shipment='shipment-6'; but then how can I get the up and below 2 rows? And also how can I achieve the order of these 5 rows from the big database? [see second table, leftside of shipments...]

Thank you,
Cem Louis
0
Comment
Question by:cemlouis
  • 4
  • 3
10 Comments
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12350108
What version of MySQL are you running?
0
 

Author Comment

by:cemlouis
ID: 12350248
MySQL 4.0.15 (or newer)
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12350390
Here's a kludgy way using a temporary table, multiple queries, and mysql variables:

SELECT @value:=value FROM tableName WHERE shipment = 6;
CREATE TABLE myTemp SELECT * FROM tableName WHERE shipment = 6;
INSERT INTO myTemp SELECT * FROM tableName WHERE value > @value ORDER BY value LIMIT 2;
INSERT INTO myTemp SELECT * FROM tableName WHERE value < @value ORDER BY value DESC LIMIT 2;
SELECT  * FROM myTemp ORDER BY value;

It might be easier to do it in PHP - you could then skip the temporary table and the mysql variable.

If you upgrade to version 4.1 or later, there may be a better method using subqueries.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:cemlouis
ID: 12350822
Hi snoyes_jw,

ok then when I insert the above rows with these INSERT INTO myTemp SELECT * FROM tableName WHERE value > @value ORDER BY value LIMIT 2; so how can I know my third row's order in the main db?
0
 

Author Comment

by:cemlouis
ID: 12351302
Hi,

How can I know the row number of my shipment_name if I use a SELECT shipment_name FROM db WHERE shipment_name='shipment-6' ORDER BY value DESC LIMIT 1;

Thanx...
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12351639
I'm not sure I understand these follow-up questions.

value would be the name of the field holding 9, 12, 28, ... in your example.

The only thing you would need to change for each query (once you get the field names corrected) would be the '6' in the first two.
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 250 total points
ID: 12358715
If I understand correctly, it would be easiest to do get that number in PHP.

$result = mysql_query($sql) or die(mysql_error()); // $sql is that last query from above
$rowNumber = 0;
while ($row = mysql_fetch_assoc($result)) {
  echo $rowNumber . $row['shipment'] . "<br/>\n";
  $rowNumber++;
}
0
 

Author Comment

by:cemlouis
ID: 12359848
Hi snoyes_jw,

Yes I am doing just like that but I have a half million row on my table. So it takes about five sec to list 11 rows with my selected row in the sixth position...I am wondering if  there is a way to do this with a query. I think MySQL engine is more powerful to do things like that???

Thank you for the efforth indeed...
Cem Louis
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question