Grouping Date with Datetime in MySQL
Nov0
This is very basic and very useful command for mysql. I need user registration date by date. Firstly, i calculate with PHP, after i think it must be a basic way. I found this mysql command. I hope you’ll enjoy.
SELECT DATE_FORMAT(registerdate, '%Y-%m-%d') AS dd, COUNT(id) as TotalUser FROM UserTable GROUP BY dd;
Creating Google Sitemap in PHP
Jun0
We can create dynamic sitemap with using PHP & Mysql. My example code is easy to apply your system.
<?PHP
header('content-type: text/xml');
$mysql_username = "";
$mysql_password = "";
$mysql_database = "";
$dblink = mysql_connect("localhost", "$mysql_username", "$mysql_password");
if (!$dblink) {
die('ERROR: Can not connect database' . mysql_error());
}
@mysql_select_db("$mysql_database") or die ("ERROR: Cannot select database");
echo('<'.'?xml version="1.0" encoding="UTF-8"'.'?'.'>');
echo('<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">');
$contentQ = mysql_query("SELECT * FROM content_table");
while($contentR = mysql_fetch_array($contentQ))
{
$url = $contentR["url"];
$adding_date = date("Y-m-d",strtotime($contentR["adding_date"]));
echo("<url>
<loc>http://www.example.com/".$url."</loc>
<lastmod>".$adding_date."</lastmod>
</url>
");
}
$content2Q = mysql_query("SELECT * FROM content2_table");
while($content2R = mysql_fetch_array($content2Q))
{
$url = $content2R["url"];
$adding_date = date("Y-m-d",strtotime($content2R["adding_date"]));
echo("<url>
<loc>http://www.example.com/".$url."</loc>
<lastmod>".$adding_date."</lastmod>
</url>
");
}
echo("</urlset>");
?>
Google Sitemap Plugins for Wordpress
http://www.dicontas.co.uk/blog/google-sitemap-utw-tag-wordpress-plugin/
http://www.arnebrachhold.de/redir/sitemap-home/
http://www.karailiev.net/karailievs-sitemap/
http://southcoastwebsites.co.uk/wordpress/
Calculate Last Week, Last Month in Mysql
Jun0
We need get datas from last week, last month, yesterday and today. We can do it easily in MySQL.
Getting Current Date
SELECT CURDATE();
Result : 2009-06-04
Getting Last Week Datas
SELECT DATE_ADD('2009-06-04', INTERVAL 7 DAY);
Getting Last Month Datas
SELECT DATE_ADD('2009-06-04', 1 MONTH);
Getting Last Year Datas
SELECT DATE_ADD('2009-06-04', INTERVAL 1 YEAR);
And Php example for this calculations, getting last month orders
mysql_query("SELECT * FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 1 month) <= order_date"));
For more information of Date and time functions :
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Change MySQL Password via SSH
Jun0
login ssh, type mysqladmin. A tip if you can not remember mysql password type
mysql -uadmin -p`cat /etc/psa/.psa.shadow`
mysql> USE mysql;
mysql> SELECT * FROM user;
mysql> SET PASSWORD FOR 'username'@'hostname' = PASSWORD('password_here');
mysql> FLUSH PRIVILEGES;
Converting Unescape Chars in MySQL for SEO
May0
Sometimes, i need to convert datas for search engine optimization (SEO). I use MySQL replace command for this. Just login to phpmyadmin or any tool for run SQL command. Paste command set, change table and field name than run.
UPDATE table SET field = replace(field, 'À','A'); UPDATE table SET field = replace(field, 'Á','A'); UPDATE table SET field = replace(field, 'Â','A'); UPDATE table SET field = replace(field, 'Ã','A'); UPDATE table SET field = replace(field, 'Ä','A'); UPDATE table SET field = replace(field, 'Å','A'); UPDATE table SET field = replace(field, 'Æ','AE'); UPDATE table SET field = replace(field, 'Ç','C'); UPDATE table SET field = replace(field, 'È','E'); UPDATE table SET field = replace(field, 'É','E'); UPDATE table SET field = replace(field, 'Ê','E'); UPDATE table SET field = replace(field, 'Ë','E'); UPDATE table SET field = replace(field, 'Ì','I'); UPDATE table SET field = replace(field, 'Í','I'); UPDATE table SET field = replace(field, 'Î','I'); UPDATE table SET field = replace(field, 'Ï','I'); UPDATE table SET field = replace(field, 'Ò','O'); UPDATE table SET field = replace(field, 'Ó','O'); UPDATE table SET field = replace(field, 'Ô','O'); UPDATE table SET field = replace(field, 'Õ','O'); UPDATE table SET field = replace(field, 'Ö','O'); UPDATE table SET field = replace(field, '×','Z'); UPDATE table SET field = replace(field, 'Ø','O'); UPDATE table SET field = replace(field, 'Ù','U'); UPDATE table SET field = replace(field, 'Ú','U'); UPDATE table SET field = replace(field, 'Û','U'); UPDATE table SET field = replace(field, 'Ü','U'); UPDATE table SET field = replace(field, 'ß','ss'); UPDATE table SET field = replace(field, 'à','a'); UPDATE table SET field = replace(field, 'á','a'); UPDATE table SET field = replace(field, 'â','a'); UPDATE table SET field = replace(field, 'ã','a'); UPDATE table SET field = replace(field, 'ä','a'); UPDATE table SET field = replace(field, 'å','a'); UPDATE table SET field = replace(field, 'æ','ae'); UPDATE table SET field = replace(field, 'ç','c'); UPDATE table SET field = replace(field, 'è','e'); UPDATE table SET field = replace(field, 'é','e'); UPDATE table SET field = replace(field, 'ê','e'); UPDATE table SET field = replace(field, 'ë','e'); UPDATE table SET field = replace(field, 'ì','i'); UPDATE table SET field = replace(field, 'í','i'); UPDATE table SET field = replace(field, 'î','i'); UPDATE table SET field = replace(field, 'ï','i'); UPDATE table SET field = replace(field, 'ñ','n'); UPDATE table SET field = replace(field, 'ü','u'); UPDATE table SET field = replace(field, 'ò','o'); UPDATE table SET field = replace(field, 'ó','o'); UPDATE table SET field = replace(field, 'ô','o'); UPDATE table SET field = replace(field, 'õ','o'); UPDATE table SET field = replace(field, 'ö','o'); UPDATE table SET field = replace(field, 'ø','o'); UPDATE table SET field = replace(field, 'ù','u'); UPDATE table SET field = replace(field, 'ú','u'); UPDATE table SET field = replace(field, 'û','u'); UPDATE table SET field = replace(field, 'ÿ','y');
Open and Close Port in Linux
May1
For more secure server, we have to close some ports to users. If need access to this ports, we can give permission to our IP address. We need static IP address for this operation. Please dont forget, “iptables” can be block all IP address but when you restart the server it will be clean all of this commands. (I am using Fedora)
Closing FTP port except xxx.xxx.xxx.xxx
iptables -t filter -I INPUT 1 -p tcp -s ! xxx.xxx.xxx.xxx –dport 21 -j REJECT –reject-with icmp-host-prohibited
Closing SSH port except xxx.xxx.xxx.xxx
iptables -t filter -I INPUT 1 -p tcp -s ! xxx.xxx.xxx.xxx –dport ssh -j REJECT –reject-with icmp-host-prohibited
Closing MySQL port except localhost
iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp –destination-port 3306 -j ACCEPT
Listing iptables list
iptables -L
Removing iptables data (Removing first one in list, type 2 for second one)
iptables -D INPUT 1
Forgot WordPress Admin Password
May2
Firstly; you need access to your MySQL Server and maybe access to your wp-config.php file in the root of your site.
- Go www.md5-creator.com and type in a password you want to use.Example type “123″ then copy. “123 in MD5 :” ’s result is “202cb962ac59075b964b07152d234b70″
- Than open your MySQL database, and click the wp_users table
- Change “user_pass” value to copied value. In our example “202cb962ac59075b964b07152d234b70″
- Now go to your blog and log in
Social
