Outputting in multiple rows but with pagenav as well ??

[cinq]

I have a table for data items, cars stored in the database.

I want to have a page which lists all the cars stored in the database ( thumbnails of the cars ).

How am I to go about outputting them in a single page, in rows of 5 thumbnails, for a total of 4 rows ( ie. 20 per page ) and then have page navigation ( ie. each page has 20 thumbnails ).


How do I go about limiting the sql queries in this case ?

The hack I am working on is based on magnus's vgarage.

The current code now stands at :


if ($_REQUEST['do'] == 'list')
{
globalize($_REQUEST, array('pagenumber' => INT, 'perpage' => INT));

$perpage = intval($perpage);

if ($perpage == 0 or $perpage > 200)
{
$perpage = 20;
}

if (intval($pagenumber) == 0)
{
$pagenumber = 1;
}

$limitlower = ($pagenumber - 1) * $perpage + 1;
$limitupper = ($pagenumber) * $perpage;
$counter = 0;

$carcount = $DB_site->query_first("
SELECT COUNT(*) AS cars FROM " . TABLE_PREFIX . "vbgarage_users
");

$numberpages = $carcount['cars'] / $perpage;
$numberpages = ceil($numberpages);

if (!isset($pagenumber) or ($pagenumber < 1) or ($pagenumber > $numberpages))
$pagenumber = 1;

$pos = ($pagenumber - 1) * $perpage;

$result_list = $DB_site->query("SELECT * FROM vbgarage_users ORDER BY lastactivity DESC LIMIT $pos,$perpage ");
$counter = 0;

while ($list = $DB_site->fetch_Array($result_list) AND $counter++ < $perpage)
{
$list['lastactivity'] = vbdate($vboptions['dateformat'],$list['lastactivity'],true);
$user = fetch_userinfo($list[userid]);
$cid = $list['carid'];
$result_list2 = $DB_site->query("SELECT * FROM vbgarage_comments WHERE carid=$cid");
$numcomments = mysql_num_rows($result_list2);
$result_list1 = $DB_site->query("SELECT * FROM vbgarage_images WHERE carid=$cid ORDER BY imageid DESC LIMIT 1");

while ($list1 = $DB_site->fetch_Array($result_list1))
{
eval('$latestbits .= "' . fetch_template('vbgarage_latestbits') . '";');
}
}

$pagenav = construct_page_nav($carcount[cars],"vbgarage.php?$session[sessionurl]do=$_REQUEST[do]&perpage=$perpage");
eval('print_output("' . fetch_template('vbgarage_listgarage') . '");');
}

[cinq]

The latestbits template looks like this :


<td class="alt2" colspan="1" align="center"><a href="$vboptions[bburl]/vbgarage.php?do=view&amp;id=$list1[carid]"><img src="$vboptions[bburl]/vbgarage.php?do=thumb&amp;width=100&amp;id=$list1[imageid]" border=0 /></a><br>
<span class="smallfont">
<b>$user[username]</b>
<br>
$numcomments comments<br>
Updated: $list[lastactivity]
</span></td>

[cinq]

So right now I have uploaded 6 cars with their respective images, however it starts to show in one whole row ( since per page is set to 20 ).

I would prefer it to show as 5 cars in a first row and 1 in a 2nd row.

[Velocd]

A quick reminder first.

globalize() automatically casts the variables you pass it with the type from the keyword.

e.g.

'pagenumber' => INT

So there is no reason afterwords to use intval().

The following chunk of code is from my vBulletin Member Album hack. It has a page browse system, and also sorting options, like DESC or ASC, by username, etc.

You should be able to use this script as a guideline for making yours work:


$perpage = $vboptions['album_perpage'];
$pertr = $vboptions['album_pertr'];

globalize($_GET, array(
'sort' => STR,
'order' => STR,
'ltr' => STR,
'page' => INT
));



// Verify arguments
///////////////////////



$type = $_REQUEST['type'] ? $_REQUEST['type'] : 1;

if ($sort)
{
$valid_sort = array(
'posts',
'joindate',
'dateline',
'username'
);

if (!in_array($sort, $valid_sort))
$sort = 'posts';
}
else
{
$sort = 'posts';
}

if ($order)
{
$valid_order = array(
'desc',
'asc'
);

if (!in_array($order, $valid_order))
$order = 'desc';
}
else
{
$order = 'desc';
}



// Build letter list
////////////////////////



$currentletter = '#';
$linkletter = urlencode('#');

eval('$letterbits = "' . fetch_template('album_letter') . '";');

for ($i=65; $i < 91; $i++)
{
$currentletter = chr($i);
$linkletter = &$currentletter;
$show['selectedletter'] = iif($ltr == $currentletter, true, false);

eval('$letterbits .= "' . fetch_template('album_letter') . '";');
}

if ($ltr != '')
{
if ($ltr == '#')
{
$condition = " AND username NOT REGEXP(\"^[a-zA-Z]\")";
}
else
{
$ltr = chr(intval(ord($ltr)));
$condition = ' AND username LIKE("' . addslashes_like($ltr) . '%")';
}

$leftjoin = " LEFT JOIN ".TABLE_PREFIX."user USING (userid)";
}



// Query: count rows
////////////////////////



$result = $DB_site->query_first("
SELECT COUNT(*) AS count
FROM ".TABLE_PREFIX."".($type == 2 ? 'usertextfield' : 'customprofilepic')."$leftjoin
WHERE ".($type == 2 ? "signature!=''" : 'visible=1')."$condition
");



// Build page scope
///////////////////////



$pagenumber = $page > 0 ? $page : 1;

$rows = $result['count'];

$pages = ceil($rows / $perpage);

if ($pagenumber < 1)
{
$pagenumber = 1;
}
else if ($pagenumber > $rows)
{
$pagenumber = $rows;
}

$minlimit = ($pagenumber - 1) * $perpage+1;
$maxlimit = ($pagenumber) * $perpage;

if ($maxlimit > $rows)
{
$maxlimit = $rows;

$minlimit = $minlimit > $rows ? $rows-$perpage : $minlimit;
}

$minlimit = $minlimit <= 0 ? 1 : $minlimit;

$pagenav = construct_page_nav(
$rows,
"album.php?$session[sessionurl]type=$_REQUEST[type]",
"&amp;pp=$perpage&ltr=$ltr&sort=$sort&order=$order"
);



// Request: profile picture
///////////////////////////////



if ($type == 1)
{
// Query: fetch profile pictures
////////////////////////////////////


$images = $DB_site->query("
SELECT user.userid, dateline,
comment, user.username, user.usertitle,
user.joindate, user.usergroupid
FROM ".TABLE_PREFIX."customprofilepic
LEFT JOIN ".TABLE_PREFIX."user USING (userid)
WHERE visible=1
$condition
ORDER BY $sort $order
LIMIT " . ($minlimit-1) . ", $perpage
");

$type = 'image';

$count = 1;
$rows = $DB_site->num_rows($images);

if (!$rows)
{
eval('$album_list = "' . fetch_template('album_'.$type.'_list') . '";');
}
else
{
while ($image = $DB_site->fetch_array($images))
{
$image['musername'] = fetch_musername($image);

$image['comment'] = fetch_censored_text($image['comment']);

eval('$album_list .= "' . fetch_template('album_image_list') . '";');

if ($count == $pertr && $count != $rows)
$count = 0;

$count++;
}

while ($count != 1 && $count <= $pertr)
{
eval('$album_list .= "' . fetch_template('album_image_list') . '";');

$count++;
}
}
}


Some of the code in there, like the letters portion and $type variable, don't apply in your case.

Use the $perpage and $pertr variables to control how many images are shown per page, and how many of that page are shown per row.

I use the following template with vB conditionals to show the images.

album_image_list


<if condition="$rows">
<if condition="$count == 1">
<tr>
</if>

<if condition="$image">
<td width="25%" class="<if condition="$count%2 == 0">alt1<else />alt2</if>" valign="top">
<table cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top" style="width: 1px; padding-left: 20px"><a href="member.php?$session[sessionurl]u=$image[userid]"><img src="./image.php?u=$image[userid]&type=pthumb" border="0" /></a></td>
<td valign="top" style="padding-left: 20px"><a style="font-size:14pt" href="member.php?$session[sessionurl]u=$image[userid]">$image[musername]</a><br /><span class="smallfont">$image[usertitle]<br /><br /><i>$image[comment]</i></span></td>
</tr>
</table>

</td>
<else />
<td width="25%" class="alt1"></td>
</if>

<if condition="$count == $pertr">
</tr>
</if>
</if>


Hope this helps.

[cinq]

This is so cool, i think this is more or less working, will test it further.
I love you Velo ! :D:D

Oh, I have another question though,
In the query I make with the left join,


The situation is this, in my vbgarage_images table, there can be a few images tied to a particular car ( cid ).
But I only wanted to select ONE image for a particular car, how do I go about this ?

Is there a way to insert a DINSTINCT in the sql statement ?

[cinq]

Ok i placed a GROUP BY u.cid in the sql statement and this proceeds to show just ONE thumbnail per car.
However, the page navi is now not accurate.

How can i rectify this ?
Pretty confused here. :(

[cinq]

Ok i got it working
i think haha

Thanks again Velo !! :D