Attributes v2 search lookup

My supervisor wants me to create an advanced drop down based search that will search over attributes on products and categories. I suggested creating this using the attributes built into zen cart but he was against it, saying it was too slow and cited a site as example where they did it that way and failed. So he basically wants me to redo this functionality... but if there is another way to do it or if I can convince him that it will work well using attributes than I'd like to do it that way.

Here is what I have so far:

class dynamic_fields(){
	var $link;
	function __construct(){
		$this->link = mysql_connect(DB_SERVER, DB_SERVER_USERSNAME, DB_SERVER_PASSWORD);
		if (!$link) {
		    die('Could not connect: ' . mysql_error());
		}
		#echo 'Connected successfully';
		mysql_query('use ' . DB_DATABASE)
	}
	function __destruct(){
		mysql_close($this->link);
	}
	function admin_view_edit(){

	}
	function admin_view_admin(){
	}
	function admin_view_products_edit(){
	}
	function admin_products_create(){
	}
	function admin_categories_create(){
	}
	function admin_categories_delete(){
	}
}

and my sql:

create table products_fields (
	id int unsigned not null,
	type_id int unsigned not null, -- 0 = dropdown 1 = text
	name varchar(35) not null,
	primary key(id),
	index (name)
);
create table product_cat_values (
	id int unsigned not null,
	products_fields_id int unsigned not null,
	category_id int unsigned not null,
	value varchar(35)
	primary key(id),
	index (value),
	index (products_fields_id,category_id)
);
create table product_cat_assignment (
	id int unsigned not null,
	products_id int unsigned not null default 0,
	category_id int unsigned not null default 0,
	products_fields_id int unsigned not null,
	primary key(id),
	index (products_id,category_id,products_fields_id)
);

If you can let me know your thoughts and opinions so I can turn this into a usable module for the community that'd be nice. I'd like to use the db system in place. And I'd like to take at least a cursory consideration for language.

Anywho. That site he showed me was incredibly slow, and it wasn't fault of the server, so if anyone can let me know if that is because the way they developed it or it is an attributes system flaw, that'd be cool. I don't know of a better way to do it the database isn't done how i'd do it but I'd have to redo the entire schema to make that work better. And going through and editing every categories_edit etc page would be a pain, having to do it after every update even more so.
I have been looking into the auto_loader etc. But to be honest, any documentation I've been able to find still leaves unanswered questions.

I'm going to post updates when I can get it from my laptop today.

How in the world can I edit a post? Forum permissions say I can edit my posts but there's no button for doing it?

Regardless here's more work. It's not complete yet but getting there.

<?php
class product_fields{
	function __construct(){
	}
	function __destruct(){
	}
	function admin_view_edit(){
		global $db;
		$output = array();
		$output[]="<b>Edit this Categories Product's fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$sql = "select pf.name,pfa.id from product_fields_assign pfa, product_fields pf where pfa.category_id=$category_id and pfa.product_fields_id=pf.id group by pf.id";
		$p = $db->Execute($sql);
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="</select><br><b>Remove Fields</b><br><select name='remove fields'>";
		$p->MoveFirst();
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="</select></form>";
		return $output;
	}
	function admin_view_admin(){
		global $db;
		$output = array();
		$output[]="<b>Admin Product Fields -- Add</b>
			<form action='' method='POST'>
			Name: <input type='text' name='name'><br>
			Type: <select name='type'>
				<option value=0>Dropdown</option>
				<option value=1>Text</option>
			</select><br>
			<br><input type='submit' value='Add'>
			</form>
		";
		$output[]="<b>Admin Product Fields -- Edit</b>
			<form action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			Type: <select name='type'>
				<option value=0>Dropdown</option>
				<option value=1>Text</option>
			</select><br>
			Name: <input type='text' name='name'><br>
			<input type='submit' value='Update'><br>
			</form>
		";
		$output[]="<b>Admin Product Fields -- Delete</b>
			<form action='' method='POST'>
			Field: <select name='id'>
			</select>
			<input type='submit' value='Delete'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Add</b>
			<form action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			Value to Add: <input type='text' name='value'><br>
			<input type='submit' value='Add'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Edit</b>
			<form action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			A New Value: <input type='text' name='value'><br>
			<input type='submit' value='Edit'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Delete</b>
			<form action='' method='POST'>
			Field: <select name='id'>
			</select>
			Field Value: <select name='id'>
			</select>
			<input type='submit' value='Delete'>
			</form>
		";
		$sql = "select pf.name,pfa.id,pv.value from product_fields_assign pfa, product_fields pf, product_values pv where pfa.category_id=$category_id and pv.products_fields_id=pf.id and pfa.product_fields_id=pf.id";
		$p = $db->Execute($sql);
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="<b>Edit Product Fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$output[]="<b>Delete Product Fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$output[]="</select></form>";
		return $output;
	}
	function admin_view_products_edit(){
		global $db;
	}
	function admin_products_create($product_id,$category_id){
		/*global $db;
		$sql = "select * from product_fields where category_id=$category_id";
		$p = $db->Execute($sql);
		while(!$p->EOF
			$sql = "insert into product_fields_assign values(NULL,NULL,$category_id,".$p->fields['id'].")";
			$i = $db->Execute($sql);
		}*/
	}
	function admin_categories_delete(){
		global $db;
	}
}
?>

