count_records_select with OR clause

count_records_select with OR clause

by Mark B -
Number of replies: 7

Hi there,

I am wanting to know if there is an easy way to do a count_records_select which has an AND clause which i would like to be met, then after that check if any of the OR clause statements are met.  Unfortunately when i try doing this it seems to match the AND clause or any of the OR clauses, when i want it to only output if the AND clause is met with any OR clause

Shown below is the code i am trying on a custom made table; for some reason it keeps returning true if none of the OR clauses are met when i don't want it to do this:

if ($DB->count_records_select('user_criteria', 'userid = ? AND criteriaid = ? AND (valueid = ? OR valueid = ? OR valueid = ? OR valueid = ?)', array($student->id, $unitcrit->id, 289, 288,287,286)))

{

$unitstatus = " - PA";

}

for some reason $unitstatus is always equaling PA when userid and criteriaid are true, even if the OR is not met.  Any ideas?

I would have tried a get_records_list but you cannot have an AND clause in there too can you?

Many thanks,

Mark

Average of ratings: -
In reply to Mark B

Re: count_records_select with OR clause

by Darko Miletić -

First of all you should avoid OR if possible.

Second, this is what needs to be done here:

$queryparams = ['userid' => 123, 'criteriaid' => 223];
// Values for valueid.
list($sql, $params) = $DB->get_in_or_equal([1,2,3,4,5,6], SQL_PARAMS_NAMED);
$queryparams += $params;
$rt = $DB->count_records_select('user_criteria',
                                'userid = :userid AND criteriaid = :criteriaid AND valueid '.$sql,
                                $queryparams);




Average of ratings: -
In reply to Darko Miletić

Re: count_records_select with OR clause

by Mark B -

The use of square brackets in the queryparams is correct?  Keeps complaining that it is unexpected here.


I have changed queryparams to an array() type format and this has worked; however the line starting with list says it has an unexpected square bracket

$queryparams = array('userid' => $student->id, 'criteriaid' => $unitcrit->id);

// Values for valueid.

/*list($sql, $params) = $DB->get_in_or_equal([286,287,288,289], SQL_PARAMS_NAMED);


Any ideas?

Average of ratings: -
In reply to Mark B

Re: count_records_select with OR clause

by Darko Miletić -

[] is equivalent of array(). If you have php older than 5.4 do th change.

Average of ratings: -
In reply to Darko Miletić

Re: count_records_select with OR clause

by Mark B -
Ok i have got a step further but it is still outputting the same results as it did before:


My code is now showing: 


$queryparams = array('userid' => $student->id, 'criteriaid' => $unitcrit->id);

// Values for valueid.

list($sql, $params) = $DB->get_in_or_equal(array(286,287,288,289), SQL_PARAMS_NAMED);

$queryparams += $params;

if ($DB->count_records_select('user_criteria', 'userid = :userid AND criteriaid = :criteriaid AND valueid '.$sql,$queryparams))

{

$unitstatus = " - PA";

}

$unitstatus is never equal to " - PA" 

Average of ratings: -
In reply to Mark B

Re: count_records_select with OR clause

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

If you add $DB->set_debug(true) just before you call $DB->count_records_select() (and then $DB->set_debug(false) afterwards), Moodle will output the SQL query + params it generates for that line of code.

You can then copy and paste this into adminer or PHPMyAdmin (or whatever software you use to connect to your database backend), manually substitute in the params and see what happens. This should allow you to figure out exactly what is wrong with your query and/or the data in the database.


Average of ratings: -
In reply to Davo Smith

Re: count_records_select with OR clause

by Mark B -

Hi and thanks for your suggestion but apparently an error is in the syntax, any suggestions?.  I put debug on and here is the outputted query:

SELECT COUNT('x') FROM modl_user_criteria WHERE userid = ? AND criteriaid = ? AND valueid IN (?,?,?,?) [array ( 0 => '1605', 1 => '13687', 2 => 286, 3 => 287, 4 => 288, 5 => 289, )]


PHPMYADMIN says:

#1064 - 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 '? AND criteriaid = ? AND valueid IN (?,?,?,?) [array ( 0 => '1605', 1 =>' at line 1



Average of ratings: -
In reply to Mark B

Re: count_records_select with OR clause

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I'll repeat my original instructions with added emphasis:

You can then copy and paste this into adminer or PHPMyAdmin (or whatever software you use to connect to your database backend), manually substitute in the params and see what happens. This should allow you to figure out exactly what is wrong with your query and/or the data in the database.

So the first ? needs to be replaced with the first param (1605)

The second ? needs to be replaced with the second param (13687)

etc.

Average of ratings: -