How to use PHP natsort to sort out mysql records-CodePudding

I have been trying to sort out my mysql records with mysql queries but seems like its just impossible to do it in the natural order. I have values such as a1, a2, a3 through a200 and then b1 and so on but when sorting them out in mysql I get results such as a1, a10, a100 and any kind of undesired results so I wonder if php natsort is a better bet.

My table looks like this:

NAME         POSITION
a1             45
a2             67
a3             89

and so on, the point is to update the value in position according to the natural order of the value in NAME so a1 should be position 1, a2 should be 2 and so on. This is my PHP so far:

$values = $mysqli->query("SELECT name FROM table ORDER BY position ASC");
$sortit = natsort($values);

foreach($sortit as $pos=>$nam) {
 $mysqli->query("UPDATE table SET position=$pos WHERE name=$nam");
}

but I dont know where to go from here. Could you please put me in the right direction?

CodePudding user response：

There's no need to use ORDER BY in your query, since you're going to reorder it in PHP.

You need to get an array of all the names. $mysqli->query() returns a result object, not an array of the values.

$result = $mysqli->query("SELECT name FROM table");
$values = [];
while ($row = $result->fetch_assoc()) {
    $values[] = $row['name'];
}
natsort($values);
$stmt = $mysqli->prepare("UPDATE table SET position = ? WHERE name = ?");
$stmt->bind_param("is", $pos, $nam);
foreach ($values as $pos => $nam) {
    $stmt->execute();
}

natsort() doesn't return the sorted array, it sorts the array in place.

CodePudding user response：

Okay, thanks to Barmar for putting me in the right direction. This might not be the most elegant solution but it works just fine:

$result = $connect->query("SELECT name FROM table");
$values = [];
while ($row = $result->fetch_assoc()) {
    $values[] = $row['image'];
}

$sorted = $values;
natsort($sorted);
$i = 0;
foreach($sorted as $pos=>$nam) {
 $num = $i  ;
 $sql = "UPDATE table SET position = '$num' WHERE name = '$nam'";
 mysqli_query($connect, $sql);
}