Category Archives: Website Design and Development

info and code blocks for use in website design and development

Connect to MySQL with PDO

This code connects to the DB and closes it:

 

/*** mysql hostname ***/
$hostname = ‘localhost’;

/*** mysql username ***/
$username = ‘username’;

/*** mysql password ***/
$password = ‘password’;

try {
$dbh = new PDO(“mysql:host=$hostname;dbname=mysql”, $username, $password);
/*** echo a message saying we have connected ***/
echo ‘Connected to database’;

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

 

Insert into database, put before close the DB


/*** INSERT data ***/
$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'troy')");

/*** echo the number of affected rows ***/
echo $count;

Select from the DB:


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '
';
}

Insert into DB:


/*** INSERT data ***/
$count = $dbh->exec("UPDATE animals SET animal_name='bruce' WHERE animal_name='troy'");

/*** echo the number of affected rows ***/
echo $count;

 

Fetch Code


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";

/*** fetch into an PDOStatement object ***/
$stmt = $dbh->query($sql);

/*** echo number of columns ***/
$result = $stmt->fetch(PDO::FETCH_ASSOC);

/*** loop over the object directly ***/
foreach($result as $key=>$val)
{
echo $key.’ – ‘.$val.’
‘;
}

Fetch object


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";

/*** fetch into an PDOStatement object ***/
$stmt = $dbh->query($sql);

/*** echo number of columns ***/
$obj = $stmt->fetch(PDO::FETCH_OBJ);

/*** loop over the object directly ***/
echo $obj->animal_id.’
‘;
echo $obj->animal_type.’
‘;
echo $obj->animal_name;

 

Create RSS feed from DB

RSS.class.php


getDetails() . $this->getItems();
}
private function dbConnect()
{
DEFINE ('LINK', mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
}
private function getDetails()
{
$details = '


Quickplay Blog http://www.quickplaysport.com Quickplaysport French News
EN
';

/* can add this in if needs be

image_title
image_url image_link image_width
image_height

*/
return $details;
}
private function getItems()
{

$itemsTable = "aw_blog";
$this->dbConnect($itemsTable);
$query = "SELECT * FROM ". $itemsTable . " WHERE store_id = 0 AND status = 1 ORDER BY post_id DESC ";
$result = mysql_db_query (DB_NAME, $query, LINK);
$items = '';
while($row = mysql_fetch_array($result))
{

$link = "http://www.quickplaysport.com/blog/" . $row['identifier'];
$attribution_link = "The article " . $row['title'] . " first appeared on Quickplaysport.com

";

$items .= '
' . $row['title'] . ' ' . $link . '
';
}
$items .= '
';
return $items;
}
}
?>

[/code]

mysql_connect.php

index.php


GetFeed();
?>

get skinURL and Base URL in phtml and content blocks

GET SKIN URL, GET MEDIA URL, GET BASE URL, GET STORE URL

To Retrieve URL path in STATIC BLOCK

To get SKIN URL
{{skin url=’images/sampleimage.jpg’}}

To get Media URL
{{media url=’/sampleimage.jpg’}}

To get Store URL
{{store url=’mypage.html’}}

To get Base URL
{{base url=”}}

TO Retrieve URL path in PHTML

Not secure Skin URL
<?php echo $this->getSkinUrl(‘images/sampleimage.jpg’) ?>

Secure Skin URL
<?php echo $this->getSkinUrl(‘images/ sampleimage.gif’,array(‘_secure’=>true)) ?>

Get  Current URL
<?php $current_url = Mage::helper(‘core/url’)->getCurrentUrl();?>

Get Home URL
<?php $home_url = Mage::helper(‘core/url’)->getHomeUrl();?>

Get Magento Media Url
<?php Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_LINK);?>
<?php Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_MEDIA);?>

Get Magento Skin Url
<?php Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_SKIN);?>

Get Magento Store Url
<?php Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_WEB);?>
Get Magento Js Url
<?php Mage::getBaseUrl(Mage_Core_Model_Store::URL_TYPE_JS);?>

Fixing Magentos login error on localhost

To solve the login problem that seems to occur when Magento is installed on localhost goto:

app\code\core\Mage\Core\Model\Session\Abstract\Varien.php

search and comment these lines out (around line 85):

/*      // session cookie params
$cookieParams = array(
‘lifetime’ => $cookie->getLifetime(),
‘path’     => $cookie->getPath(),
‘domain’   => $cookie->getConfigDomain(),
‘secure’   => $cookie->isSecure(),
‘httponly’ => $cookie->getHttponly()
);

if (!$cookieParams[‘httponly’]) {
unset($cookieParams[‘httponly’]);
if (!$cookieParams[‘secure’]) {
unset($cookieParams[‘secure’]);
if (!$cookieParams[‘domain’]) {
unset($cookieParams[‘domain’]);
}
}
}
*/

Then clear the cache and login again.

It may also help to change the database entries in core_data_config to 127.0.0.1 instead of localhost

Migrating Magento to New Server

Migrating Magento to New Server

First login to current server via SSH

Then use this command within public_html folder:
tar -czf backup.tar.gz *

now copy the backup file to new server, unzip in location on new server

Now backup the DB

mysqldump -u USERNAME -p DBNAME > db_backup.sql

copy this to new server too. If WAMP do this: Windows > CMD

then goto this dir:
cd wamp\bin\mysql\mysql4.1.22\bin
Copy the DB backup to this dir

Now enter this command:
mysql.exe -u root -p databasename < filename.sql

no need for password

also change the core_config table to reflect new server

 

Magento database cleansing

Magento Tables that can be Truncated to speed up the website

TRUNCATE TABLE dataflow_batch_export;
TRUNCATE TABLE dataflow_batch_import;
TRUNCATE TABLE log_customer;
TRUNCATE TABLE log_quote;
TRUNCATE TABLE log_summary;
TRUNCATE TABLE log_summary_type;
TRUNCATE TABLE log_url;
TRUNCATE TABLE log_url_info;
TRUNCATE TABLE log_visitor;
TRUNCATE TABLE log_visitor_info;
TRUNCATE TABLE log_visitor_online;
TRUNCATE TABLE report_viewed_product_index;
TRUNCATE TABLE report_compared_product_index;
TRUNCATE TABLE report_event;

Setup Multistores in Magento with stores in subfolders

To setup mutlistores in subfolders within your Magento install:

1. Create the new stores and websites within the magento backend

2. set CMS pages as the home page for each store

3. Copy index.php into subfolders and .htaccess

4. set the base URLs for each store to be the subfolders with a trailing slash – Need to set the directories for skin and JS too as it cant have the subfolder name in

5. chaneg the 2 lines like this: $compilerConfig = MAGENTO_ROOT . ‘/includes/config.php’;

to:

$compilerConfig = ‘../includes/config.php’;

Then comment out these lines:

/* Store or website code */
//$mageRunCode = isset($_SERVER[‘MAGE_RUN_CODE’]) ? $_SERVER[‘MAGE_RUN_CODE’] : ‘fr’;

/* Run store or run website */
//$mageRunType = isset($_SERVER[‘MAGE_RUN_TYPE’]) ? $_SERVER[‘MAGE_RUN_TYPE’] : ‘store’;

//Mage::run($mageRunCode, $mageRunType);

and add this one:
Mage::run(‘fr’, ‘website’); //(for me: Mage::run(‘kbc’, ‘website’);)

Delete All Magento orders from database

Use the following SQL query to delete all Magento orders from the database:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
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 `sales_order_aggregated_created`;
TRUNCATE `sendfriend_log`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;
ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1;
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 `sales_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` 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 `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;