Corey St-Jacques Corey St-Jacques - 5 months ago 7
PHP Question

sql results show up on phpmyadmin but doesn't return records to my website

I have this really basic sql code I've copied over which ranks 10 people's scores, it seems to work fine on the phpmyadmin website, although on my website it returns this message: SQLSTATE[HY000]: General error

My guesses are that this message appears because the SET function doesn't allow for a returning record.

So my solution is to change the SQL code I copied to do the same thing but written differently to work on my website.

Click here to see the SQL code in phpMyAdmin

I know a lot of you will be asking for the php code so here it is.

$servername = "mysql12.000webhost.com";
$username = "corey";
$password = "pass";
$dbname = "database";
$sql = "SQL CODE PLACED HERE. PASTED BELOW";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare($sql);
$stmt->execute();

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$rows = new RecursiveArrayIterator($stmt->fetchAll());
echo json_encode($rows);
}
catch(PDOException $e){
echo "Error: " . $e->getMessage();
}
$conn = null;


and the SQL code here:


SET @count =0;
SELECT *
FROM (SELECT * , @count := @count +1 AS rank
FROM montsbattlefront
WHERE RacesWon >=0
ORDER BY RacesWon DESC
) AS t
WHERE rank
BETWEEN 0
AND 100000


The PHP code works perfectly fine.
The SQL code is where I have the most issues with.
I tried replacing SET with something else like a BEGIN END to allow for local variables and the usage of SET, although this just opened up more problems than before, since phpmyadmin does not really make support for DELIMITER very clear. Does anybody know a simple solution to write simple SQL code to do what I want to do? I'm not an SQL pro, I just know the basics.

For example instead of using @count, I can use COUNT() or ROW_NUMBER() for some reason ROW_NUMBER also does not work with phpmyadmin. If anyone can find a solution to my basic but huge problem, I will be so happy.

Regards,

Corey S.

Answer

Try removing the set statement and then you can initialize the user-defined variable in a subquery instead:

SELECT *
FROM (
    SELECT * , @count := @count +1 AS rank
    FROM montsbattlefront, (SELECT @count := 0) t
    WHERE RacesWon >=0
    ORDER BY RacesWon DESC
) AS t
WHERE rank BETWEEN 0 AND 100000
Comments