URL which reads data from SQL with spaces in column names -- %20 + %2B not working

Question

I am generating this table from my SQL server: http://128.199.206.198/ELOC/map/location2.php

Sorting works fine as long there is no space in the column name. But when i want to sort "Device Name" for example. It doesn't sort correctly.

I assume the problem is within this code: <th><a href="location2.php?column=Device Name&order=

Already changed the space with %20 and with + and with %2B but none works.

<th><a href="location2.php?column=Device Name&order= -> Sorts in wrong order

<th><a href="location2.php?column=Device%20Name&order= -> Results in a white screen

<th><a href="location2.php?column=Device+Name&order= -> Results in a white screen

<th><a href="location2.php?column=Device%2BName&order= -> Sorts in wrong order

I read that urlencode could solve my problem? But i couldn't get it to work. Maybe i used it in a wrong way. After hours of trying and reading i have to give up and hope someone can tell me how to change the code accordingly. Sorry for my script kiddy knowledge.

Here the complete code: `

<?php
// Below is optional, remove if you have already connected to your database.
$mysqli = mysqli_connect('IPaddress', 'USer', 'PassWD', 'table');

// For extra protection these are the columns of which the user can sort by (in your database table).
$columns = array('Time','Ranger','Device Name','ID');

// Only get the column if it exists in the above columns array, if it doesn't exist the database table will be sorted by the first item in the columns array.
$column = isset($_GET['column']) && in_array($_GET['column'], $columns) ? $_GET['column'] : $columns[0];

// Get the sort order for the column, ascending or descending, default is descending.
$sort_order = isset($_GET['order']) && strtolower($_GET['order']) == 'asc' ? 'ASC' : 'DESC';


// Get the result...
if ($result = $mysqli->query('SELECT * FROM txt2sql_log ORDER BY ' .  $column . ' ' . $sort_order)) {
    // Some variables we need for the table.
    $up_or_down = str_replace(array('ASC','DESC'), array('up','down'), $sort_order); 
    $asc_or_desc = $sort_order == 'ASC' ? 'desc' : 'asc';
    $add_class = ' class="highlight"';

    
    ?>
    <!DOCTYPE html>
    <html>
        <head>
            <title>PHP & MySQL Table Sorting by CodeShack</title>
            <meta charset="utf-8">
            <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.3.1/css/all.css" integrity="sha384-mzrmE5qonljUremFsqc01SB46JvROS7bZs3IO2EmfFsd15uHvIt+Y8vEf7N7fWAU" crossorigin="anonymous">
            <style>
            html {
                font-family: Tahoma, Geneva, sans-serif;
                padding: 10px;
            }
            table {
                border-collapse: collapse;
                width: 80%;
            }
            th {
                background-color: #54585d;
                border: 1px solid #54585d;
            }
            th:hover {
                background-color: #64686e;
            }
            th a {
                display: block;
                text-decoration:none;
                padding: 10px;
                color: #ffffff;
                font-weight: bold;
                font-size: 13px;
            }
            th a i {
                margin-left: 5px;
                color: rgba(255,255,255,0.4);
            }
            td {
                padding: 10px;
                color: #636363;
                border: 1px solid #dddfe1;
            }
            tr {
                background-color: #ffffff;
            }
            tr .highlight {
                background-color: #f9fafb;
            }
            </style>
        </head>
        <body>
            <table>
                <tr>
                    <th><a href="location2.php?column=Time&order=<?php echo $asc_or_desc; ?>">Time<i class="fas fa-sort<?php echo $column == 'Time' ? '-' . $up_or_down : ''; ?>"></i></a></th>
                    <th><a href="location2.php?column=Device Name&order=<?php echo $asc_or_desc; ?>">Device Name<i class="fas fa-sort<?php echo $column == 'Device Name' ? '-' . $up_or_down : ''; ?>"></i></a></th>
                    <th><a href="location2.php?column=Battery volts&order=<?php echo $asc_or_desc; ?>">Battery volts<i class="fas fa-sort<?php echo $column == 'Battery volts' ? '-' . $up_or_down : ''; ?>"></i></a></th>                  
                    <th><a href="location2.php?column=Ranger&order=<?php echo $asc_or_desc; ?>">Ranger<i class="fas fa-sort<?php echo $column == 'Ranger' ? '-' . $up_or_down : ''; ?>"></i></a></th>
                    <th><a href="location2.php?column=GPS Accuracy&order=<?php echo $asc_or_desc; ?>">GPS Accuracyy<i class="fas fa-sort<?php echo $column == 'GPS Accuracy' ? '-' . $up_or_down : ''; ?>"></i></a></th>
                    <th><a href="location2.php?column=ID&order=<?php echo $asc_or_desc; ?>">ID<i class="fas fa-sort<?php echo $column == 'ID' ? '-' . $up_or_down : ''; ?>"></i></a></th>
                
                </tr>
                <?php while ($row = $result->fetch_assoc()): ?>
                <tr>
                    <td<?php echo $column == 'Time' ? $add_class : ''; ?>><?php echo $row['Time']; ?></td>
                    <td<?php echo $column == 'Device Name' ? $add_class : ''; ?>><?php echo $row['Device Name']; ?></td>
                    <td<?php echo $column == 'Battery volts' ? $add_class : ''; ?>><?php echo $row['Battery volts']; ?></td>
                    <td<?php echo $column == 'Ranger' ? $add_class : ''; ?>><?php echo $row['Ranger']; ?></td>
                    <td<?php echo $column == 'Last GPS Accuracy' ? $add_class : ''; ?>><?php echo $row['Last GPS Accuracy']; ?></td>
                    <td<?php echo $column == 'ID' ? $add_class : ''; ?>><?php echo $row['ID']; ?></td>
                </tr>
                <?php endwhile; ?>
            </table>
        </body>
    </html>
    <?php
    $result->free();
}
?>

`