create table product_fields (
    id int unsigned not null auto_increment,
    type_id int unsigned not null, -- 0 = dropdown 1 = text
    name varchar(35) not null,
    primary key(id),
    index (name)
);
create table product_values (
    id int unsigned not null auto_increment,
    products_fields_id int unsigned not null,
    value varchar(35)
    primary key(id),
    index (value),
    index (products_fields_id,category_id)
);
create table product_fields_assign (
    id int unsigned not null auto_increment,
    products_id int unsigned not null default 0,
    category_id int unsigned not null default 0,
    products_fields_id int unsigned not null,
    primary key(id),
    index (products_id,category_id,products_fields_id)
);  

Edit time limits, for the lose.

Here's another update, feedback is muchly appreciatory.

<?php
class product_fields{
	function __construct(){
	}
	function __destruct(){
	}
	function admin_view_edit(){
		global $db;
		$output = array();
		$output[]="<b>Edit this Categories Product's fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$sql = "select pf.name,pfa.id from product_fields_assign pfa, product_fields pf where pfa.category_id=$category_id and pfa.product_fields_id=pf.id group by pf.id";
		$p = $db->Execute($sql);
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="</select><br><b>Remove Fields</b><br><select name='remove fields'>";
		$p->MoveFirst();
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="</select></form>";
		return $output;
	}
	function admin_view_admin(){
		global $db;
		$output = array();
		$output[]=<<<SCRIPT
<script type="javascript">
var updateids = Array('fadd','fedit','fdelete','vadd','vedit','vdelete');
var commandurl =  'includes/productfields.php?c=';
function sendCmnd(){
	var url = encodeURIComponent(commandurl + this.id);
	new Ajax.Request(url, {
	  method: 'post',
	  onSuccess: function(transport) {
	    var notice = $('notice');
	    notice.update(transport.responseText);
	  }
	});

}
function init(){
	for(i in updateids){
		updateids[i].onSubmit=sendCmnd;
	}
}
onload=init;
</script>
SCRIPT;
		$output[] = "<div id='notice'></div>";
		$output[]="<b>Admin Product Fields -- Add</b>
			<form id='fadd' action='' method='POST'>
			Name: <input type='text' name='name'><br>
			Type: <select name='type'>
				<option value=0>Dropdown</option>
				<option value=1>Text</option>
			</select><br>
			<br><input type='submit' value='Add'>
			</form>
		";
		$output[]="<b>Admin Product Fields -- Edit</b>
			<form id='fedit' action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			Type: <select name='type'>
				<option value=0>Dropdown</option>
				<option value=1>Text</option>
			</select><br>
			Name: <input type='text' name='name'><br>
			<input type='submit' value='Update'><br>
			</form>
		";
		$output[]="<b>Admin Product Fields -- Delete</b>
			<form id='fdelete' action='' method='POST'>
			Field: <select name='id'>
			</select>
			<input type='submit' value='Delete'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Add</b>
			<form id='vadd' action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			Value to Add: <input type='text' name='value'><br>
			<input type='submit' value='Add'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Edit</b>
			<form id='vedit' action='' method='POST'>
			Field: <select name='id'>
			</select><br>
			A New Value: <input type='text' name='value'><br>
			<input type='submit' value='Edit'>
			</form>
		";
		$output[]="<b>Admin Product Values -- Delete</b>
			<form id='vdelete' action='' method='POST'>
			Field: <select name='id'>
			</select>
			Field Value: <select name='id'>
			</select>
			<input type='submit' value='Delete'>
			</form>
		";
		$sql = "select pf.name,pfa.id,pv.value from product_fields_assign pfa, product_fields pf, product_values pv where pfa.category_id=$category_id and pv.products_fields_id=pf.id and pfa.product_fields_id=pf.id";
		$p = $db->Execute($sql);
		while(!$p->EOF){
			$output[]="<option value='".$p->fields['id']."'>".$p->fields['name']."</option>";
			$p->MoveNext();
		}
		$output[]="<b>Edit Product Fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$output[]="<b>Delete Product Fields</b><form action='' method='POST'><br>Add Fields<br><select name='addfield'>";
		$output[]="</select></form>";
		return $output;
	}
	function admin_view_products_edit($products_id){
		global $db;
		//Select Categories
		$sql = "select categories id where products_id=$products_id";
		$r = $db->Execute($sql);
		$categories = array();
		$ouput='';
		while(!$r->EOF){
			$categories[] = $r->fields('categories_id');
			$r->MoveNext();
		}
		//Select the list of fields applying to this product or category
		foreach($categories as $k=>$v){
			$sql = "select p.*,v.* from product_fields p, product values v, product_fields_assign where p.id=v.products_fields_id and p.id=pv.products_fields_id and (pv.productsid=$products_id or pv.categories_id=$categories_id)";
			$r = $db->Execute($sql);
			$type = get_type_input_text($r->fields('type_id'));
			while(!$r->EOF){
				$sql = "select * product_fields_state where id={$r->fields('id')}";
			}
			$output.="{$r->fields('name')}: <input type='$type' name='id_{$r->fields('id')}' value='{$r->fields('value')}'><br>";
			if($type==0){
				$sql = "select * from product_fields p, product";
				$e = $db->Execute($sql);
				$output.="<select name=''>";
				while(!$e->EOF){
					$output.="<option value='type'></option>";
				}
				$output.="</select>";
			}
		}
		$output.="<input type='submit' value='submit'>";
		return $output;
	}
	function get_type_input_text($type_id){
		$type='';
		switch ($type_id){
		case 0:
			$type='dropdown';
			break;
		case 1:
			$type='text';
			break;
		}
		return $type;
	}
	function admin_fields_list($categories_id,$products_id=null){
		global $db;
		$sql = "select * from product_fields_assign where products_id=$products_id or categories_id=$categories_id'";
		$r = $db->Execute($sql);
		$output = '';
		while(!$r->EOF){
			$sql = "select * from product_fields where id=".$r->fields('id');
			$e = $db->Execute($sql);
			$ouput.="<option value='{$e->fields('id')}'>{$e->fields('name')}</option>";
			$r->MoveNext();
		}
		return $output;
	}
	function admin_values_list($product_fields_id){
		global $db;
		$sql = "select * from product_values where products_fields_id=$products_fields_id)";
		$r = $db->Execute($sql);
		$output = '';
		while(!$r->EOF){
			$ouput.="<option value='{$r->fields('id')}'>{$r->fields('value')}</option>";
			$r->MoveNext();
		}
		return $output;
	}
	function admin_fields_add(){
		global $db;
		$sql = "insert into product_fields values(NULL,$_POST[type_id],'$_POST[name]')";
		$r = $db->Execute($sql);
	}
	function admin_fields_edit(){
		global $db;
	}
	function admin_fields_delete(){
		global $db;
	}
	function admin_values_add(){
		global $db;
	}
	function admin_values_edit(){
		global $db;
	}
	function admin_values_delete(){
		global $db;
	}
}
?>

