Extracting data from database

[MySQL_Narb]

foreach($players as $player)
            {
                ?>

                <a href="user.php?user=<?php echo htmlentities($player['playerName'], ENT_NOQUOTES); ?>" target="_self" class="tableRow">
                    <span class="rankColumn">
                    <span class="Olde"><?php echo number_format($x); ?></span>
                    </span>
                    <span class="nameColumn">
                    <span class="Olde"><?php echo htmlentities($player['playerName'], ENT_NOQUOTES); ?></span>
                    </span>
                    <span class="levelColumn">
                        <span class="Olde"><?php echo number_format($player[$this->whatSkill($skill).'lvl']); ?></span>
                    </span>
                    <span class="xpColumn">
                    <span class="Olde"><?php echo $player[$this->whatSkill($skill).'xp']; ?></span>
                    </span>
                </a>

                <?php
                $x++;
            }

 

 

I'm trying to rewrite a website script for someone, without modifying the database. Sadly they did this kind of badly.

 

Here you can see:

 

<?php echo number_format($player[$this->whatSkill($skill).'lvl']); ?>

 

Works perfectly. The level is outputted. But for some reason, this:

 

<?php echo $player[$this->whatSkill($skill).'xp']; ?>

 

Only outputs text, and not the XP (See example picture). Any idea as to why this line of code doesn't work but the other does?

[PFMaBiSmAd]

Apparently the $player array doesn't contain the expected data. What does the following debugging code show and how is the data being put into the $players array? -

 

echo "<pre>",print_r($player,true),"</pre>";

 

 

[MySQL_Narb]

Seems the database is returning the text:

 

[Fishingxp] => Fishingxp
[2] => Fishingxp

 

I'm not exactly sure why it's returning that. Looking at my query, everything seems to be fine:

 

case 11:
                return $this->database->processQuery("SELECT `playerName`,`Fishinglvl`,'Fishingxp' FROM `skills` ORDER BY `Fishinglvl` DESC,`Fishingxp` DESC LIMIT $start,$per", array(), true);
                break;

 

 

[PFMaBiSmAd]

You code is apparently storing the key as the value, rather than the value as the value.

[MySQL_Narb]

You code is apparently storing the key as the value, rather than the value as the value.

 

Do you have any idea on how it's happening? I see nowhere in my code where a mixup like that is possible.

 

Edit: I found the problem, but not the solution.

 

return $this->database->processQuery("SELECT `playerName`,`Cookinglvl`,'Cookingxp' FROM `skills` ORDER BY `Cookinglvl` DESC,`Cookingxp` DESC LIMIT $start,$per", array(), true);

 

Is my code. Whenever I switch the places of Cookinglvl and Cookinglvl (in this instance, the switch make the query: `playerName`,`Cookingxp`,`Cookinglvl`), the Cookinglvl stops working and Cookingxp starts to work. Any idea of why this is?

[PFMaBiSmAd]

The problem is probably in the code in the processQuery method.

[MySQL_Narb]

The problem is probably in the code in the processQuery method.

 

I've used my method on other projects bigger than this one and have never encountered such a problem:

 

public function processQuery($query, array $binds, $fetch)
    {
        $query_handle = $this->dbc->prepare($query);
        if(!$query_handle->execute($binds))
        {
            $error = $query_handle->errorInfo();
            echo $error[2];
}
        
        //update insertId var
        $this->insertId = $this->dbc->lastInsertId();
        
        //incase we ever want to get the number of rows affected
        //we set our row_count variable to the number of rows
        //affected
        $this->row_count = $query_handle->rowCount();
        
        if($fetch)
        {
            return $query_handle->fetchAll();
        }
    }

[mikosiko]

in this queries:

                return $this->database->processQuery("SELECT `playerName`,`Fishinglvl`,'Fishingxp' FROM `skills` ORDER BY `Fishinglvl` DESC,`Fishingxp` DESC LIMIT $start,$per", array(), true);

 

    and

return $this->database->processQuery("SELECT `playerName`,`Cookinglvl`,'Cookingxp' FROM `skills` ORDER BY `Cookinglvl` DESC,`Cookingxp` DESC LIMIT $start,$per", array(), true);

 

do you realize that you are enclosing the fields Fishingxp and Cookingxp in single quotes instead of backticks?.... is that what you want to do?

[MySQL_Narb]

in this queries:

                return $this->database->processQuery("SELECT `playerName`,`Fishinglvl`,'Fishingxp' FROM `skills` ORDER BY `Fishinglvl` DESC,`Fishingxp` DESC LIMIT $start,$per", array(), true);

 

    and

return $this->database->processQuery("SELECT `playerName`,`Cookinglvl`,'Cookingxp' FROM `skills` ORDER BY `Cookinglvl` DESC,`Cookingxp` DESC LIMIT $start,$per", array(), true);

 

do you realize that you are enclosing the fields Fishingxp and Cookingxp in single quotes instead of backticks?.... is that what you want to do?

 

...........

 

 

Thank you. How you noticed that, I'm not too sure. Once again, thank you.

[Pikachu2000]

That right there is a great reason not to use backticks when they aren't needed.

[MySQL_Narb]

That right there is a great reason not to use backticks when they aren't needed.

 

When do you consider them to be necessary? If I remember correctly, I read an article telling me to use them. Not too sure, though.

[Pikachu2000]

When the database/table/field name is one of the MySQL reserved words, or contains special characters.

[MySQL_Narb]

When the database/table/field name is one of the MySQL reserved words, or contains special characters.

 

Well I use to forget a lot of the reserved words and usually generated some errors that took me a while to locate. So, I moved on to just using `.

 

I'll try and cut back on my usage of them.

[xyph]

Nothing really wrong with backticks, assuming you actually use them It does add another layer of potential problems, as you've found out.