2

I'm working on a little application for competition sailors, but i have a problem. I need the data from the 2 rows above and 2 rows beneath the row from the specific sailor. My database looks like this:

enter image description here

And this is the SQL i use to get the data above and beneath:

$data_above= mysql_query("SELECT * FROM Laser_Radiaal WHERE points < (SELECT points FROM Laser_Radiaal WHERE PLACE = ID) ORDER BY PLACE DESC LIMIT 2");

$data_under= mysql_query("SELECT * FROM Laser_Radiaal WHERE points < (SELECT points FROM Laser_Radiaal WHERE PLACE = ID) ORDER BY points ASC LIMIT 2");

But it don't work, what do i wrong?

Thanks!

EDIT: I get the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource
Dharman
  • 30,962
  • 25
  • 85
  • 135
Pim Praat
  • 43
  • 1
  • 8
  • 1
    Well, in once case you have `order by `place asc` and in the other `points asc`. I would expect `points desc` and `points asc`. – Gordon Linoff Jan 12 '14 at 14:25
  • Hello Gordon, thanks for your fast reaction, the asc/desc issue is just a little mistake i made when trying to get it working. But it still doesn't work. – Pim Praat Jan 12 '14 at 14:30

3 Answers3

1

Your first query pulls the correct results but in reverse order.Use array_reverse() to reverse order of result.

Use >= in second query and LIMIT 3 to pull the target value and the two above.

Use array_merge to merge the two arrays.

The following code (using PDO) illustrates this.

//Prepare below query
    $query1 ="SELECT * FROM Laser_Radiaal WHERE points < 
                 (SELECT points FROM Laser_Radiaal WHERE PLACE = ?)
                  ORDER BY PLACE DESC LIMIT 2";
    $stmt1 = $dbh->prepare($query1);
    // Assign parameter
    $stmt1->bindParam(1,$ID);
    //Execute query
    $stmt1->execute(); 
    $result1 = $stmt1->fetchAll();
    $result1r = array_reverse($result1);
    //Prepare abovequery including target 
    $query2 ="SELECT * FROM Laser_Radiaal WHERE points >= 
                  (SELECT points FROM Laser_Radiaal WHERE PLACE = ?) 
                   ORDER BY PLACE ASC LIMIT 3";
    $stmt2 = $dbh->prepare($query2);
    // Assign parameter
    $stmt2->bindParam(1,$ID);
    //Execute query
    $stmt2->execute();  
    $result2 = $stmt2->fetchAll();
    $finalResult = array_merge(array_reverse($result1), $result2);
    print_r(array_values($finalResult));
david strachan
  • 7,174
  • 2
  • 23
  • 33
0

In both cases you are doing WHERE points < ... but in the case of below (or above, depending on how you look at it) don't you want WHERE points > ... ?

So, this:

$data_above= mysql_query("SELECT * FROM Laser_Radiaal WHERE points < (SELECT points FROM Laser_Radiaal WHERE PLACE = ID) ORDER BY PLACE DESC LIMIT 2");

$data_under= mysql_query("SELECT * FROM Laser_Radiaal WHERE points > (SELECT points FROM Laser_Radiaal WHERE PLACE = ID) ORDER BY points ASC LIMIT 2");
Scott
  • 198
  • 6
  • Thanks, i have tried it but i still get the following error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given – Pim Praat Jan 12 '14 at 14:44
  • Hmm, so I'm not an expert on that particular topic, maybe someone who knows more about that can chime in, but if I may suggest perhaps instead of doing `SELECT * FROM Laser_Radiaal` see if it works better if you select specific columns from that table, i.e. `SELECT ID FROM Laser_Radiaal`? – Scott Jan 12 '14 at 14:49
  • Did you see [this question](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select)? The OP gets the same error message as you, and user "scompt.com" says this: "Check $result before passing it to mysql_fetch_array. You'll find that it's false because the query failed..." – Scott Jan 12 '14 at 15:08
  • I just thought I'd point out that this question really contains two separate issues and, while the first question seems specific to MySQL (i.e. how to return two rows above and below a particular record in a SQL query), the second is really a PHP question (i.e. how to troubleshoot a particular warning related to mysql_fetch_array)? Also, if the thread I linked to in my previous comment doesn't solve your second problem, then you might get a quicker answer if you create a separate question for it and include mysql_fetch_array in the title. – Scott Jan 12 '14 at 16:27
0

Please check following example it will help you on this 

select * from games order by w;
+--------+------+------+------+------+
| name   | p    | w    | d    | l    |
+--------+------+------+------+------+
| team b |    1 |    0 |    1 |    0 |
| team a |    1 |    0 |    0 |    1 |
| team c |    1 |    1 |    0 |    0 |
| team d |    2 |    2 |    0 |    0 |
+--------+------+------+------+------+
4 rows in set (0.00 sec)


mysql> SELECT NAME,P,W,D,L,@curRank := @curRank + 1 as rank FROM (select NAME,P,W,D,L from games order by w DESC) x,(SELECT @curRank := 0) r;
+--------+------+------+------+------+------+
| NAME   | P    | W    | D    | L    | rank |
+--------+------+------+------+------+------+
| team d |    2 |    2 |    0 |    0 |    1 |
| team c |    1 |    1 |    0 |    0 |    2 |
| team b |    1 |    0 |    1 |    0 |    3 |
| team a |    1 |    0 |    0 |    1 |    4 |
+--------+------+------+------+------+------+
4 rows in set (0.01 sec)


mysql> SELECT NAME,P,W,D,L,rank FROM (SELECT NAME,P,W,D,L,@curRank := @curRank + 1 as rank,if(name ="team b",@curRank := @curRank*-1,0)  AS required_rank FROM (select NAME,P,W,D,L from games order by w DESC) x,(SELECT @curRank := 0) r ) p order by abs(rank) desc limit 3;
+--------+------+------+------+------+------+
| NAME   | P    | W    | D    | L    | rank |
+--------+------+------+------+------+------+
| team b |    1 |    0 |    1 |    0 |    3 |
| team c |    1 |    1 |    0 |    0 |    2 |
| team a |    1 |    0 |    0 |    1 |   -2 |
+--------+------+------+------+------+------+
3 rows in set (0.00 sec)
Mahesh Madushanka
  • 2,902
  • 2
  • 14
  • 28