How can I join the count in MySQL?

If I have two tables one is:

CAR

| ID |  MAKER |---------------|  1 |  Honda ||  2 | Toyota ||  3 |   Ford ||  4 |  Honda ||  5 |   Ford ||  6 |  Honda |

where id is the car id number & maker is the maker of the car

and the other is

Purchase

| CUSTID | CARID |------------------|      1 |     1 ||      1 |     4 ||      1 |     6 ||      2 |     1 ||      2 |     2 ||      2 |     4 ||      2 |     6 ||      3 |     2 ||      4 |     5 ||      4 |     2 |

where custid is the id of the customer & carid is the id to a specific car

Is there a way to join the two together & then figure out which customers have bought ALL the Hondas?

Try this query:

select P.CustIDfrom purchase Pinner join CAR C on P.CarID=C.CarIDWHERE C.Maker like '%Honda%'group by CustIDhaving count(P.CarID)=    (select count(*) from CAR C where C.Maker like '%Honda%')

SQL FIDDLE

How to create distributed/mirrored dynamic scripting website (Step - 4 Region detection and redirection)

Step four: Region detection & redirection
First of all, download the latest database

wget software77.net/geo-ip/?DL=1 -O /path/IpToCountry.csv.gz

Create a table in your website database & import the data from the csv file

CREATE TABLE IF NOT EXISTS `ip2country` (  `start` int(10) unsigned NOT NULL DEFAULT '0',  `end` int(10) unsigned NOT NULL DEFAULT '0',  `registry` varchar(50) NOT NULL,  `assigned` varchar(50) NOT NULL,  `a2` char(2) NOT NULL DEFAULT '',  `a3` char(3) NOT NULL DEFAULT '',  `country` varchar(100) NOT NULL DEFAULT '',  PRIMARY KEY (`start`,`end`),  KEY `a2` (`a2`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;LOAD DATA LOCAL INFILE 'YOUR_PATH/IpToCountry.csv' INTO TABLE ip2country FIELDS TERMINATED BY ',' ENCLOSED BY '"'  LINES TERMINATED BY '\n';

Now just need add a few line of php code at the beginning of index.php file to handle the redirect.

//If the visitor is search engine spider, then do not redirect.function check_if_spider(){	$spiders = array('Googlebot', 'Yammybot', 'Openbot', 'Yahoo', 'Slurp', 'msnbot', 'ia_archiver', 'Lycos', 'Scooter', 'AltaVista', 'Teoma', 'Gigabot', 'Googlebot-Mobile');	foreach ($spiders as $spider)	{		if (eregi($spider, $_SERVER['HTTP_USER_AGENT']))		{			return TRUE;		}	}	return FALSE;}function checkip(){                if(check_if_spider()) return;		$client_ip=ip2long($_SERVER['REMOTE_ADDR']);		$rs = $db->query("select `a2` from `ip2c` where `start` <= $client_ip & `end` >= $client_ip");		$row = $db->fetch_array($rs);		//$row[0] will be the country code.                switch($row[0]){                    case 'US':                        //..301 Redirection                        header("HTTP/1.0 301 Moved Permanently");			header("Location: http://us.example.com".$_SERVER['REQUEST_URI']);                     break;                    default:                       //... 301 Redirection                    break;                }	}}checkip();

How to create distributed/mirrored dynamic scripting website (Step - 1 File synchronization)

This tutorial introduce a effortless way to build a distributed dynamic scripting website between Linux servers in two different zone.

To build up a dynamic website mirror for different zone/region/country without using Content Delivery Network, you should consider:

1. File Synchronization
2. Database Synchronization
3. Security
4. Region Detection And Redirection

Step one: File Synchronization
We will use rsync for file synchronization

rsync --progress --stats --compress --rsh=/usr/bin/ssh --recursive --times --perms --links --exclude "*bak" --exclude "*~" --exclude "config.php"  /var/www/path/* DESTINATION:/var/www/path/

It is recommended to create a batch file & run this command every 5 minutes, you may need to detect if rsync is already running, checkout here to find out how to detect if a process is already running. You may have muptiple hosts, yet the principle should be the same.

Next: How to create distributed/mirrored dynamic scripting website (Step – 2 Database synchronization)

How to create distributed/mirrored dynamic scripting website (Step - 2 Database synchronization)

Step two: Database Syncrhonization
This is the key of the dynamic website synchronization, I will use mysql server 5.x for example, if you are using a different database server, you will need to find out how to configure their replication.

1. Configure Master & Slave Server
Create a mysql user for replication

GRANT REPLICATION SLAVE ON *.* to 'user_rep'@'SLAVE_IP_ADDRESS' IDENTIFIED BY 'password'; 

Edit my.cnf file [mysqld] section
server-id=10
log-bin=mysql-bin
innodb_flush_logs_at_trx_commit=1
innodb_support_xa=1
sync_binlog=1

2. Copy the database:
We need to lock table on master server before we dump the database

FLUSH TABLE WITH READ LOCK;SHOW MASTER STATUS;

Write down the bin log file record & the postion & leave this connection open, then create a new ssh connection to the master server & dump the databse with:

#mysqldump -u root -p --database | gzip -c > all_database.sql.gz

Copy all_database.sql.gz to slave server & extract the files & import into slave database

source all_database.sql

An alternative way is to copy the content of folder /var/lib/mysql/data/* from master to slave.

Now you received exactly the same database on the salve server, then run SQL command on slave server

CHANGE MASTER TO master_host='MASTER_IP', master_user='user_rep', master_password='password',master_log_file='mysql-bin.XXXXXX',master_log_pos=XXXXX;START SLAVE;SHOW SLAVE STATUS; 

If you see Thread IO & Thread SQL are both running, then your database should be synchronized
Now go back to master server mysql session & run

UNLOCK TABLE;

Next: How to create distributed/mirrored dynamic scripting website (Step – 3 Security)

Mysql Change current database characterset

Assuming you have a database with default character set latin-1 & wish to convert it to utf-8, it is very simple to convert it without any external tool

1. Dump the database:

mysqldump -h localhost -u changeit -pchangeit --opt --compatible=mysql40 --default-character-set=latin1 database>database.sql

parameter –compatible=mysql40 is to dump the database without charset settings.

2. Create a database utf8 default:

CREATE DATABASE `database_new` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

3. Reimport the database

mysql -h localhost -u changeit -pchangeit --default-character-set=utf8 database_new < database.sql

Now you have received the utf8 character set database_new.

Run multiple SQL query in PHP when using SQL variables

Some times you may need to run mulitple query in PHP to obtain the query result you want. eg

SELECT `column` INTO @column FROM table LIMIT 1;SET @query = CONCAT("SELECT DISTINCT table2.",@column,"  FROM table2");PREPARE stmt FROM @query;EXECUTE stmt;

Here is an example function

function doMultiQuery{if(mysqli_multi_query($con, $query)){  do {     if ($result = mysqli_store_result($con)) {           if(mysqli_num_rows($result)>0)           {                 return $result;           }           @mysqli_free_result($con);     }   } while (@mysqli_next_result($con));}}