Difference when declaring variables in Mysql / SQL

You may have notice that there is a lot of differences in writing queries in SQL vs. Mysql; One of this difference concerns the definition of variables:

One way to do it in SQL :

DECLARE @ID VARCHAR(15)
SELECT  @ID ='000000001'

SELECT * from [TABLE] (nolock) WHERE  ID = @cID;

(we could use SET instead of SELECT - here, we have used VARCHAR but  any other kind of variable could have been used!!)

The equivalent in Mysql will be the following :

SELECT @ID:= '000000001';

SELECT * from [TABLE] (nolock) WHERE  ID = @cID;

Managing errors with cURL library access

Having one URL ($sURL), we would like to get the associated file content (this can be any file, not only an  XML file):

Here is the PHP code using cURL library with the 'cur_exec' function to get the content, and the 'curl_errno' function to get the possible error message !!!

<?php
$sUrl = "http://tycaron.blogspot.fr/2014/10/managing-errors-with-curl-library-access.ht";
$bError = false;
$sXml = GetCurl_Response($sUrl);
 if ($sXml === false)
{
        $bError = true;
        $aErrors[] = GetCurl_Error($sUrl);
}

if ($bError) var_dump($aErrors);

/*
 * get the file content related to an URL
* @param  string $url
* @return string
*/
function GetCurl_Response($url)
{
  // create curl resource
  $curl = curl_init($url);

  // var_dump($curl);
  curl_setopt($curl, CURLOPT_FAILONERROR, true);
  curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
  curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
  curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
  $result = curl_exec($curl);


  //close curl resource to free up system resources
  curl_close($curl);
  return $result;
}

/*
 * get cURL error (return 'false' if no error)
 * @return string
*/
function GetCurl_Error($url)
{
  // create curl resource
  $curl = curl_init($url);

  // var_dump($curl);
  curl_setopt($curl, CURLOPT_FAILONERROR, true);
  curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
  curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
  curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
  curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
  $result = curl_exec($curl);
 if ($result===false)
   $error_curl = 'Erreur Curl : ' .curl_error($curl).' ('.curl_errno($curl).')';
  else
   $error_curl = false;

  //close curl resource to free up system resources
  curl_close($curl);
  return $error_curl ;
}

?>

Having made volontarily an error when writing URL, the output error message will then be :
array(1) { [0]=> string(66) "Erreur Curl : The requested URL returned error: 404 Not Found (22)" }

Rewriting URL

When dealing with resful webservice, one of the principle for the architecture is to use "cleaning URL" or the also called Semantic URL  ; one way to do that is to add some rules to redirect URL within an .htaccess file;

Say you have a resource "users",. I could set up a number of URIs like so:

/api/users     when called with GET, lists users
/api/users     when called with POST, creates user record
/api/users/1   when called with GET, shows user record
               when called with PUT, updates user record
               when called with DELETE, deletes user record

To have a correct representation of a RESTful architecture, one can define  an .htaccess file to so that it redirects any of these URIs to one file, 'user.php' :

.htaccess
Options +FollowSymLinks
RewriteEngine On
RewriteRule ^api/([0-9a-zA-Z]+)/([0-9_-]+)$ user.php [L]

user.php

<?php

    $path = $_SERVER['REQUEST_URI'];

    $url_elements = explode('/', $path);

    $verb = $_SERVER['REQUEST_METHOD'];

    echo $verb."<br>";

    echo "ID = ".$url_elements[5]."<br>";

    echo "NAME = ".$url_elements[6]."<br>";;

    

?>

In doing so, while accessing the following URL http://localhost/api/user/12, the screen will print out :

ID = user
NAME = 12

Calling a database stored procedure from PHP

If a stored procedure retuns a 'select' query within a database, a PHP function calling that stored procedure will be similar to :

function ExecStoreProcSelect()
{
$oStatement = $this->mssql->prepare("Exec [Procedure name]  @Json= :Json ;");
  $oStatement ->bindValue(':Json', $sJson, \PDO::PARAM_STR);

$bResult = $oStatement->execute();
if (!$bResult)
{
return false;
}
return $oStatement->fetchAll(\PDO::FETCH_ASSOC);
/* OR 
  $sSelect = "SELECT @@ROWCOUNT as nb_line_return"; // give the nb of line being returned during the last instruction

  $aResults = $this->mssqlBosql->query($sSelect)->fetch(\PDO::FETCH_ASSOC);
  if ($aResults['nb_line_return'] < 1) { return array('errors' => array('No return by the stored   procedure !')); }
  else { return true; }
*/
} 

If no return is expected from the stored procedure, this function could be simplify like this :

function ExecStoreProcUpdate()
{
$oStatement = $this->mssql->prepare("Exec [Procedure name];");
$bResult = $oStatement->execute();
if (!$bResult)
{
return false;
}
return true;
} 


In mysql, the syntax will be slightly different (cf . http://www.mysqltutorial.org/php-calling-mysql-stored-procedures/) using CALL instead of EXEC :

for a stored procedure like this one :

"
DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    in  p_customerNumber int(11),

    out p_customerLevel  varchar(10))

BEGIN

    DECLARE creditlim double;

    SELECT creditlimit INTO creditlim

    FROM customers

    WHERE customerNumber = p_customerNumber;

    IF creditlim > 50000 THEN

    SET p_customerLevel = 'PLATINUM';

    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN

        SET p_customerLevel = 'GOLD';

    ELSEIF creditlim < 10000 THEN

        SET p_customerLevel = 'SILVER';

    END IF; 

END$$

"

The access through PHP can be done via :

require_once 'dbconfig.php';

$customerNumber = 103;

try {

    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    // execute the stored procedure

    $sql = 'CALL GetCustomerLevel(:id,@level)';

    $stmt = $conn->prepare($sql);

    $stmt->bindParam(':id', $customerNumber, PDO::PARAM_INT);

    $stmt->execute();

    $stmt->closeCursor();

    // execute the second query to get customer's level

    $r = $conn->query("SELECT @level AS level")->fetch(PDO::FETCH_ASSOC);

    if ($r) {

        echo sprintf('Customer #%d is %s', $customerNumber, $r['level']);

    }

} catch (PDOException $pe) {

    die("Error occurred:" . $pe->getMessage());

}

The use of "===" instead "==" to test the result of a function

When managing one error, which is returning from a function, I have been faced with the importance of using "= = =" instead of " =  = " ;

Imagine the following case: a function with database query within a foreach with a control from the query return (here, we have just a 'select' but it could have been an 'insert',  an 'update', a 'delete' etc..)


[..]
foreach ($parcels as $aparcel)
{
$events = $this->getEvents($aparcel[id]);
if ($events === false)
        {
$_aMsgs['errors'][$i] = "Error in the resulting data getParcelsEvents";
$i = $i + 1;
continue;
       }
       else { //using $events for doing stuff even if empty
     
       }
}
[..]

protected function getEvents($parcelsid)
{

   $sQuery = "select [fields]
   from [table] (nolock)
   where no_colis = '$parcelsid';";

$oStatement = mysql->prepare($sQuery);

$bResult = $oStatement->execute();
if (!$bResult)
{
//echo $sQuery.PHP_EOL;
return false;
}
return $oStatement->fetchAll(\PDO::FETCH_ASSOC);
}


At start, I was using "if ($events = = false)" to control the results from the getEvents function, but, doing so,  one empty result from the database query was considered as an error - that was not the behavior that I was expected ; the solution was to use "=  =  =" to be sure to test only the "false" returning case and not that empty case !!!

From now on, I am using mostly the triple "=" when dealing return of a function!