convert value from routes/uri to string

Controller

PHP Code:

public function verification($activation, $email)
{
    $user = $this->model->user('user_activation', $activation);
} 


Model

PHP Code:

public function user($key, $value)
{
    return $this->db->query("
       SELECT *
         FROM users
         WHERE $key = $value // The problem here
     ")->row();
} 


The error message is directed to $ value, the value of $ activation is taken from routes / url.
I try to use single quotes so that it becomes '$ value'. and the results are correct.
my question is whether using single quotes doesn't matter? or is there a better way?

It's easier with Query Builder syntax:

PHP Code:

public function user($key, $value)
{
   return $this->db->where($key,$value)->get('users')->row();
} 


It's easier with Query Builder syntax:

PHP Code:

public function user($key, $value)
{
   return $this->db->where($key,$value)->get('users')->row();
} 


Previously thanks for the answer, I know and before I tried using the query builder and it worked.
but the query builder has restrictions (in my opinion). therefore I used a manual query because of its flexibility and I decided to always use manual queries.

But I will try to understand the query builder to be more flexible.

You can also try query bindings:

PHP Code:

$sql = "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 


You can also try query bindings:

PHP Code:

$sql = "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 


it doesn't work

You can also try query bindings:

PHP Code:

$sql = "SELECT * FROM users WHERE $key = ?";
$this->db->query($sql, array($value)); 


it doesn't work

Did you do this?
It is likely what Wouter60 was implying.

PHP Code:

public function user($key, $value)
{
    $sql = "SELECT * FROM users WHERE $key = ?";
    $query = $this->db->query($sql, array($value));
    return $query->row();
} 


The double quotes to define the query string are required.

Use of query binding is highly desirable for the security it provides.

Did you do this?
It is likely what Wouter60 was implying.

PHP Code:

public function user($key, $value)
{
    $sql = "SELECT * FROM users WHERE $key = ?";
    $query = $this->db->query($sql, array($value));
    return $query->row();
} 


The double quotes to define the query string are required.

Use of query binding is highly desirable for the security it provides.

I know and I have tried it and failed if I use the method above. the error is only caused because the value of value is not string type. if I use a basic query I am not happy to see $value flanked by single quotes, if I use an automatic query builder consider the value of $value to be a string but the disadvantage (in my opinion) if using this method is less flexibility.

if you are willing to help me to change it to a query builder I am very happy.

PHP Code:

   public function user_campaign($key, $value)
    {
        return $this->db->query("
            SELECT *
            FROM (
                SELECT *
                FROM users
                WHERE $key = $value
            ) users_campaigns
            INNER JOIN campaigns
            ON users_campaigns.id = campaigns.id
        ")->row();
    } 


I know and I have tried it and failed if I use the method above. the error is only caused because the value of value is not string type. if I use a basic query I am not happy to see $value flanked by single quotes, if I use an automatic query builder consider the value of $value to be a string but the disadvantage (in my opinion) if using this method is less flexibility.

In my testing, it does not matter if $value is a string or an integer. Nor does it matter if the string is flanked with single quotes or not.
My test set up has the $key field of type int. I can set $value to a digit or a string representation of that digit and the correct record is returned.

In other words, using this code

PHP Code:

$sql = "SELECT * FROM users WHERE $key = ?";
$query = $this->db->query($sql, array($value)); 


I get the same results from either of these

PHP Code:

$value = 307;
$value = '307'; 


Are using some other non-string data type for the $key field?

if you are willing to help me to change it to a query builder I am very happy.

PHP Code:

   public function user_campaign($key, $value)
    {
        return $this->db->query("
            SELECT *
            FROM (
                SELECT *
                FROM users
                WHERE $key = $value
            ) users_campaigns
            INNER JOIN campaigns
            ON users_campaigns.id = campaigns.id
        ")->row();
    } 


I don't see why you are using the sub-select. Here is how to do the above using Query Builder, but without the sub-select.

PHP Code:

   public function user_campaign($key, $value)
    {
        return $this->db
            ->where($key, $value)
            ->join('campaigns', 'users_campaigns.id = campaigns.id', 'inner')
            ->get('users')
            ->row();
    } 


I don't see why you are using the sub-select. Here is how to do the above using Query Builder, but without the sub-select.

PHP Code:

   public function user_campaign($key, $value)
    {
        return $this->db
            ->where($key, $value)
            ->join('campaigns', 'users_campaigns.id = campaigns.id', 'inner')
            ->get('users')
            ->row();
    } 


I think in basic queries it is required to select in sub-queries, am I correct?

I have used the logic above before but with basic queries that are required to use sub-select so that it gets erorr which is caused to clash in the ID column, that's why I rotate that logic.


the above method does produce the data that I want. but the method above is not a sub-query.
the concern is that in the future I have to use multiple sub-queries.

what if I faced a query like this (this is only for example):

PHP Code:

public function user_campaign($var1, $var2, $var3, $var4 )
{
    return $this->db->query("
        SELECT *
        FROM (
            SELECT *
            FROM users
            WHERE $var1 = '$var2'
        ) a
        INNER JOIN (
            SELECT *
            FROM campaigns
            WHERE $var3 = '$var3'
        ) b
        ON a.id = b.id
    ")->row();
} 


I apologize in advance if I bother you