Jump to content

imqqmi

Members
  • Content count

    11
  • Joined

  • Last visited

Profile Information

  • Real Name
    Josha Beukema

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. imqqmi

    Mod in progress- grid attributes

    I know exactly what you mean. I've spend a lot of time searching for documentation as the sourcecode (let alone others' contribs) are not commented. I'm by no means a hard core developer and only dived right into this because I had no choice. I would start a wiki for developers but v3 is around the corner so I guess v2.2 will be phased out soon. I can only help you with your first question: how are attributes stored. In a post on the mysql forum I've made an illustration of how the products and attributes relate to each other and has helped me greatly to understand how the database is set up. the header is the table name with the columns as subheaders: I've highlighted with colors which ID's match in each table. You can see that the products table has a matching products_id in products_attributes. Each product can have multiple attributes assigned, stored in each row of the products_attributes table. So there can be more than one products_id in the attrib table. please see myphpadmin (if your host supports this tool) and look in the database. The designers made a mistake in naming ID's as in the products_attributes table there's a options_id and options_values_id, which matches with the differently named products_options_id and products_options_values_id. I didn't know that at the time I made the illustration. The products_options_id describe the group name of an attribute (like color) and products_options_values_id describes the kind of attribute, like blue, red or orange. ID's are of course numbers only, each table (products_options and products_options) also contains a name field which has a string. When an attribute is assigned/added to a product, a new row is added to the products_attributes table, when an attribute is removed, the row is removed also. There's no enabled/disabled toggle as one would expect, although this is much more efficient space wise, it can be difficult to check which are present and which aren't. I haven't touched the shopping cart yet, I can't comment on that but it would be best to print out the class (or use multiple monitors to lay source code side by side and go through it all, adding comments when certain parts are understood. I hope version 3 will be better commented and documented. At least its more object oriented now so it should be easier to understand and extend. Good luck!
  2. I had the same problem with attributes. I use the Adam contribution to add drop down menus for all attributes grouped by optiongroups. This will search with an OR function so selecting more options adds more matches rather than less. The solution to this may well be usable for your situation. Diego Medina from the forums.mysql.com suggested the folowing query: SELECT count(*) as cnt, products_id FROM products_attributes WHERE options_values_id =1 OR options_values_id =3 OR options_values_id =5 GROUP BY products_id HAVING cnt = 3 ORDER BY NULL; This can be rewritten as: SELECT distinct count(products_id) as cnt, products_id FROM products_attributes WHERE options_values_id in (1,3,5) GROUP BY products_id HAVING cnt = 2 ORDER BY products_id"; Which is exactly like the one Adam has written without the count() and HAVING clause. How it works: The query counts the number of matching products_id, this should match the number of attributes the user selected. In the Adam code this is done in a while(){} loop. Each loop adds an attribute and the 'cnt =' is updated accordingly. So if there are 3 attributes selected by the user, each product should produce 3 matches and is returned by the database, 2 matches and the row is not returned. You can modify his code by adding the count and having clause. This will take care of the query, but will fail because the split_page_results.php class tries to strip out certain parts just to get a matching rows count. My solution (although crude and inefficient) was to run the full query and get the row count and feed in into the class variable. Like I said it tries to strip the having clause which uses the count() and the query fails if count() isn't followed by a HAVING cnt = x. see his contrib: http://addons.oscommerce.com/info/2337 His code: $attributes_query = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'"); $option_value_selected = false; $products_stock_attributes_array = array(); while ($attributes = tep_db_fetch_array($attributes_query)) { if (isset($HTTP_GET_VARS[$attributes["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes["products_options_name"]])) $option_value_selected = true; } // the above while loop is to check to see if any values were selected if ($option_value_selected == true){ $attributes_query2 = tep_db_query("select * from " . TABLE_PRODUCTS_OPTIONS . " where language_id = '" . (int)$languages_id . "'"); while ($attributes2 = tep_db_fetch_array($attributes_query2)) { if (isset($HTTP_GET_VARS[$attributes2["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes2["products_options_name"]])) { $str_selected_values = $str_selected_values . $HTTP_GET_VARS[$attributes2["products_options_name"]] . ','; } } $str_selected_values = substr($str_selected_values,0,strlen($str_selected_values) - 1); $where_str .= "and pa.products_id = p.products_id and pa.options_values_id in (".$str_selected_values.")"; My modified code in advanced_search_result.php: $attributes_query = tep_db_query("select * from products_options where language_id = '" . (int)$languages_id . "'"); $option_value_selected = false; $products_stock_attributes_array = array(); while ($attributes = tep_db_fetch_array($attributes_query)) { if (isset($HTTP_GET_VARS[$attributes["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes["products_options_name"]])) $option_value_selected = true; } // the above while loop is to check to see if any values were selected if ($option_value_selected == true){ $attributes_query2 = tep_db_query("select * from products_options where language_id = '" . (int)$languages_id . "'"); //Inblik from OR to AND START $i=0; //Inblik END while ($attributes2 = tep_db_fetch_array($attributes_query2)) { if (isset($HTTP_GET_VARS[$attributes2["products_options_name"]]) && !empty($HTTP_GET_VARS[$attributes2["products_options_name"]])) { $str_selected_values = $str_selected_values . $HTTP_GET_VARS[$attributes2["products_options_name"]] . ','; //Inblik from OR to AND START $i++; //Inblik END } } if( i ) { //Inblik from OR to AND START $having1_str = "group by p.products_id having cnt = ".$i." ";//" GROUP BY p.products_id HAVING total = ".$i." "; $select_str .= ", count(p.products_id) as cnt "; //Inblik END } $str_selected_values = substr($str_selected_values,0,strlen($str_selected_values) - 1); $where_str .= "and pa.products_id = p.products_id and pa.options_values_id in (".$str_selected_values.") "; Further down find: $listing_sql = $select_str . $from_str . $where_str . $order_str; Replace with: $listing_sql = $select_str . $from_str . $where_str . $having1_str . $order_str; In split_page_results.php (catalog/includes/classes/) From line 57 I have this (I don't have the original code handy) changed code is between the inblik comments: $pos_order_by = strpos($this->sql_query, ' order by', $pos_from); if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by; if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) { $count_string = '' . tep_db_input($count_key); } else { $count_string = tep_db_input($count_key); } // Inblik start if( pos_having ) $pos_shorter = 0; // Check if having is present in query else $pos_shorter = $pos_from; $finalquery = "select count(" . $count_string . ") as total, count($count_string) as cnt " . substr($this->sql_query, $pos_from, $pos_to-$pos_shorter); //echo '<br> pos_to '.$pos_to.'<br> pos_having '.$pos_having.'<br> pos_group_by '.$pos_group_by.'<br> pos_from '.$pos_from; //echo '<br>'. substr($this->sql_query, $pos_from, ($pos_to - pos_from)); //echo '<br><br>'.$finalquery.'<br>'; $result = mysql_query($this->sql_query) or die(mysql_error()); $matches = mysql_num_rows($result); //echo '<br>matches = '.$matches; $this->number_of_rows = $matches; //inblik end $this->number_of_pages = ceil($this->number_of_rows / $this->number_of_rows_per_page); How to change your code to do the same for extra fields is up to you. Good luck!
  3. imqqmi

    Simple Visitor Newsletter

    Solution found: In Configuration/email options change E-Mail Transport Method to smtp for windows/Mac Os servers.
  4. imqqmi

    Simple Visitor Newsletter

    I too am facing this problem. I've double checked the installation and everything is as it should be. I got an error saying that oscom.table_customers_temp doesn't exist. I've added the table and doesn't complain about it anymore. But no actual mails are being sent. TIA
×