从MySQL检索数据-仅登录用户 [英] Retrieving Data from MySQL - only logged in user

问题描述

我现在正在构建一个新项目，并完成了我的登录/注册脚本.到目前为止，它可以正常工作，但是现在我需要一个新功能，而且我不确定应该怎么做.

I am building a new project right now and finished my login/registration script. It is working so far but now I need a new function and I am not sure how exactly I should do that.

如果用户成功登录，则该用户将看到的第一页是他的个人资料.在此页面上，我通过以下查询获取数据:

If a user logged in with success the first page the user will see is his profile. On this page I get my data with the following query:

<?php 
session_start();

if(empty($_SESSION)) // if the session not yet started
   session_start();

if(!isset($_SESSION['email'])) { //if not yet logged in
   header("Location: login.php");// send to login page
   exit;
}

include 'header.php';

$get = "SELECT * FROM user" or die(mysql_error());
$result_get = mysqli_query($connect, $get);
$_SESSION['data'] = mysqli_fetch_assoc($result_get);
 
?>

在我的HTML代码中，我使用以下代码获取数据:

And inside my HTML code I get the data with the following code:

Firstname: <?php echo $_SESSION['data']['firstname']; ?>
Lastname: <?php echo $_SESSION['data']['lastname']; ?>
Username <?php echo $_SESSION['data']['username']; ?>

现在的问题是，我只需要显示当前登录用户的数据即可.现在我的查询是"SELECT * FROM user".但是我想可以将此查询更改为仅从当前登录用户接收到数据的内容.类似于"SELECT * FROM user WHERE SESSION"?！

Problem is now, that I need to show the data only from the user which is currently logged in. Right now my query is "SELECT * FROM user" but I think I can change this query to something, that only the data are received from the currently logged in user. Something like "SELECT * FROM user WHERE SESSION"?!

我不确定如何实现这一目标.

I am not sure how I can achieve that.

推荐答案

您可以这样完成:

<?php
# Store the user input username
if (isset($_SESSION['email']) && strlen($_SESSION['email']) > 0) {
    $email = $_SESSION['email'];
} else {
    // Die the error
    printf('No email address available');
    exit;
}

# Set DB connection details
$DBHost = 'localhost';
$DBUser = 'username';
$DBPass = 'password';
$DBName = 'database';
// Configure error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

# Create a database connection for PHP to use
$link = mysqli_connect($DBHost, $DBUser, $DBPass, $DBName);
// Set encoding type to uft8
mysqli_set_charset($link, 'utf8mb4');

# Query the database
// Build the query
$query = 'SELECT `firstname`,`lastname`,`username` FROM `table` WHERE `email` = ? LIMIT 1 ';
// Prepare it
$stmt = $link->prepare($query);
// Bind in the user input data so as to avoid SQL injection
$stmt->bind_param('s', $email);
// Execute the query
$stmt->execute();
// Bind the results to some variables
$stmt->bind_result($firstname, $lastname, $username);
// Fetch the data
$stmt->fetch();
// Close the query
$stmt->close();

# Build the html
$pageHtml = '
<p>First Name: '.$firstname.'</p>
<p>Last Name: '.$lastname.'</p>
<p>User Name: '.$username.'</p>
';

# Display the html
echo $pageHtml;

进一步阅读

MySQLi手册:

http://php.net/manual/en/book.mysqli.php

关于MySQLi连接:

About MySQLi Connections:

http://php.net/manual/en/mysqli.quickstart.connections.php

关于MySQLi预备语句:

About MySQLi Prepared Statements:

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

关于数据库表索引和何处"使用它们...双关语意:)

About database table indexes and "where" to use them... Pun intended :)

数据库索引如何工作?

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

这篇关于从MySQL检索数据-仅登录用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！