Archive for the ‘MySQL’ Category

Google maps, mySQL locations, PHP service

Wednesday, May 4th, 2016

HTML:

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
    <link rel="stylesheet" href="dealers.css">
</head>
    <body>
        <div class="row mapSection">
            <div class="col-xs-12">

                <div id="map"></div>
                <div id="panel">
                    <div id="entryRow">
                        <div><select id="locationSelect" style="width:100%;visibility:hidden"></select></div>
                        <input type="text" id="addressInput" />
                        <select id="radiusSelect">
                            <option value="5" selected>5 miles</option>
                            <option value="25" selected>25 miles</option>
                            <option value="100">100 miles</option>
                            <option value="200">200 miles</option>
                            <option value="500">500 miles</option>
                        </select>
                        <input type="text" id="find" value="Search"/>
                        <input type="button" id="refresh" value="redo Search"/>
                    </div>
                    <ul class="resultDealers">
                    </ul>
                </div>

            </div>
        </div>

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS" crossorigin="anonymous"></script>
        <script src="https://maps.googleapis.com/maps/api/js?key=***YOUR*KEY*HERE***" type="text/javascript"></script>
        <script type="text/javascript" src="Maps.js"></script>
    </body>
</html>

CSS:

body {
  font-family: sans-serif;
  margin: 0px;
  padding: 0px;
}
#map,
#panel {
  height: 500px;
  float: left;
}
#map {
  width: 60%;
}
#entryRow {
  margin: 0 0 0 15px;
}
#panel {
  width: 40%;
}
.resultDealers {
  padding: 0;
  margin: 15px;
  overflow: scroll;
  height: 420px;
}
.resultDealers li {
  list-style-type: none;
  border-left: 1px solid;
  border-top: 1px solid;
  border-right: 1px solid;
  padding: 10px;
}
.resultDealers li:last-child {
  border-bottom: 1px solid;
}
.highlightDealer {
  background: #E8E8E8;
}
.markerBox {
  cursor: pointer;
}
.markerBox:hover {
  background: #E3F4F9;
}
#panel .feature-filter label {
  width: 130px;
}
p.attribution,
p.attribution a {
  color: #666;
}
#find {
  background: #DEDCDC;
  border: 1px solid gray;
  width: 65px;
  padding: 2px;
  text-align: center;
  font-weight: bold;
  cursor: pointer;
  margin-left: 15px;
}
#find:hover {
  background: #F9F4F4;
}
#radiusSelect {
  margin-left: 15px;
}
#addressInput {
  float: left;
  margin-left: 5px;
  padding-left: 10px;
  width: 65px;
}
#locationSelect {
  margin: 10px 5px;
  width: 95% !important;
}

JavaScript:

/* ---------  Start Maps.js  --------- */

Maps Module
//Usage: Maps.init();

