Solved

An MySQL order problem...

Posted on 2004-10-19
10
198 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

896 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now