Making a simple client portal mySQL & PHP

General PHP Help
#1

Making a simple client portal mySQL & PHP

Hi all! Newbie here and I’m trying to make a simple client portal using mySQL & PHP but I’ve hit some broader issues that I’m hoping some of you may be able to point me in the right direction.

I run a small building & design company and I want my clients to be able to log into a portal to see the status of their project(s).

On their page, I want 4 sub-pages which go to various stages like

  • design
  • technical
  • building

And within each of those stages, a list of all of the relevant details per stage so for planning for example, they can see

  • the names of the design team
  • the telephone numbers of the design team
  • simple yes/no/tbc for each of the team as to whether they’ve finished their part of the work or not

They won’t need to dynamically interact with any of it, it’s just a basic read-only tracker of theira project.

So far I’ve constructed the basic login/logout mechanism and a landing page, but now I’m stuck.

I have the mSQL basic database with the relevant tables set up ie one for projects, one for clients, one for each of the stages. I’m not sure how I pull information from the database based on the login of the user and their relationship to the information in the tables above.

For the record, I’ve looked at premade CMS systems, project management plugins, premade PHP stuff, but nothing is quite right, which is why I’m trying to do this myself.

Can anyone help/point me in the right direction?

Thanks

Jon

#2

It isn’t a simple task, but it isn’t complex either.

So a user logging in is the project manager?

#3

The user logging in is the client so they can see the status of their project.

I’m currently testing queries at the moment, and while I have managed to connect a page to my database and select & print some results, I’m not sure how I filter those results out based on the user that’s logged in (if that makes sense?)

#4

You would be using join queries that filter on the user id.

#5

This is an example of one I’ve written.

SELECT project.user_id, users.username, project.projectref, project.status, project.site_ad 
FROM users INNER JOIN project ON users.id = project.user_id
WHERE (((project.user_id)=13));

Where it says 13, I want that to be the ‘id’ (user id) from the current session

If I use

<?php echo htmlspecialchars($_SESSION["id"]); ?>

It prints 13 when logged in. How do I link that session id, to the query, where the 13 currently is? I don’t think I can just insert that php statement into the SQL statement.

#6

You would us a prepared query, with a ? place-holder in it for the value, then supply the value when the query gets executed.

If you are not already, you should use the php PDO extension. It is much simpler, taking fewer statements, and more consistent, the result from a prepared and non-prepared query can be treated the same, than the mysqli extension. Use a prepared query to supply dynamic/external/unknown values to the sql query statement. Use implicit binding, by supplying an array of the input values to the ->execute([…]) method call. Use exceptions for errors and in most cases let php catch and handle the exception where it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed or logged the same as php errors.)

#7

I don’t know much about php at all, but I believe what I’m using is procedural. I’m afraid I don’t really understand what you mean otherwise :slightly_frowning_face:

#8

You don’t need to understand OOP, to be able to use the four different PDO statements needed to do this - connection, prepare, execute, and one of the fetch methods. After you have made the connection, in a variable named $pdo, the php code for this task would be -

$sql = "SELECT p.user_id, u.username, p.projectref, p.status, p.site_ad 
FROM users u INNER JOIN project p ON u.id = p.user_id
WHERE p.user_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_SESSION['id']]);
$result = $stmt->fetchAll(); // assuming the query will match a set of data, fetch all the rows into $result
// at this point you can test/loop over $result at the appropriate place in your html document to produce the output that you want.

Note: if you use table alias names in your query, it will shorten and simplify the syntax, reducing the chance of errors.

#9

Noted about aliases, I’ll update it soon!

Thanks…I’ve set it up like yours but it’s not showing anything from the table at all…

<?php
// Initialize the session
session_start();
 
// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
?>
<?php
$servername = "****";
$username = "****";
$password = "****";
$dbname = "****";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>
<p><h2>Which are my projects?</h2></p>
<?php
$sql = "SELECT p.user_id, u.username, p.projectref, p.status, p.site_ad 
FROM users u INNER JOIN project p ON u.id = p.user_id
WHERE p.user_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_SESSION['id']]);
$result = $stmt->fetchAll();

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        
		echo "Project Ref: " . $row["projectref"]. " Project Status: " . $row["status"]. " Project Address: " . $row["site_ad"]. "<br>";
    }
} else {
    echo "You haven't appointed us on any projects yet";
}
?>
<?php
$conn->close();
?>

Where did I go wrong? :confused: (And thanks so much for your help!!)

#10 
?>
<?php

Breaking in and out of PHP is completely unnecessary.

What does happen? Are you redirected to login? Does the “You haven’t appointed us on any projects yet” message appear?

The tutorials you have looked into don’t apply with this, so this code is obsolete.