Maps = (function ( window, google ) {

	var map,
		markers = [],
		infoWindow,
		locationSelect;

	var load = function load () {
			map = new google.maps.Map(document.getElementById("map"), {
				center: new google.maps.LatLng(40, -100),
				zoom: 4,
				mapTypeId: 'roadmap',
				// scrollWheel: false,
				// zoomControl: false,
				streetViewControl: false,
				rotateControl: false,
				fullscreenControl: false,
				navigationControl: false,
				scaleControl: false,
				// draggable: false,
				mapTypeControl: false,
				mapTypeControlOptions: {style: google.maps.MapTypeControlStyle.DROPDOWN_MENU}
		});
		infoWindow = new google.maps.InfoWindow();

		locationSelect = document.getElementById("locationSelect");
		locationSelect.onchange = function() {
			var markerNum = locationSelect.options[locationSelect.selectedIndex].value;
			if (markerNum != "none"){
				google.maps.event.trigger(markers[markerNum], 'click');
			}
		};

		refreshMap();

		google.maps.event.addListener(map, 'zoom_changed', function() {
			//$('#radiusSelect option:selected').next().attr('selected', 'selected');
			//console.log(map.getCenter());
			//searchLocationsNear (map.getCenter());
		});

	}

	var bindInfoWindow = function bindInfoWindow ( marker, map, infoWindow, html ) {
      google.maps.event.addListener(marker, 'click', function() {
        infoWindow.setContent(html);
        infoWindow.open(map, marker);
      });
    }

    var refreshMap = function refreshMap () {
    	if(navigator.geolocation) {
			navigator.geolocation.getCurrentPosition(function(position) {

				var latLng = new google.maps.LatLng(position.coords.latitude,position.coords.longitude);

				var geocoder = new google.maps.Geocoder();

				geocoder.geocode({ 'latLng': latLng}, function (results, status) {
					if (status == google.maps.GeocoderStatus.OK) {

						for ( var j = 0; j < results[0].address_components.length; j++ ) {
							for ( var k = 0; k < results[0].address_components[j].types.length; k++ ) {
								if ( results[0].address_components[j].types[k] == "postal_code" ) {
									zipcode = results[0].address_components[j].short_name;
								}
							}
						}

						document.getElementById("addressInput").value = zipcode;
						searchLocations();
					} else {
						console.log("Geocoding failed: " + status);
					}
				});
			});
		}
	}

	var downloadUrl = function downloadUrl ( url, callback ) {
		var request = window.ActiveXObject ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest;
		request.onreadystatechange = function() {
			if ( request.readyState == 4 ) {
				request.onreadystatechange = doNothing;
				callback(request.responseText, request.status);
			}
		};

		request.open('GET', url, true);
		request.send(null);
	}

	var searchLocations = function searchLocations () {
		var address = document.getElementById("addressInput").value;
		var geocoder = new google.maps.Geocoder();
		geocoder.geocode({address: address}, function(results, status) {
			if (status == google.maps.GeocoderStatus.OK) {
	  			searchLocationsNear(results[0].geometry.location);
			} else {
	  			alert(address + ' not found');
			}
		});
	}

	var clearLocations = function clearLocations() {
		infoWindow.close();
		for (var i = 0; i < markers.length; i++) {
			markers[i].setMap(null);
		}
		markers.length = 0;

		locationSelect.innerHTML = "";
		var option = document.createElement("option");
		option.value = "none";
		option.innerHTML = "See all results:";
		locationSelect.appendChild(option);
	}

	var searchLocationsNear = function searchLocationsNear ( center ) {
		clearLocations();

		var radius = document.getElementById('radiusSelect').value;
		var searchUrl = '/dealers.php?lat=' + center.lat() + '&lng=' +  center.lng() + '&radius=' + radius;
		downloadUrl( searchUrl, function(data) {
			var xml = parseXml(data);
			var markerNodes = xml.documentElement.getElementsByTagName("marker");
			var bounds = new google.maps.LatLngBounds();
			for (var i = 0; i < markerNodes.length; i++) {
				var id = markerNodes[i].getAttribute("id");
				var name = markerNodes[i].getAttribute("name");
				var address = markerNodes[i].getAttribute("address");
				var city = markerNodes[i].getAttribute("city");
				var state = markerNodes[i].getAttribute("state");
				var zip = markerNodes[i].getAttribute("zip");
				var url = markerNodes[i].getAttribute("url");
				var email = markerNodes[i].getAttribute("email");
				var distance = parseFloat(markerNodes[i].getAttribute("distance"));
				var latlng = new google.maps.LatLng(
					parseFloat(markerNodes[i].getAttribute("lat")),
					parseFloat(markerNodes[i].getAttribute("lng")));

				createOption(name, distance, i);
				createMarker(id, latlng, name, address, city, state, zip, url, email);
				bounds.extend(latlng);
			}
			map.fitBounds(bounds);
			locationSelect.style.visibility = "visible";
			locationSelect.onchange = function() {
			 var markerNum = locationSelect.options[locationSelect.selectedIndex].value;
			 google.maps.event.trigger(markers[markerNum], 'click');
			};
		});
	}

	var createMarker = function createMarker ( id, latlng, name, address, city, state, zip, url, email ) {
		var html = "<div class='marker-" + id + "'><b>" + name + "</b> <br/>" + address +  "<br/>" + city + " " + state + ", " + zip + "<br />" + url + "<br /><a href='mailto:" + email + "'>" + email + "</a></div>";
		var marker = new google.maps.Marker({
			map: map,
			position: latlng
		});
		google.maps.event.addListener(marker, 'click', function() {
			//infoWindow.setContent(html);
			//infoWindow.open(map, marker);
			$("li[class*='marker-']").removeClass('highlightDealer');
			$("li.marker-" + id).addClass('highlightDealer');
			window.location.href = "#marker-"+ id;
			window.scrollTo(0, 0);

			map.setZoom(16);
			map.setCenter(marker.getPosition());
		});
		markers.push(marker);

		$("#panel ul").append("<li class='markerBox marker-" + id + "'><a name='marker-" + id + "'></a><b>" + name + "</b><br/>" + address +  "<br/>" + city + " " + state + ", " + zip + "<br />" + url + "<br /><a href='mailto:" + email + "'>" + email + "</a></li>");
		$('.marker-' + id).on('click', function() {
			$("li[class*='marker-']").removeClass('highlightDealer');
			$("li.marker-" + id).addClass('highlightDealer');
			map.setZoom(16);
			map.setCenter(marker.getPosition());
		});
	}

	var createOption = function createOption ( name, distance, num ) {
		var option = document.createElement("option");
		option.value = num;
		option.innerHTML = name + "(" + distance.toFixed(1) + ")";
		locationSelect.appendChild(option);
	}

	var parseXml = function parseXml(str) {
		if (window.ActiveXObject) {
			var doc = new ActiveXObject('Microsoft.XMLDOM');
			doc.loadXML(str);
			return doc;
		} else if (window.DOMParser) {
			return (new DOMParser).parseFromString(str, 'text/xml');
		}
	}

    function doNothing() {}

	$('#find').on('click', function() {
		$('#panel ul').empty();
		searchLocations();
	});
	$('#addressInput').on('click', function () {
		$(this).val('');
	});
	$('#radiusSelect').on('change', function () {
		$('#panel ul').empty();
		searchLocations();
	});
	$('#refresh').on('click', function () {
		var bounds = map.getBounds();
		var ne = bounds.getNorthEast(); // LatLng of the north-east corner
		var sw = bounds.getSouthWest(); // LatLng of the south-west corder

		var nw = new google.maps.LatLng(ne.lat(), sw.lng());
		var se = new google.maps.LatLng(sw.lat(), ne.lng());
		console.log('nw: ' + nw);
		console.log('se: ' + se);
		//searchLocationsNear();
		//searchLocations();
	});

	return {
		load: load
	}

})( window, google );

