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 :

PHP Code:

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 :

PHP Code:

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

PHP Code:

'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:

PHP Code:

$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 !

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 !!