CodeIgniter Forums

Full Version: Validation: in_db[table.field]
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Imagine that you have a list of fruits in your database and your users need to select their favorite fruits (but it must be a fruit that previously existed in the database) ...

Currently, to validate the entry, you must select all the fruits from your database, implode the results array and put it into the validation rule with something like this: 

Code:
in_list['. Implode (',', $ result). ']'

It would be amazing if you could validate it with something like this: 

Code:
in_db[fruits.id]

Where "fruits" is the name of the table and "id" is the name of the field.

In addition, the query for the second validation method would be faster than the first one because in the second case you can search for a primary or unique key, while in the first you must search for all the "fruits" in the database.


Note: I am aware that currently you can still validate the entry by looking for the primary key outside the validation rules ... but this is not fun if the idea of using the validation rules is to facilitate the lives of the programmers.
How would this be different than `is_unique`?

"Checks if this field value exists in the database. Optionally set a column and value to ignore, useful when updating records to ignore itself."

is_unique[table.field,ignore_field,ignore_value]
Is the opposite. 

While `is_unique` returns an error message if the value exist in the DB and continues if the value does not exist. `in_db` might return the error if the values does not exists and should continue if the value exist.

I think is easier to understand if instead of `in_db` we name it `not_is_unique`.
I created the feature and here is the Pull Request.
Great request. I have the same exactly rule in my validation rules file.

I share my code...

app/Libraries/Rules.php
PHP Code:
<?php

namespace App\Validation;

use 
Config\Database;

class 
Rules
{
    /**
     * Checks the database to see if the given value exist in db table.
     *
     * Example:
     *    in_db[table.field]
     *
     * @param string $str
     * @param string $field
     * @param array  $data
     * 
     * @author Alejandro D. Guevara <[email protected]>
     *
     * @return boolean
     */
    
public function in_db(string $str nullstring $field, array $datastring &$error null): bool
    
{
        
// Break the table and field apart
        
sscanf($field'%[^.].%[^.]'$table$field);

        
$db Database::connect($data['DBGroup'] ?? null);

        
$row $db->table($table)
                
  ->select('1')
                
  ->where($field$str)
                
  ->limit(1);

        if (
$row->get()->getRow() !== null)
        {
            return 
true;
        }

        
$error lang('Validation.in_db', ['table' => $table]);
        return 
false;
    }


app/Config/Validation.php
PHP Code:
(...)
    public 
$ruleSets = [
        (...)
        \
App\Validation\Rules::class,
    ];
(...)