OOP Login - Accessing the database

[gregan]

Hey guys,

 

New to the forums. Came for some help and a better understanding!

 

So I have completed and watched the entire OOP Login tutorial and I have began trying to implement my own features.

 

To avoid confusion - I have renamed the tutorial's members/v_members to main/v_main.. And am using the members/v_members for this users page.

 

I am working on creating a members page where you can see all of the registered users. My issue is with accessing the database and how I should be doing this.

 

a) How can I successfully complete this task and cleanup my code?

$stmt = "SELECT id, username, email, userlevel FROM users"; global $Database;

Should this line be in v_members or members?

c) I think my issue is with my foreach loop, but I am not sure how to correct this.

 

 

 

members.php

<?php

include("includes/database.php");
include("includes/init.php");

$Template->load("views/v_members.php");

 

v_members.php

<div>

	<?php
		$alerts = $this->getAlerts();
		if ($alerts != '') { 
			echo '<ul class="alerts">' . $alerts . '</ul>'; 
		}
	?>

	<?php
		//Get users
		$stmt = "SELECT id, username, email, userlevel FROM users";
		global $Database;
	?>

	<div class="row">
		<?php foreach ($Database->query($stmt) as $row) { ?>
			ID: <?php echo $row['id']; ?> <br />
			User: <?php echo $row['username']; ?> <br />
			Email: <?php echo $row['email']; ?> <br />
		<?php } ?>
	</div>

 

My page results are as follows. I only have 1 entry in the database.

ID: 
User: 
Email: 
ID: 
User: 
Email: 
ID: 
User: 
Email: 
ID: 
User: 
Email: 
ID: 
User: 
Email: 

[falkencreative]

I would start by structuring your code a bit differently. I would create a new model (perhaps called "m_users.php"?) and create a method within the model that will return the database query results. Your controller will call the method, store the results in a variable, and pass the variable to the view using $Template's setData() method.

 