create table product_fields (
    id int unsigned not null auto_increment,
    type_id int unsigned not null, -- 0 = dropdown 1 = text
    name varchar(35) not null,
    primary key(id),
    index (name)
);
create table product_values (
    id int unsigned not null auto_increment,
    products_fields_id int unsigned not null,
    value varchar(35)
    primary key(id),
    index (value),
    index (products_fields_id,category_id)
);
create table product_fields_state (
    id int unsigned not null auto_increment,
    products_id int unsigned not null,
    product_fields_id int unsigned not null,
    product_value_id int unsigned not null default 0,
    text_value varchar(255) not null default '',
    primary key(id),
    index(products_id,product_fields_id,product_value_id),
    index(text_value)
)
create table product_fields_assign (
    id int unsigned not null auto_increment,
    products_id int unsigned not null default 0,
    categories_id int unsigned not null default 0,
    products_fields_id int unsigned not null,
    primary key(id),
    index (products_id,category_id,products_fields_id)
);  

You can certainly use Zen built in attributes to do that, and it can be fast.

Here is part of the code I used:

        $options_ids = zen_db_input(implode("','", $options_ids));
        global $db;
        $products_ids = $db->Execute("SELECT products_id, options_values_id FROM ".TABLE_PRODUCTS_ATTRIBUTES." WHERE options_values_id IN($options_ids)");
        $attributes = array();
        while(!$products_ids->EOF){
            $attributes[$products_ids->fields['options_values_id']][] = $products_ids->fields['products_id'];
            $products_ids->MoveNext();
        }
        
        $result = array();
        foreach($attributes as $row){
            if(empty($result))
                $result = $row;
            else 
                $result = array_intersect($result, $row);
        }
        
        $products_ids = implode("','", $result);
        
        $where = "p.products_id IN('$products_ids')";