$(function(){
    loadModules.start();
})

var loadModules = loadModules || {};
loadModules.start = function(){
    // Maps module init
    Maps.load();
};
/* ---------  End Maps.js --------- */

PHP:

<?php
    header("Access-Control-Allow-Origin: *");
    $servername = "***Your*DB*URL***";
    $username = "***Your*DB*USERNAME***";
    $password = "***Your*DB*PASS***";
    $dbname = "***Your*DB*NAME***";

    // Get parameters from URL
    $center_lat = $_GET["lat"];
    $center_lng = $_GET["lng"];
    $radius = $_GET["radius"];

    // Start XML file, create parent node
    $dom = new DOMDocument("1.0");
    $node = $dom->createElement("markers");
    $parnode = $dom->appendChild($node);

    // Opens a connection to a mySQL server
    $connection=mysql_connect ($servername, $username, $password);
    if (!$connection) {
      die("Not connected : " . mysql_error());
    }

    // Set the active mySQL database
    $db_selected = mysql_select_db($dbname, $connection);
    if (!$db_selected) {
      die ("Can\'t use db : " . mysql_error());
    }

    // Search the rows in the markers table
    $query = sprintf("SELECT id, name, address, city, state, zip, url, email, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM dealers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
      mysql_real_escape_string($center_lat),
      mysql_real_escape_string($center_lng),
      mysql_real_escape_string($center_lat),
      mysql_real_escape_string($radius));
    $result = mysql_query($query);

    $result = mysql_query($query);
    if (!$result) {
      die("Invalid query: " . mysql_error());
    }

    header("Content-type: text/xml");

    // Iterate through the rows, adding XML nodes for each
    while ($row = @mysql_fetch_assoc($result)){
        $node = $dom->createElement("marker");
        $newnode = $parnode->appendChild($node);
        $newnode->setAttribute("id", $row['id']);
        $newnode->setAttribute("name", $row['name']);
        $newnode->setAttribute("address", $row['address']);
        $newnode->setAttribute("city", $row['city']);
        $newnode->setAttribute("state", $row['state']);
        $newnode->setAttribute("zip", $row['zip']);
        $newnode->setAttribute("url", $row['url']);
        $newnode->setAttribute("email", $row['email']);
        $newnode->setAttribute("lat", $row['lat']);
        $newnode->setAttribute("lng", $row['lng']);
        $newnode->setAttribute("distance", $row['distance']);
    }

    echo $dom->saveXML();

?>

Mysql Command Line Import

Monday, March 12th, 2012

navigate to the folder containing the .sql file you’d like to import

>mysql -user -password databaseToImportInto < newData.sql

return – no ; (unless you are IN mysql>)

password:

example:

>mysql -root -p someDB<newData.sql (return)

password: (return – default config has none, unless you’ve, hopefully, changed it, then enter it)

MYSQL Query clear all data in a column

Tuesday, December 13th, 2011

UPDATE table SET column = ”

example:

UPDATE wp_posts SET post_excerpt = ”

WordPress creating users in MySQL via script wp_user_level wp_capabilities wp_users

Monday, January 3rd, 2011

Scenario:
Creating a WordPress blog with an entire company of users.
We want employees to already have an account, so that registration may be turned OFF otherwise.
From an excel list of employees, we’ll extract their email, make an account for each email in the WordPress database, and then they may change their passwords themselves. You can imagine what the php script will look like, to extract the email, and foreach insert the following (uniqueusername and 5 replaced of course with incrementing id numbers and changing emailnames@ourcompany.com):

 

INSERT INTO wp_users
(id,user_login,user_pass,user_email,display_name)
VALUES
(‘5′,’uniqueusername’,md5(‘pass’),’uniqueusername@ourcompany.com’,’uniqueusername’);

 

// to note, this below is simultaneously a nice example of how to insert multiple rows with one MySQL INSERT statement

 

