Is there any reason why this php / mysql behaves differently on localhost and remote server?

Question

I have my code working well on localhost, but when loaded on the remove server it behaves differently - the id on localhost shows as it should (see image) - id is in order.

However, on the remote server it is not, yet the code is identical!!

I have tried running just the sql query in myphpadmin on the remote server and it is fine.

$con = mysqli_connect("localhost", "******", "******", "ps10");

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: ".mysqli_connect_error();
}

if ($_POST['selClass'] == 'All records' OR $_SESSION['selClass'] == 'All records') {
    $query_rs4 = "SELECT * 
                  FROM  marker
                  INNER JOIN users ON marker.class = users.class";
} else {
    if (empty($_POST['selClass']) && empty($_SESSION['selClass'])) {
        $query_rs4 = "SELECT * 
                      FROM  marker
                      INNER JOIN users ON marker.class = users.class";
    } else {
        $query_rs4 = "SELECT * 
                      FROM  marker
                      INNER JOIN users ON marker.class = users.class
                      WHERE users.username='{$_SESSION['selClass']}'";
    }
}

$rs4 = mysqli_query($con, $query_rs4) or die(mysqli_error($con));
$row = mysqli_num_rows($rs4);

echo "There are ".$row." students in this class";

Have you verified the ids on the remote server, in the database? — aynber, May 30 '19 at 14:45
Also see [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — Raymond Nijland, May 30 '19 at 14:55
*"id is in order."* What order? SQL tables/resultsets are by ISO/ANSI ISO standards definition **orderless** without using `ORDER BY` .. *" 4) If an is not specified, then the ordering of the rows of Q is implementation-dependent."* So you are playing russian roulette here.. If you need to have a deterministic (fixed) order always atleast use a `ORDER BY` on a column which has a `PRIMARY` or `UNIQUE` key.. As a `ORDER BY` on columns which has only unique values still can cause a non deterministic (random) result — Raymond Nijland, May 30 '19 at 15:01
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, May 30 '19 at 16:47
Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, May 30 '19 at 16:47

score 2 · Answer 1 · edited May 30 '19 at 14:59

2

Besides the fact that you don't show us the code that creates the table, which is functioning differently in the two machines, remember that saying

SELECT * FROM tableA JOIN tableb

means that since there can be fields with the same name in the two tables you cannot be sure what field gets returned.

For example, if both the tables had an id field, using that query means that you don't know if you'll get tableA.id or tableB.id...

If, instead you say:

select tableA.* from tableA join tableB

You will be sure that the id field will come from tableA...

Also, if you want the result ordered by id, you should EXPLICTLY add an ORDER BY tableA.id clause, otherwise the server can decide to give you the records in the order it likes, even a different one each time you do the query...

edited May 30 '19 at 14:59

Raymond Nijland

11,488
2
22
34

answered May 30 '19 at 14:53

Shores

95
7

1

*"otherwise the server can decide to give you the records in the order it likes"* without ORDER BY the optimizer/executioner **will** give you the records in anny order that best suits the query. – Raymond Nijland May 30 '19 at 14:56
@RaymondNijland I must say that I've seen various SQL servers being very repeatable: if the query only changes parameters and not structure, they repeatably return the records in the same order, but that said, the only way to be sure the records get returned in a specific ordering is to ask it explicitly with a ORDER BY clause... – Shores Jul 08 '19 at 19:42

score 1 · Accepted Answer · answered May 30 '19 at 14:56

The reason is that the SQL query is returning multiple columns with the name id.

The the result array/hash allows only one 'id' element i.e. $row['id'] gets assigned a single value.

The difference between the two environments could be as simple as someone did an

ALTER TABLE `somereferencedtable` ADD `id` INT UNSIGNED ;

And that broke the code.

The fix is to follow best practice patterns:

Ditch the lazy ass * from the SELECT, and explicitly specify the expressions to be returned, with all column references fully qualified. Where there are duplicate column names, provide aliases...

SELECT users.id AS id, marker.id AS marker_id, user.class, marker.sub, ... 
  FROM

This aid the future reader, not having to lookup which columns come from which tables, ... as well as avoiding the code breaking, when someone adds a column to a table in the future, causing "ambiguous column" error, or silently wrong behavior (as demonstrated in the question).

Also, without an ORDER BY clause, MySQL is free to return rows in any order.

*"Also, without an ORDER BY clause, MySQL is free to return rows in any order."* If you need to have a deterministic (fixed) order always atleast use a `ORDER BY` on a column which has a `PRIMARY` or `UNIQUE` key.. As a `ORDER BY` on columns which has only unique values still can cause a non deterministic (random) result — Raymond Nijland, May 30 '19 at 15:00

Is there any reason why this php / mysql behaves differently on localhost and remote server?

2 Answers2