Today I searched mysql get age from date of birth. I was looking get some demographic data from my database’s user table. I found a good starting query from java2s.com which returns each users age in a table called bird.
SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth))
- (RIGHT(CURDATE(),5)
AS age
FROM Bird;
I modified the query to get the average age.
SELECT avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))) as averageAge
FROM user_info;
I also wanted to see the average age for each sex.
SELECT sex, avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))) as averageAge
FROM user_info
group by sex
Then i wanted to see the percentage of male and female in the database.
SELECT sex, (100 * count(sex)/(
select count(sex) from user_info
)) as percentSex
FROM user_info
group by sex
Also i wanted to see the percentages for a certain age group
// XX replace with age range you are looking for
SELECT (100 * count(birthday)/(
select count(birthday) from user_info
)) as percentage
FROM user_info
where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX
I wanted to see what percentages for male and female were for a certain age group.
// XX replace with age range you are looking for
SELECT sex, (100 * count(birthday)/(
select count(birthday) from user_info where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX
)) as percentSex
FROM user_info
where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))
between XX and XX
group by sex
This data will be extremely helpful in my marketing and I was surprised by the results I found.
Today I searched how to allow free shipping for a specific shopper group in the Virtuemart shopping cart. There was a shopper group extension in development for this, but it does not work with our 1.1 version of Virtuemart.
I decided to dive into the source code and tackle the problem on my own. Below are the details of how to implement this on your Virtuemart system should you have need.
The first thing I did was added a column to the shopper group table. This will flag the shopper group to have free shipping in the cart if you so desire.
/*You may have to alter the table name if your prefix does not start with jos_vm_ which is the default*/
ALTER TABLE `jos_vm_shopper_group` ADD COLUMN `shopper_group_freeshipping` TINYINT(1) UNSIGNED AFTER `default`;
There is on PHP file that must be edited. It is located in the following folder under your Joomla installation directory.
\administrator\components\com_virtuemart\classes\ps_checkout.php
At or around line 63 you will see the following if statement
if($vendor_freeshipping > 0 && $vars['order_subtotal_withtax'] >= $vendor_freeshipping) {
That line needs to be replaced with the following
$db = new ps_DB();
$q = 'SELECT shopper_group_freeshipping from #__{vm}_shopper_vendor_xref xref
inner join #__{vm}_shopper_group shopper on xref.shopper_group_id = shopper.shopper_group_id
where xref.user_id = ' . $_SESSION['auth']["user_id"];
$db->query($q);
$db->next_record();
$free_shoppergroup_shipping = $db->f('shopper_group_freeshipping') == 1;
//original line at 63
if(($vendor_freeshipping > 0 && $vars['order_subtotal_withtax'] >= $vendor_freeshipping) || $free_shoppergroup_shipping) {
A little bit further down there is this if statement
if(empty($_REQUEST['ship_to_info_id']) && ps_checkout::noShipToNecessary()) {
$db = new ps_DB();
You can comment out or remove the $db = new ps_DB();. It is now already being instantiated due to our new code.
There is nothing else that needs to be changed. You will have to manually flag shopper groups in the db using the new shopper_group_freeshipping column that was added. This should have a value of 1 for any shopper groups that need to get free shipping. All other groups can have a value of NULL or 0 to get the standard shipping you have configured in your installation.
I will make another post if I tackle adding a checkbox on the shopper group to update this field. If anyone else does it please let me know and I will post your code or provide a link to your blog.
Today I searched for a version of ChrisControl that would work with Windows 7. I had been using this at work with XP and it is awesome for doing LAN remote control support. It allows for connection to a windows machine via RDP or VNC. VNC is automatically installed on the target machine and connects without a password. This is all assuming you have access to the administrative share of the target machine. It also automatically uninstalls VNC after the support session has ended. There is no need to have someone at the other end of the connection initiate it or accept it.
I was really bummed when I found out my version of ChrisControl did not work with Windows 7. I found out that have a new beta version that has worked great so far. If you do support, I strongly recommend this program. The beta version can be downloaded here.
Today I searched on how to get the city of a user based on their ip address. I was looking to do this using PHP and preferably for free. There was quiet a bit out there that had a free database you could download to cross reference that would return the country. However, we wanted to be able to get the city and state as well. After quiet a bit of googling, I came across a free API from ipgp.net. They allow you to post the ip to a URL and they return an XML packet with the county, a path to the image of the country’s flag, city, state, isp, latitude, and longitude. They have an example on how to do this on their page. We created our own class below for your use that is a little cleaner. It can be included in your php scripts and you don’t have to worry about downloading anything into MySQL.
/*
Provided by http://www.todayisearched.com
*/
class GeoIP {
/*
Returns all possible Geo Ip information in an associative array. The following pieces of information
are returned:
IP - IP ADDRESS LOOKED UP
CODE - COUNTRY CODE
COUNTY - COUNTRY NAME
FLAG - PATH TO IMAGE OF THE COUNTRY'S FLAG
CITY - CITY NAME
REGION - STATE NAME
ISP - ISP NAME
LAT - LATITUDE CORDINATE
LNG - LONGITUDE CORDINATE
USAGE:
$ipinfo = GeoIP :: getGeoArray('xxx.xxx.xxx.xxx');
echo $ipinfo['CITY'] . ', ' . $ipinfo['STATE'];
*/
public static function getGeoArray($ip) {
$file = "http://www.ipgp.net/api/xml/". $ip;
$xml_parser = xml_parser_create();
$fp = fopen($file, "r");
$data = fread($fp, 80000);
xml_parse_into_struct($xml_parser, $data, $vals);
$iplookup = array();
foreach ($vals as $v) {
if (isset($v['tag']) && isset($v['value'])) {
$iplookup[$v['tag']] = $v['value'];
}
}
xml_parser_free($xml_parser);
fclose($fp);
return $iplookup;
}
//shortcut to get the city name
public static function getCity($ip) {
$a = self :: getGeoArray($ip);
return $a['CITY'];
}
//shortcut to get the state name
public static function getState($ip) {
$a = self :: getGeoArray($ip);
return $a['REGION'];
}
}
Follow these steps to implement the above script:
- Copy the script above and save it into a php file on your server
- Add include_once(‘Path To The PHP File Saved On #1′);
- Use the code snippet below in your php to obtain geoip information.
include_once "geoip.php";
$ipinfo = GeoIP :: getGeoArray($_SERVER['REMOTE_ADDR']);
echo 'Hello, Your City And State Is ' . $ipinfo['CITY'] . ', ' . $ipinfo['REGION'];
Thanks http://www.ipgp.net for providing this invaluable service!