Categorie: Tutorials

Available qyt on the cart page in Magento 1

For the available qyt on the cart page use below code;


	   	<!-- start available qty to product in app\design\frontend\ultimo\colorfabb\template\checkout\cart\item\default.phtml-->
        < ?php

		$productId = $_item->getProductId();
		$product = Mage::getModel('catalog/product')->load($productId);
		if($product->getTypeId() == "configurable"){
			$sku = $_item->getSku();
			$simpProdId = Mage::getModel('catalog/product')->loadByAttribute('sku',$sku)->getId();
			$product = Mage::getModel('catalog/product')->load($simpProdId);
		}

		$quantity = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product)->getQty();
		?>
		<span style="font-weight: bold;color:#ff0000;">Available qty:< ?php echo (int) $quantity;?></span>
		
		<!-- einde available qty to product -->

magento

Magento orders by sku

SQL code for orders by sku


SELECT sales_flat_order.increment_id,
 sales_flat_order_item.order_id,
 sales_flat_order_item.sku,
 sales_flat_order.status,
 sales_flat_order_item.name,
 sales_flat_order.customer_email
 FROM dbname.sales_flat_order_item sales_flat_order_item
 INNER JOIN dbname.sales_flat_order sales_flat_order
 ON (sales_flat_order_item.order_id = sales_flat_order.entity_id)
 WHERE (sales_flat_order_item.sku = 'fill in sku no ')
 AND (sales_flat_order.status &lt;&gt; 'Canceled')
ORDER BY sales_flat_order.increment_id DESC

magento

Disable paypal logo from sidebar in Magento

System > Configuration > Payment Methods

> Paypal All in One Payments Solutions
> Paypal Payments Advanced (Includes Express Checkout)
> Basic Settings – Paypal Payments Advanced
> Frontend Expereince settings
> Paypal Product Logo

magento

Sql query: Delete all Orders, Sales & Customer Data in Magento

Sql query: Delete all Orders, Sales & Customer Data in Magento

# Tested on Magento CE 1.4.1.1 - 1.9.0.1

SET FOREIGN_KEY_CHECKS=0;

##############################
# SALES RELATED TABLES
##############################
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;
TRUNCATE `sales_invoiced_aggregated`;            # ??
TRUNCATE `sales_invoiced_aggregated_order`;        # ??
TRUNCATE `log_quote`;

ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;

#########################################
# DOWNLOADABLE PURCHASED
#########################################
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

#########################################
# RESET ID COUNTERS
#########################################
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

##############################
# CUSTOMER RELATED TABLES
##############################
TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `tag_properties`;            ## CHECK ME
TRUNCATE `wishlist`;
TRUNCATE `log_customer`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;

