This is my first attempt at using the database class. I'm able to do simple queries, but is it possible to use MySQL functions in the query? For example, something like the following. Note concat() and rand() functions.
$result = DB::select(
'client_id',
'company',
CONCAT('folder', hp_thumbnail) as thumbnail_path,
)
->from("clients")
->where('client_display', '=', 1)
->order_by(rand())
->execute();
Thanks,
Mitchell
Also can't get the WHERE condition to work with IS NOT NULL. Tried:
$result = DB::select(
'testimonial'
)
->from("clients")
->where('testimonial', '', 'IS NOT NULL')
->execute();
Also tried:
->where('testimonial', 'IS NOT NULL', '')
and
->where('testimonial', 'IS NOT NULL')
and
->where('testimonial IS NOT NULL')
I managed to figure out how to select database items using MySQL functions. You need to use DB::expr so that no extra quotes or escaping is performed. See CONCAT and RAND below.
I still can't figure out how to use a WHERE clause such as "WHERE testimonial IS NOT NULL". I've tried the following:
->where(DB::expr('testimonial IS NOT NULL'))
->where(DB::expr('testimonial IS NOT NULL'), '')
->where(DB::expr('testimonial IS NOT NULL'), '', '')
->where(DB::expr('testimonial IS NOT NULL'), null)
->where(DB::expr('testimonial IS NOT NULL'), null, null)
I've tried to find documentation on using the WHERE clause but only found examples in the Database Usage section of the docs.
$result = DB::select(
'client_id',
'company',
DB::expr("CONCAT('$image_folder', hp_thumbnail) AS thumbnail")
)
->from("clients"wink
->where('client_display', '=', 1)
->order_by(DB::expr('RAND()'))
->execute();
I went through the core/classes/database/query/builder.php file and was able to reverse engineer how to generate a MySQL WHERE clause of the form: WHERE field IS NULL
->where('field', '=', null)
To generate WHERE field IS NOT NULL
->where('field', '!='', null)
Note that from what I see, you cannot use the '<>' operator, you must use '!='. This could be corrected by changing builder.php, line 86 from:
elseif ($op === '!=')
to:
elseif ($op === '!=' || $op === '<>')
For those of us who struggled through learning that the clause: WHERE field = NULL and WHERE field != NULL is NOT valid MySQL, this was not the syntax I would have expected. I think this would be good to add to the documentation, perhaps as an example.
In fact, an example such as the following would show how to use DB::expr for MySQL functions as well as WHERE field IS NOT NULL. Both of these were not obvious to me.
$image_folder = 'path/to/files';
$result = DB::select(
'testimonial',
'name',
DB::expr("CONCAT('$image_folder', hp_thumbnail) AS thumbnail")
)
->from('clients')
->where('testimonial', '!=', null)
->and_where('testimonial', '<>', '')
->order_by(DB::expr('RAND()'))
->execute();
Contrary to some other frameworks we don't believe in creating 10 ways to do the same thing, especially for something this small. So no, we won't add a second way for negative comparisson.
Not a problem. I appreciate your philosophy. I also found that the MySQL phrase "WHERE field IS NOT NULL" can be achieved using:
->where('field', 'IS NOT', null)
which is much more in keeping with MySQL syntax
Donate
