Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

Boris Kamp
Boris Kamp
16,660 Points

filter array data

Hi,

Im fetching data from the MySQL db and displaying it on the Wordpress frontend. The db exists out of 49 rows (each row represents one client) The idea is to display this info on an already custom made profile page.

this code fetches all the rows:

try {
    $results = $db->query("SELECT id, bedrijf, titel, voorletters, tussenvoegsel, achtervoegsel, meisjesnaam, thuis_adres, thuis_postcode, thuis_plaats, thuis_land, mobiele_telefoon, email_thuis, geboortedatum, bsn_nummer FROM persoonsinfo ORDER BY id ASC");
} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
$users_info = $results->fetchAll(PDO::FETCH_ASSOC);

When a user is logged in, the user will have a user id which will be the same as the row from the db, that way i can dynamically display the customer info. Im wondering how I can filter

$users_info = $results->fetchAll(PDO::FETCH_ASSOC);

to only fetch the current user's info, and not fetch the whole db table. I have to create some kind of line that get's the user's id and puts that in the fetch function to represent the table row to fetch

Thanks!

4 Answers

I would say in this instance you should change your query.

I would start by using the user's ID when they log in. When the user logs in, have your code create a session and as the form checks to see which user logs in, have the ID from the table stored in the global $_SESSION variable as $_SESSION["id"].

that way you could change the query to something like this:

$results = $db->query("SELECT * FROM persoonsinfo WHERE id = ?", $_SESSION["id"]);
} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}

$users_info = $results->fetchAll(PDO::FETCH_ASSOC);

Now you have the info for one person in the associate array and not the info of 49 people in the associate array and the info retrieved is changed based on who is logged in because once you log in, a new session starts and a new id is saved into the global variable.

I hope that helps.

Boris Kamp
Boris Kamp
16,660 Points

Thank you Shawn! That seems like a smart approach to me. Unfortunately Im not fully understanding it though. So first I have to do this:

// Start the session
session_start();

// Set session variables
$_SESSION["user_id"] = "get user ID get function to be placed here";
?>

that get's the user ID everytime a user gets to their page.

I don't understand what the WHERE id = ?" part does. does this link the users ID to the table's row?

Thanks!!

Hi,

Sorry to confuse you. the question mark (?) is a bind param, basically a placeholder. Basically we are calling the databases in two different fashions. I created a query function for me to use personally so that its dynamic and I can call it whenever I need it. I use a lot of controller pages in my PHP to keep it separate from content pages.

This is what I normally do when I do a user situation:

1st they have to login: For this I use a simple form with a method of post.

   <form action="login.php" method="Post">
      <input type="text" name="username" placeholder="username">
      <input type="password" name="password" placeholder="password">
      <input type="submit" value="submit">

If you noticed, that sends the information in a $_POST variable to login.php.

