I cannot concatenate variable in a query

Question

I have a URL and in it a code that will vary all the time (Ex: usuario/perfil/39).

I used the following code to get this URL value:

$valor_id =  $_SERVER['REQUEST_URI'];
$cod =  explode("/",$valor_id);
$codigo = $cod[4];

So I need to use this code in a SELECT. I did so:

$sql = "SELECT tipo FROM users WHERE ID = " . $codigo;

And I made the following mistake:

Fatal error: Error executing query: SELECT type FROM users WHERE ID = - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '' at line 1 in C: xampp htdocs festas Registry mysqldb.class.php on line 243

When I do that:

$sql ="SELECT tipo FROM users WHERE ID = 36";

It works right, so I believe the problem lies in concatenating the variable in the query. Note: in BD, the type field is integer.

Part of the code that says the error occurs:

public function executeQuery( $queryStr )
{
    if( !$result = $this->connections[$this->activeConnection]->query( $queryStr ) )
    {
        trigger_error('Error executing query: ' . $queryStr .' - '.$this->connections[$this->activeConnection]->error, E_USER_ERROR); //LINHA 243
    }
    else
    {
        $this->last = $result;
    }

}

Answer 1

Probably your code just needs this setting:

$codigo = $cod[3];

Reason: if you have a URL in the format

http://exemplo.com/usuario/perfil/39

the variable $_SERVER['REQUEST_URI']; will contain /usuario/perfil/39, therefore, after the explode you will have the following structure:

0 => ''          (valor antes da 1a barra)
1 => 'usuario'   (valor entre a 1a barra e a 2a)
2 => 'perfil'    (valor entre a 2a barra e a 3a)
3 => '39'        (valor após a 3a barra)

Answer 2

Checks whether the value of $Cod[4] is really the value you need. But if the ID is the last value of the URL, do it this way:

$valor_id =  $_SERVER['REQUEST_URI'];
$cod =  explode("/",$valor_id);
$codigo = array_pop($cod);

Answer 3

This url certainly uses Apache’s Rewrite engine, so it’s easier and more reliable to edit htaccess and do things like this:

RewriteRule ^([a-zA-Z]+)/([a-zA-Z]+)/([0-9]+)$ page.php?usuario=$1&perfil=$2&id=$3

Then in the file php just get the $_GET['id']. There will be no mistake.

If you do not have access to htaccess and know that id is always the last element of the url

$valor_id =  $_SERVER['REQUEST_URI'];
$cod =  explode("/",$valor_id);

$ultimo = count($cod) - 1;

$codigo = $cod[$ultimo];

$limit = array("options" => array("min_range" => 1));

$id = (!empty($codigo)) ? filter_var($codigo, FILTER_VALIDATE_INT, $limit) : FALSE

if($id)
{
   //executa pesquisa
}
else
{
   echo "ERRO";
}

Answer 4

You can check the output of your array this way:

var_dump($cod);

This should give you a clear notion of what is being returned by the URL. Since the URL can be changed by the user I recommend to always test if the value actually exists.

$valor_id =  $_SERVER['REQUEST_URI'];
$cod =  explode("/",$valor_id);
$codigo = '-1';
if (count($cod) >= 3) {
  $codigo = end($cod); // Pega o ultimo valor do array
}

I also recommend to clean the variable before using it in the query:

$codigo = is_numeric($codigo) ? intval($codigo) : -1;

Finally, try the use of preparedStatements. In addition to making life much easier, they prevent a number of problems, such as SQL-Injection;