Display category selected multi type records

8 posts by 2 authors in: Forums > CMS Builder
Last Post: December 6, 2010   (RSS)

By ht1080z - November 30, 2010

Hi,

I have two tables (sections), one category type [cat_items] & one multi type [used_items].
the [cat_items] contains static data and displayed in the list page (test.php) where every listed item showing by side how many item counted from the [used_items]. (the page is for used-trucks and different accessories)

http://vournas.gr/test.php

After choosing category, on the detail page i want to display in the main div all [used items] where the selected category is founded.

test.php: for listing the categories and the counted record numbers from [used_items]
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'bcmAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/vournas/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($cat_itemsRecords, $cat_itemsMetaData) = getRecords(array(
'tableName' => 'cat_items',
'loadUploads' => '0',
'allowSearch' => '0',
));

// load records
list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'loadUploads' => '0',
'allowSearch' => '0',
));

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Vournas Test</title>
<link href="css/styles.css" rel="stylesheet" type="text/css" media="screen">
</head>

<body>

<div class="container">
<div class="sidebar1">
<ul class="nav">
<?php $cat_count=array();
foreach($used_itemsRecords as $used_item){
@$cat_count[$used_item['item']]++;
}
?>
<?php foreach ($cat_itemsRecords as $record): ?>
<li><a href="<?php echo $record['_link'] ?>"><?php echo $record['name']; ?>&nbsp;(<?php echo intval(@$cat_count[$record['name']]); ?>)</a></li>
<?php endforeach ?>
</ul>
<!-- end .sidebar1 --></div>
<div class="content">
<!-- end .content --></div>
<!-- end .container --></div>
</body>
</html>

test_details.php: for listing the categories and display all the counted records from [used_items]

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'bcmAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/vournas/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($cat_itemsRecords, $cat_itemsMetaData) = getRecords(array(
'tableName' => 'cat_items',
'where' => whereRecordNumberInUrl(1),
'loadUploads' => '0',
'limit' => '1',
));
$cat_itemsRecord = @$cat_itemsRecords[0]; // get first record

$urlNum = getNumberFromEndOfUrl();

// load records
list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => $item = $urlNum,
'loadUploads' => '0',
'allowSearch' => '0',
));
$used_itemsRecord = @$used_itemsRecords[0]; // get first record

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Vournas Test</title>
<link href="css/styles.css" rel="stylesheet" type="text/css" media="screen">
</head>
<body>

<div class="container">
<div class="sidebar1">
<ul class="nav">
<?php $cat_count=array();
foreach($used_itemsRecords as $used_item){
@$cat_count[$used_item['item']]++;
}
?>
<?php foreach ($cat_itemsRecords as $record): ?>
<li><a href="<?php echo $record['_link'] ?>"><?php echo $record['name']; ?>&nbsp;(<?php echo intval(@$cat_count[$record['name']]); ?>)</a></li>
<?php endforeach ?>
</ul>
<!-- end .sidebar1 --></div>
<div class="content">
<h1><?php echo $used_itemsRecord['item'] ?></h1><br />
<p><?php echo $cat_itemsRecord['content'] ?></p>


<!-- end .content --></div>
<!-- end .container --></div>
</body>
</html>

in the test_record.php i made to show only one category and wrong record from the [used_items].

Can anybody advise me on this. See also attached the 2 tables in screenshot for field details.

Thank you in advance,
Attachments:

cat_items_001.jpg 94K

used_items_001.jpg 94K

Re: [ht1080z] Display category selected multi type records

By Jason - November 30, 2010

Hi,

The problem is in your getRecords call in test_details.php. Try this:

$urlNum = getNumberFromEndOfUrl();

// load records
list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => "item='".intval($urlNum)."'",
'loadUploads' => '0',
'allowSearch' => '0',
));

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [ht1080z] Display category selected multi type records

By Jason - December 1, 2010

Hi,

I've taken a look at your code and I've noticed a couple of things. Firstly it seems that the 'item' field in used items is storing the categories name, not the number, but we're passing in the number in the url string. Here is how we can get around this.

In test_details.php, we first have to change our query that gets the categories to always return all categories.
Try this:
list($cat_itemsRecords, $cat_itemsMetaData) = getRecords(array(
'tableName' => 'cat_items',
'allowSearch' => false,
'loadUploads' => false,
));

Right after this we need to create an array that stores all the categories name by their record num:
$catNumToName = array();
foreach($cat_itemsRecords as $cat){
$catNumToName[$cat['num']]=$cat['name'];
}

We can now do our search for the records that have the selected category:

$urlNum = getNumberFromEndOfUrl();
// load records
list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => "item='".mysql_escape($catNumToName[$urlNum])."'",
'loadUploads' => '0',
'allowSearch' => '0',
));

Next, we can change how we output our categories so that we keep an accurate count:

<ul class="nav">
<?php foreach ($cat_itemsRecords as $record): ?>
<?php $where = "item ='".mysql_escape($catNumToName[$record['num']])."'";?>
<li><a href="<?php echo $record['_link'] ?>"><?php echo $record['name']; ?>&nbsp;(<?php echo mysql_select_count_from('used_items',$where);?>)</a></li>
<?php endforeach ?>
</ul>