The way you are trying to query the database is incorrect above (and that code, as I mentioned above, doesn't belong in the view.) Just creating a variable to hold the SQL statement isn't enough -- you also need to call mysqli's query() function. Take a look at m_auth.php's validateLogin() method to see how to properly access the database object. Since I don't think my OOP Login tutorial shows a simple query without the WHERE statement, take a look at my CRUD tutorial here for more information about using MySQLi to query a database. http://www.killersites.com/community/index.php?/topic/3064-basic-php-system-view-edit-add-delete-records-with-mysqli/ (specifically look at view.php)

[gregan]

Hey thanks for the great response... Below is my attempt at following your suggestion. Although I have run into error while running the setData on the query(), which is because it's looking for a string.

 

edit: Also, is it correct having the loop occur in the view? I just began thinking about having the loop in the controller, then each time through set somehow set a two d array or use the setData in a loop? $data->setData['username'] = $row->username; I am not sure how to do get them in the view though, inside another loop?

 

members.php

<?php

include("includes/database.php");
include("includes/init.php");

$Template->setData('query', $Users->getAllUsers());

$Template->load("views/v_members.php");

 

v_members.php

<?php
			 if ($result = $this->getData['query'])
                       {
                               // display records if there are records to display
                               if ($result->num_rows > 0)
                               {
                                       // display records in a table
                                       echo "<table border='1' cellpadding='10'>";

                                       // set table headers
                                       echo "<tr><th>ID</th><th>Username</th><th>Email</th><th>User Level</th><th></th><th></th></tr>";

                                       while ($row = $result->fetch_object())
                                       {
                                               // set up a row for each record
                                               echo "<tr>";
                                               echo "<td>" . $row->id . "</td>";
                                               echo "<td>" . $row->username . "</td>";
											echo "<td>" . $row->email . "</td>";
                                               echo "<td>" . $row->userlevel . "</td>";
                                               echo "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>";
                                               echo "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>";
                                               echo "</tr>";
                                       }

                                       echo "</table>";
                               }
                               // if there are no records in the database, display an alert message
                               else
                               {
                                       echo "No results to display!";
                               }
                       }
			?>

 

m_users.php

<?php

/*
Users Class
*/

class Users {

/*
	Constructor
*/
function __construct() {
}

/*
	Functions
*/
function getAllUsers() {
	global $Database;
	$results = $Database->query("SELECT id, username, userlevel, email FROM ". TBL_USERS ."");
	return $results;
}

}

[falkencreative]

Looks like you just about have this in place.

 

You can fix your setData() issue by changing that function slightly:

 

	function setData($name, $value, $clean = true)
{
	if ($clean)
	{
		$this->data[$name] = htmlentities($value, ENT_QUOTES);
	}
	else
	{
		$this->data[$name] = $value;
	}
}

This way, if you update the setData line:

 

$Template->setData('query', $Users->getAllUsers(), false);

You shouldn't run into the issue where the htmlentities() function is looking for a string.

 

One other alternative way to do it would be to move your loop inside your controller. Rather than using the setData function on $Users->getAllUsers(), you would create a temporary variable, add the data to the temporary variable, and then pass that temporary variable to the view. Then, in your view, you'd just need to echo out the variable. Something like this:

 

$str = '';
if ($result = $Users->getAllUsers())
                       {
                               // display records if there are records to display
                               if ($result->num_rows > 0)
                               {
                                       // display records in a table
                                       $str .= "<table border='1' cellpadding='10'>";

                                       // set table headers
                                       $str .= "<tr><th>ID</th><th>Username</th><th>Email</th><th>User Level</th><th></th><th></th></tr>";

                                       while ($row = $result->fetch_object())
                                       {
                                               // set up a row for each record
                                               $str .= "<tr>";
                                               $str .= "<td>" . $row->id . "</td>";
                                               $str .= "<td>" . $row->username . "</td>";
                                               $str .= "<td>" . $row->email . "</td>";
                                               $str .= "<td>" . $row->userlevel . "</td>";
                                               $str .= "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>";
                                               $str .= "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>";
                                               $str .= "</tr>";
                                       }

                                       $str .= "</table>";
                               }
                               // if there are no records in the database, display an alert message
                               else
                               {
                                       $str .= "No results to display!";
                               }
                       }
                       $Template->setData('query', $str);

Should you have the loop in the controller, or in the view? I can't really say which is the "right" way to approach this, since I have heard arguments for both. Personally, as long as the HTML I am generating is relatively simple, I think I prefer keeping the loop within the controller so the view stays as clean as possible.

[gregan]

Hey thanks so much for helping me understand the process.

 

I have made the changes and have it successfully working. I have personally decided to keep the loop in the view for now just for personal preference. I understand why it may be better to have in the controller, I just feel that some pages may become to complex to continue to do this. For example, a homepage that may have to show recent posts, recent members, recent photos, etc... I think it may get a bit too complex in the controller and feel the loop would be better in the view.

 

 

Here is my final code for reference for my files.

 

<?php

include("includes/database.php");
include("includes/init.php");

$Template->setData('query', $Users->getAllUsers(), false);

$Template->load("views/v_members.php");

 

<div class="row">
			 <?php
			if ($result = $this->getData('query')) {
				if ($result->num_rows > 0)
				{
						echo "<table border='1' cellpadding='10'>";
						echo "<tr><th>ID</th><th>Username</th><th>Email</th><th>User Level</th><th></th><th></th></tr>";

						while ($row = $result->fetch_object())
						{
								echo "<tr>";
								echo "<td>" . $row->id . "</td>";
								echo "<td>" . $row->username . "</td>";
								echo "<td>" . $row->email . "</td>";
								echo "<td>" . $row->userlevel . "</td>";
								echo "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>";
								echo "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>";
								echo "</tr>";
						}

						echo "</table>";
				}
				else
				{
						echo "No results to display!";
				}
			}
			?>

	</div>

 

function setData($name, $value, $clean = TRUE) {
	if($clean) {
		$this->data[$name] = htmlentities($value);
	} else {
		$this->data[$name] = $value;
	}
}

 

class Users {

/*
	Constructor
*/
function __construct() {
}

/*
	Functions
*/
function getAllUsers() {
	global $Database;
	$results = $Database->query("SELECT id, username, userlevel, email FROM ". TBL_USERS ."");
	return $results;
}
}

[gregan]

One more question... If I want to bind a value first before returning the query... How do I successfully return this?

 

Thanks

 

 

function getUserInfo($id) {
	global $Database;
	$stmt = $Database->query("SELECT * FROM ". TBL_USERS ." WHERE id = ?");
	$stmt->bind_param("i", $id);
	$stmt->execute();
	return $stmt;
}

[falkencreative]

My MySQLi CRUD code available here http://www.killersites.com/community/index.php?/topic/3064-basic-php-system-view-edit-add-delete-records-with-mysqli/ has an example of using bind_param (about 1/2 of the way down in records.php. I believe you need to use the bind_result() function to retrieve the results, and you could probably have getUserInfo() return an array of the results, which you could then pass to your view using setData().

[gregan]

Hey, I attempted your suggestion and am not getting any success. I am not even sure if I am calling my array correctly in the 3rd code box, but it does not seem to be storing anything at all in $rows.

 

Any advice would be appreciated.

 

Greg

 

function getUserInfo($id) {
	global $Database;
	$stmt = $Database->prepare("SELECT username, userlevel, email FROM ". TBL_USERS ." WHERE id = ?");
	$stmt->bind_param('i', is_numeric($id));
	$results = $stmt->execute();
	$stmt->store_result();
	$stmt->bind_result($username, $userlevel, $email);

	$rows = array();
	while ($stmt->fetch()) {   
	   $newrow = array(); 
	   $newrow['id'] = $id; 
	   $newrow['username'] = $username;  
	   $newrow['userlevel'] = $userlevel; 
	   $newrow['email'] = $email; 
	   $rows[] = $newrow; 
	} 

	if (count($rows) == 0) {
		return false;
	} else {
		return $rows;
	}
}

 

$Template->setData('query', $User->getUserInfo(2), false);

 

 

if ($result = $this->getData('query')) {

			echo $result[0]['username'];
}

[falkencreative]

I'm pretty sure you need to be using the is_numeric function differently. is_numeric() returns true or false, so your query probably looks like this:

 

"SELECT username, userlevel, email FROM ". TBL_USERS ." WHERE id = true"

 

The rest of the code looks about correct, so after you fix the is_numeric issue, it's just a matter of debugging.

 

Keep in mind you can use "exit;" to cancel any further progress of PHP, so, for example, you could edit your getUserInfo() function to echo out one of the temporary variables to check that it is working as it should, and then stop the rest of the program with exit.

[gregan]

Ughhhh, stupid mistake by me on the is_numeric. Everything works correctly after I fixed that.

 

Thanks you really assisted me a lot in this process.

 

Is your "build a CMS" MVC OOP? I am interested and knowing a bit more about this video course.

http://www.killervideostore.com/video-courses/build-a-cms.php

[falkencreative]

Yes, it is. It's based off the OOP Login series, actually, so you have actually started quite a bit of the CMS already.

 

You can take a look at the preview video here:

 

 

for an overview of what I cover in the course. There are also quite a few free sample videos on the KillerPHP Youtube Account -- just "see all" link to view all of the videos that Stefan has uploaded, and then search for "CMS". http://www.youtube.com/user/killerphp

 

The series is either available for premium subscribers to the KillerSites Video Library http://killersites.com/university or as a stand-alone purchase: http://killervideostore.com/video-courses/build-a-cms.php

[gregan]

Ben,

 

I hope to purchase this in the near future, looks like it could help me a lot... Thanks

 

I was successful in getting the getUserInfo($id) class to work successfully, I have now run into another challenge while creating a page for a unique id. I am trying to figure out how I can figure out a way to know if the $id being viewed is the $id of the user currently logged in.

 

$Template->setData('loggedin', $Auth->checkLoginStatus(), false);

 

I am able to get the login status, although not the logged in id. I am thinking the best way to perform this would be to set a $_SESSION['id'], in the validateLogin function? And then check if $_GET['id'] == $_SESSION['id']?

 

Also most likely would be useful to store username and userlevel into a $_SESSION?

 

Thanks

Greg

[falkencreative]

I am able to get the login status, although not the logged in id. I am thinking the best way to perform this would be to set a $_SESSION['id'] in the validateLogin function? And then check if $_GET['id'] == $_SESSION['id']?

Yes, that sounds about right.

[gregan]

Sorry about the thread kind of evolving as I continue to develop off of the tutorial of the OOP Login.

 

I was successful in the process of setting the sessions for a id, username, and level. I figured I would post the code for it that I have below.

 

While working on this, I realized that it may be most efficient to create class variables(var $id, var $username, var $email, & var $userlevel, etc...) in my User class (m_user.php) and set them whenever a user is logged in or out. So that I can easily access the currently logged in User's information while in the User class with $this-> rather then accessing the SESSION everytime I need the id or say... LastName of the currently logged in user. Just putting my thoughts out there as I work on expanding this CMS a bit to improve on managing the users & userlevels, typing it out helps me think about it.

 

edit: as well as possibly being an easier way to track active users & active guests.

 

function validateLogin($user, $pass) {
	global $Database;
	if($stmt = $Database->prepare("SELECT id, username, userlevel, email FROM ". TBL_USERS ." WHERE username = ? AND password = ?")) {
		$stmt->bind_param("ss", $user, md5($pass . $this->salt));
		$stmt->execute();
		$stmt->store_result();
		$stmt->bind_result($id, $username, $userlevel, $email);
		//Logged in, or Not Logged In
		if($stmt->num_rows > 0) {
			while($stmt->fetch()) {
				$_SESSION['loggedin'] = TRUE;
				$_SESSION['id'] = $id;
				$_SESSION['username'] = $username;
				$_SESSION['userlevel'] = $userlevel;
				$_SESSION['email'] = $email;
			}

			$stmt->close();
			return TRUE;
		} else {
			$stmt->close();
			$_SESSION['username'] = GUEST_NAME;
			$_SESSION['userlevel'] = GUEST_LEVEL;
			return FALSE;
		}
	} else {
		die("ERROR: Could not prepare MySQLi statement.");
	}
}

[falkencreative]

The challenging thing with using class variables is that you'll have to create them every time your page loads, since your objects get recreated from scratch on every page load (unless you are doing something that stores your objects in the session?). This would mean you'd have an extra database call to get the user information on every page load. Using a session variable may still be your best bet.