INSERT INTO wp_usermeta
(user_id,meta_key,meta_value)
VALUES
(‘5′,’wp_capabilities’,’a:1:{s:6:”author”;s:1:”1″;}’),
(‘5′,’wp_user_level’,’10’);

 

Thus, the full script looks something like this:

 

$employees = array(’employee1@somesite.com’,’employee2@somesite.com’,’employee3@somesite.com’,’andsoon…’);

 

$startNumber = 10; // if you already have users, pick the next highest number, as it uses this for several tables below, we need them to happen at the same time so that attributes belong to specific users…

 

$db_name = “databasename”;
$db_host = “localhost”;
$db_username = “username”;
$db_password = “password”;

 

// note the nice error reporting so that we can troubleshoot connections, users, table selections, and so forth…
$link = mysql_connect($db_host, $db_username, $db_password);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
else
{
echo ‘Connected successfully<br />’;
}

 

$db_selected = mysql_select_db($db_name, $link);
if (!$db_selected) {
die (‘Can\’t use foo : ‘ . mysql_error());
}
else
{
echo ‘Selected manyvoices database successfully<br />’;
}

 

foreach($employees as $employee)
{
list($user_name, $therest) = explode(‘@’,$employee);

 

$query1 = “INSERT INTO wp_users
(id,user_login,user_pass,user_email,display_name)
VALUES
(‘” . $startNumber . “‘,'” . $user_name . “‘,md5(‘pass’),'” . $employee . “‘,'” . $user_name . “‘)
“;
//echo $query1 . “<br />”;

 

$result1 = mysql_query($query1);
if(!result1){
die(‘Error: ‘ . mysql_error());
}
else{
echo “User: ” . $user_name . ” : Added<br />”;
}

 

$query2 = “INSERT INTO wp_usermeta
(user_id,meta_key,meta_value)
VALUES
(‘” . $startNumber . “‘,’wp_capabilities’,’a:1:{s:6:\”author\”;s:1:\”1\”;}’),
(‘” . $startNumber . “‘,’wp_user_level’,’10’)
“;
//echo $query2 . “<br />”;

 

$result2 = mysql_query($query2);
if(!result2){
die(‘Error: ‘ . mysql_error());
}
else
{
echo “User Credentials: ” . $user_name . ” : Updated<br />”;
}

 

$startNumber++;
}

 

mysql_close($link);

 

 

MySQL optimization refresher notes

Wednesday, November 17th, 2010

reference: http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1/Optimizing-MySQL-Queries-and-Indexes.htm

Notes from above reference:

  • place EXPLAIN before your SELECT and learn a lot!
  • index index index…
  • in query, if possible, leave indexed field (usually after WHERE…) alone, or else every record must be calculated prior to conditional analysis…
  • ANALYZE TABLE tablename;
  • OPTIMIZE TABLE tablename
[quote block]

EXPLAIN SELECT employee_number,firstname,surname
FROM employee
WHERE employee_number= '10875';
+--------+----+-------------+----+-------+----+---+---------+
|table   |type|possible_keys|key |key_len|ref |rows| Extra  |
+--------+----+-------------+----+-------+----+---+---------+
|employee|ALL |NULL         |NULL|   NULL|NULL|  2|whereused|
+--------+----+-------------+----+-------+----+---+---------+

So what are all these things?

  • table shows us which table the output is about (for when you join many tables in the query)
  • type is an important one – it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
  • possible_keys Shows which possible indexes apply to this table
  • key And which one is actually used
  • key_len give us the length of the key used. The shorter that better.
  • ref Tells us which column, or a constant, is used
  • rows Number of rows mysql believes it must examine to get the data
  • extra Extra info – the bad ones to see here are “using temporary” and “using filesort”

[quote block end]

framework.zend.com “Create a Model and Database Table” tutorial fixed, full working code, adapted for MySQL

Monday, September 20th, 2010

http://framework.zend.com/manual/1.10/en/learning.quickstart.create-model.html

Well, I agonized over getting this, and if you read the comments on the tutorial, many others have as well, working on my home Snow Leopard as well as on my work XP pc, and I spent countless hours looking for a post just such as this one is now, to no avail, so here it all is, hope it helps somebody:

hosts

In the event that you don’t have this done already, here is what should go in your environment’s hosts file…

Mac: /private/etc/hosts

PC:  C:\WINDOWS\system32\drivers\etc

# Setup “components” Virtual Host
<VirtualHost *:80>
ServerName project
DocumentRoot “C:\xampp\htdocs\project\public// or /Library/WebServer/Documents/project/public

<Directory “C:\xampp\htdocs\project\public” // or /Library/WebServer/Documents/project/public>
Options Indexes FollowSymLinks Includes
AllowOverride All
Order allow,deny
Allow from all
</Directory>
</VirtualHost>

create the database that this will use

CREATE TABLE IF NOT EXISTS `guestbook` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(32) DEFAULT NULL,
`comment` text,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

add dummy data to the database

INSERT INTO `guestbook` (`id`, `email`, `comment`, `created`) VALUES
(1, ‘ralph@schindler.com’, ‘hello’, ‘2010-09-17 00:00:00’),
(2, ‘foo@bar.com’, ‘goodbye’, ‘2010-09-17 00:00:00’);

if you haven’t done so already, create the user this app will use to connect, and give the user the priviledges they’ll need

CREATE USER ‘guestbook’@’localhost’ IDENTIFIED BY ‘guestbook’;

GRANT ALL ON ‘guestbook’ TO ‘guestbook’@’localhost’;

project/application/Bootstrap.php

<?php
class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
public function _initAutoloader()
{
// Create an resource autoloader component
$autoloader = new Zend_Loader_Autoloader_Resource(array(
‘basePath’    => APPLICATION_PATH,
‘namespace’ => ‘Application’
));

// Add some resource types
$autoloader->addResourceTypes(array(
‘forms’ => array(
‘path’ => ‘forms’,
‘namespace’ => ‘Form’
),
‘models’ => array(
‘path’ => ‘models’,
‘namespace’ => ‘Model’
),
));

// Return to bootstrap resource registry
return $autoloader;
}

protected function _initActionHelpers()
{
Zend_Controller_Action_HelperBroker::addPath(APPLICATION_PATH .’/helpers’);
}

protected function _initDoctype()
{
$this->bootstrap(‘view’);
$view = $this->getResource(‘view’);
$view->doctype(‘XHTML1_STRICT’);
}
}

