OPTIMIZE TABLE foo;
ALTER TABLE foo ORDER BY something;

-- Table: ce_mak

-- DROP TABLE IF EXISTS `ce_mak`;

CREATE TABLE `ce_mak` (
  `Mak_ID`              int(10) AUTO_INCREMENT NOT NULL,
  `Mak_Baslik`          longtext,
  `Mak_KulID`           int(10),
  /* Keys */
  PRIMARY KEY (`Mak_ID`)
) ENGINE = MyISAM
  CHARACTER SET `utf8` COLLATE `utf8_general_ci`;

INSERT INTO `ce_mak` (`Mak_ID`, `Mak_Baslik`, `Mak_KulID`) VALUES (1, 'Deneme', 93);
INSERT INTO `ce_mak` (`Mak_ID`, `Mak_Baslik`, `Mak_KulID`) VALUES (2, 'Deneme1', 75);
INSERT INTO `ce_mak` (`Mak_ID`, `Mak_Baslik`, `Mak_KulID`) VALUES (3, 'Deneme2', 75);
INSERT INTO `ce_mak` (`Mak_ID`, `Mak_Baslik`, `Mak_KulID`) VALUES (3, 'Deneme2', 85);


-- Table: ce_yaz

-- DROP TABLE IF EXISTS `ce_yaz`;

CREATE TABLE `ce_yaz` (
  `Yaz_ID`         int(10) AUTO_INCREMENT NOT NULL,
  `Yaz_Isim`       longtext,
  /* Keys */
  PRIMARY KEY (`Yaz_ID`)
) ENGINE = MyISAM
  CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
  
  INSERT INTO `ce_yaz` (`Yaz_ID`, `Yaz_Isim`) VALUES (85, 'Ali');
  INSERT INTO `ce_yaz` (`Yaz_ID`, `Yaz_Isim`) VALUES (2, 'Veli');
  INSERT INTO `ce_yaz` (`Yaz_ID`, `Yaz_Isim`) VALUES (3, 'Ahmet');
  INSERT INTO `ce_yaz` (`Yaz_ID`, `Yaz_Isim`) VALUES (4, 'Mehmet');


-- Table: ce_uye

-- DROP TABLE IF EXISTS `ce_uye`;

CREATE TABLE `ce_uye` (
  `Uye_ID`         int(10) AUTO_INCREMENT NOT NULL,
  `Uye_Isim`       longtext,
  /* Keys */
  PRIMARY KEY (`Uye_ID`)
) ENGINE = MyISAM
  CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
  
  INSERT INTO `ce_uye` (`Uye_ID`, `Uye_Isim`) VALUES (93, 'Cetin');
  INSERT INTO `ce_uye` (`Uye_ID`, `Uye_Isim`) VALUES (75, 'Metin');
  INSERT INTO `ce_uye` (`Uye_ID`, `Uye_Isim`) VALUES (3, 'Ekin');
  INSERT INTO `ce_uye` (`Uye_ID`, `Uye_Isim`) VALUES (4, 'Gulin');

SELECT DISTINCT
	invoice.invoice_id,
	invoice.invoice_total,
	invoice.invoice_paid,
	invoice.invoice_tax,
	invoice.ts_printed,
	invoice.ts_add,
	ROUND(invoice.invoice_total-invoice.invoice_paid, 2) AS to_pay,
	orders.customer_ref,
	delivery_note.delivery_note_id
	FROM
	invoice
	Inner Join order_items ON order_items.invoice_ref = invoice.invoice_id
	Inner Join orders ON order_items.order_ref = orders.order_id
	Inner Join delivery_note ON order_items.delivery_note_ref = delivery_note.delivery_note_id
	WHERE
	orders.customer_ref = '$cid'
	AND
	invoice.invoice_paid < invoice.invoice_total
	ORDER BY
	invoice.invoice_id DESC

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("my_db", $con);
 
$result = mysql_query("SELECT * FROM Persons
WHERE FirstName='Peter'");
 
while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }
?>

sudo mkdir /var/mysql
sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /var/mysql/mysql.sock
sudo chown _mysql /var/mysql/mysql.sock
sudo chmod 777 /var/mysql/mysql.sock

