Help needed: SELECT specific user data from MySQL in PHP

[raneyron]

Hello. I'm a newbie so sorry if this isn't the best forum to post my problem.

 

I am using a MySQL and PHP to create a web app. I have authentication, and I can register users. I also have a form that users provide information and it is successfully inserting data into a table in my database.

 

I will use fictional fields for my database table called meal_info:

 

username

dateStartedDiet

numberMealsPerDay

costPerMeal

 

Problem: Select user-specific data from the MySQL database, using Session username to select only the current user's data, then display it and do some calculations.

 

Here is thecode at the top, and I am fairly sure it's working:

 

session_start(); //execute commone code

require("common.php"); //includes code to connect to database, etc.

if(empty($_SESSION['user'])) 

    { 

        // If they are not, we redirect them to the login page. 

        header("Location: login.php"); 

         

        // Remember that this die statement is absolutely critical.  Without it, 

        // people can view your members-only content without logging in. 

        die("Redirecting to login.php"); 

    }

 

Here is the part of the code that has to do with displaying user data:

 

$userID = $_SESSION['user']['username']; //create a variable that is the session username which is identical to the field in our MySQL table

$query = "SELECT * FROM meal_info WHERE username = $userID"; //our SELECT statement

$result = db->query($query); //execute the query 

 

$row_count = $result->num_rows;//count the rows in the table and place in variable to use in incremental loop code

 

for ($i = 0; $i < $row_count; $i++) : $row = $result->fetch_assoc(); //for each row in the table, fetch and create and array

 

$dateStart = $row['dateStartedDiet'];

$numberMeals = $row['numberMealsPerDay'];

$costMeal = $row['costPerMeal'];

 

 

echo $dateStart;

echo $numberMeals;

echo $costMeal;

 

 

 

 

 

 

 

[Barand]

And your problem is what?

[Psycho]

And what are your results. What is or is not happening? I see some issues in your code, but I don't see any specific errors.

 

Here are some things to address:

 

1. Don't use the mysql_ functions. They are deprecated. Use mysqli_ or, better yet, PDO.

 

2. Don't put user data directly into a query. Use prepared statements to protect against SQL Injection. Session values are typically safe, but it's best to be over cautious.

 

3. After you run the query, you don't check for errors or to even check if there were results

 

4. After you run the query you have a loop to get the records. You should only get one record in this scenario. You just need to see if a record was returned and get it. No need for a loop. Even so, if you have a scenario where there are multiple records, don't get the count and do a foreach loop. Just use a while loop

 

while($row = $result->fetch_assoc()
{
    //Do something with $row
}

[wezhind]

You appear to be referencing the username ($userID) as an integer in your SQL:

$query = "SELECT * FROM meal_info WHERE username = $userID"; //our SELECT 
statement

whereas it should presumably be a string/text? so your sql should probably be more like this:

$query = "SELECT * FROM meal_info WHERE username = '$userID'"; //our SELECT 
statement

Good luck.

 

p.s. wrapping your code in the <code></code> tag or clicking the <> button on the textarea controls would make it a LOT easier to read your code etc.

Edited January 9, 2015 by wezhind

[raneyron]

Barand, well said. I wasn't clear about the results. The problem is I get a blank page.

 

Psycho, my results are that nothing is happening. I will try to brush up on PDO and prepared statements. I will look into how to check for errors. I will try the while loop.

 

Wezhind, thanks for the tips about string vs. integer and using the code tags.

 

If anyone has any good links about this specific topic of extracting data from a MySQL database in PHP, I would appreciate it. I really just need to see some code that works so I can learn from it. I have found a whole lot of examples online that just didn't work properly.

[cyberRobot]

Have you tried outputting your variables to make sure things work as expected? I would likely start with $userID. You could try the following:

<?php
//...
 
$userID = $_SESSION['user']['username'];
var_dump($userID);
 
//...
?>

If the ID came through properly, you could check the $row_count variable to make sure a row is being returned.

 

 

Also, it looks like you are using MySQLi for the database connection. If that's correct, you can find some information about checking for MySQL errors here:

http://php.net/manual/en/mysqli.error.php

[mac_gyver]

the code you did post has two php syntax errors and the code never runs.

 

one of the errors is in the for() loop syntax. you have the start of the alternate syntax using : but you don't have the corresponding endfor;, that if you do what Psycho has suggested of not looping to retrieve one row or using a while(){} loop, will likely go away.

 

the second syntax error is a missing $ on the instance of your database connection, in $db, in this line - $result = db->query($query); //execute the query

 

to get php to help you find syntax errors, you must have php's error_reporting set to E_ALL and display_errors set to ON in your php.ini on your development system. you cannot put the settings into your code and have them show you syntax errors because your code never runs to change the settings.

[raneyron]

Thanks cyberRobot. I have made sure that the $userID is returning correctly. I did not have a $row_count returned at all, so I think the big problem was getting the connection made to the database. I do not have error code yet. Here is my code so far this morning after reading these replies (remember I am using fake database items):

$userID = $_SESSION['user']['username'];
echo $userID . "<br/>";

$query = " 
            SELECT 
                username, 
                startDate, 
                mealsDay, 
                caloriesMeal, 
                costMeal 
            FROM diet_info 
            WHERE 
                username = :userID 
        "; 
		
$statement = $db->prepare($query);
$statement->bindValue(':userID', $userID);
$statement->execute();
$info = $statement->fetchAll();
$row_count = $statement->rowCount();
echo $row_count . "<br/>";
$statement->closeCursor();

while($row = $info->fetch_assoc()
{
    //Do something with $row
    $startDate = $row['startDate'];
    $mealsDay = $row['mealsDay'];
    $caloriesMeal = $row['caloriesMeal'];
    $costMeal = $row['costMeal'];
}
	
}
echo $startDate . "<br/>";
    echo $mealsDay . "<br/>";
    echo $caloriesMeal . "<br/>";
    echo $costMeal . "<br/>";

Edited January 9, 2015 by raneyron

[cyberRobot]

You'll need to remove the semi-colon here:

while($row = $info->fetch_assoc()

And it looks like you have any extra curly bracket here:

   $costMeal = $row['costMeal'];
}
 
}
echo $startDate . "<br/>";