project/public/index.php

<?php
// Define path to application directory
defined(‘APPLICATION_PATH’)
|| define(‘APPLICATION_PATH’, realpath(dirname(__FILE__) . ‘/../application’));

// Define application environment
defined(‘APPLICATION_ENV’)
|| define(‘APPLICATION_ENV’, (getenv(‘APPLICATION_ENV’) ? getenv(‘APPLICATION_ENV’) : ‘development’));

// Ensure library/ is on include_path
set_include_path(implode(PATH_SEPARATOR, array(
realpath(APPLICATION_PATH . ‘/../library’),
get_include_path(),
)));

/** Zend_Application */
require_once ‘Zend/Application.php’;

// Create application, bootstrap, and run
$application = new Zend_Application(
APPLICATION_ENV,
APPLICATION_PATH . ‘/configs/application.ini’
);
$application->bootstrap()
->run();

project/application/configs/application.ini

[production]
phpSettings.display_startup_errors = 0
phpSettings.display_errors = 0
includePaths.library = APPLICATION_PATH “/../library”
bootstrap.path = APPLICATION_PATH “/Bootstrap.php”
bootstrap.class = “Bootstrap”
appnamespace = “Application”
resources.frontController.controllerDirectory = APPLICATION_PATH “/controllers”
resources.frontController.params.displayExceptions = 0
resources.view[] =
resources.layout.layoutPath = APPLICATION_PATH “/layouts/scripts/”

resources.db.adapter = “PDO_MYSQL”
resources.db.params.host = “localhost”
resources.db.params.username = “guestbook”
resources.db.params.password = “guestbook”
resources.db.params.dbname = “guestbook”

[staging : production]

[testing : production]
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1

[development : production]
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1
resources.frontController.params.displayExceptions = 1

project/application/controllers/GuestbookController.php

<?php
class GuestbookController extends Zend_Controller_Action
{

public function init()
{
/* Initialize action controller here */
}

public function indexAction()
{
$guestbook = new Application_Model_GuestbookMapper();
$guestbook->fetchAll();
//var_dump($guestbook);
$this->view->entries = $guestbook->fetchAll();
}
}

project/application/models/Guestbook.php

<?php
class Application_Model_Guestbook
{
protected $_comment;
protected $_created;
protected $_email;
protected $_id;

public function __construct(array $options = null)
{
if (is_array($options)){
$this->setOptions($options);
}
}

public function __set($name, $value)
{
$method = ‘set’ . $name;
if ((‘mapper’ == $name) || !method_exists($this, $method)){
throw new Exception(‘Invalid guestbook property’);
}
$this->$method($value);
}

public function __get($name)
{
$method = ‘get’ . $name;
if ((‘mapper’ == $name) || !method_exists($this, $method)){
throw new Exception(‘Invalid guestbook property’);
}
return $this->$method();
}

public function setOptions(array $options)
{
$methods = get_class_methods($this);
foreach($options as $key => $value){
$method = ‘set’ . ucfirst($key);
if (in_array($method, $methods)){
$this->$method($value);
}
}
}

public function setComment($text)
{
$this->_comment = (string) $text;
return $this;
}

public function getComment()
{
return $this->_comment;
}

public function setEmail($email)
{
$this->_email = (string) $email;
return $this;
}

public function getEmail()
{
return $this->_email;
}

public function setCreated($ts)
{
$this->_created = $ts;
return $this;
}

public function getCreated()
{
return $this->_created;
}

public function setId($id)
{
$this->_id = (int) $id;
return $this;
}

public function getId()
{
return $this->_id;
}
}