BTW, my code was part of a larger module to:
Select all products in a certain category that have all the attributes selected by user(such as yellow, XL, new tshirts)

I initially tried to use many select statements in 1 single query, but that killed my database. This version uses 2 sql queries but is way faster.

Hope it helps you in some way.

Thanks Yellow 1912, I'll check that out :)

By the way, is this module available to the public?

Thanks,
clindauer

clindauer:

Thanks Yellow 1912, I'll check that out :)

By the way, is this module available to the public?

Thanks,
clindauer
Not yet, but will be soon ^_^. I still have some clean up and checking to do.

heh, cool.
Thanks!

Wow, that will nice option. If that will be what i want, i will send you some donation...

yellow1912:

You can certainly use Zen built in attributes to do that, and it can be fast.

Here is part of the code I used:

    $options_ids = zen_db_input(implode("','", $options_ids));
    global $db;
    $products_ids = $db->Execute("SELECT products_id, options_values_id FROM ".TABLE_PRODUCTS_ATTRIBUTES." WHERE options_values_id IN($options_ids)");
    $attributes = array();
    while(!$products_ids->EOF){
        $attributes[$products_ids->fields['options_values_id']][] = $products_ids->fields['products_id'];
        $products_ids->MoveNext();
    }
    
    $result = array();
    foreach($attributes as $row){
        if(empty($result))
            $result = $row;
        else 
            $result = array_intersect($result, $row);
    }
    
    $products_ids = implode("','", $result);
    
    $where = "p.products_id IN('$products_ids')";

I would like to give my customer the option to select a category and the wanted attributes so only that result will be displayed.

Where do i put this code ? and is this all i need ?

Thanks alot

How do I add this code to my website? I offer a lot of product design options and I really need to give my customers a way to search for product options.

Please let me know.