echo 'export PATH=/Applications/MAMP/Library/bin:$PATH' >> ~/.bash_profile

mysql -u USERNAME -p DBNAME < FILE.sql

ALTER TABLE
table_name
CHANGE column_name column_name int(11)
AFTER other_column_name

show create database mysql;

DROP PROCEDURE IF EXISTS createQueenslandWards;
DELIMITER //
CREATE PROCEDURE createQueenslandWards()
	BEGIN
		DECLARE done INT DEFAULT 0;
		DECLARE wardName varchar(255);
		DECLARE Lang varchar(255);
		DECLARE Lat varchar(255);
		DECLARE cursorWard CURSOR FOR SELECT DISTINCT `ward` FROM `regions` WHERE `territory` = 'QLD';
		DECLARE cursorLangLat CURSOR FOR SELECT `centroid_y`, `centroid_x` FROM `regions` WHERE `ward` = wardName;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

		OPEN cursorWard;

		REPEAT
			FETCH cursorWard INTO wardName;
			IF NOT done THEN
				OPEN cursorLanglat;
					block2: BEGIN
						DECLARE doneLangLat INT DEFAULT 0;
						DECLARE firstLang varchar(255) DEFAULT '';
						DECLARE firstLat varchar(255) DEFAULT '';
						DECLARE i int DEFAULT 1;
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneLangLat = 1;
						
						REPEAT
							FETCH cursorLangLat INTO Lang, Lat;
							
							IF i == 1 THEN
								SET firstLang = Lang;
								SET firstLat = Lat;
							END IF;
							
							INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', Lang, ' ', Lat ,')')), `type` = wardName, to_text = CONCAT(Lang, ',', Lat), `description` = 'Queensland territory';
							SET i = i + 1;
						UNTIL doneLangLat END REPEAT;

						INSERT INTO `points` SET `coordinates` = GeomFromText(CONCAT('POINT(', firstLang, ' ', firstLat ,')')), `type` = wardName, to_text = CONCAT(firstLang, ',', firstLat), `description` = 'Queensland territory';

					END block2;
				CLOSE cursorLangLat;
			END IF;
		UNTIL done END REPEAT; 

		CLOSE cursorWard;

	END//
DELIMITER ;
CALL createQueenslandWards();