while($row = $result->fetch_assoc()) {
        
		echo "Project Ref: " . $row["projectref"]. " Project Status: " . $row["status"]. " Project Address: " . $row["site_ad"]. "<br>";
    }

Should be something more like,

foreach($result as $row)
{
    echo "<p>Project Ref: {$row["projectref"]} Project Status: {$row["status"]} Project Address: {$row["site_ad"]}</p>";
}
#11

I tried redoing it so it was all based on PDO, but it still returns nothing (I’ve added a couple of additional fields and a table). It returns the headings in a table, but no data underneath. As soon as I change the ? for 13, it pulls in two projects (which are joined to user #13).

<?php
// Initialize the session
session_start();
 
// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true){
    header("location: login.php");
    exit;
}
echo "<table style='border: solid 1px black;'>";
echo "<tr><th>User ID</th><th>Username</th><th>Project Reference</th><th>Project Status</th><th>Project Address</th></tr>";

class TableRows extends RecursiveIteratorIterator {
    function __construct($it) {
        parent::__construct($it, self::LEAVES_ONLY);
    }

    function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
    }

    function beginChildren() {
        echo "<tr>";
    }

    function endChildren() {
        echo "</tr>" . "\n";
    }
}

$servername = "xxxx";
$username = "xxxx";
$password = "xxxx";
$dbname = "xxxx";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT p.user_id, u.username, p.projectref, p.status, p.site_ad 
						FROM users u INNER JOIN project p ON u.id = p.user_id
						WHERE p.user_id = ?");
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$_SESSION['id']]);
	$result = $stmt->fetchAll();
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?>
#12

That makes me think the session value isn’t there

#13

If I add
echo htmlspecialchars($_SESSION["id"]);
to the code, the page shows “13”. That’s the id of the user logged in, so I’m assuming the value is there?

#14

You have a copy/paste error because you are not doing what was stated in the reply/shown in the code. You are also repeating statements for no apparent reason.

At a minimum, php is not helping you by reporting and displaying all the errors it detects. Find the php.ini that your system is using and set error_reporting to E_ALL and set display_errors to ON. You may need to restart your web server to get any changes made to the php.ini to take effect.

Next, when you make the database connection, in addition to setting the error mode to exceptions, you need to set emulated prepared queries to false, and set the default fetch mode to assoc.

Also, don’t output the raw database statement errors onto your web page. This will only help hackers when they intentionally trigger errors (the connection error information contains your database host name/ip address, connection username, and web server/account path information.) In most cases, there’s no good reason for you to have try/catch logic in your code for database statement errors. If you remove that and let php catch the exception, it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed or logged the same as php errors.) The exception to this rule is when inserting or updating duplicate or out of range user supplied data. In this case, your code should catch the exception, test if the error number is for something your code can handle, then set up a message for the visitor telling them what was wrong with the data they supplied.

Lastly, php will destroy all resources that were created when your script runs, so, you don’t need to close the database connection.

#15

Replace the session value with 13 here rather than the question mark spot.

#16

I’m sorry phdr, I’m not being intentionally stupid! I’m just trying to learn and there’s a lot of parts to coding that I don’t really understand. I don’t think I can change any ini files since I’m directly testing on a server online. The raw database statement errors I will remove when I’ve sorted it, I thought it would just be good for my general troubleshooting at this point in time.

I think I haven’t integrated your code into my updated one correctly. This is where mine is (using the fixed user ID):

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare("SELECT p.user_id, u.username, p.projectref, p.status, p.site_ad 
    							FROM users u INNER JOIN project p ON u.id = p.user_id
    							WHERE p.user_id = 13");
        $stmt->execute();

        // set the resulting array to associative
        $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
        foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
            echo $v;
        }
    }

I’m not sure where yours below goes into mine above…

$sql = "SELECT p.user_id, u.username, p.projectref, p.status, p.site_ad 
FROM users u INNER JOIN project p ON u.id = p.user_id
WHERE p.user_id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_SESSION['id']]);
$result = $stmt->fetchAll();
#17

Sorry Aston, how do I do that?

#18 
$stmt->execute([13]);
#19

Ideally, you should work on your local machine, keep every thing under version control, and deploy what the current working version in the master branch to the live (production) server.

That way you don’t have to remember to remove debugging code or turn off error displays in the script, the environment would handle those details. But it’s a learning process as well.

#20

No. You ALWAYS need error handling for any statement that can fail. On a live/public server, you would LOG all the errors, so that you will know if a visitor managed to do something that causes an error.

If you do what I wrote, the switching between displaying and logging will happen simply based on the php display_errors and log_errors settings.

You would have a ‘local’ php.ini that you can make changes to.

Sponsor our Newsletter | Privacy Policy | Terms of Service