Direct SQL Queries In Magento

Let’s assume we need to fetch data from direct database and we don’t want to use model in magento. At that time we can also access data direct from database using magento database connection and simple SQL query.

Below is the code for how can we access data using simple SQL query.

$new_db_resource = Mage::getSingleton('core/resource');
$connection = $new_db_resource->getConnection('magento');
$data='SELECT design_id FROM sales_flat_quote_item WHERE item_id = '.$_item->getId();
$outout=$connection->fetchRow($data);
print_r($outout);

SQL constraints

FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
CHECK – Ensures that the value in a column meets a specific condition
NOT NULL – It will not accept NULL value.
DEFAULT – Specifies a default value for a column
UNIQUE – Ensures that each row for a column must have a unique value
PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column have a unique identity which helps to find a particular record in a table more easily and quickly

Get subcategories of a main category in magento

To insincere sales it is important that your store must be user friendly where user easily find his/ her needed product and easily navigate in store.

one of important page in magento is to display subcategory of main category. i.e let\’s assume we have store with category Women and we are selling all products which was related to women so it is preferable if we bifurcate all products so customer can easily find product.

Below is the code for display all sub-category of parent category.

$layerobject = Mage::getSingleton('catalog/layer');
$_category = $layerobject->getCurrentCategory();
$currentCategoryId= $_category->getId();
$childrencategory = Mage::getModel('catalog/category')->getCategories($currentCategoryId);
foreach ($childrencategory as $category)
{
echo $category->getName();
echo $category->getRequestPath();
}

Add top links in magento

Magento is an ecommerce platform built on open source technology and which use MVC system. It is managing all request response using Model, View and Controller. So, all view file call base on controller mapping as per there configuration in layout/*.xml file.

Layout file is use to define phtml file on specific module request. Let’s say if we want to add top link in magento at that time we need to use below code.

Add custom link to top link in magento


<block type="page/template_links" name="top.links" as="topLinks">
<action method="addLink" translate="label title">
<label>Blog</label>
<url>/blog</url>
<title>Blog</title>
<prepare></prepare>
<urlparams></urlparams>
<position>1</position>
</action>
</block>

If you want to open same URL in new tab at that time you can use below code.


<block type="page/template_links" name="top.links" as="topLinks">
<action method="addLink" translate="label title">
<label>Blog</label>
<url>/blog</url>
<title>Blog</title>
<prepare></prepare>
<urlparams></urlparams>
<position>1</position>
<liparams></liparams>
<aparams><!--[CDATA[target="_blank"]]--></aparams>
</action>
</block>

Mysql functions

MySQL Group By Clause – The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
MySQL IN Clause – This is a clause, which can be used along with any MySQL query to specify a condition.
MySQL BETWEEN Clause – This is a clause, which can be used along with any MySQL query to specify a condition.
MySQL UNION Keyword – Use a UNION operation to combine multiple result sets into one.
MySQL COUNT Function – The MySQL COUNT aggregate function is used to count the number of rows in a database table.
MySQL MAX Function – The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
MySQL MIN Function – The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
MySQL AVG Function – The MySQL AVG aggregate function selects the average value for certain table column.
MySQL SUM Function – The MySQL SUM aggregate function allows selecting the total for a numeric column.
MySQL SQRT Functions – This is used to generate a square root of a given number.
MySQL RAND Function – This is used to generate a random number using MySQL command.
MySQL CONCAT Function – This is used to concatenate any string inside any MySQL command.
MySQL DATE and Time Functions – Complete list of MySQL Date and Time related functions.
MySQL Numeric Functions – Complete list of MySQL functions required to manipulate numbers in MySQL.
MySQL String Functions – Complete list of MySQL functions required to manipulate strings in MySQL.

How to import large Database in phpmyadmin xampp

When you work in your local system you may face problem while uploading large database.

When you face such problem you can apply below the solution for it.

By default, Xampp has database import limit is 2MB. So if you want to upload database which has size more then 2MB at that time you need to increase database max upload size. Below are the steps on how to increase database max upload size.

Increase Database import size from php.ini file.

Here are the steps how we can increase database import size.

=> Go to xampp/php/
=> Find file php.ini
=> Edit php.ini and find for upload_max_filesize ( you will find it near line no:922).
=> Update value as per your requirement.
=> Save file
=> Restart your xampp server and mysql
=> Now open phpmyadmin and now you are able to upload file more then 2MB.

Change magento admin password from database

To change admin password need to follow below steps:

1) Login to Cpanel or access Database
2) Go to phpmyadmin
3) click on sql section
4) Run below query

(Note: please update NEWPASSWORD with your new password and ADMINUSER with your admin name).

UPDATE `admin_user` SET `password` = MD5('NEWPASSWORD') WHERE `username` = 'ADMINUSER';