But perhaps there is more code...and that bracket may be needed.

 

 

 

Do you have PHP errors enabled? You can do that by adding the following to the top of your script:

<?php
//REPORT ALL PHP ERRORS
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>

[raneyron]

Updated code:

error_reporting(E_ALL);
ini_set('display_errors', 1);

$userID = $_SESSION['user']['username'];
echo $userID . "<br/>";
 
$query = " 
            SELECT 
                username, 
                startDate, 
                mealsDay, 
                caloriesMeal, 
                costMeal 
            FROM diet_info 
            WHERE 
                username = :userID 
        "; 
		
$statement = $db->prepare($query);
$statement->bindValue(':userID', $userID);
$statement->execute();
//$info = $statement->fetchAll();
$row_count = $statement->rowCount();
echo $row_count . "<br/>";
$statement->closeCursor();

while($row = $statement->fetch_assoc()){
    //Do something with $row
    $startDate = $row['startDate'];
    $mealsDay = $row['mealsDay'];
    $caloriesMeal = $row['caloriesMeal'];
    $costMeal = $row['costMeal'];
}
	

    echo $startDate . "<br/>";
    echo $mealsDay . "<br/>";
    echo $caloriesMeal . "<br/>";
    echo $costMeal . "<br/>";

The row count returns "1" so that is correct for one record, right? I have one error when I run this code:

 

Fatal error: Call to undefined method PDOStatement::fetch_assoc() in/home5/aihreaco/public_html/quitApp/private.php on line 60

[Psycho]

Correction to my previous comment. I mistakenly stated you were using mysql_ functions (as apposed to mysqli_) functions. So, you are good there.

 

 

 I will try the while loop.

 

A while() loop is the typical way to iterate over the records returned from a result set when the query can potentially return multiple records. The scenario you have should only ever return 0 or 1 records. Therefore, a loop of any kind is unnecessary. Here is a slight rewrite of what you had that includes how I would fetch the results.

 

error_reporting(E_ALL);
ini_set('display_errors', 1);
 
 //Fetch User ID from session
$userID = $_SESSION['user']['username'];
echo "Session User ID: " . htmlentities($userID) "<br/>\n";
 
//Create and run query
$query = "SELECT username, startDate, mealsDay, caloriesMeal, costMeal 
            FROM diet_info 
            WHERE  username = :userID "; 
$statement = $db->prepare($query);
$statement->bindValue(':userID', $userID);
$result = $statement->execute();
 
//Check results
if(!$result)
{
    //Query failed
    echo "Query failed";
    //Add debugging code
}
elseif(!$statement->rowCount())
{
    //No results returned
    echo "No user found for user " . htmlentities($userID);
   //Add debugging code
}
else
{
    //A record was returned, display results
    $user = $statement->fetch_assoc();
 
    echo "Start Date: {$row['startDate']}<br/>\n";
    echo "Meals per Day: {$row['mealsDay']}<br/>\n";
    echo "Calories per Meal: {$row['caloriesMeal']}<br/>\n";
    echo "Cost per Meal: {$row['costMeal']}<br/>\n";
}
 
$statement->closeCursor();

[raneyron]

Thanks Psycho! 

 

If I am correct, I am actually now using PDO.

 

I am getting this error:

 

Fatal error: Call to undefined method PDOStatement::fetch_assoc() in/home5/aihreaco/public_html/quitApp/private.php on line 74

Edited January 9, 2015 by raneyron

[raneyron]

I think I got it working everyone. Thanks. 

 

I searched the error and found a solution on another website. Instead of this:

$user = $statement->fetch_assoc();

I used this bit of code:

$row = $statement->fetch(PDO::FETCH_ASSOC);

I think it is working but I need to figure out the code needed to set variables for each of the fields in the record. For example:

$startDate = $row['startDate'];    $mealsDay = $row['mealsDay'];    $caloriesMeal = $row['caloriesMeal'];    $costMeal = $row['costMeal'];

Edited January 9, 2015 by raneyron

[mac_gyver]

I need to figure out the code needed to set variables for each of the fields in the record

 

 

actually, you should avoid doing that whenever possible. database code should be as general purpose as you can make it, without line after line of hard-coded logic and variables.

 

the purpose of this block of code is to retrieve one record of information for a specific user. it's the higher-level calling code that knows about the database table/fields that are in the sql query statement and what data will be returned. the database code itself should just return the requested information in a generic form, i.e. the $row array that was fetched.