www.webdeveloper.com
Results 1 to 11 of 11

Thread: retrieving data from mysql db and displaying

  1. #1
    Join Date
    Dec 2013
    Posts
    57

    retrieving data from mysql db and displaying

    I've been struggling to write some code so that when a user logs in with their username and password the email address associated with that account can be displayed on the next page. I have created three rows in my mysql table: user_name, user_password and user_email.

    Here is the code I have so far. I can log in to the account and everything works fine but I want to have ONLY the email address of the account that logs in to be displayed

    PHP Code:
       $this->db_connection = new mysqli('localhost''root''1''test');

     
                 
    // create a database connection, using the constants from config/db.php (which we loaded in index.php)
                   
    if ($this->db_connection->connect_errno) {
                 echo 
    "Connection Failed " $this->db_connection->connect_errno "";
                 }
     
        
     
                 
    // if no connection errors (= working database connection)
                 
    if (!$this->db_connection->connect_errno) {
     
                     
    // escape the POST stuff
                     
    $this->user_name $this->db_connection->real_escape_string($_POST['user_name']);
                     
    $this->user_password $this->db_connection->$_POST['user_password'];
                     
                     
    $user_name $this->db_connection->real_escape_string($_POST['user_name']);

     
                     
    // database query, getting all the info of the selected user
                     
    $sql "SELECT user_name, user_password, user_email
                             FROM members
                             WHERE user_name = '" 
    $this->user_name "'";
                     
    $query $this->db_connection->query($sql);
                    
    $result $query->fetch_object();

                    
    // if the username exists and if the password is a correct match
                     
    if (($query->num_rows == 1) && ($_POST['user_password'] === $result->user_password)) {
        
                  
    $_SESSION['user_name'] = $result->user_name;
                  
    $_SESSION['user_logged_in'] = 1;
                  
                  
    $_SESSION['user_login_status'] = 1;
                  
                  
                  
    setcookie("_time""cookie_value"time() + 3600);

                
                
    //redirect to this page if the user has logged in successfully
                
    header("Location: testing.php");
                
                
                         } 
                         
                       else {
                             
    $this->errors[] = "Wrong username password combination";
                            } 

  2. #2
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    I revised your code a bit (I suggest storing/comparing passwords after hashing them, i.e. SHA-1, SHA-2, SHA-3)
    PHP Code:
        $this->db = new mysqli('localhost''root''1''test');

        
    // create a database connection, using the constants from config/db.php (which we loaded in index.php)
        
    if ($this->db->connect_errno) {
            echo 
    "Connection Failed {$this->db_connection->connect_errno}";
        
    // if no connection errors (= working database connection)
        
    } else {
            
    // escape the POST stuff
            
    $this->user_name $this->db->real_escape_string($_POST['user_name']);
            
    $this->user_password $this->db->real_escape_string($_POST['user_password']);

            
    // database query, getting all the info of the selected user
            
    $sql "SELECT user_name, user_email FROM members WHERE user_name = '{$this->user_name}' AND user_password = '{$this->user_password}'";
            
    $query $this->db->query($sql);

            
    // if the username exists and if the password is a correct match
            
    if ($query !== false) {
                
    $result $query->fetch_object();

                
    $_SESSION['user_name'] = $result->user_name;
                
    $_SESSION['user_logged_in'] = 1;
                
    $_SESSION['user_login_status'] = 1;

                
    setcookie("_time""cookie_value"time() + 3600);
                
    //redirect to this page if the user has logged in successfully
                
    header("Location: testing.php");
                exit;
            } else {
                
    $this->errors[] = "Wrong username password combination";
            } 
    I don't see anything that echoes any user data... only error messages. On successful login it redirects, no real place to echo anything.

  3. #3
    Join Date
    Dec 2013
    Posts
    57
    I was echoing things on the page that you are redirected to after successfully logging in (testing.php).
    I was able to successfully echo the username on the testing.php page but I don't know how to echo the email.
    I want to know how I can get the user_email from my db.

  4. #4
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    This should do the trick
    Code:
    "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'"
    If you plan to use other columns of data from that table (elsewhere in the script) you can add to the SELECT statement (e.g. run one query instead of two).

    I would recommend adding a user_id field (PRIMARY, AUTO INCREMENT) to the table. And use that to pass around in your session data.

  5. #5
    Join Date
    Dec 2013
    Posts
    57
    Quote Originally Posted by ShrineDesigns View Post
    This should do the trick
    Code:
    "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'"
    If you plan to use other columns of data from that table (elsewhere in the script) you can add to the SELECT statement (e.g. run one query instead of two).

    I would recommend adding a user_id field (PRIMARY, AUTO INCREMENT) to the table. And use that to pass around in your session data.
    Shouldn't it be "WHERE user_email = '{$_SESSION['user_name']}'"

    I added this code but it's not been working though I wasn't given any errors

    Code:
     $sql2 = "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'";
                            $query2 = $this->db_connection->query($sql2);
                            $result2 = $query->fetch_object();
                            
                            if ($result2 == true) {
                            $_SESSION['user_email'] = $result2->user_email;
                            }
    On the testing I php I tried echoing $_SESSION['user_email'] but nothing appeared.

  6. #6
    Join Date
    Dec 2013
    Posts
    57
    Also, how would I be able to use a user_id field with my session data.

  7. #7
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    Shouldn't it be "WHERE user_email = '{$_SESSION['user_name']}'"
    Why? You are storing the user_name in the session, so why would user_name be equal to user_email?

    Code:
     $sql2 = "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'";
                            $query2 = $this->db_connection->query($sql2);
                            $result2 = $query->fetch_object();
                            
                            if ($result2 == true) {
                            $_SESSION['user_email'] = $result2->user_email;
                            }
    On the testing I php I tried echoing $_SESSION['user_email'] but nothing appeared.
    Did you initialize the session (i.e. calling session_start())? If you just gonna stuff the user_email into the session data... why not do that in the login part? Seems silly to use two queries when only one is needed...
    Last edited by ShrineDesigns; 05-23-2014 at 10:09 PM.

  8. #8
    Join Date
    Dec 2013
    Posts
    57
    Yes I already did session start. This is what I tried but it still isn't working when I try to echo it. The $_SESSION[user_name] works when echoing on the next page but the $_SESSION[user_email] doesn't work when I try to output it to the next page.

    // escape the POST stuff
    $this->user_name = $this->db_connection->real_escape_string($_POST['user_name']);
    $this->user_password = $this->db_connection->real_escape_string($_POST['user_password']);


    // database query, getting all the info of the selected user
    $sql = "SELECT user_name, user_password, user_email
    FROM members
    WHERE user_name = '{$this->user_name}' AND user_password = '{$this->user_password}'";
    $query = $this->db_connection->query($sql);
    $result = $query->fetch_object();

    $sql2 = "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'";
    $query2 = $this->db_connection->query($sql2);
    $result2 = $query->fetch_object();

    if ($result2 == true) {
    $_SESSION['user_email'] = $result2->user_email;
    }

    // if the username exists and if the password is a correct match
    if (($query->num_rows == 1) && ($this->user_password === $result->user_password)) {

    $_SESSION['user_name'] = $result->user_name;
    $_SESSION['user_logged_in'] = 1;

    $_SESSION['user_login_status'] = 1;


    setcookie("_time", "cookie_value", time() + 3600);


    //redirect to this page if the user has logged in successfully
    header("Location: testing.php");


    }

  9. #9
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    Quote Originally Posted by ShrineDesigns View Post
    Seems silly to use two queries when only one is needed...
    your first query:
    Code:
    "SELECT user_name, user_password, user_email
      FROM members
        WHERE user_name = '{$this->user_name}' AND user_password = '{$this->user_password}'"
    your second query (completely obsolete by the first query, $_SESSION['user_name'] is not set yet):
    Code:
    "SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'"
    (using my previous post as a base)
    PHP Code:
            // escape the POST stuff
            
    $this->user_name $this->db->real_escape_string($_POST['user_name']);
            
    $this->user_password $this->db->real_escape_string($_POST['user_password']);

            
    // database query, getting all the info of the selected user
            
    $sql "SELECT user_name, user_email FROM members WHERE user_name = '{$this->user_name}' AND user_password = '{$this->user_password}'";
            
    $query $this->db->query($sql);

            
    // if the username exists and if the password is a correct match
            
    if ($query !== false) {
                
    $result $query->fetch_object();

                
    $_SESSION['user_name'] = $result->user_name;
                
    $_SESSION['user_email'] = $result->user_email// added user_email onto session data
                
    $_SESSION['user_logged_in'] = 1;
                
    $_SESSION['user_login_status'] = 1;

                
    setcookie("_time""cookie_value"time() + 3600);
                
    //redirect to this page if the user has logged in successfully
                
    header("Location: testing.php");
                exit; 

  10. #10
    Join Date
    Dec 2013
    Posts
    57
    Thanks a lot that works.

  11. #11
    Join Date
    Dec 2002
    Location
    Seattle, WA
    Posts
    1,843
    You're most welcome.
    Glad to help ^_^

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles