Inserting PHP strings in reverse order in Mysql

I'm stumped; I am guessing this is something simple ...?

I am inserting data collected from PHPExcel (although it probably doesn't matter). On my test box (Windows WAMP server) this works fine. When I run on a linux server, the data enters the database from the bottom up. My first line is the title (name, email, address, etc.). It ends up last in SQL. Of course I can undo the output, but I know better what I'm doing wrong, especially since I'm not sure if it's 100% predictable.

I tried using prep stmt inside a loop - doesn't make any difference.

Any help?

// Prep ahead of loop to save cpu time
$insert_stmt = $dbc->prepare
("INSERT INTO contacts (id, name, dept, u1, u2) VALUES (0, ?, ?, ?, ?)");

for ($row = $chosenRow; $row < sizeof($temp) +1; $row++){
    // To allow dynamic # of cols, must always check to see if data is available
    $u1 = array_key_exists(1, $temp[$row])?$temp[$row][1]:"";
    $u2 = array_key_exists(2, $temp[$row])?$temp[$row][2]:"";


    $insert_stmt->bind_param('siss', $temp[$row][0], $importToDept,$u1,$u2);

    // PRINTING OF THIS PARAM ROW HERE SHOWS PROPER ORDER

    // Execute the prepared query.
    if (! $insert_stmt->execute() ) {
        doError();
        exit();
    }
}

      

Contacts creation table (but looking at the database via PHPAdmin shows that the rows are reversed in the DB, so probably not a creation issue):

$items = $dbc->query("SELECT * FROM contacts WHERE dept = $dept_id");

    echo "<table style='border-collapse:collapse; border:0px; margin-top:25px;'>";
    $row = 0;
    foreach ($items as $v){
        if ($limit > 0 && $row > $limit)
            break;
        echo "<tr>";
        foreach ($v as $key=>$col){
            if ($key == "id" || $key == "dept")
                continue;
            if ($row == 0)
                echo "<th>$col</th>";
            else 
                echo "<td>$col</td>";
        }
        echo "</tr>";
        $row++;
    }
    echo "</table>";

      

+3


source to share


1 answer


OK, a bit of searching in the MYSQL manual pointed me in the right direction. This type of insert and query is not something I have ever depended on, so I had no idea what I was doing wrong.

For those who in the future may make the same mistake in assumption, here's the problem:

I assumed (correctly believe) that the data goes to the DB, FIFO. In other words, they DO NOT enter the DB in reverse order. I focused on the contribution when I had to think about how the query pulled it. @Evan de la Cruz was right in asking about making the application and got me hunting in the right direction.



The problem is the sort order of the query. SQL does not guarantee the sort order on return. It gives whatever is fastest / most efficient. Even in PHPMyadmin looking for my bug hunt.

For me, the answer is to simply sort by id, which is the auto_increment field.

NTN.

+2


source







All Articles