##############################
# ADDITIONAL LOGS
##############################
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`;
### ??? TRUNCATE `log_summary`

ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
### ??? ALTER TABLE `log_summary` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

MySQL query caching for ex. Magento

Login Plesk MySQL

On server with Parallels Plesk Panel versions 8.x, 9.x and above admin password is stored in file /etc/psa/.psa.shadow. Use the following command to get the password:

~# cat /etc/psa/.psa.shadow

Since version 10 admin password stored in file /etc/psa/.psa.shadow is encrypted. Use the following command to get admin password in plain text:

~# /usr/local/psa/bin/admin --show-password

Despite version of Parallels Plesk Panel you may use the following command to login to mysql with root privileges:

~# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

MySQL query caching

mysql -u username -p

SHOW VARIABLES LIKE 'have_query_cache';

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.01 sec)

Hieronder zie je wat de meest belangrijke onderdelen betekenen in de bovenstaande lijst:

query_cache_size : Dit is de grootte van de cache in bytes. Wanneer je de waarde van deze instelling op het getal 0 zet zul je effectief caching uitzetten. query_cache_type : Deze waarde moet ingesteld staan op ON of het getal 0 om query caching standaard aan te hebben staan. query_cache_limit – Dit is de maximale query grootte (wederom in bytes) dat gecached zal worden.

Wanneer de instelling query_cache_size op 0 staat, of je deze zelf wilt aanpassen zul je onderstaande query uit moeten voeren. Hou er rekening mee dat de waarde in bytes is! Bijvoorbeeld: wanneer je 16 megabyte wil toewijzen aan de cache zul je deze moeten berekenen met : 1024 x 1024 x 16 = 16777216. Stel dan 16777216 in als de waarde voor de instelling query_cache_size. SET GLOBAL query_cache_size = 16777216;

SET GLOBAL query_cache_size = 16777216;

De overige instellingen zijn op een soortgelijke manier in te stellen: SET GLOBAL query_cache_type = 1; SET GLOBAL query_cache_limit = 1048476;

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 1048476;

Hoe kun je er zeker van zijn dat je instellingen werken? Simpel, je kunt hier de query SHOW STATUS voor gebruiken om alle variabelen te laten tonen die beginnen met “Qc”. Laten we een kijkje nemen: ======

SET GLOBAL query_cache_size = 16777216;

SHOW STATUS LIKE 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759696 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.01 sec)

magento mysql

Bulk edit price in Magento with SQL query

Select Prices

SELECT 
catalog_product_entity_decimal 
WHERE
catalog_product_entity_decimal.attribute_id=75

Update Prices with Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = 35.9500
WHERE
catalog_product_entity_decimal.attribute_id=75

(catalog_product_entity_decimal.attribute_id=75 = price)

Empty Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = NULL
WHERE
catalog_product_entity_decimal.value_id=120

Update Prices with %

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value = catalog_product_entity_decimal.value*1.19
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

1.19 is 19% tax
Id=75 is the price
Id = 76 is the special price (see table eav_attribute table for codes)

Update Prices with Rounded Value

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value= ROUND(catalog_product_entity_decimal.value,0)
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

Run this if you want all your prices to be also rounded e..g 25.00 instead of 25.01

Now Refresh Cache in Magento

magento

Password Reset for Joomla , WordPress and Joomla (new password = NEWPASS)

Reset password with Navicat Lite

Joomla:

UPDATE jos_users
SET password = MD5('NEWPASS') 
WHERE username = "admin"

Wordpress:

UPDATE wp_users 
SET user_pass = MD5( 'NEWPASS' ) 
WHERE user_login = "admin";

Drupal:

UPDATE users 
set pass= '$S$CVrRLeIr/2RlAxZgzPyu58yXbhev5OItQNCjXMrgMq9IriUKyUe.' 
WHERE uid = 1;

If you had the following message:
Sorry, there have been more than 5 failed login attempts for this account. It is temporarily blocked. Try again later or request a new password.
You can :

DELETE FROM flood

Here are three methods for importing large SQL files

Method 1: Bigdump

http://www.ozerov.de/bigdump.php

I used a script called BigDump.php, a staggered SQL dump importer. It executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped to keep you from running into your server’s limits. Instructions for using it are on the BigDump website. Basically you place your SQL file in a folder on your server, along with the bigdump.php file. You edit that file with your database information and then visit the page on your server and set the import to go. This is a fairly quick process and will save you a lot of time.

Method 2: SSH

If you have root access to your server, create a backup using SSH

cd to the directory where you want to store your dump file:

cd /var/www/vhosts/yourdomain.com/httpdocs/sqldump

Then tell mysql to dump it out:

mysqldump –add-drop-table -u dbuser -p your_db > mybackup.sql

It will prompt you for your password. Everything should dump out without a problem.

*Note: The directory where you’re sending the dump file needs to be writeable by the server. Also, you don’t really want to leave it in that folder permanently. Just drop it there and pull it off your server so that you can manipulate it if you have to.
How to Import a Large SQL File

Importing is usually a bit messier. If you’re duplicating this database for testing purposes or moving your site to a new server, a large dump file, even when gzipped, can pose some challenges in the import process. This is the part where things can really stall out, and you need a few tricks up your sleeve.

Ordinarily I would just use phpMyAdmin to import the file. Because it’s so large, the first thing you should do is try importing via SSH:

mysql -u your_database -p db_user < mybackup.sql
Common Error when using SSH:

"got a packet bigger than ‘max_allowed_packet’ bytes"

Now what? Depending on the size of your file, running this command may fix it for you:

mysql> set global max_allowed_packet=1000000000;
mysql> set global net_buffer_length=1000000;

Then try importing your database after this. If it still doesn’t work, you might try splitting it up.

Method 3: splitting up your SQL file

Download a program that will split up your SQL file, such as SQLDumpSplitter2. This is a free SQL dump file splitter that will let you determine how big your chops will be and will automatically cut and save your split SQL file. Yes, this is a pain, but it is sometimes the only way to get this accomplished.

http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/

Create the splits and then upload them to a directory on your server.

If you want to restore the dump, you have to execute the yourdatabase_DataStructure.sql first because it contains the Tables structure. After that, you can execute the other .sql-files as they contain the data of the now existing tables. Using SSH, CD to your directory again and make sure to send this first:

mysql -u db_user -p db_name < yourbackup_DataStructure.sql

Then your splits:
mysql -u db_user -p db_name < yourbackup_1.sql
mysql -u db_user -p db_name < yourbackup_2.sql
etc…

import sql

@font -face example

@font -face is supported by the top browsers using various font types, for example Safari, Opera, Firefox and Chrome handle .TTF and .OTF, Internet Explorer can handle .EOT, and what’s probably worth a mention, Firefox 3.6 now handles .WOFF, and Opera supports .SVG.

@font-face implementation:

CSS:

@font-face {
font-family: “Museo 300”;
src: url(“fonts/Museo300-Regular.eot”);
src: local(“Museo 300”), local(“Museo 300”),
url(“fonts/Museo300-Regular.otf”) format(“opentype”),
url(“fonts/Museo300-Regular.woff”) format(“woff”),
url(“fonts/Museo300-Regular.svg#Museo 300”) format(“svg”);
}

Explanation:

font-family: “Museo 300”;
This is the font name, and how you will call it in your stylesheet.

src: url(“Museo300-Regular.eot”);
This will enable your font on Internet Explorer.

src: local(“Museo 300”), local(“Museo 300”),
This is the local setting which will call it from your machine if it exists.

url(“Museo300-Regular.otf”) format(“opentype”),
This covers Safari, Opera and Firefox below 3.6

url(“Museo300-Regular.woff”) format(“woff”),
This is the Firefox 3.6 file type.

url(“Museo300-Regular.svg#Museo 300”) format(“svg”);
This is the Opera font type. This will cover Safari, Firefox, Internet Explorer, Chrome and Opera at the very least and it provides a local setting which will find the local file on your machine. This can be altered to accommodate any other OpenType font or TrueType font (IF you have permission to use it!)

Usage:
#main {font: 18px “Museo 300”, Helvetica, Arial, sans-serif;}

When using the @font-face font in other elements, its good practice to have a few fallback fonts for users who are using a dated browser or browser that don’t support @font-face.

You can download the example along with the various font types which you can run locally and view in the various browsers, and use the sample code to adapt for your own uses.
If you have the .OTF file you can use the Font Squirrel Generator, http://www.fontsquirrel.com/fontface/generator , which will create the .SVG, .WOFF and .EOT formats for you to use.

Download Bestanden Bekijk Demo

CSS font

Show module positions of any Joomla! website?

You would like to put some Module at your site but don’t know where to put it…. Or “Nice site, is it Joomla! ?”… Or “How did they use some area at that Joomla! site? Is it a Module?”… You can easily find out yourself, without being logged in!

Add the parameter ?tp=1 behind the URL, e.g. http://www.joomla.org/?tp=1
The following parameters can be used: 0=normal, 1=horiz, -1=no wrapper.