Category Archives: Web Site Admin

htaccess force download file

Using the below part of code inside your htaccess file, you restrict the browser to download the file instead of displaying it in a seperate tab.

Two versions of the code, one for many filetypes and the second just only for pdf:

Various file types:

<FilesMatch "\.(?i:doc|odf|pdf|rtf|txt)$">
  Header set Content-Disposition attachment
</FilesMatch>

Only pdf:

<FilesMatch "\.(?i:doc|odf|pdf|rtf|txt)$">
  Header set Content-Disposition attachment
</FilesMatch>

taken form the drupal comnunity forum

google charts using mysql recordset made easy

General

if your programming skills are limited, then you could use the below code to integrate google charts to your intranet or internet web site.

As i first step  i usually visit the the google charts page and select a chart that matches my desired outcome. Lets say that we have a list of cd collections that we want to process and we need to know graphically how many we have already processed or not. For that i would choose a pie chart.

Mysql Part

Next we need to retrieve the necessary data from the database using the appropriate query.

mysql_query

mysql_queryIn my case i only need to have two columns, one that shows the cd collection visibility type (1=visible, 0= hidden, 3=other status), and the count of each type of visibility.

PHP Part

Next, the php part of the mysql connection:

mysql_select_db($database_localhost, $localhost);
$query_Recordset1 = "select count(*) as count,visible from cdcollection where type=1 and serial=1 group by visible";
$Recordset1 = mysql_query($query_Recordset1, $localhost) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

then we transform the data to an array format:

$data[0] = array('visible','count'); 
 for ($i=1; $i<($totalRows_Recordset1+1); $i++)
 {
 $data[$i] = array(mysql_result($Recordset1, $i-1,'visible'),
 (int) mysql_result($Recordset1, $i-1, 'count'));
 }

Google Charts Script

The below script should be included in the <head> of your document in order to initialize the google charts library.

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

Then right below add the following script part, which basically transform  the data array to json and then it uses it to create the chart.

