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 09: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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.13656 seconds
  • Memory Usage 3,019KB
  • Queries Executed 13 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (6)bbcode_code
  • (3)bbcode_php
  • (4)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (11)postbit
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (71):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates