PHP Select fields from database where username equals X

Question

im having problems in PHP with selecting Infomation from a database where username is equal to $myusername

I can get it to echo the username using sessions from the login page to the logged in page.

But I want to be able to select things like 'bio' and 'email' from that database and put them into variables called $bio and $email so i can echo them.

This is what the database looks like:

Any ideas?:/

The 'equals' operator is a very strict operator. Are you sure the input is the exact same as the username in the table? And also, could you post some of your code to retrieve database entries? — Rick Slinkman, Jun 29 '13 at 15:32
never store password as plain text. it takes very little effort to hash it, it can be as simple as using password_hash function (if using php5.5+) or including a library such as phpass) — Kris, Jun 29 '13 at 15:48

score 4 · Accepted Answer · edited Jan 12 '17 at 12:31

You should connect to your database and then fetch the row like this:

// DATABASE INFORMATION
$server = 'localhost';
$database = 'DATABASE';
$dbuser = 'DATABASE_USERNAME';
$dbpassword = 'DATABASE_PASSWORD';

//CONNECT TO DATABASE
$connect = mysql_connect("$server", "$dbuser", "$dbpassword")
    OR die(mysql_error());
   mysql_select_db("$database", $connect);
//ALWAYS ESCAPE STRINGS IF YOU HAVE RECEIVED THEM FROM USERS
$safe_username = mysql_real_escape_string($X);
//FIND AND GET THE ROW
$getit = mysql_query("SELECT * FROM table_name WHERE username='$safe_username'", $connect);
$row = mysql_fetch_array($getit);

//YOUR NEEDED VALUES
$bio = $row['bio'];
$email = $row['email'];

Note 1:

Dont Use Plain Text for Passwords, Always hash the passwords with a salt

Note 2:

I used MYSQL_QUERY for your code because i don't know PDO or Mysqli, Escaping in MYSQL is good enought but Consider Using PDO or Mysqli , as i don't know them i can't write the code with them for you

You should consider learning prepared statements... this is a very bad answer — Masivuye Cokile, Jan 12 '17 at 12:41

score 2 · Answer 2 · answered Jun 29 '13 at 17:22

Simplistic PDO examples.

Create a connection to the database.

$link = new PDO("mysql:host=$db_server;dbname=$db_name", $db_user, $db_pw, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Use the $link variable when creating (preparing) and executing your SQL scripts.

$stmt = $link->prepare('insert into `history` (`user_id`) values(:userId)');
$stmt->execute(array(':userId' => $userId));

Code below will read data. Note that this code is only expecting one record (with 2 data elements) to be returned, so I'm storing whatever is returned into a single variable (per data element), $webId and $deviceId.

$stmt = $link->prepare('select `web_id`, `device_id` from `history` where `user_id` = :userId');
$stmt->execute(array(':userId' => $userId));
while($row = $stmt->fetch()) {
    $webId = $row["web_id"];
    $deviceId = $row["device_id"];
}

Sabashan Ragavan · Answer 3 · 2013-06-29T16:21:21.830

From the picture I can see you are using phpMyAdmin - a tool used to handle MySQL databases. You first must make a connection to the MySql server and then select a database to work with. This is shown how below:

<?php
$username = "your_name"; //Change to your server's username
$password = "your_password"; //Change to your server's password
$database = "your_database" //Change to your database name
$hostname = "localhost"; // Change to the location of your server (this will prolly be the same for you I believe tho 

$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

$selected = mysql_select_db($database, $dbhandle)
  or die("Could not select examples");
?>

Then you can write something like this:

<?php
$bio = mysql_query("SELECT bio FROM *your_database_table_name* WHERE username='bob' AND id=1");
?>

and

<?php
$email = mysql_query("SELECT email FROM *your_database_table_name* WHERE username='bob' AND id=1");
?>

Where *your_database_table_name* is the table in the database you selected which you are trying to query.

When I was answering your question, I was referencing this site: http://webcheatsheet.com/PHP/connect_mysql_database.php. So it might help to check it out as well.

PHP Select fields from database where username equals X

3 Answers3

Note 1:

Note 2:

Linked