Issue with a select statement

[Jay88]

So i haven't coded in a while ... and can't figure out why is this happening

 

 

If i did a Select statement like this ...

 

$sql = "SELECT id, player, team, date, min, fppmp, fpts FROM NBA2017 WHERE player='$player' ORDER BY date DESC LIMIT 10 ";

 

it works!!

 

 

 

But i have 42 columns on the DB.... So I changed it to this ..

 

$sql = "SELECT * FROM NBA2017 WHERE player='$player' ORDER BY date DESC LIMIT 10 ";

 

and i get no results ...

 

 

Maybe basic but i need help!!!  Thanks in advance!!

[benanamen]

Your approach is wrong in the first place. You never ever put variables in the query. You need to use prepared statements and PDO. 

 

https://phpdelusions.net/pdo

 

Also, if you have 42 columns in a single table it is highly likely your DB design is wrong. A DB is not a spreadsheet. Look up and learn Database normalization.

Edited February 15, 2017 by benanamen

[gizmola]

I see nothing wrong with the query that would explain this. There is a concern that date is a keyword in many databases. However if the query worked originally it should still work with the column wildcard. With that said, you didn't tell us what database you are using, nor did you provide any other code. For example, the query might actually be working, but your code extracting the data from the result set might have an issue.

 

If it's mysql you could try for a quick fix by using backtics around the date keyword like so:

 

SELECT * FROM NBA2017 WHERE player='$player' ORDER BY `date` DESC LIMIT 10";

[Jacques1]

Maybe basic but i need help!!!

 

Sorry, but I'm afraid we've run out of crystal balls.

