Displaying Data to Specific User

Moorcam · April 25, 2018

Hi guys,

I am trying to display data to a user who submitted it. So, user A submits data to the db and also user B. I want it so that user A cannot see user B's data but only the data they have submitted. Hope that makes sense.

Here is the code along with the table of data to be displayed.

                       <div class="panel-body">
                            <div class="table-responsive">
                                <table class="table table-striped table-bordered table-hover" id="bookings_table">
                                    <thead>
                                        <tr>
                                            <th class="text-center">Issue ID</th>
                                            <th class="text-center">Driver Name</th>
                                            <th class="text-center">Date Submitted</th>
                                            <th class="text-center">Fleet Number</th>
                                            <th class="text-center">Issue</th>
                                            <th class="text-center">Description</th>
                                            <th class="text-center">Priority</th>
                                            <th class="text-center">Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>
<?php
$check = isset($_SESSION['username']);
if($stmt = $link -> prepare("SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username = ?")) {
        $stmt -> execute();
        $stmt -> bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
        while($stmt->fetch()) {
        ?>
                                        <tr class="odd gradeX">
                                            <td class="text-center"><?php echo $issue_id; ?></td>
                                            <td class="text-center"><?php echo $driver_name; ?></td>
                                            <td class="text-center"><?php echo $submit_date; ?></td>
                                            <td class="text-center"><?php echo $fleet_number; ?></td>
                                            <td class="text-center"><?php echo $issue_name; ?></td>
                                            <td class="text-center"><?php echo $issue_description; ?></td>
                                            <td class="text-center"><?php echo $issue_priority; ?></td>
                                            <?php if($issue_status == "Pending")
                                            {
                                        ?>
                                            <td class="text-center warning"><?php echo $issue_status;?></td>
                                            <?php }else if($issue_status == "Open"){ ?>
                                            <td class="text-center danger"><?php echo $issue_status; ?></td>
                                            <?php }else if($issue_status == "Repaired"){ ?>
                                            <td class="text-center success"><?php echo $issue_status ;} ?></td>
                                             
                                        </tr>
<?php
        }
        $stmt -> close();
}
mysqli_close($link);
?>

If I remove the WHERE clause, it displays data from both users. Placing the WHERE clause shows no data.

Can someone have a look and see where I am going wrong and maybe point me in the right direction?

Many thanks in advance.

Barand · April 25, 2018

You need to provide a value for that "?" placeholder (bind_param() )

Moorcam · April 25, 2018

You need to provide a value for that "?" placeholder (bind_param() )

Thanks Barand.

I have added $username to the bind_result and still showing blank.

Barand · April 25, 2018

But you stored the username in $_SESSION['username'], not $username

$check = isset($_SESSION['username']);

and you need to use bind_param() (as already stated), and not add it to the bind_result()

Moorcam · April 25, 2018

But you stored the username in $_SESSION['username'], not $username

and you need to use bind_param() (as already stated), and not add it to the bind_result()

Ok. Have done this. Still same result:

