top5

Introducing

Today, i am going to introduce you some advices on what technologies your Startup should use before being launched in mainstream, and conclude this article by the Top 5 PHP/MySQL Queries and Algorithm your Startup should know. As i was evolving in freelance development from 2011 to 2014 a few of my customers could qualify my work as a bit experimented slater for small cap businesses, because my job was to advise, execute,and train a team of apprentice. I was likely incredibly unknown in their market, during my 3 years of “FAIL” entrepreneurship. That’s being said, nevertheless none of the websites launched while i was into the business experienced deficit. My adventure just stopped since i realized my inability to hire news persons into my enterprise, so i had decided to leave this job for the lack of a better word. Working as developper, designer, former, integrator, community manager, pentester without any helps from the external world ( such as investors nor customers) was really challenging but disappointing. However I earned some lessons by doing this.

The following key to success:

  • Think outside the box.
  • Learn to “eat your own dog’s food”.
  • Update frequently your work and clean your workspace.
  • Collaborate (even if it was not  financially possible).
  • Create frameworks.
  • Make it scalable and suitable.
  • Make it simple.
  • control Space and Time
  • Build your own space, learn to use your time.
  • Learn to fail as a step to success.
  • Be intellectually honest with yourself and your customers.
  • Switch your mindset depending on your opportunities.
  • Respect descendingly the law (from worst to least worst).

It is critical to make the difference between  what is your work situation, what are the chores to be shared, and what slavery is. If i use the word “slave” that’s mainly for this quote :

Those who do not move, do not notice their chains.

I obviously do not give a shit about Mrs Rozalia Luxemburg ‘s life, this sentence has almost nothing to do in this article. I am just shocked by slavery ,of the new “open-air prison” mindset impact could probably create into the current Startup Ecosystem, at a first glance we know who could be the stakesholders. On the other hand those ones could be a false positive (Re-Thinking process).

Experiences

Building a source-code, programming, prototyping, engineering are activities that seems to be very similar but they aren’t. Recently, where i use to live in France (Epinal/Nancy) the trends followed  are that developers build LEGO from existing source code, the engineers are programming  from scratch privately, and distributing bullshit publicly. It is not true but sometimes, some of them respect theses myths and rules, to my mind ,they need credits for that.

First of all, while you are developing source code for a project you have to stay focused on:

  • The source-code.
  • The time spent, the benefits expected.
  • The project (API, Website, Wepapp, Mobile app, applet etc..).
  • Your skill to produce a clean source code.
  • Compliance of the source-code with the market.
  • Compliance of your programming skills with your knowledge.
  • Number of people  working with you.
  • Social impact of your work.

The programming paradigm used into theses following examples are :

  • functionnal programming ,
  • object oriented programming,
  • event-based programming.

Be secure against SQL injections

Do not start a program without thinking about his security implications, if you do not know how to secure, learn by failing and re-think. These lines of code permit to sanitize your inputs against SQL injections, use the clean() function to avoid the evil-minded user to perfom attacks on your variables.

<?php
//fonction de sécurisation contre les injections sql
		function clean($variable) {
				$variable2 = utf8_decode($variable);
				if (get_magic_quotes_gpc())
					{
			$variable2 = stripslashes($variable2);
		}
		$variable2 = mysql_real_escape_string($variable2);
		$variable2 = utf8_encode($variable2);
		return $variable2;
		}
?>

1) Google’s Store Locator Algorithm

This algorithm is distributed freely on Google’s developers website, it permit to find locations based on a marker table, (phpsqlsearch_genxml.php)  , the formula used is Haversine’s formula , this formula permit to calculate accurately each point into a great circle of distance between 2 points, such as proximity into  an astronomic spheroid . The SQL formula used by google use a simplified arc-cosine function, it could be used for geo-fencing purposes.

a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)

// Search the rows in the markers table
$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers 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);

2) SQL between statement without BETWEEN syntax

This interesting SQL function was used into tel2geo website for retreiving the most rapidly the phone numbers between the 2 quantitative prefixes.

SELECT * FROM $table_name WHERE EZABPQMD_Debut <=$numero AND EZABPQMD_Fin >=$numero;

3) SQL between statement without BETWEEN syntax Object-Oriented-Programming

This PHP/MySQL script written in object oriented programming permit to extract the IP data in binary format into a human readable ip address , and select 1 entry from the database by descending order.

<?php
protected function init_geolocaliser($table_name, $addr_type, $addr_start) {
		//Requête de sélection dans la base de donnée
		$q = $this->db->prepare("select * from `{$table_name}` where addr_type = ? and ip_start <= ? order by ip_start desc limit 1");
		$q->execute(array($addr_type, $addr_start));
		return $q->fetchObject();
	}
	
public function geolocaliser($addr, $table_name = "lookup") {
		if ($ret = $this->init_geolocaliser($table_name, self::Addr_Type($addr), inet_pton($addr))) {
			//retrouve les 2 morceaux d'addresse IP dans la BDD
			$ret->ip_start = inet_ntop($ret->ip_start);
			$ret->ip_end = inet_ntop($ret->ip_end);
			return $ret;
		}
?>

4) Reverse geocoding algorithm – Mercator Projection

This following function is a script i created in 2011 and integrated later into maps API . This function convert decimal coordinates (such as those used in google maps, openstreetmaps etc…) in a pixel coordinate, and scale the result into the appropriated size of a cylindric projection with (2 dimensions).

mercatorformula

$sql = "SELECT * FROM `".$table_name."`";
$result = mysql_query($sql);

function recuperationcoords($lat, $lon, $largeur, $hauteur)
{  
    $x = (($lon + 180) * ($largeur / 360));
    $y = ((($lat * -1) + 90) * ($hauteur / 180));
    return array("x"=>round($x),"y"=>round($y));
}
//... plusieurs autres fonctions
while ($row = mysql_fetch_array($result)) {
$lat = $row['lat'];
$long = $row['longi'];
$point = recuperationcoords($lat, $long, $echelle_x, $echelle_y);
posesurlamap($image, $point, $rouge);
}

5) Fetching data into table in function of Time and display n results per pages (Event-Based Programming)

This example was another project (not released officialy) but brings me a good skill and training in PHP/MySQL. The following sources permit to display a wall of 6 photos (latest) from a defined user login, and ordered them by time. The next request is counting the number of photos for the user.

$premiereEntree=($pageActuelle-1)*$per_page; 
// On calcul la première entrée à lire
$retourphotos=mysql_query("select photorenomme from images where userphoto='$login' ORDER BY time DESC LIMIT $premiereEntree,$per_page");
//...  fonctions php et code html entre les deux
$per_page = 6;
$resultatdecompte = mysql_query("select count(photorenomme) AS total FROM images WHERE photoprofil=1 AND userphoto='$login'"); 
$donnees_total=mysql_fetch_assoc($resultatdecompte);
 //On range retour sous la forme d'un tableau.
$totaldephotos=$donnees_total['total']; 
$nombreDePages=ceil($totaldephotos/$per_page);

Links

License

Top 5 PHP/MySQL Queries and Algorithm your Startup should know. Those scripts are free license. Copyright (C) 2016 Thibaut LOMBARD Credits : google.com

Those scripts are free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

Those scripts are distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this script. If not, see http://www.gnu.org/licenses/.

2 thoughts on “Top 5 PHP/MySQL Queries and Algorithm your Startup should know

Comments are closed.