MYSQLi does not return rows, although it has SQL and PHP syntax
The SQL code I have is working in HeidiSQL when run over and over, but when I clone it in PHP and run mysqli_query ($ db, $ sql) it doesn't work.
The following PHP / MySQL code is up and running perfectly.
$sql = "select `ID`,`User` from (
select * from
(SELECT
`ID`,
`User`,
`BI`,
(@cnt:= @cnt + (`BI`/(select SUM(`BI`) from `ax`))) as `Entirety`
from `ax` as `t`
CROSS JOIN (SELECT @cnt := 0) AS var
order by `BI`
) d
where `Entirety`>(@rnd)
order by `BI`
) as `l`
cross join (select (@rnd := rand()) as `RandomValue`) as var2
limit 1;";
Then I run $ sql via
$result = mysqli_query($db,$sql);
$results = mysqli_fetch_array($result);
where $ db is a valid and open connection to the MySQL server. But returning an object when I do
print_r($result);
appears as
mysqli_result Object (
[current_field] => 0
[field_count] => 2
[lengths] =>
[num_rows] => 0
[type] => 0
)
I don't want this and num_rows must be "1" as shown in HeidiSQL when I run it. Here's a HeidiSQL image showing the results:
Does anyone have any idea?
COLUMNS table ax
: ID
, User
and BI
. In SQL, it creates two temporary columns called "Entirety", which is the probability counter, and a column named RandomValue
, which is rand () from 0 → 1.
The only row in this table has values (1,1,10). Even though it is 10, it has Entirety
of '1', which means that it is 100% guaranteed to be selected. Despite this, nothing is selected.
source to share
If you add EXPLAIN to your query in PHP, mysqli will tell you an error for one subquery:
Impossible WHERE noticed after reading const tables
and
const row not found
This could mean a lot, but in this context, my rate is invalid on the column. I sure that
select (@rnd := rand()) as `RandomValue`
performed after
where `Entirety`>(@rnd)
and therefore not given.
So why does it work on your client? Because the client maintains the connection. So it doesn't execute the first time the query is run, but the second time it has the saved @rnd value from the first execution. You can check this by renaming @rnd to @ rnd1 and executing the query once.
So the solution (as Matt pointed out) is to set the @rnd variable first and then use it.
Here is a related SO question about custom variables in subqueries:
Custom variable in MySQL subquery
From my client:
First time EXPLAIN:
Second time EXPLAIN:
Hope it helps.
source to share
Interesting here: if you change the order of your cross-attached query for the @rnd variable:
select `ID`,`User` from
(select (@rnd := rand()) as `RandomValue`) as var2 CROSS JOIN
(
select * from
(SELECT
`ID`,
`User`,
`BI`,
(@cnt:= @cnt + (`BI`/(select SUM(`BI`) from `ax`))) as `Entirety`
from `ax` as `t`
CROSS JOIN (SELECT @cnt := 0) AS var
order by `BI`
) d
where `Entirety`>(@rnd)
order by `BI`
) as `l`
limit 1;
... then I believe it is logically the same request. However this version returns the string you expect when run from PHP and the standard MySQL client for me, whereas I can reproduce your lack of output from PHP with your original.
NB: I still cannot say why this is happening. Even if this technically answers your question, I personally won't mark it as an answer and leave it to attract other answers from people who can figure out what's really going on in at least a day or two. If it is still open 48 hours later, I will send a bounty to it, as I am curious.
source to share
The essence of the problem is that it is @rnd
not initialized when evaluating it. This is mainly a problem of order of operations.
Assertion works in HeidiSQL because the statement uses the value that was previously assigned @rnd
by the previously executed statement. (We know that MySQL user variables retain their values when executing queries throughout the session, MySQL database connection).
To evaluate RAND()
once, at the start of a statement, we can move the assignment down to the lowest level of the inline view, which will be evaluated first. (This works because of the way MySQL evaluates an inline view query to materialize a "derived table" before the outer query is executed.
If I had a guarantee that the column BI
contains positive integer values, then I would rather return the result using a statement like this:
SELECT d.ID
, d.User
FROM ( SELECT t.ID
, t.User
, t.BI
, @rt := @rt + t.BI AS rt
FROM `ax` t
CROSS
JOIN ( SELECT @rt := 0
, @rnd := FLOOR(1 + RAND() * (r.tot – 1))
FROM ( SELECT SUM(q.BI) AS tot FROM `ax` q ) r
) s
ORDER BY t.BI
) d
WHERE d.rt > @rnd
ORDER BY d.BI
LIMIT 1
NB The MySQL Reference indicates that the behavior we observe with custom variables in a statement is not guaranteed and cannot be relied upon.
But we are seeing consistent behavior in at least MySQL 5.1 and 5.5. The MySQL documentation makes it clear that future versions of MySQL may change this behavior.
(If BI
it is not an integer, or could be zero or negative, then the method used by the operator to determine the "current total" ( rt
) may not work correctly. I believe the OP's query may have a similar problem with non-integer or negative values.)
source to share