<?php
$check = isset($username);
if($stmt = $link -> prepare("SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username=?")) {
    $stmt->bind_param('s',$username);
        $stmt -> execute();
        $stmt -> bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
        while($stmt->fetch()) {
        ?>

Barand · April 25, 2018

Have you checked that $username contains the correct value? You ignore the value in $check so you aren't sure it even exists.

Moorcam · April 25, 2018

Have you checked that $username contains the correct value? You ignore the value in $check so you aren't sure it even exists.

I know I am doing it wrong but for the life of me can't figure out the correct way. Searched google etc and anything I find that is even close to it doesn't work.

I am checking the username as was suggested on another website (can't even find that now lol) because I am trying to display information that the logged in user submitted and only display that specific information and not show that user information that was submitted by another user.

Barand · April 25, 2018

Have you tried even the most basic of debugging techniques?

What do the following output?

echo $username . '<br>';
echo $_SESSION['username'] . '<br>';

Moorcam · April 25, 2018

Have you tried even the most basic of debugging techniques?

What do the following output?
echo $username . '<br>';
echo $_SESSION['username'] . '<br>';

That is bloody strange. I have echo $_SESSION['username']; in the header and in index.php and they work. This page has everything that index.php has and the same header.php required to it and it is not showing?

EDIT

It shows the username if placed outside of the sql statement. So obviously there is an issue with the code.

Edited April 25, 2018 by DanEthical

Barand · April 25, 2018

Have you callled session_start() at the top of this page?

Moorcam · April 25, 2018

Have you callled session_start() at the top of this page?

Yep, it's on all pages from header.php

Moorcam · April 25, 2018

I have even changed it to use the OOP style statement from here: http://php.net/manual/en/mysqli.prepare.php

Still the same.

cyberRobot · April 25, 2018

Does the script work if you replace the username variable with a hard coded value?

ginerjm · April 25, 2018

I took your code and tried to make it more complete. Note - it is highly recommended to NOT CONJOIN your html code and your php code. Keep them separate and just used php vars in the html code where you want the dynamically generated results to show up.

$check = isset($_SESSION['username']);
// WHAT IS THE POINT OF THE ABOVE STATEMENT IF YOU NEVER ANALYZE THE RESULT?

$q = "SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username = ?";
//  CREATE THE QUERY STATEMENT ITSELF BY ITSELF!

$stmt = $link->prepare($q);
if(!$stmt)
{
	//	YOU NEED TO HANDLE THE POSSIBILITY OF FAILURE HERE
	ECHO 'PREPARE STATEMENT FAILED';
	EXIT();
}
ELSE
{
    // YOU NEED A BIND STATEMENT HERE TO ASSIGN A VALUE TO THAT ? PARAMETER.
	if (!$stmt->execute())
	{
		//  HANDLE FAILURE OF THE EXECUTE HERE
		ECHO 'EXECUTE STATEMENT FAILED';
		EXIT();
	}
	ELSE
	{
		$stmt->bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
		while($stmt->fetch()) 
		{
			// NOTE HOW THE USE OF HEREDOCS (LOOK IT UP!) MAKES THIS CODE EASIER TO READ AND WRITE
			$code=<<<heredocs
			<tr class="odd gradeX">
			<td class="text-center">$issue_id</td>
			<td class="text-center">$driver_name</td>
			<td class="text-center">$submit_date</td>
			<td class="text-center">$fleet_number</td>
			<td class="text-center">$issue_name</td>
			<td class="text-center">$issue_description</td>
			<td class="text-center">$issue_priority</td>
			// NOTE THAT THE 'heredocs;' line MUST be in column 1.
heredocs;
			echo $code;
			if($issue_status == "Pending")
			{
				echo "<td class='text-center warning'>$issue_status</td>";
			}
			else if($issue_status == "Open")
			{
				echo "<td class='text-center danger'>$issue_status</td>";
			}
			else if($issue_status == "Repaired")
			{
				echo "<td class='text-center success'>$issue_status</td>";
			}
			echo '</tr>';
		}
		$stmt->close();	// NOT NECESSARY?
		mysqli_close($link);	// NOT NECESSARY
	}
}

// THIS SHOULD ALL BE AT THE START OF YOUR WORK. THEN ADD THE OUTPUT OF THE HTML PAGE BELOW HERE. THAT MEANS INSTEAD OF USING ECHO ABOVE ALL OF THE GENERATED RESULTS S/B ASSIGNED TO A PHP VAR THAT IS THEN PLACED IN THE HTML BELOW.

Have you looked at the php manual under mysqli to see examples of how to do this very simple process?

Moorcam · April 25, 2018

I took your code and tried to make it more complete. Note - it is highly recommended to NOT CONJOIN your html code and your php code. Keep them separate and just used php vars in the html code where you want the dynamically generated results to show up.

$check = isset($_SESSION['username']);
// WHAT IS THE POINT OF THE ABOVE STATEMENT IF YOU NEVER ANALYZE THE RESULT?

$q = "SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username = ?";
//  CREATE THE QUERY STATEMENT ITSELF BY ITSELF!

$stmt = $link->prepare($q);
if(!$stmt)
{
	//	YOU NEED TO HANDLE THE POSSIBILITY OF FAILURE HERE
	ECHO 'PREPARE STATEMENT FAILED';
	EXIT();
}
ELSE
{
    // YOU NEED A BIND STATEMENT HERE TO ASSIGN A VALUE TO THAT ? PARAMETER.
	if (!$stmt->execute())
	{
		//  HANDLE FAILURE OF THE EXECUTE HERE
		ECHO 'EXECUTE STATEMENT FAILED';
		EXIT();
	}
	ELSE
	{
		$stmt->bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
		while($stmt->fetch()) 
		{
			// NOTE HOW THE USE OF HEREDOCS (LOOK IT UP!) MAKES THIS CODE EASIER TO READ AND WRITE
			$code=<<<heredocs
			<tr class="odd gradeX">
			<td class="text-center">$issue_id</td>
			<td class="text-center">$driver_name</td>
			<td class="text-center">$submit_date</td>
			<td class="text-center">$fleet_number</td>
			<td class="text-center">$issue_name</td>
			<td class="text-center">$issue_description</td>
			<td class="text-center">$issue_priority</td>
			// NOTE THAT THE 'heredocs;' line MUST be in column 1.
heredocs;
			echo $code;
			if($issue_status == "Pending")
			{
				echo "<td class='text-center warning'>$issue_status</td>";
			}
			else if($issue_status == "Open")
			{
				echo "<td class='text-center danger'>$issue_status</td>";
			}
			else if($issue_status == "Repaired")
			{
				echo "<td class='text-center success'>$issue_status</td>";
			}
			echo '</tr>';
		}
		$stmt->close();	// NOT NECESSARY?
		mysqli_close($link);	// NOT NECESSARY
	}
}

// THIS SHOULD ALL BE AT THE START OF YOUR WORK. THEN ADD THE OUTPUT OF THE HTML PAGE BELOW HERE. THAT MEANS INSTEAD OF USING ECHO ABOVE ALL OF THE GENERATED RESULTS S/B ASSIGNED TO A PHP VAR THAT IS THEN PLACED IN THE HTML BELOW.

Have you looked at the php manual under mysqli to see examples of how to do this very simple process?

Thank you so much for this and the explanation. Means a lot.

I have put it in and it says "PREPARED STATEMENT FAILED".

ginerjm · April 25, 2018

That's a start. And it points out WHY you should always CHECK that things worked.

So - are all of your query fields spelled correctly? Is there a field called 'username' in the table?

And if you have actually setup a new script to utilize this code, why don't you POST it so we can see what you are REALLY RUNNING?

mac_gyver · April 25, 2018

EDIT

It shows the username if placed outside of the sql statement. So obviously there is an issue with the code.

define: outside the sql statement?

there's nothing in the posted code that would affect the value or changes the program's variable scope. you probably need to post all the code that would be needed to reproduce the problem for anyone here to help find the problem.

do you have php's error_reporting set to E_ALL and display_errors set to ON, so that php would help you by reporting and displaying all the errors it detects?

mac_gyver · April 25, 2018

// YOU NEED TO HANDLE THE POSSIBILITY OF FAILURE HERE

or if you use exceptions to handle database statement (connection/query/prepare/execute) errors, and let php catch and handle the error, you don't have to added a bunch of logic in your code at all.

Moorcam · April 25, 2018

No, username is not in this table. I am trying to get the data that username has submitted. I am presuming that having $_SESSION['username'] will help identify what user submitted it?

Sure. Here is the whole file including the rewritten code from ginerjm:

<?php
require_once('templates/header.php');
require_once('templates/sidebar.php');
?>

        <div id="page-wrapper" >
            <div id="page-inner">
                <div class="row">
                    <div class="col-md-12">
                     <h2><i class="fa fa-wrench"></i> Maintenance Requests </h2>
                     <h5>Issues are dealt with in priority order and are removed from the system three months after repair. </h5>
                    </div>
                </div>
                 <!-- /. ROW  -->
                 <hr />
            <div class="row">
                <div class="col-md-12">
                     <!-- Advanced Tables -->
                    <div class="panel panel-default">
                        <div class="panel-heading">
                             <i class="fa fa-info"></i> Listed below are the Maintenance Requests you have submitted and their current status.
                        </div>
                        <div class="panel-body">
                            <div class="table-responsive">
                                <table class="table table-striped table-bordered table-hover" id="bookings_table">
                                    <thead>
                                        <tr>
                                            <th class="text-center">Issue ID</th>
                                            <th class="text-center">Driver Name</th>
                                            <th class="text-center">Date Submitted</th>
                                            <th class="text-center">Fleet Number</th>
                                            <th class="text-center">Issue</th>
                                            <th class="text-center">Description</th>
                                            <th class="text-center">Priority</th>
                                            <th class="text-center">Status</th>
                                        </tr>
                                    </thead>

                                    <tbody>
<?php
$check = isset($_SESSION['username']);
// WHAT IS THE POINT OF THE ABOVE STATEMENT IF YOU NEVER ANALYZE THE RESULT?

$sql = "SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username = ?";
//  CREATE THE QUERY STATEMENT ITSELF BY ITSELF!

$stmt = $link->prepare($sql);
if(!$stmt)
{
// YOU NEED TO HANDLE THE POSSIBILITY OF FAILURE HERE
ECHO 'PREPARE STATEMENT FAILED';
EXIT();
}
ELSE
{
    // YOU NEED A BIND STATEMENT HERE TO ASSIGN A VALUE TO THAT ? PARAMETER.
if (!$stmt->execute())
{
//  HANDLE FAILURE OF THE EXECUTE HERE
ECHO 'EXECUTE STATEMENT FAILED';
EXIT();
}
ELSE
{
$stmt->bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
while($stmt->fetch()) 
{
$code=<<<heredocs
<tr class="odd gradeX">
<td class="text-center">$issue_id</td>
<td class="text-center">$driver_name</td>
<td class="text-center">$submit_date</td>
<td class="text-center">$fleet_number</td>
<td class="text-center">$issue_name</td>
<td class="text-center">$issue_description</td>
<td class="text-center">$issue_priority</td>
heredocs;
echo $code;
if($issue_status == "Pending")
{
echo "<td class='text-center warning'>$issue_status</td>";
}
else if($issue_status == "Open")
{
echo "<td class='text-center danger'>$issue_status</td>";
}
else if($issue_status == "Repaired")
{
echo "<td class='text-center success'>$issue_status</td>";
}
echo '</tr>';
}
}
}
?>
                                    </tbody>

                                </table>
                                <a href="submit-request.php"><button class="btn-default">New Request</button></a>
                            </div>
                            
                        </div>
                    </div>
                    <hr>
                    <!--End Advanced Tables -->
                </div>
            </div>
            </div>
                <!-- /. ROW  -->

    </div>
             <!-- /. PAGE INNER  -->
            </div>
         <!-- /. PAGE WRAPPER  -->
        </div>
     <!-- /. WRAPPER  -->
<?php
require_once('templates/footer.php');
?>

Thanks guys.

Edited April 25, 2018 by DanEthical

cyberRobot · April 25, 2018

No, username is not in this table.

So there's no column in the database which ties the user's data to a specific user? If not, that will need to be your next step.

Moorcam · April 25, 2018

So there's no column in the database which ties the user's data to a specific user? If not, that will need to be your next step.

The username is tied to the logged in user. $username is in a session in the users table.

cyberRobot · April 25, 2018

Is there a column in the maintenance_requests table which ties a record to the user? If so, the query needs to be modified to use that column. I imagine that means you'll need to pull something from the users table, like a user ID, so that you can get the users records from maintenance_requests.

Moorcam · April 25, 2018

Got it.

You guys are awesome.

On submitting the form to add a new Maintenance Request, I have added an extra row for username. This tells the request who is logged in and what data to display.

	$sql = "INSERT INTO `maintenance_requests`(`driver_name`, `driver_email`, `fleet_number`, `speedo_reading`, `submit_date`, `issue_name`, `issue_description`, `issue_status`, `issue_priority`,`username`) VALUES ('".$driver_name."','".$driver_email."', '".$fleet_number."','".$speedo_reading."','".$submit_date."','".$issue_name."', '".$issue_description."', 'Open', 'Medium','".$username."')";
	$link->query($sql) or die("Cannot Insert");//update or error
	}

Then, displaying the data:

$sql = "SELECT issue_id, driver_name, submit_date, fleet_number, issue_name, issue_description, issue_priority, issue_status FROM maintenance_requests WHERE username = '" . $_SESSION['username'] . "'";
//  CREATE THE QUERY STATEMENT ITSELF BY ITSELF!

$stmt = $link->prepare($sql);
if(!$stmt)
{
	//	YOU NEED TO HANDLE THE POSSIBILITY OF FAILURE HERE
	ECHO 'PREPARE STATEMENT FAILED';
	EXIT();
}
ELSE
{
    // YOU NEED A BIND STATEMENT HERE TO ASSIGN A VALUE TO THAT ? PARAMETER.
	if (!$stmt->execute())
	{
		//  HANDLE FAILURE OF THE EXECUTE HERE
		ECHO 'EXECUTE STATEMENT FAILED';
		EXIT();
	}
	ELSE
	{
		$stmt->bind_result($issue_id, $driver_name, $submit_date, $fleet_number, $issue_name, $issue_description, $issue_priority, $issue_status);
		while($stmt->fetch()) 
		{
			$code=<<<heredocs
			<tr class="odd gradeX">
			<td class="text-center">$issue_id</td>
			<td class="text-center">$driver_name</td>
			<td class="text-center">$submit_date</td>
			<td class="text-center">$fleet_number</td>
			<td class="text-center">$issue_name</td>
			<td class="text-center">$issue_description</td>
			<td class="text-center">$issue_priority</td>
heredocs;
			echo $code;
			if($issue_status == "Pending")
			{
				echo "<td class='text-center warning'>$issue_status</td>";
			}
			else if($issue_status == "Open")
			{
				echo "<td class='text-center danger'>$issue_status</td>";
			}
			else if($issue_status == "Repaired")
			{
				echo "<td class='text-center success'>$issue_status</td>";
			}
			echo '</tr>';
		}
	}
}
?>

WHERE username = '" . $_SESSION['username'] . "'";

Is what was needed.

I have logged in as two different users and it only displays the data they have submitted.

Thank you all so much. Major obstacle overcome.

ginerjm · April 25, 2018

That would be "an extra column".

Now you need to revert this query back to a prepared one so you avoid un-filtered input being used.

Edited April 25, 2018 by ginerjm

Moorcam · April 25, 2018

You cannot reference a name that is NOT defined in the tables being used in the query. Doh!

hahaha Yep, this deserves Best Answer LOL, silly Irish boy here. Well, learnt something new. Thanks again.

Sign In

Displaying Data to Specific User

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information