project/application/models/GuesbookMapper.php

<?php
class Application_Model_GuestbookMapper
{
protected $_dbTable;

public function setDbTable($dbTable)
{
if (is_string($dbTable)){
$dbTable = new $dbTable();
}
if (!$dbTable instanceof Zend_Db_Table_Abstract){
throw new Exception(‘Invalid table data gateway provided’);
}
$this->_dbTable = $dbTable;
return $this;
}

public function getDbTable()
{
if (null === $this->_dbTable){
$this->setDbTable(‘Application_Model_DbTable_Guestbook’);
}
return $this->_dbTable;
}

public function save(Application_Model_Guestbook $guestbook)
{
$data = array(
’email’    =>    $guestbook->getEmail(),
‘comment’    =>    $guestbook->getComment(),
‘created’    =>    date(‘Y-m-d H:i:s’),
);

if (null === ($id = $guestbook->getId())){
unset($data[‘id’]);
$this->getDbTable()->insert($data);
} else {
$this->getDbTable()->update($data,array(‘id = ?’ => $id));
}
}

public function find($id, Application_Model_Guestbook $guestbook)
{
$result = $this->getDbTable()->find($id);
if (0 == count($result)){
return;
}
$row = $result->current();
$guestbook->setId($row->id)
->setEmail($row->email)
->setComment($row->comment)
->setCreated($row->created);
}

public function fetchAll()
{
$resultSet = $this->getDbTable()->fetchAll();
//var_dump($resultSet);
$entries = array();
foreach ($resultSet as $row){
$entry = new Application_Model_Guestbook();
$entry->setId($row->id)
->setEmail($row->email)
->setComment($row->comment)
->setCreated($row->created);
$entries[] = $entry;
}
return $entries;
}
}

project/application/models/DbTable/Guestbook.php

<?php
class Application_Model_DbTable_Guestbook extends Zend_Db_Table_Abstract
{
/** Table name */
protected $_name    = ‘guestbook’;
}

project/application/views/scripts/guestbook/index.phtml

<p><a href=”<?php echo $this->url(
array(
‘controller’ => ‘guestbook’,
‘action’ => ‘sign’
),
‘default’,
true) ?>“>Sign Our Guestbook</a></p>

Guestbook Entries: <br />
<dl>
<?php foreach ($this->entries as $entry): ?>
<dt><?php echo $this->escape($entry->email) ?></dt>
<dd><?php echo $this->escape($entry->comment)?></dd>
<?php endforeach ?>
</dl>

final URL to hit: http://project/guestbook

MySQL Mac osx Terminal change root password

Friday, September 17th, 2010

Silly thing to note, but I had to Google for this too long to do it again…

Have to give credit where credit is due, thank you:

http://www.cyberciti.biz/faq/mysql-change-root-password/

conclusion, command:

mysqladmin -u root password NEWPASSWORD

Mac OSX remove MySQL

Sunday, September 12th, 2010
  1. sudo rm /usr/local/mysql
  2. sudo rm -rf /usr/local/mysql*
  3. sudo rm -rf /Library/StartupItems/MySQLCOM
  4. sudo rm -rf /Library/PreferencePanes/My*
  5. (Edit /etc/hostconfig) sudo vi /etc/hostconfig (Remove line MYSQLCOM=-YES)
  6. sudo rm -rf /Library/Receipts/mysql*
  7. sudo rm -rf /Library/Receipts/MySQL*
  8. sudo rm -rf /var/db/receipts/com.mysql.*

MySQl sub query example 2

Tuesday, August 17th, 2010

SELECT
q.questionId,
q.question,
q.questionTopic,
q.questionDate,
q.courseId,
q.customerId,
v.voted_this
FROM
” . $course_database_questions_table_name . ” q
LEFT JOIN
(SELECT questionId voted_this FROM votes WHERE customerId = ‘” . $customer_id . “‘) v
ON
q.questionId = v.voted_this
WHERE
q.courseId = ” . $courseId . ”
ORDER BY q.questionDate DESC

MySQL sub query example – nice

Monday, August 16th, 2010

$cp_output = “”;
if(!$get_cp_data = mysql_query(”
SELECT
q.*,
c.*,
vote_counts.Tally
FROM
questions q
LEFT JOIN
(SELECT
questionId,
count(1) as Tally
FROM
votes
GROUP BY
questionId
) vote_counts
ON
q.questionId = vote_counts.questionId
LEFT JOIN
customers c
ON
c.customerId = q.customerId
WHERE
q.courseId = 1
ORDER BY
vote_counts.Tally desc

“)){ echo mysql_error(); }

