By RRLAG on

First off, I'm not a SQL expert nor am I well versed in Drupal.

Right now I have a basic page built for our users.

<?php
	$query = "SELECT u.name, f.field_user_ldap_givenname_value, l.field_user_ldap_sn_value, i.filename, d.field_user_ldap_department_value, p.field_user_ldap_position_value, u.mail
			FROM users AS u
			LEFT OUTER JOIN field_data_field_user_ldap_givenname AS f ON u.uid=f.entity_id
			LEFT OUTER JOIN field_data_field_user_ldap_sn AS l ON u.uid=l.entity_id
			LEFT OUTER JOIN field_data_field_user_ldap_department AS d ON u.uid=d.entity_id
			LEFT OUTER JOIN field_data_field_user_ldap_position AS p ON u.uid=p.entity_id
			LEFT OUTER JOIN file_managed AS i ON u.picture=i.fid
			LEFT OUTER JOIN field_data_field_user_ldap_location AS b ON u.uid=b.entity_id
			WHERE b.field_user_ldap_location_value='Building Name'
			ORDER BY l.field_user_ldap_sn_value;";

$result = db_query($query);
  
		
foreach ($result as $post) {
?>
<div class="row">
	<div class="span2 pic-teacher">
		<div class="borders">
			<img class="img-rounded shadow" src="/sites/default/files/public/pictures/<?php print $post->filename?>" align="right" onerror="this.src='/sites/all/modules/contrib/media/images/icons/default/image-x-generic.png';"></img>
		</div>
	</div>		
	<div class="span5">
		<h4>
			<?php print $post->field_user_ldap_givenname_value; ?>
			<?php print $post->field_user_ldap_sn_value; ?>
		</h4>
		<h6><em>Department:&nbsp;<?php print $post->field_user_ldap_department_value; ?></em></h6>
		<h6><em>Position:&nbsp; &nbsp; &nbsp;<?php print $post->field_user_ldap_position_value; ?></em></h6>
		<ul class="data-staff">
			<li>
				<i class="icon-phone"></i>+1 234567
			</li>
			<li>
				<a href="#" onClick="window.open('<?php echo recaptcha_mailhide_url ($mailhide_pubkey, $mailhide_privkey, $post->mail); ?>', '', 'toolbar=0,scrollbars=0,location=0,statusbar=0,menubar=0,resizable=0,width=500,height=300'); return false;" title="Reveal this e-mail address">
				<i class="icon-envelope"></i>
				</a>
			</li>
			<li>
				<a href="#">
					<i class="icon-skype"></i>
				</a>
			</li>
			<li><a href="#">
					<i class="icon-linkedin"></i>
				</a></li>
		</ul>
</div>
</div>					<hr></hr>             
<?php
	}

?>

Basically it's a nice list with photo on the left and user details on the right. Now when I get to 30, 40, 50+ people it becomes unbearable to scroll through the entire list. I would love to have a limited number of results with a pager and a search box.

I know that drupal has the pager functionality, but I can only seem to get that to work with individual nodes and db_select which I cannot for the life of me figure out how to do with my obnoxious query. I would love to use the builtin drupal database functionality so that I can do a pager more easily and stay away from standard SQL queries.

Can somebody please point me in the right direction?

Thanks!

⋅ Categories: Drupal 7.x

Comments

Stefan Lehmann’s picture

Stefan Lehmann commented

Is there any particular reason why you don't use the Views module?

Creating a pageable list of users is done in minutes with that.

I like cookies!

Jaypan’s picture

Jaypan commented

You can do this:

$sql = 'FROM {some_table} WHERE some_other_column  = :some_value';
$vars = array(':some_value' => 'something');
$total = db_query('SELECT COUNT(some_column) ' . $sql, $vars)->fetchField();
$per_page = 20; // Items to show per page
$current_page = pager_default_initialize($total, $per_page);
$results = db_query_range('SELECT some_column ' . $sql, $current_page * $per_page, $per_page, $vars);
// do something with your results here

You can then output your pager after your content using either theme():

$pager = theme('pager');

Or as part of a render array

'pager = array
(
  '#theme' => 'pager',
),
RRLAG’s picture

RRLAG commented

The reason I didn't want to use views is that I guess I don't know how to tweak the visual aspect of it as much. Jaypan, your solution worked beautifully!

Here is what I ended up doing:

<?php
			$sql =		'FROM users AS u
						LEFT OUTER JOIN field_data_field_user_ldap_givenname AS f ON u.uid=f.entity_id
						LEFT OUTER JOIN field_data_field_user_ldap_sn AS l ON u.uid=l.entity_id
						LEFT OUTER JOIN field_data_field_user_ldap_department AS d ON u.uid=d.entity_id
						LEFT OUTER JOIN field_data_field_user_ldap_position AS p ON u.uid=p.entity_id
						LEFT OUTER JOIN file_managed AS i ON u.picture=i.fid
						LEFT OUTER JOIN field_data_field_user_ldap_location AS b ON u.uid=b.entity_id
							WHERE b.field_user_ldap_location_value= :building';

$vars = array(':building' => 'Building Name');
$total = db_query('SELECT COUNT(u.uid) ' . $sql, $vars)->fetchField();
$per_page = 10;
$current_page = pager_default_initialize($total, $per_page);
$results = db_query_range('SELECT u.name, f.field_user_ldap_givenname_value, l.field_user_ldap_sn_value, i.filename, d.field_user_ldap_department_value, p.field_user_ldap_position_value, u.mail ' . $sql . ' ORDER BY l.field_user_ldap_sn_value', $current_page * $per_page, $per_page, $vars);
?>
<?php
foreach ($results as $post) {
?>
<div class="row">
	<div class="span2 pic-teacher">
		<div class="borders">
			<img class="img-rounded shadow" src="/sites/default/files/public/pictures/<?php print $post->filename?>" align="right" onerror="this.src='/sites/all/modules/contrib/media/images/icons/default/image-x-generic.png';"></img>
		</div>
	</div>		
	<div class="span5">
		<h4><?php print $post->field_user_ldap_givenname_value; ?> <?php print $post->field_user_ldap_sn_value; ?>
</h4>
<h6><em>Department:&nbsp;<?php print $post->field_user_ldap_department_value; ?></em></h6>
<h6><em>Position:&nbsp; &nbsp; &nbsp;<?php print $post->field_user_ldap_position_value; ?></em></h6>
		<ul class="data-staff">
			<li>
				<i class="icon-phone"></i>+13.225.324.22
			</li>
			<li>
				<a href="#" onClick="window.open('<?php echo recaptcha_mailhide_url ($mailhide_pubkey, $mailhide_privkey, $post->mail); ?>', '', 'toolbar=0,scrollbars=0,location=0,statusbar=0,menubar=0,resizable=0,width=500,height=300'); return false;" title="Reveal this e-mail address">
					<i class="icon-envelope"></i>
				</a>
			</li>
			<li>
				<a href="#">
					<i class="icon-skype"></i>
				</a>
			</li>
			<li><a href="#">
					<i class="icon-linkedin"></i>
				</a></li>
		</ul>
</div>
</div>					<hr></hr>             
<?php
	}

?>
<?php
$pager = theme('pager');
print render($pager);
?>

Thanks again!

Stefan Lehmann’s picture

Stefan Lehmann commented

Re: Views ... Yeah I get that and I have been guilty thinking about this the same way. Be assured it's definitely possible, not too hard and will safe you some headache in the long run. :-)

I like cookies!