ChrisianBartram ChrisianBartram - 4 months ago 9
MySQL Question

MySQL Select Row from table WHERE value > 0

Hey im having trouble constructing this mysql query its stumped me for about a week now im hoping someone has the answer and can help me learn!

my table looks like this:
my table looks like this

What I want
Im looking to get all of the values out of my table WHERE the username OR email = 'abc123' AND WHERE the values of attack strength defense agility etc... are > 0

So basically how could I only get rows where the values in my columns are greater than 0 and the values are associated with the correct username and email

any help would be much appreciated! im not sure if i explained this the best way but id love to help clarify!

here's what i was working with so far

SELECT Attack, Strength, Defense, Magic, Ranged, Prayer, Runecrafting, Dungeoneering, Construction, Constitution, Agility, Herblore, Thieving, Crafting, Slayer, Hunter, Mining, Smithing, Fishing, Cooking, Firemaking, Woodcutting, Farming, Summoning FROM experience WHERE email = abc123 OR username = abc123


Couldn't find a way to get the data out of the database to way I wanted it so I decided it would be better to simply pull out all the data from the table and filter it using php

$stmt2 = $DB_con->prepare("SELECT * FROM experience WHERE username = :uname OR email = :email");
$stmt2->execute(array(':uname' => $_SESSION['username'], ':email' => $_SESSION['email']));
$rowExperience = $stmt2->fetch(PDO::FETCH_ASSOC);

$skills = array("Attack", "Strength", "Defense", "Magic", "Ranged", "Prayer", "Runecrafting", "Dungeoneering", "Construction",
                 "Constitution", "Agility", "Herblore", "Thieving", "Crafting", "Fletching", "Slayer", "Hunter", "Mining", "Smithing","Fishing", "Cooking", "Firemaking", "Woodcutting", "Farming", "Summoning");

           for($i = 0; $i < count($skills); $i++) {
                if(intval($rowExperience[$skills[$i]]) > 0) {
                    echo '<td style="color:grey;">' . $skills[$i] . ' - ' . $rowExperience[$skills[$i]] . '</td>';