DROP TABLE IF EXISTS `points`;
CREATE TABLE `points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coordinates` point NOT NULL,
  `type` varchar(20) DEFAULT 'click',
  `to_text` varchar(255) DEFAULT '',
  `description` text DEFAULT '',
  PRIMARY KEY (`id`),
  SPATIAL KEY `coordinates` (`coordinates`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `polygons`;
CREATE TABLE `polygons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `polygon_data` polygon NOT NULL,
  `type` varchar(20) DEFAULT 'click',
  `description` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  SPATIAL KEY `polygon_data` (`polygon_data`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.88866 138.164063)') , type = 'QLD', to_text = '-16.88866 138.164063', `description` = 'Queensland border'; # First/Last point
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 138.186035)'), type = 'QLD', to_text = '-25.972532 138.186035', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.972532 141.262207)'), type = 'QLD', to_text = '-25.972532 141.262207', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.897268 141.262207)'), type = 'QLD', to_text = '-28.897268 141.262207', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.820292 148.952637)'), type = 'QLD', to_text = '-28.820292 148.952637', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.279873 150.270996)'), type = 'QLD', to_text = '-28.279873 150.270996', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.704722 151.501465)'), type = 'QLD', to_text = '-28.704722 151.501465', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-28.550429 153.391113)'), type = 'QLD', to_text = '-28.550429 153.391113', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-22.97323 150.534668)') , type = 'QLD', to_text = '-22.97323 150.534668', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.994263 146.052246)'), type = 'QLD', to_text = '-18.994263 146.052246', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.705467 144.470215)'), type = 'QLD', to_text = '-14.705467 144.470215', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-14.620439 143.635254)'), type = 'QLD', to_text = '-14.620439 143.635254', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-11.280414 142.536621)'), type = 'QLD', to_text = '-11.280414 142.536621', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.988152 141.350098)'), type = 'QLD', to_text = '-16.988152 141.350098', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.03585 140.251465)') , type = 'QLD', to_text = '-18.03585 140.251465', `description` = 'Queensland border';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-16.88866 138.164063)') , type = 'QLD', to_text = '-16.88866 138.164063', `description` = 'Queensland border'; # First/Last point


INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-19.367813 141.04248)') , type = 'QLD_AREA_1', to_text = '-19.367813,141.04248', `description` = 'Queensland block/1';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-25.100192 141.306152)') , type = 'QLD_AREA_1', to_text = '-25.100192,141.306152', `description` = 'Queensland block/1';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-22.97323 150.534668)') , type = 'QLD_AREA_1', to_text = '-22.97323,150.534668', `description` = 'Queensland block/1';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-18.994263 146.052246)') , type = 'QLD_AREA_1', to_text = '-18.994263,146.052246', `description` = 'Queensland block/1';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-19.367813 141.04248)') , type = 'QLD_AREA_1', to_text = '-19.367813,141.04248', `description` = 'Queensland block/1';

INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-21.646536 143.217773)') , type = 'click', to_text = '-21.646536,143.217773', `description` = 'QLD, IN!';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-20.662941 134.165039)') , type = 'click', to_text = '-20.662941,134.165039', `description` = 'Northern territory, OUT!';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(-27.867569 144.008789)') , type = 'click', to_text = '-27.867569,144.008789', `description` = 'QLD, IN!';
INSERT INTO `points` SET `coordinates` = GeomFromText('POINT(50.068658 14.430542)') , type = 'click', to_text = '50.068658,14.430542', `description` = 'Europe/prague, OUT!';

-- PROCEDURE THAT CREATES QLD MAP POLYGON
DROP PROCEDURE IF EXISTS createPolygon;
DELIMITER //
CREATE PROCEDURE createPolygon(IN `pointType` VARCHAR(50))
	BEGIN
		DECLARE pointIdStart int;
		DECLARE pointIdEnd int;
		DECLARE polygonData text DEFAULT '';
		DECLARE polygonToInsert polygon;
		DECLARE coord text DEFAULT '';
		
		SET pointIdStart = (SELECT MIN(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);
		SET pointIdEnd = (SELECT MAX(`id`) as `id` FROM `points` WHERE `type` = pointType LIMIT 1);

		WHILE pointIdStart <= pointIdEnd DO
			SET coord = (SELECT `to_text`  FROM `points` WHERE `type` = pointType AND `id` = pointIdStart LIMIT 1);
			SET coord = REPLACE(coord, ',', ' ');
			SET polygonData = concat(coord, ',', polygonData);
			SET pointIdStart = pointIdStart + 1;
		END WHILE;
		SET polygonToInsert = GeomFromText(CONCAT('POLYGON((', SUBSTR(polygonData, 1, CHAR_LENGTH(polygonData) - 1) , '))'));
		INSERT INTO `polygons` SET `polygon_data` = polygonToInsert, `type` = pointType, `description` = '';
	END//
DELIMITER ;
CALL createPolygon('QLD');
CALL createPolygon('QLD_AREA_1');

/**
 * FUNCTION THAT CHECKS WHETHER coordinate IS WITHIN polygon
 * @param VARCHAR(50) Lat,Lng
 * @param VARCHAR(50) Name/Type of polygon
 */
DROP FUNCTION IF EXISTS checkCoordinatesInPolygon;
DELIMITER //
CREATE FUNCTION checkCoordinatesInPolygon(`coordinates` VARCHAR(50),`polygonType` VARCHAR(50)) RETURNS int
	BEGIN
	DECLARE polygonData polygon;
	SET polygonData = (SELECT polygon_data FROM `polygons` WHERE `type` = polygonType LIMIT 1);
	SET coordinates = REPLACE(coordinates, ',', ' ');
	RETURN (SELECT contains(polygonData, GeomFromText(CONCAT('POINT(', coordinates ,')'))) LIMIT 1);
	END//
DELIMITER ;
SELECT checkCoordinatesInPolygon('-20.730086,144.470215', 'QLD_AREA_1'), checkCoordinatesInPolygon('-22.97323,139.680176', 'QLD_AREA_1'), checkCoordinatesInPolygon('-21.386249,143.986816', 'QLD_AREA_1');