retrieving data from mysql db and displaying

@ytesfay80May 23.2014

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

[code=php] $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”; } [/code]

to post a comment

PHP

10 Comments(s) _↴

@ShrineDesignsMay 23.2014 — #I revised your code a bit (I suggest storing/comparing passwords [U]after[/U] hashing them, i.e. SHA-1, SHA-2, SHA-3)

[code=php]	$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";
   }
 [/code]

I don't see anything that echoes any user data... only error messages. On successful login it redirects, no real place to echo anything.

@ytesfay80authorMay 23.2014 — #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.

@ShrineDesignsMay 23.2014 — #This should do the trick"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 [I]user_id[/I] field (PRIMARY, AUTO INCREMENT) to the table. And use that to pass around in your session data.

@ytesfay80authorMay 23.2014 — #This should do the trick"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 [I]user_id[/I] field (PRIMARY, AUTO INCREMENT) to the table. And use that to pass around in your session data.[/QUOTE]

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;
   }
 [/CODE]

On the testing I php I tried echoing $_SESSION['user_email'] but nothing appeared.

@ytesfay80authorMay 23.2014 — #Also, how would I be able to use a user_id field with my session data.

@ShrineDesignsMay 24.2014 — #Shouldn't it be "WHERE user_email = '{$_SESSION['user_name']}'"[/QUOTE]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;
   }
 [/CODE]

On the testing I php I tried echoing $_SESSION['user_email'] but nothing appeared.[/QUOTE]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...

@ytesfay80authorMay 24.2014 — #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");
 
 
   }

@ShrineDesignsMay 24.2014 — #Seems silly to use two queries when only one is needed...[/QUOTE]
your first query:

"SELECT user_name, user_password, [B]user_email[/B]
 FROM members
 WHERE user_name = '{$this-&gt;user_name}' AND user_password = '{$this-&gt;user_password}'"

your second query (completely obsolete by the first query, $_SESSION['user_name'] is not set yet):"SELECT user_email FROM members WHERE user_name = '{$_SESSION['user_name']}'"
(using my previous post as a base)

[code=php]        // 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;
 [/code]

@ytesfay80authorMay 25.2014 — #Thanks a lot that works.

@ShrineDesignsMay 25.2014 — #You're most welcome.

Glad to help ^{^_^{^}}

Also in #PHP _↴

help reformatting form "textarea" data in PHP.saving file locally and remotely at the same time using PHP PHP-->XML, works in IE and Firefox but not Safari

Success!

Help @ytesfay80 spread the word by sharing this article on Twitter...

Tweet This

retrieving data from mysql db and displaying

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

retrieving data from mysql db and displaying

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