ordering mysql table using an equation with $_GET variables

Question

Here is what i am trying to do

{
{
    $sql="SELECT ISO_id,gdp,population,country_name,gold,silver,bronze,total FROM Country ORDER BY (46034/($_GET["x"]*bronze)+($_GET["y]*silver]+(z*gold)*(gdp/population))"; // chosen to order my html table with the number the medals each country won at the olympics
    }
    }

however i do not know how to include the $_GET inside the statements

There is an SQL injection possible with your query. Use [prepared statements](http://bobby-tables.com/php.html) instead. — Marcel Korpel, May 10 '13 at 14:44
You should use a prepared statement for this, and bind the $_GET values. — andrewsi, May 10 '13 at 14:44
you have to put a column name after ORDER BY statement, i don't know wha you try to do here — Nagasaki, May 10 '13 at 14:45

score 0 · Answer 1 · edited May 23 '17 at 11:55

To use a $_GET parameter in your current statement, use:

$sql="
    SELECT ISO_id,gdp,population,country_name,gold,silver,bronze,total 
    FROM Country
    ORDER BY (46034/(" . $_GET["x"] . "*bronze)+(" . $_GET["y"] . "*silver]+(z*gold)*(gdp/population))";

However, please be aware that this leaves you open to SQL Injection attacks, so you should consider filtering your input before using it in such a query. For details, see How to prevent SQL injection in PHP?

Also, please note that there appears to be an error in your ORDER BY clause as stated by Nagasaki in the comments. You should order on a column name.

thats what im trying to do im trying to order the table by this equation — user2359244, May 10 '13 at 14:53

score 0 · Answer 2 · answered May 10 '13 at 15:04

$sql="SELECT 
    ISO_id,
    gdp,
    population,
    country_name,
    gold,
    silver,
    bronze,
    total,
    (46034/(" . $_GET["x"] . "*bronze)+(" . $_GET["y"] . "*silver]+(z*gold)*(gdp/population)) as number_medals
FROM Country
ORDER BY number_medals";

Be careful of sql injection....

score 0 · Answer 3 · answered May 10 '13 at 15:05

its a good idea to first make the get variable into a separate variable then from there clean it up because putting get variables straight into an sql statement leaves you open to sql injection.

Say the url looks like this. http://www.mysite.com?ID=4 to get at the id you would go $_GET[ID] or to be safe $id = $_GET[ID] and from here do some work to clean any bad data from the id. http://php.net/manual/en/function.mysql-real-escape-string.php this will help you out a bit but still its never 100% save.

I remember back when i was trying to figure this out and i didnt want to look at alternatives i just wanted to make it work but a word of advice would be to use PDO prepared statements. They are a little tricky to understand at first but this guide is pretty good and will explain how to do it quite easily. PDO keeps you safe from sql injection.

PDO Prepared Statement Tutorial

score -1 · Answer 4 · answered May 10 '13 at 14:44

-1

just add Braces

{ { $sql="SELECT ISO_id,gdp,population,country_name,gold,silver,bronze,total FROM Country ORDER BY (46034/({$_GET["x"]}*bronze)+({$_GET["y]}*silver]+(z*gold)*(gdp/population))"; // chosen to order my html table with the number the medals each country won at the olympics } }

answered May 10 '13 at 14:44

leslieyuen

1

There is still SQL injection possible with this answer. Don't do this! – Marcel Korpel May 10 '13 at 14:45
that makes absolutely no sense ? – user2359244 May 10 '13 at 14:45
i know there is SQL injection possible but i am trying to do this for practicing coding i want to know how to include $_GET statements and numbers in the mysql statement – user2359244 May 10 '13 at 14:46

ordering mysql table using an equation with $_GET variables

4 Answers4