<script type="text/javascript"> 
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart1);
function drawChart1() {
var data = google.visualization.arrayToDataTable(<?php echo json_encode($data)?>);

 var options = {
 title: 'Collections',
 hAxis: {title: 'Year', titleTextStyle: {color: 'red'}},
 colors: ['#e0440e', '#e6693e', '#ec8f6e', '#f3b49f', '#f6c7b6']
 };


var chart = new google.visualization.PieChart(document.getElementById('chart_div1'));
 chart.draw(data, options);
</script>

I am explaining the red parts of the above code:

  • The json_encode($data) part transform the $data array to json. So in the mysql part if you used some other name for your dataset it should also be changed here.
  • The part right after “var options = {  “ includes all the settings of the chart. This is where you specify the title, the colour style, the fonts etc. Depending on what you need to do adequate documentation exists on the net..
  • Finally, the last red part PieChart is where you tell the api what chart you need it to render. This could be modified to ColumnChart or Histogram or any other form the available options you can find in the google chart development page.

See below the dashboard i have create for my self using three different recordsets.

My Google Charts Dashboard
My Google Charts Dashboard

output_buffering in Wamp causing issues in the insert form

As mentioned earlier this week i changed my development machine to newer vmware win 7. During this, also upgraded from Wamp 2.2 to Wamp 2.5. Fortunatelly, i still keep both environments in parallel since various issues come up.

One of these issues is the one found today in php form (created with the dreamweaver CC wizard) that inserts some values into a mysql table. In my new dev machine i got these errors:

apache erros

The old dev environment worked fine, the prod environment worked fine, the new dev threw errors.

I started by trying to find any compatibility issues in the PHP versions between Wamp Server 2.2 and 2.5, could not locate anything.

The solution came from the Stack Exchange (again). It appears that the php.ini on the new server came with the default value of

output_buffering = 4096

which should be changed to

output_buffering = on

which was the default on the old dev machine. To change the value in Wamp server just go to PHP menu and edit the php.ini to the mentioned value.

wamp.2.5

One alternative suggested by lots of people, which i did not try was to add

ob_start();

at the beggining of the script, which turns on the output buffering feature for the specific script.

mysql change single column character set

It turn`s out that there is an easy way to change the character set and collation of a single column in mysql. I usually have this issue since my provider has a swedish collation that is chosen by default when creating a column. I only realize after i get data in of course.. Here it is..

ALTER TABLE `packs`
MODIFY `packsname` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

To change the character set (and collation) for all columns in an existing table, use…

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

Mysql Duplicate and Modify Row

I was trying to find an easy way to duplicate a table row in mysql, and it appears that mysql can handle that pretty easily.  Just Use:

insert INTO `server`.`ad` (`adgrp`,`adname`,`ad`,`adlink`,`adtype`,`adprovider`,`visible`) SELECT `adgrp`,`adname`,`ad`,`adlink`,`adtype`,`adprovider`,`visible` FROM `server`.ad where idad=46;

to duplicate any row on any table, or use the  CONCAT() function to add text to the newly created row. In my case i needed to set the name just to be “_copy” of the original one:

insert INTO `server`.`ad` (`adgrp`,`adname`,`ad`,`adlink`,`adtype`,`adprovider`,`visible`) SELECT `adgrp`,CONCAT(`adname`,"_COPY"),`ad`,`adlink`,`adtype`,`adprovider`,`visible` FROM `server`.ad where idad=46;

In the case above the idad column is of course the primary key and auto-increment, so it cannot be copied.

PHP- Mysql database encoding and question-marks in the text (and how to get rid of them)

For my developing project i use Mysql and Dreamweaver set up with Wamp. I would say that i very familiar with databases and how to handle data etc..

Prerequisites to this project was that i could not modify either my php.ini or mysql.ini, since i am on a shared hosting plan.

One issue I had and could not fix though was that i got question-marks  for special characters like quotes or Cyrillic or French characters using ascent. I did work on this many hours but with no luck. Today i finally have managed to solve it. Here is how:

Since all my databases already have data and are online, i created copies of them using the correct charset. So:

1. I Exported all the tables in my databases using mysqldump and recreated them using utf8  character set and utf8_unicode_ci table/column collation. This is essential to make sure all data are saved in the correct format

2. I made sure that all my pages had the following two meta tags in the head tag:

<meta charset="UTF-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

3. Finally, and that is what was missing all this time, is to make sure that all communication with the database is done through UTF. Apparently this does not happen automatically when you set the php encoding and the database collation/encoding. To do so, every time you query your database,  you need to specify the communication  encoding format.

mysql_select_db($database_localhost, $localhost);

$query_collections = sprintf("Select  * from table WHERE packbrand=%s and serial='1' AND VISIBLE='1' order by collection asc", GetSQLValueString($colname_collections, "text"));

mysql_query("set names 'utf8'");

This  last part basically fixed it.

What did not work for me:

  • Trying to figure our whether your text editor (like notepad++) is wrongly converting your query to a different encoding.
  •  Pass the set names command in mysql workbench. While typing this i am just now realizing that this would not make any difference on way or an other, because it is the retrieval of the data in PHP that caused issues and not the displaying or the typing in..

One down many to go. Move to next project.

Sitemap & Sitemap Index xml files using php and mysql

For some time now I have been generating sitemap xml files for google submission using Microsoft Excel. Always knew that there would be a way to generate it on the fly/on request and never bother with that again. But how?

Documentation is not very clear because you need to use multiple programming languages, and also because the whole xml validation process seems very sensitive to errors. Since i am not an experienced programmer, stack exchange became my guide and i have finally managed to do it.

If you have multiple pages, you probably need to create intermediate xmlindex pages. These are basically specially marked index files that point to other index files. Warning: Nested xmlindex files are not allowed by Google (even though are at the end followed and indexed).

<?php Header('Content-type: text/xml');

require_once('../Connections/localhost.php'); 

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
 if (PHP_VERSION < 6) {
 $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 }

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
 case "text":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break; 
 case "long":
 case "int":
 $theValue = ($theValue != "") ? intval($theValue) : "NULL";
 break;
 case "double":
 $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
 break;
 case "date":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break;
 case "defined":
 $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
 break;
 }
 return $theValue;
}
}

mysql_select_db($database_localhost, $localhost);
$query_Recordset1 = "select * from sitemaps where `use`='sitemap'";
$Recordset1 = mysql_query($query_Recordset1, $localhost) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$xml=new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
 .'<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
 .'</sitemapindex>'); 
do { $url=$xml->addChild('sitemap');
 $website="http://www.tharsitis.gr/".strtolower(str_replace(' ','-',$row_Recordset1['url']));
 $url->addChild('loc',$website);
 "<br/>";
 //$url->addChild('lastmod','XXXXXXXX'); OPTIONAL
 } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
mysql_free_result($Recordset1);
print($xml->asXML());
?>

This is the xml sitemap  index code that worked for me. I created the database recordset using Dreamweaver. It retrieves all the xml index urls from Mysql database.

At the second part of the project i needed to generate the sitemap xml files that the xml index would be point at.

This is the code that worked for me:

<?php Header('Content-type: text/xml');

require_once('../Connections/localhost.php'); 

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
 if (PHP_VERSION < 6) {
 $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 }

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
 case "text":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break; 
 case "long":
 case "int":
 $theValue = ($theValue != "") ? intval($theValue) : "NULL";
 break;
 case "double":
 $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
 break;
 case "date":
 $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
 break;
 case "defined":
 $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
 break;
 }
 return $theValue;
}
}

$rs1_Recordset1 = "-1";
if (isset($_GET['id'])) {
 $rs_Recordset1 = strtolower(str_replace ( '-', ' ',$_GET['id']));
}

mysql_select_db($database_localhost, $localhost);
$query_Recordset1 = sprintf("SELECT packbrand,packsuuid,packcollection,packsname,folder FROM packs where collectionid=%s and visible='1' order by serial asc",GetSQLValueString($rs_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $localhost) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$xml=new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
 .'<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">'
 .'</urlset>'); 
do { 
 $brand=$row_Recordset1['packbrand'];
 $collection=$row_Recordset1['packcollection'];
 $url=$xml->addChild('url');
// $image="image:image";
 $website="http://www.tharsitis.gr/".strtolower(str_replace(' ','-',$brand."/".$collection."/".$row_Recordset1['packsuuid']));
 $img="http://www.tharsitis.gr/packagecache/".$row_Recordset1['folder']."/".$row_Recordset1['packsuuid'].".jpg";
 $url->addChild('loc',$website);
 $url->addChild('priority','0.7');
 $image = $url->addChild('image:image', null, 'http://www.google.com/schemas/sitemap-image/1.1');
 $image->addChild('image:loc',$img, 'http://www.google.com/schemas/sitemap-image/1.1');
 $image->addChild('image:caption',$row_Recordset1['packsname'], 'http://www.google.com/schemas/sitemap-image/1.1');
 } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
mysql_free_result($Recordset1);
print($xml->asXML());
?>

In both files you can skip the initial part where i build the recordset. This was all done automatically by Dreamweaver. Save the files as php files.
Finally, i needed to modify my .htaccess file so that it translated every php  request matching the above files to xml. This is a requirement to get Google validate the files correctly.

Rewriterule ^xmlindex.xml$ /xml/xmlindex.php [L]
Rewriterule ^p(.*).xml$ /xml/xmlpacks.php?id=$1 [L]

As you can see in the second line of the below htaccess code, I use url variables to generate dynamic xml sitemaps for different sets of urls.  Vuala!

The importance of sitemap.xml

Google Webmaster Tools Menu
Google Webmaster Tools Menu

 

 

 

 

A colleague of mine recently announced to me that he and a couple of his friends  hired a software company to develop a web site for them. Curious as i am i visited the web site directly and started searching inside the code to find out which platform they used, if they have social integration, SEO tags etc. It turned out that none of these was present due the fact that it`s just a start up site, and the budget was not enough to cover for all of this. All ok but at least i think they should have added their newly created website to the webmaster tools of Google and Bing.

According to my opinion even if  it is a start up site, you need to use webmaster tools to let Google and Hotmail know that you are already running, and so that they can start indexing the individual web pages you have. For that reason you need to create a sitemap file and place it on the root of the www. I usually name these files sitemap.xml and either create them by hand or by application modules (like in wordpress for example). Keep in mind that google needs to have an xml file as a sitemap and not a php page that would generate xml formatted text, not being able to find a solution to this yet.

Google Webmaster Tools
Google Webmaster Tools
Bing Web Master Tools Menu
Bing Web Master Tools Menu

The benefits are numerous.  You can check your page in terms of structured data, linked pages, coding errors. You also can see different statistics of your web page such as inbound, outbound links as well as search queries. Google additionally also lets you see the indexing status of your submitted sitemaps as well as some keyword preference based on the submitted urls. Finally, on the relative submenu, you are able to monitor the google crawler as it crawls your web site for errors, and also be able to see how Google “reads” your web page, by using the “Fetch as Google” functionality.

Bing webmaster tools provides matching functionality, combining some “analytics” type data though. e.g SEO Analyzer, Link Explorer, Page Traffic etc.

Closing this, I strongly believe that creating a sitemap and adding it to Bing /Google webmaster tools is one of the first actions a webmaster should do, after going public. These are great tools that guarantee you that the big players in search engines can “see” you good.

Google Web Master Tools

Bing Web Master Tools

SEO Must haves that apply to any web page

Update: Yandex also has a Webmaster Tool for their search engine. Check it out

SVN dump and load

Since a lot of time i use the tortoise svn with visualsvn as my developing repository.

I have it installed locally on the vmware that i use. The repositories are also created locally. At some point i needed a smart way to backup /copy all my repositories from the vmware to the Synology NAS I have sitting at home.  I used to sync all the repos through dropbox but took much time and was not fool proof. I ended up installing the SVN sever on the synology NAS.  After opening the right ports on ly router everything seemed to work ok, but how do you transfer the repo from the local machine to the nas svn???

First i exported the repositories from the local developing machine:

svnadmin dump E:\Wamp\Repositories\tharsitis.gr > E:\Wamp\Repositories\tharsitis.gr.dump

After creating an empty repository in the synology SVN, i copied the previously generated dump file in the synology SVN folder.

synology svn
synology svn

 

 

 

 

 

 

Now the loading…

Login via SSH with root rights on to the SVN and run the following:

where:

svnadmin load  /volume1/SVN/tharsitis.gr < /volume1/SVN/tharsitis.gr.dump --ignore-uuid

where  –ignore-uuid : ignore any repos UUID found in the stream

WTF is Semalt.com?

No Semalt
No Semalt

As soon as i installed the google analytics code for tharsitis.gr , found out that i got too many  hits from Brazil.  Digging a bit more in the analytics report,  found that all the brazilian hits were coming from semal.com…

Semalt.com is an Ukrainian company that crawls the internet and generates reports on how your site compares to the rest of the internet, or the competition. Simply as that. Now why it needs to crawl my wordpress blog 16 times a day, i do not know. I only have like 5 posts yet :).

Any how, if you want to prevent semalt.com from crawling your web site, there are a couple of ways you can do that. One of them is through the .htaccess file. For wordpress i have changed my htaccess file to below (added the lines after # END WordPress, got it from here):

# Switch rewrite engine off in case this was installed under HostPay.
RewriteEngine Off
SetEnv DEFAULT_PHP_VERSION 53
DirectoryIndex index.cgi index.php index.html
# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>
# END WordPress
SetEnvIfNoCase Via evil-spam-proxy spammer=yes
SetEnvIfNoCase Referer evil-spam-domain.com spammer=yes
SetEnvIfNoCase Referer evil-spam-keyword spammer=yes
SetEnvIfNoCase Via pinappleproxy spammer=yes
SetEnvIfNoCase Referer semalt.com spammer=yes
SetEnvIfNoCase Referer poker spammer=yes
Order allow,deny
Allow from all
Deny from env=spammer

Alternatively you could also visit their web site and ask them to remove your page.. (it did not work for me).