Jump to content

Issue with a select statement


Jay88

Recommended Posts

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!! ::)

Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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";

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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";    
            }
 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 ....  ;)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.