Jump to content

Displaying Data to Specific User


Moorcam

Recommended Posts

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.

Link to comment
Share on other sites

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()) {
        ?>
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

 

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.