MySQL Query Generator

Question

i need to read from a database only one row, and the row generate separated the info's This is the code:

<?php
session_start();
ob_start();
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set("display_errors", 1);
set_time_limit(10);

// database connection
$sql_ip = "localhost";
$sql_user = "mysql_user";
$sql_password = "mysql_password";
$sql_database = "database_name";

$dbc = mysql_connect($sql_ip, $sql_user, $sql_password);
if (!$dbc) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db($sql_database);

if($_GET["order"]){
    if($_SESSION["orders"][$_GET["order"]]=="ASC" || $_SESSION["orders"][$_GET["order"]]==""){
        $order=" ".$_GET["order"]." DESC ";
        $_SESSION["orders"][$_GET["order"]]="DESC";
    }
    elseif($_SESSION["orders"][$_GET["order"]]=="DESC"){
        $order=" ".$_GET["order"]." ASC ";
        $_SESSION["orders"][$_GET["order"]]="ASC";
    }
}else{
   $order="skillpoints DESC"; 
}

$sql="SELECT * FROM skillpoints_v2 ORDER BY $order LIMIT 30";
$db=mysql_query($sql);
while($b=mysql_fetch_array($db, MYSQL_ASSOC)) {		
	$players[]=$b;
}

function convert_steamid($string) {
    $steamid        = strtolower($string);
    $tmp            = explode(':',$steamid);
    $steamcid        = (($tmp[2]*2)+$tmp[1]) + 1197960265728;
    $formatted_cid     = number_format($steamcid, 0, '.', '');
    return 'http://steamcommunity.com/profiles/7656' . $formatted_cid;
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>SkillPoints</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<table id="mytable" cellspacing="0">
     <tr>
        <th scope="col" abbr="POSITION">#</th>
        <th scope="col" abbr="NICK">NICK</th>
        <th scope="col" abbr="KILLS"><a href="?order=kills">KILLS</a></th>
		<th scope="col" abbr="DEATHS"><a href="?order=deaths">DEATHS</a></th>
		<th scope="col" abbr="HEADSHOTS"><a href="?order=headshots">HEADSHOTS</a></th>
		<th scope="col" abbr="KNIFE-KILLS"><a href="?order=knife_kills">KNIFE KILLS</a></th>
		<th scope="col" abbr="KNIFE-DEATHS"><a href="?order=knife_deaths">KNIFE DEATHS</a></th>
		<th scope="col" abbr="GRENADE-KILLS"><a href="?order=grenade_kills">GRENADE KILLS</a></th>
		<th scope="col" abbr="GRENADE-DEATHS"><a href="?order=grenade_deaths">GRENADE DEATHS</a></th>
		<th scope="col" abbr="BOMB-EXPLOSIONS"><a href="?order=bomb_explosions">BOMB EXPLOSIONS</a></th>
		<th scope="col" abbr="DEFUSED-BOMBS"><a href="?order=defused_bombs">DEFUSED BOMBS</a></th>
		<th scope="col" abbr="OWN-ROUNDS"><a href="?order=own_rounds">OWN ROUNDS</a></th>
		<th scope="col" abbr="SKILLPOINTS"><a href="?order=skillpoints">SKILLPOINTS</a></th>
    </tr>
    <?php $i=0;foreach($players as $player){$i++;?>
    <tr>
        <th scope="row" class="spec<?php print ($i%2==0?'alt':'')?>"><?php print $i?></th>
		<td><a href="<?php print convert_steamid($player["authid"])?>" target="_blank"><?php print $player["nick"]?></a></td>
        <td><?php print $player["kills"]?></td>
		<td><?php print $player["deaths"]?></td>
		<td><?php print $player["headshots"]?></td>
		<td><?php print $player["knife_kills"]?></td>
		<td><?php print $player["knife_deaths"]?></td>
		<td><?php print $player["grenade_kills"]?></td>
		<td><?php print $player["grenade_deaths"]?></td>
		<td><?php print $player["bomb_explosions"]?></td>
		<td><?php print $player["defused_bombs"]?></td>
		<td><?php print $player["own_rounds"]?></td>
		<td><?php print $player["skillpoints"]?></td>
    </tr>
    <?php }?>
</table>
</body>
</html>

I need to select authid first, and generate for everyone kills, deaths etc. I don't know what can i do, and how it's works.

Since you are just starting out, get off of mysql_ db layer and onto mysqli or PDO — Drew
– Drew, Commented Sep 24, 2016 at 23:51
WARNING: If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and has been removed in PHP 7. A replacement like PDO is not hard to learn and a guide like PHP The Right Way helps explain best practices. Make sure your user parameters are properly escaped or you will end up with severe SQL injection bugs. — tadman
– tadman, Commented Sep 25, 2016 at 0:28

Funk Forty Niner · Accepted Answer · 2016-09-25 00:56:37Z

4

There is a problem in your SQL Query. You are not using the value of the variable $order.

Try using

$sql="SELECT * FROM skillpoints_v2 ORDER BY ".$order." LIMIT 30";

edited Sep 25, 2016 at 0:56

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Sep 24, 2016 at 23:52

EnclosedMail

1296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Twini · Accepted Answer · 2021-07-29 07:55:33Z

1

Try changing this code from:

$sql="SELECT * FROM skillpoints_v2 ORDER BY $order LIMIT 30";
$db=mysql_query($sql);
while($b=mysql_fetch_array($db, MYSQL_ASSOC)) {     
    $players[]=$b;
}

to:

$sql="SELECT * FROM skillpoints_v2 ORDER BY $order LIMIT 30";
$result = $dbc->query($sql);
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        // display your data
    }
} else {
    echo "0 results";
}

edited Jul 29, 2021 at 7:55

Twini

19516 bronze badges

answered Sep 25, 2016 at 0:03

Wilson Ng

2012 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL Query Generator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related