• Show all the relevant code, not just a random SQL snippet. How do you process the result set?
• Be more specific. Is there an error? Or does the query succeed and just happens to have an empty result set?
• Do some basic debugging yourself. Print $player and run the exact query in phpmyadmin (or whatever it is you're using). What happens? An error? An empty result set?

The database layout is definitely broken. You don't create a new table for every year, and I agree with benanamen that the 42 columns are fishy.

[Jay88]

Still didn't work ....  here is the whole code is his ....

 

 $sql = "SELECT * FROM NBA2017 WHERE player='$player' ORDER BY `date` DESC LIMIT 10";
    $result = $connect_NBAdata->query($sql);    

        if ($result->num_rows > 0) {
            while($row =$result->fetch_assoc()){
                    $id = $row ["id"];
                    $player = $row ["player"];
                    $team = $row ["team"];
                    $matchup = $row ["Matchup"];
                    $date = $row ["date"];
                    $wl = $row ["W/L"];
                    $min = $row ["min"];
                    $pts = $row ["pts"];
                    $fgm = $row ["fgm"];
                    $fga = $row ["fga"];
                    $fgpct = $row ["fgpct"];
                    $pm3 = $row ["3pm"];
                    $pa3 = $row ["3pa"];
                    $ppct3 = $row ["3ppct"];
                    $ftm = $row ["ftm"];
                    $fta = $row ["fta"];
                    $ftpct = $row ["ftpct"];
                    $oreb = $row ["oreb"];
                    $dreb = $row ["dreb"];
                    $reb = $row ["reb"];
                    $ast = $row ["ast"];
                    $stl = $row ["stl"];
                    $blk = $row ["blk"];
                    $tov = $row ["tov"];
                    $pf = $row ["pf"];
                    $plusmin = $row["pm"];
                    $opp = $row ["opp"];
                    $ha = $row ["ha"];
                    $fpts = $row ["fpts"];
                    $fppmp = $row ["fppmp"];
                    $pos = $row ["pos"];
                    $ptype = $row ["ptype"];
                    $nonptsfpp = $row ["nonptsfpp"];
                    $ptsfppm = $row ["ptsfppm"];
                    $twopts = $row ["2pts"];
                    $threepts = $row ["3pts"];
                    $ft = $row ["ft"];
                    $util = $row ["util"];
                    $utilpmp = $row ["utilpmp"];
                    $caltot = $row ["caltot"];
                    $year = $row["year"];
                    $count = $row ["count"];

                    echo "<br>" . "Player: " . $player ." - ". $date . " - " . $team . " - " . $fppmp . " - " .$fpts  ;

                    }  
              }else {
            echo "0 results";    
            }
 

[Jay88]

Sorry, but I'm afraid we've run out of crystal balls.

• Show all the relevant code, not just a random SQL snippet. How do you process the result set?
• Be more specific. Is there an error? Or does the query succeed and just happens to have an empty result set?
• Do some basic debugging yourself. Print $player and run the exact query in phpmyadmin (or whatever it is you're using). What happens? An error? An empty result set?

The database layout is definitely broken. You don't create a new table for every year, and I agree with benanamen that the 42 columns are fishy.

 no error i just don't get results

[Jay88]

I see nothing wrong with the query that would explain this. There is a concern that date is a keyword in many databases. However if the query worked originally it should still work with the column wildcard. With that said, you didn't tell us what database you are using, nor did you provide any other code. For example, the query might actually be working, but your code extracting the data from the result set might have an issue.

 

If it's mysql you could try for a quick fix by using backtics around the date keyword like so:

 

SELECT * FROM NBA2017 WHERE player='$player' ORDER BY `date` DESC LIMIT 10";

I posted my whole code ....Still didn't work

[mac_gyver]

the reason the code went from working to not, when you changed to the * is because the capitalization of your actual column names don't match what the php code is using. when you were specifically selecting the columns, the names in the SELECT statement are what was used in the retrieved data AND they matched the letter case of whatever the column names actually are because in the sql statement the letter case of the column names don't matter.

 

if you do a print_r($row); in your code, you will see what the associative array index names are.

 

constancy counts in programming, whatever your column names are, should be used throughout your code.

 

also, if had php's error reporting set to E_ALL and display_errors set to ON (preferably in the php.ini on your development system), php would help you by reporting and displaying all the errors it detects.

 

next, don't write out line after line of code who's only purpose is to copy variables to other variables. use the original $row[...] variables.

 

finally, when something doesn't work, provide the actual output you are getting. getting 'no results' has different meaning depending on who you are and what your definition of results is.

[Barand]

Do you have php error reporting switched on? You certainly aren't checking if there was a mysql error.

[Jay88]

Do you have php error reporting switched on? You certainly aren't checking if there was a mysql error.

Noj SQl error and is on but i will check again , thx

[Jay88]

the reason the code went from working to not, when you changed to the * is because the capitalization of your actual column names don't match what the php code is using. when you were specifically selecting the columns, the names in the SELECT statement are what was used in the retrieved data AND they matched the letter case of whatever the column names actually are because in the sql statement the letter case of the column names don't matter.

 

if you do a print_r($row); in your code, you will see what the associative array index names are.

 

constancy counts in programming, whatever your column names are, should be used throughout your code.

 

also, if had php's error reporting set to E_ALL and display_errors set to ON (preferably in the php.ini on your development system), php would help you by reporting and displaying all the errors it detects.

 

next, don't write out line after line of code who's only purpose is to copy variables to other variables. use the original $row[...] variables.

 

finally, when something doesn't work, provide the actual output you are getting. getting 'no results' has different meaning depending on who you are and what your definition of results is.

 

the reason the code went from working to not, when you changed to the * is because the capitalization of your actual column names don't match what the php code is using. when you were specifically selecting the columns, the names in the SELECT statement are what was used in the retrieved data AND they matched the letter case of whatever the column names actually are because in the sql statement the letter case of the column names don't matter.

 

if you do a print_r($row); in your code, you will see what the associative array index names are.

 

constancy counts in programming, whatever your column names are, should be used throughout your code.

 

also, if had php's error reporting set to E_ALL and display_errors set to ON (preferably in the php.ini on your development system), php would help you by reporting and displaying all the errors it detects.

 

next, don't write out line after line of code who's only purpose is to copy variables to other variables. use the original $row[...] variables.

 

finally, when something doesn't work, provide the actual output you are getting. getting 'no results' has different meaning depending on who you are and what your definition of results is.

Thanks that was a lot of help ....