为什么我的两个不同的问题会回归同样的问题？

Question

I have two queries that should return the next and previous rows in my DB relative to the current row.

However they both return the same thing, the next row, and for some reason the 2nd query also returns an empty data set.

Code:

<?php

    // Credentials
    $dbhost = "localhost";
    $dbname = "buildingcodes";
    $dbuser = "***";
    $dbpass = "***";

    global $tutorial_db;

    $tutorial_db = new mysqli();
    $tutorial_db->connect($dbhost, $dbuser, $dbpass, $dbname);
    $tutorial_db->set_charset("utf8");

    //  Check Connection
    if ($tutorial_db->connect_errno) 
    {
        printf("Connect failed: %s
", $tutorial_db->connect_error);
        exit();
    }


    $id = 'R401.4 Soil tests.'; //$_GET['id'];

    echo 'id: ' . $id;
    echo '</br>';

    //////

    $query = "SELECT * FROM codes WHERE subsection > '". $id ."' ORDER BY subsection LIMIT 1";

    $result = $tutorial_db->query($query);  

    while($results = $result->fetch_array()) 
    {
        $result_array[] = $results;
    }


    echo '</br></br>';

    if (isset($result_array)) 
    {
        foreach ($result_array as $result) 
        {
            echo 'first q: ' . $result['subsection'];
            echo '</br>';   
        }
    }   



    /////


    $query = "SELECT * FROM codes WHERE subsection < '". $id ."' ORDER BY subsection LIMIT 1";

    $result = $tutorial_db->query($query);

    while($results = $result->fetch_array()) 
    {
        $result_array[] = $results;
    }


    echo '</br>';

    if (isset($result_array)) 
    {
        foreach ($result_array as $result) 
        {
            echo 'second q: ' . $result['subsection'];
            echo '</br>';   
        }
    }   

?>

Outputs:

id: R401.4 Soil tests.

first q: R401.4.1 Geotechnical evaluation.

second q: R401.4.1 Geotechnical evaluation.
second q: 

But should output:

id: R401.4 Soil tests.

    first q: R401.4.1 Geotechnical evaluation.

    second q: R401.3 Drainage.

From this set of data:

Why is this not returning the previous row?

Answer 1

Could you try this? you can test here. http://www.sqlfiddle.com/#!2/e2881/1

SELECT * from ss where subsection > 'R401.4 Soil tests'
ORDER BY SUBSTRING_INDEX(subsection, ' ', 1) ASC;
+-------------------------+
| subsection              |
+-------------------------+
| R401.4.1 Geotech.       |
| R401.4.2 Compressible   |
| R402.1 Wood foundations |
+-------------------------+
3 rows in set (0.00 sec)

SELECT * from ss where subsection < 'R401.4 Soil tests'
ORDER BY SUBSTRING_INDEX(subsection, ' ', 1) DESC;
+---------------------+
| subsection          |
+---------------------+
| R401.3 Drainage     |
| R401.2 Requirements |
| R401.1 Application  |
+---------------------+
3 rows in set (0.01 sec)

When subsection has more than two digit between dot(.) above query won't work. then probably following query works well for NEXT subsection.

    ORDER BY LENGTH(SUBSTRING_INDEX(subsection, ' ', 1)),
        SUBSTRING_INDEX(subsection, ' ', 1) ASC;

But not sure working for PREV subsection.