In login.php, this is where I would do my checks against the database to see if the user exists. because you have to constantly do queries on the data base to check certain things or pull information, for me it was just best to make a function and call that function whenever I needed something from the database. So in another file, (name it whatever you want, I named mine functions.php" you can create a function that looks like this:

My Query Function:

function query(/* $sql [, ... ] */)
     {
        // SQL statement
         $sql = func_get_arg(0);

        // parameters, if any
       $parameters = array_slice(func_get_args(), 1);

        // try to connect to database
        static $handle;
      if (!isset($handle))
       {
           try
            {
               // connect to database
                $handle = new PDO("mysql:host=HOST;dbname=DB;port=PORT",USERNAME,PASSWORD);

                // ensure that PDO::prepare returns false when passed invalid SQL
                $handle->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $handle->exec("SET NAMES 'utf8'"); 
            }
            catch (Exception $e)
           {
               echo "Could not connect to the database.";
               exit;
           }
       }

        // prepare SQL statement
        $statement = $handle->prepare($sql);
        if ($statement === false)
        {
            echo "SQL statement is incorrect, please try again";
            exit;
        }

        // execute SQL statement
        $results = $statement->execute($parameters);

       // return result set's rows, if any
        if ($results !== false)
        {
             return $statement->fetchAll(PDO::FETCH_ASSOC);
        }
        else
        {

            return false;

        }
    }

So this looks complicated and some of this I pulled from somewhere else but basically this function allows me to first read the statement, input any bind params (more on that in a second) and then do the basic call to the SQL server and receive info from the server if it returns a row with of course all the fine checks in between.

Now going back to the login.php page which is just a controller, you can check if the user exists and if the user does exist you can then start a session and store their id in $_SESSION. See what I mean below:

//query database for user with the function I created using ? as a placeholder to inject exactly how to identify the user
$user = query("SELECT * from table WHERE username = ?", $_POST["username"]);

now I have the user's info saved as a variable named $user which is now an array with key value pairs from the database. If the user has already registered before than all the content for the user should be there. Before you cross reference the password posted with the user's password, you have to make sure $user array "isset" or isn't empty in any of the key value pairs.

Once that's done It's just a matter of checking to make sure this is the correct user and there is information. you can do that with a simple conditional statement by checking the $_POST["password"] that was submitted by the form against the current $user["password"] we just received from the database.

if( $user["password"] != $_POST["password"])
{
    echo "Sorry incorrect password";
}
else{
    //This is assuming you checked everything else out and all the info is there you can do this
   session_start();
   $_SESSION = $user;
   //redirect user to home page

  //or you can do this
  $id = $user["id"];
  $_SESSION["id"] = $id;
  //redirect user to home page
}

after you start the session, you could technically place the entire array into the $_SESSION and just call from there because it will float from page to page until the user logs out. Something tells me thats a bad practice. So what I tended to do was store the id in session like the second example and when I returned to the home page, I did another query and pulled the information under a different variable.

//Now I'm on the homepage

//calling database again on new page but because it's verified previously that this is the user, I can simply do a direct call //and pull info into user directly from the database.

$user = query("SELECT * FROM table WHERE id = ?", $_SESSION["id"]);

Now I can use $user all over the page and it's just the user info, not the 49 users you've had previously. This is a way I'm familiar with. It's not the god way so use what you want, or incorporate it your own way. I just hope this helps.

Take care.

Additionally before any of this happens you can even check if the session is started and if there is a session id in place. if there is then the same user will have his info back, if there isn't then the user will be redirected to log in. For me, it was in my config.PHP, it was the first thing that got executed when a visited arrived to my site. it looked like this

    // display errors, warnings, and notices
    ini_set("display_errors", true);
    error_reporting(E_ALL);

    // requirements
    include("includes/constants.php");
    include("includes/functions.php");

    // enable sessions
    session_start();

    // require authentication for most pages
    if (!preg_match("{(?:login|logout|register)\.php$}", $_SERVER["PHP_SELF"]))
    {
        if (empty($_SESSION["id"]))
        {
            redirect("login.php");
        }
    }

hope that helps some more.

Boris Kamp
Boris Kamp
16,660 Points

Thank you Shawn!

That all looks very daunting to me but I think I get the biggest part of it. right now I setup this:

  1. A user-info-session.php file with the following content:
<?php
// Start the session
session_start();

// Set session variables
$_SESSION["user_id"] = get_current_user_id();
//echo "Session variables are set.";

echo "User ID =" . " " . $_SESSION["user_id"]
?>

This works, when Im logged in it echos 1 as the user ID (Im admin)

  1. My database.php file with the following content:
<?php

try {
    $db = new PDO("mysql:host=localhost;dbname=contacten_db;port=8889","root","root");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
} catch (Exception $e) {
    echo "Could not connect to the Database Contacten_DB";
    exit;
}

try {
    $results = $db->query("SELECT * FROM persoonsinfo WHERE userid = ?", $_SESSION["user_id"]);
} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
//echo "<pre>";
//var_dump($results->fetchAll(PDO::FETCH_ASSOC));
$user_info = $results->fetchAll(PDO::FETCH_ASSOC);

?>

next, in my page_mijnawf.php file (this is the personal page for the logged in user) I included database.php this way <?php //include (TEMPLATEPATH . '/includes/database.php'); ?>

When I load the page I get the error data could not be retrieved which is the catch hook of the query.

I still don't understand how WHERE userid = ?", $_SESSION["user_id"] represents the row of the table. does WHERE stand for the table row or something?

Thank you for taking the time and effort to help me out Shawn! I really appreciate it!

Boris Kamp
Boris Kamp
16,660 Points

Ok, I moved on a little bit and now Im able to fetch only one row at a time by using this:

try {
    $results = $db->prepare("SELECT * FROM persoonsinfo WHERE id = ?");
    $results->bindParam(1,$_SESSION["user_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}

using the var_dump($results->fetch(PDO::FETCH_ASSOC)); dumps the correct row! for example:

array(15) {
  ["id"]=>
  string(1) "1"
  ["bedrijf"]=>
  string(0) ""
  ["titel"]=>
  string(0) ""
  ["voorletters"]=>
  string(4) "test"
  ["tussenvoegsel"]=>
  string(0) ""
  ["achtervoegsel"]=>
  string(0) ""
  ["meisjesnaam"]=>
  string(0) ""
  ["thuis_adres"]=>
  string(22) "test"
  ["thuis_postcode"]=>
  string(7) "test"
  ["thuis_plaats"]=>
  string(9) "test"
  ["thuis_land"]=>
  string(0) ""
  ["mobiele_telefoon"]=>
  string(11) "test"
  ["email_thuis"]=>
  string(20) "test"
  ["geboortedatum"]=>
  string(0) ""
  ["bsn_nummer"]=>
  string(0) ""
}

so thats perfect, but now I'm hurting my head over how to echo data to the screen.

I created this: $user_info = $results->fetch(PDO::FETCH_ASSOC); and was expecting this <?php echo $user_info['voorletters']; ?> to work, but it doesn't and it's driving me crazy.

Im trying to do the echo in my page-mijnawf.php' file and in that file I did<?php include (TEMPLATEPATH . '/includes/database.php'); ?>` to include the code shown above.

What am I missing here?

I think it could be your using single quotes instead of double quotes.

I would check that out. If not double check key "voorletters" is populated.

Also, because your storing an array into a new variable it could be indexing it again because its thinking it has to store multiple arrays.

Var dumb the variable with it holding the current array. You might see the array index with a number, usually zero since its the first index. In that case if you want to call it you have to state it this way:

echo $user_info[0]["voorletters"];

Var dump it and see if its indexed by zero before the keys. If it is, then you have to call your keys like I stated it above.

Boris Kamp
Boris Kamp
16,660 Points

Somehow, While not changing anything, it works now..... Off course Im really happy about it, but it's strange that it did not at first, maybe it were my cookies or something! Anyway, I would like to thank you very much for guiding me trough this! Thanks!