while($get_cp_data_array = mysql_fetch_array($get_cp_data)){
$q_id = $get_cp_data_array[‘questionId’];

$q_fname = $get_cp_data_array[‘customerFName’];
$q_lname = $get_cp_data_array[‘customerLName’];
$q_email = $get_cp_data_array[‘customerEmail’];

$q_topic = $get_cp_data_array[‘questionTopic’];
$q_question = $get_cp_data_array[‘question’];
$q_date = $get_cp_data_array[‘questionDate’];
//$q_count = $get_cp_data_array[‘votecount’];
$q_customer = $get_cp_data_array[‘customerId’];

if($q_topic == “”){ $q_topic = “–“; }
if($q_fname != “” || $q_lname != “”){
$from = ‘<strong>’ . $q_fname . ‘ ‘ . $q_lname . ‘</strong> – ‘;
}

# Parse the question text.
$old = array(“\n”);
$new   = array(“<br/>”);
$q_question = str_replace($old, $new, $q_question);

# Output the question.
$cp_output = $cp_output . ‘
<div>’ . $q_id . ‘</div>
<div>
<div><strong>’ . $q_topic . ‘</strong></div>
<div>’ . $q_question . ‘</div>
<div><strong>Submitted</strong> ‘ . $q_date . ‘ by ‘ . $from . ‘<a href=”mailto:’ . $q_email . ‘”>’ . $q_email . ‘</a></div>
</div>
<div></div>
‘;
}

MySQL refresher

Tuesday, July 27th, 2010

datatypes:

http://w3schools.com/sql/sql_datatypes.asp

Order results by 2 columns:

SELECT column_name(s)
FROM table_name
ORDER BY column1, column2

Simple delete:

<?php
$con = mysql_connect(“localhost”,”peter”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(“my_db”, $con);
mysql_query(“DELETE FROM Persons WHERE LastName=’Griffin'”);
mysql_close($con);
?>

date() and timestamp refresher

Tuesday, July 27th, 2010

date(format,timestamp)

format is required, example:

date(“Y-m-d”)

of course, Y, m, and d stand for Year, month, day, and they may be separated by whatever you like, so the above example returns 2010-07-27, while date(“m/d/Y”) returns 07/27/2010

now for the timestamp parameter

MAKE what should be supplied for the timestamp, with mktime(hour,minute,second,month,day,year,is_dst)

$tomorrow = mktime(0,0,0,date(“m”),date(“d”)+1,date(“Y”))

date(“Y/m/d”,$tomorrow) returns tomorrow!

using a PHP variable in a MySQL IN batch query example

Friday, July 16th, 2010

Note the ‘s

$product_list = “‘1869’, ‘1929’, ‘549’, ‘189’, ‘419’, ‘176’, ‘192’, ‘310’, ‘291’, ‘514’, ‘1643’, ‘1033’;

$db_name = “dbName”;
//conditional platform logic to support multiple connection scenarios

switch($_SERVER[‘HTTP_HOST’])
{
case ‘localhost’:
$db_host = “localhost”;
$db_username = “userName”;
$db_password = “Password”;
$dblink = mysql_connect ($db_host, $db_username, $db_password);
mysql_select_db($db_name, $dblink);
break;
case ‘www-staging.site.com’: break;
case ‘www.site.com’:
include(‘/var/www/includes/connection_script.php’);
break;
}

$result = mysql_query(”
SELECT
product_id,
product_name
FROM
tableNameHere
WHERE
product_id IN
(“.$product_list.“)
“);
if (!$result) { echo “Could not successfully run query ($sql) from DB: . mysql_error(); exit; }


//let’s break out the resource so that it can be singularly requested when needed

while($one = mysql_fetch_assoc($result))
{
$prod_id = $one[‘product_id‘];
$product_name[$prod_id] = $one[‘product_name‘];
}
@
mysql_close($dblink);

MySQL phpMyAdmin text string find replace Query

Sunday, April 25th, 2010

update TABLE_NAME set FIELD_NAME =
replace(FIELD_NAME,’find this string’,’replace it with this one’);

example (for videodb to turn all “wanted” titles into “DVD” titles):

update videodb_videodata set mediatype =
replace(mediatype,’50’,’1’);

PHP MySQL date & time reference

Wednesday, April 21st, 2010

http://www.eltcalendar.com/stuff/datemysqlphp.html

thank you for this great handy reference!

pasted verbatim:

Format PHP MySQL
Time The function is:
date(format)
where “format” consists of the letters given below.
The functions are:
date_format(string, format)
time_format(string, format)
where “format” consists of the letters given below.)
hour, 2-digit, 12-hour (01-12) h %h
hour, 2-digit, 24-hour (00-23) H %H
hour, numeric, 12-hour (1-12) g %l
hour, numeric, 24-hour (0-23) G %k
minute, 2-digit (00-59) i %i
seconds, 2-digit (00-59) s %S
time, 24-hour (hh:mm:ss) none, use date(“H:i:s”) %T
time, 12-hour (hh:mm:ss AM/PM) none, use date(“g:i:s A”) %r
AM / PM (uppercase letters) A %p
am / pm (lowercase letters) a none
Days
day name, full (Sunday) l (a lowercase L) %W
day name, abbreviation (Sun) D %a
day as number of week (0-6) none, use a getdate() array:
First do this:
$dateInfo = getdate();
Your day as number of the week is $dateInfo[wday];
%w
day number of the year z
(Note: 0-365; ex: January 1st is 0)
%j
(Note: 001-366; ex: January 1st is 001)
Dates
month name in full (January) F %M
month name, abbreviated (Jan) M %b
month, 2-digit (01-12) m %m
month, no leading 0 (1, 2, 3 – 12) n %c
day of month, 2-digit (01, 02, …) d %d
day of month, no leading 0 (1, 2) j %e
day of month with ordinal suffix (1st, 2nd, 3rd, 4th…) none. Use jS %D
ordinal suffix (st, nd, rd, th) S none; use %D (above)
year, 4 digit (2001) Y %Y
year, 2 digit (00-99) y %y
Examples: To write: Use this: Use this:
Sunday, June 03, 2001 date(‘l, F d, Y’) date_format(date, ‘%W, %M %d, %Y’)
Sun., Jun 3rd, 2001. 11:30 AM date(‘D., M jS, Y.g:i A’) date_format(datetime, ‘%a., %b %D, %Y. %l: %i %p’)
Or if there are two columns, one date and one time, you’d use:
date_format(date, ‘%a., %b %D, %Y.’)
time_format(time, ‘%l: %i %p’)
See the note below)
2001-06-03 date(‘Y-m-d’) (see important note below; this is default for date-type columns so you don’t have to use any special function or formatting at all.)