Define "does not work"... Where exactly does it block? Is it $column that is not set correctly? Or is the query having an error? Or the query result wrong? — Salketer, Dec 07 '22 at 12:51
You can try the link from the post and sort after "Device Name". It sorts the table in a wrong order. Only the columns with a space are sorting "wrongly" — EDsteve, Dec 07 '22 at 12:55
Ok, but did you try troubleshooting something? Is it sorting badly because the SQL is wrong? Or is the $column value wrong? etc... — Salketer, Dec 07 '22 at 13:00
`$sort_order == 'ASC' ? 'desc' : 'asc';` will sort descending when you want it sort ASC... — Salketer, Dec 07 '22 at 13:02
Thanks for taking your time. I updated the post: Results in a white screen Sorts in wrong order I am pretty sure it is because $column value is wrong. When i rename the column name in the SQL database to Device_Name and adjust the code accordingly. All works fine. — EDsteve, Dec 07 '22 at 13:04
Why not check for errors? If there is a blank page, could this be a result of the horribly insecure database query? Are you sure you even have a database colum named `Device Name` with a space in between? — Nico Haase, Dec 07 '22 at 13:04
Also, please don't use irrelevant tags - or explain how that white page is related to HTML, or a table — Nico Haase, Dec 07 '22 at 13:05
I have narrowed down the problem to be exist only if there is a space in the column name. And yes. It exist in the DB. When i say sorts in a wrong order. I found out right now that it sorts it after Time. — EDsteve, Dec 07 '22 at 13:10
Time is the default value... Could you check the value inside $column? — Salketer, Dec 07 '22 at 13:13
I am no coder so i don't know how to do that. I was hoping it would be a simple problem easy to figure out for someone with more experience. How can i check the value inside $column? — EDsteve, Dec 07 '22 at 13:16

NineBerry · Answer 1 · 2022-12-07T13:17:20.270

0

In order to use spaces in table or column names in MySQL, you need to embed the name in backticks.

select * from txt2sql_log order by `Device Name`

Include the backticks when building the query from the variables. You can use the backticks each time, even when the column name doesn't contain spaces.

See How to select a column name with a space in MySQL

You need to make the change here:

if ($result = $mysqli->query('SELECT * FROM txt2sql_log ORDER BY ' .  $column . ' ' . $sort_order)) {

Change this to

if ($result = $mysqli->query('SELECT * FROM txt2sql_log ORDER BY `' .  $column . '` ' . $sort_order)) {

edited Dec 07 '22 at 13:17

answered Dec 07 '22 at 13:06

NineBerry

26,306
3
62
93

When you look at these two lines it already is in backticks: Device Name > – EDsteve Dec 07 '22 at 13:13
That's single quotes and not backticks. It's also not the right place. That's where you change the format of the column title in the table, not where the SQL Query is constructed – NineBerry Dec 07 '22 at 13:14
@EDsteve I've extended the answer with more details – NineBerry Dec 07 '22 at 13:18
Wow. I would have never thought about changing that. Works like a charm now. Thanks so much. I was so desperate already :) – EDsteve Dec 07 '22 at 13:24

URL which reads data from SQL with spaces in column names -- %20 + %2B not working

1 Answers1