Why are my two different questions returning the same thing?

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\n", $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: DB Screen Shot

Why is this not returning the previous row?

I doubt that you can get meaningful results when comparing strings directly.What does blah>someotherblah even means? — Mihai, Dec 12 '13 at 03:23
I recommend using strictly integer IDs. You're seeing if a string type is less than another string type, which is going to give you weird results if not done right. — Zarathuztra, Dec 12 '13 at 03:35
@Zarazthuztra - would it work if cleaned the `subsection` value to something like `401.1.1` or whatever before comparison? I cant really use my auto increment `id` field because its not in order. — ian, Dec 12 '13 at 03:46
http://stackoverflow.com/questions/14403313/return-a-float-number-from-a-string-using-mysql — Mihai, Dec 12 '13 at 04:01

Jason Heo · Accepted Answer · 2013-12-12T04:26:15.597

0

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.

edited Dec 12 '13 at 04:26

answered Dec 12 '13 at 04:21

Jason Heo

9,956
2
36
64

That works but I may run into situations of double digits between `.` I was trying with: `$query = "SELECT * FROM codes WHERE subsection = '". $id ."' ORDER BY subsection ASC LIMIT 1,1` but as soon as I add the `offset` `,1` it returns blank. – ian Dec 12 '13 at 04:41
I did a test and it seemed to work with double digits between `.` – ian Dec 12 '13 at 04:43
@ian wow, Really? what you are looking for is called `NATURAL SORT`. but people are saying that there are no definitely solution for natural sort. works only some situation. Refer here. http://stackoverflow.com/questions/153633/natural-sort-in-mysql So, I was preparing suggest something like tree structure (i.e parent subsection, child subsection, http://www.slideshare.net/billkarwin/models-for-hierarchical-data) Anyway So far so good! – Jason Heo Dec 12 '13 at 04:56

Why are my two different questions returning the same thing?

1 Answers1