Finally, you can output the used items that have been returned. The variable $used_itemRecords will hold all the record from used_items that have the category that was selected.

Give this a try and let me know if you run into any issues.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Display category selected multi type records

By ht1080z - December 3, 2010

Hi Jason,

Thank you for the above great help, i already implemented and added additional code to display the records in the selected category in the main DIV.

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php


// load viewer library
$libraryPath = 'bcmAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/vournas/public_html/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

list($cat_itemsRecords, $cat_itemsMetaData) = getRecords(array(
'tableName' => 'cat_items',
'allowSearch' => false,
'loadUploads' => false,
));

$catNumToName = array();
foreach($cat_itemsRecords as $cat){
$catNumToName[$cat['num']]=$cat['name'];
}

$urlNum = getNumberFromEndOfUrl();

// load records
list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => "item='".mysql_escape($catNumToName[$urlNum])."'",
'loadUploads' => true,
'allowSearch' => '0',
));
$used_itemsRecord = @$used_itemsRecords[0]; // get first record

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Vournas Test</title>
<link href="css/styles.css" rel="stylesheet" type="text/css" media="screen">
</head>
<body>

<div class="container">
<div class="sidebar1">
<ul class="nav">
<?php foreach ($cat_itemsRecords as $record): ?>
<?php $where = "item ='".mysql_escape($catNumToName[$record['num']])."'";?>
<li><a href="<?php echo $record['_link'] ?>"><?php echo $record['name']; ?>&nbsp;(<?php echo mysql_select_count_from('used_items',$where);?>)</a></li>
<?php endforeach ?>
</ul>
<!-- end .sidebar1 --></div>
<div class="content">
<h1><?php echo $used_itemsRecord['item'] ?></h1><br />
<ul class="inav">
<?php foreach ($used_itemsRecords as $record) : ?>
<?php foreach ($record['photos'] as $upload) ?>
<li><a href="<?php echo $record['_link'] ?>"><img src="<?php echo $upload['thumbUrlPath'] ?>" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" />&nbsp;&nbsp;<?php echo $record['model']; ?>&nbsp;&nbsp;(<?php echo $record['year']; ?>)&nbsp;&nbsp;<?php echo $record['price']; ?>&#8364;</a></li>
<?php endforeach ?>
</ul>
<!-- end .content --></div>
<!-- end .container --></div>
</body>
</html>

Now i try to create the last detail page in the row, where is only the selected record from the selected category records appear. I created a new test_itemdetails.php, but there is a problem:

1.) In the URL i get new NUM from the page test_details.php (which is correct, the NUM of the selected record from $used_itemsRecords) and its useless for displaying the record.
We already used the $urlNum = getNumberFromEndOfUrl(); for counting the records for the left category menu and its needed on all pages.
Can i post the selected record NUM in other way to the test_itemdetails.php?

Another one is just issue: when we select category without any record in it, of course just open a blank page. There is any way to ignore this and the page remain the same?

Link: http://vournas.gr/test.php

Any suggestions would be appreciated.
Thank you in advance,

Re: [ht1080z] Display category selected multi type records

By Jason - December 3, 2010

Hi,

This shouldn't be a problem. In test_details.php, the number we were passing was a "category" number and we were using it to get ALL the used_items records under that category. We aren't actually using those records as a count (our count is done for each category using sql).

Here is what we had in test_details.php

list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => "item='".mysql_escape($catNumToName[$urlNum])."'",
'loadUploads' => true,
'allowSearch' => '0',
));

To pull out just the record whose record number is in the URL we change the code to this:

list($used_itemsRecords, $used_itemsMetaData) = getRecords(array(
'tableName' => 'used_items',
'where' => "num='".mysql_escape($urlNum)."'",
'loadUploads' => true,
'allowSearch' => '0',
'limit' => 1,
));

So the single record is stored in $used_ItemsRecords.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Display category selected multi type records

By ht1080z - December 5, 2010

Jason,

That works great! Thank you! [:)]

Also i wanted to add two more feature to the project.

1. Search in the [used_items] in selected fileds. I added the form code to TEST.PHP:
<form method="POST" action="search_details.php">
<input type="text" name="model_keyword,year,description_keyword" value="">
<input type="submit" name="submit" value="Search">
</form>
but no additional help i found in the http://www.interactivetools.com/docs/cmsbuilder/viewer_search.html how exactly the search string posted and the search will be done in the search_details.php page.

2. How can i create a dropdown select menu with values of the [cat_items] and make additional way to choose category and behave like the left category menu?

Please advise,

Re: [ht1080z] Display category selected multi type records

By Jason - December 6, 2010

Hi,

CMS Builder can automatically do the search using the getRecords function.
http://www.interactivetools.com/docs/cmsbuilder/viewer_options.html

Just make sure you DON'T use
'allowSearch' => 0,

and the search will be performed.

As for creating a drop down, you could try something like this.


<form name="catSearch" id="catSearch" method="get" action="test_details.php">
<select name="cat_item" onchange="document.catSearch.submit();">
<?php echo getSelectOptionsFromTable('cat_items','num','name','',true);?>
</select>
</form>

Selecting a value from the drop down should trigger a form submit.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/