Important Note:

The choice in MySQL between date_format() and time_format() depends on the type of column you have stored your date in.

MySQL column type: Data format in the column (all parts are required) Example: Use:
date YYYY-MM-DD 2001-06-03 date_format() to get date values
time (see “NB” below) hh:mm:ss 13:30:00 time_format() to get time values
datetime YYY-MM-DD hh:mm:ss 2001-06-03 13:30:00 date_format() to get date and/or time values

NB: the time type for MySQL columns actually represents an amount of time elapsed; thus, it can be negative or positive. However, it can also be used to represent the time of day, where it represents the amount of time elapsed since 1 second past midnight. If you forget to add :00 for seconds (for example, using 13:30 for 1:30 p.m.), your time value will be interpreted as the number of minutes and seconds after midnight, or 12:30 a.m. plus 30 seconds.

MySQL phpMyAdmin Query change table names syntax

Tuesday, April 13th, 2010

RENAME TABLE

database_name.table_name TO database_name.new_table_name,

database_name.table2_name TO database_name.new_table2_name;

NOTE the comma and the semicolon!

phpMyAdmin Server config.inc.php remote database connection

Monday, March 15th, 2010

C:\xampp\phpMyAdmin\config.inc.php

add this (or similar) at the bottom:

$i++;

$cfg[‘Servers’][$i][‘auth_type’] = ‘config’;
$cfg[‘Servers’][$i][‘user’] = ‘user’;
$cfg[‘Servers’][$i][‘password’] = ‘password’;
$cfg[‘Servers’][$i][‘host’] = ‘your_host’;
$cfg[‘Servers’][$i][‘verbose’] = ‘Description to appear in phpMyAdmin’;

//end

params/reference: http://wiki.phpmyadmin.net/pma/Config

And then control remote MySQL databases in your localhost/phpmyadmin

under Actions: Server: dropdown

*note: When connecting to a Godaddy DB, you must enable accessible remotely when creating the database!

Php db connection close()

Monday, March 8th, 2010

@mysql_close($dblink);

or the network persistence traffic cop might come calling…YOU

MySQL comments

Monday, March 8th, 2010

start of line left only (must have space after dashes):
— comment

OR
/* */

example:

if(!$result = mysql_query(”
SELECT
products_id,
FROM
runwayProductInfo,
WHERE
products_id IN (‘545′,’100’)
/* hello */
— comment
ORDER BY FIELD(runwayProductInfo.products_id, ‘100’,’545′)
“)){ echo mysql_error(); exit; }

Php MySQL query shortcut syntax

Monday, March 8th, 2010

$query = “SELECT
o.FirstName, o.LastName, o.Addr1, o.Addr2, o.City,
s.Name AS State, s.Abbrv as StateAbbrv, o.ZipCode,
o.Phone1, o.EmailAddr, o.CCLastFour, o.CCExp, o.PromoCode
FROM orders o
INNER JOIN states s ON s.StateID = o.StateID
WHERE o.OrderID = ” . $this->_orderID;