I am using the following mysql query:

CREATE TEMPORARY TABLE 
temp_table
ENGINE=MyISAM 
AS (SELECT age, performance, wind,name,surname,dob,prov,pos,place,date, @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := performance, CONCAT(name,' ',surname) AS 'athlete' FROM results p, ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r WHERE event='60m' AND gender='male' AND age='senior' AND year='2017' AND wind<=2 AND wind<>'NW' ORDER BY performance ASC);
SELECT @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank,@incRank := @incRank + 1 temp1,@prevRank := performance temp2, rank pos,MIN(performance) performance, wind,athlete, dob, prov, pos, place, date FROM temp_table, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1, @s:=0) r GROUP BY athlete ORDER BY performance ASC LIMIT 0,500

I am using the following to test the result in php:

<?php
$con=mysqli_connect("localhost","***","***","***");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"CREATE TEMPORARY TABLE temp_table ENGINE=MyISAM AS (SELECT age, performance, wind,name,surname,dob,prov,placing,place,date, @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := performance, CONCAT(name,' ',surname) AS 'athlete' FROM results p, ( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r WHERE event='60m' AND gender='male' AND age='senior' AND year='2017' AND wind<=2 AND wind<>'NW' ORDER BY performance ASC) SELECT @curRank := IF(@prevRank = performance, @curRank, @incRank) AS rank,@incRank := @incRank + 1 temp1,@prevRank := performance temp2, rank pos,MIN(performance) performance, wind,athlete, dob, prov, placing, place, date FROM temp_table, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1, @s:=0) r GROUP BY athlete ORDER BY performance ASC LIMIT 0,500");

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['pos'] . "</td>";
echo "<td>" . $row['athlete'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

I however get the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/seefle0/public_html/test.php on line 17

Any ideas what I can do to fix the query in php?

mysqli_query is probably returning FALSE on failure, instead of the results of the query. I don't remember what it is offhand, but there should be a way to pull the error message.

Member Avatar for diafol

Error reporting in the php. Net manual. Try running this query in a phpmyadmin to see what you get

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.