Hello, I have an array of usernames and i want to see the id's they correspond to. I've done this:
$sql="SELECT u.id AS userid
FROM {user} u
WHERE u.username IN('".implode(',',$results)."')";
$userids = $DB->get_records_sql($sql);
echo $userids;
EDIT: Not getting a syntax error anymore, but prints the word Array on my page.
Cheers,
Ricardo Godinho
Do you know what SQL injection is? (http://xkcd.com/327/)
Use $DB->get_in_or_equals.
Same result for me. I'm certain i have users registered on Moodle, and my variable $results have usernames.
Or, in this case
$userids = $DB->get_records_list('user', 'username', $results, '', 'id');
Still the same result.It's really strange, it is as if my array was empty, but if I echo it before the query, i get the expected usernames. $dataClip contains usernames from an url xml(after the processing of simpleXMLElement ) and $allUsers has all usernames of Moodle.
$results=array_intersect((array)$dataClip,(array)$allUsers);
echo implode('<br/>',$results); I get 3 usernames.
After the query and using var_dump($userids) I get nothing. Array(0) {}
So, after some tinkering, if i pass the usernames directly, it works. I get the ids of a.cabral and a.maia. d.mateus not registered. Why doesn't work with the array, if the same usernames are there?
$sql="SELECT u.id AS userid
FROM {user} u
WHERE u.username IN('a.cabral','a.maia','d.mateus')";
$userids = $DB->get_recordset_sql($sql);
foreach ($userids as $ids) {
$userid = $ids->userid;
echo $userid.'<br/>';
}
I found out that moodle doesn't recognize usernames without apostrophes. Doing this query is the right way to go:
$usernames = array('username1','username2',...);
list($usernamewhere, $usernameparams)= $DB->get_in_or_equal($usernames, SQL_PARAMS_NAMED,'username');
$sql ="SELECT u.id AS userid, u.username
FROM {user} u
WHERE u.username {$usernamewhere}";
$users = $DB->get_records_sql($sql, $usernameparams);
foreach($users as $user){
echo $user->userid .' - '. $user->username .'<br />';}But since my resulting array can have different values/size, it's not pratical at all. So, before the query, I have this
function apply_quotes($item){return"'".mysql_real_escape_string($item)."'";}
$dataClip=array_map("apply_quotes",$dataClip);
$allUsers=array_map("apply_quotes",$allUsers);
$results=array_intersect((array)$dataClip,(array)$allUsers);Changing my query to this, gives me no output. Can anyone help me?
list($usernamewhere, $usernameparams)= $DB->get_in_or_equal($results, SQL_PARAMS_NAMED,'username');
$sql ="SELECT u.id AS userid, u.username
FROM {user} u
WHERE u.username {$usernamewhere}";
$users = $DB->get_records_sql($sql, $usernameparams);
foreach($users as $user){
echo $user->userid .' - '. $user->username .'<br />';}I don't understand why you're applying quotes to each element in your user array.
Surely you don't actually have any users in your database who have the username:
'username1'
with the quotes included? Surely you want to be searching for usernames like:
username1
without the quotes (which is what your original, working, code does)?
I don't understand what you mean by 'resulting array can have different values/size, it's not pratical at all' - do you mean that you want to get a subset of elements? In which case, there are built-in limit and limitfrom params to get_records_sql:
$DB->get_records_sql($sql, $usernameparams, [offset of first record to return], [number of records to return]);
Alternatively, you could use an array_intersect to remove some of the users from the original list (but without also doing the mysql_escape or adding extra quotes to them).
I wanted to apply quotes because I was led to believe, from my previous code, that usernames on Moodle would be only recognized with quotes. I asked this question on stackoverflow.com concerning the origins of my syntax error and that was the answer i got. Here is a excerpt :
" {} is specific to Moodle, it replaces the table name with the full table name - database + prefix.
http://docs.moodle.org/dev/Data_manipulation_API#moodle_database::get_records_sql.28.29
$DB->get_records_sql() returns an array of objects
For the original question, you are getting a syntax error because the usernames are not enclosed in quotes. "
And then he proceeds with the query.
What I mean by "resulting array vary in values/size" is because the information( students) I extract from my campus Information System may vary according to the course. After the intersection i may get 20 matches, or 100, or 200. Depends on the size of the course.
So, to summarize, what I ultimately want to do is to enable a teacher to click an Item on his course Admin Block and mass enroll students from our SIS without any UI. That runs a script. The script communicates with an url of my campus Information system to extract usernames( non quoted). I cross match them to the totality of moodle user's database( array_intersect). And now, since the enrollment is based on Id's, with the usernames i have, I want to know the corresponding Id to each username from that resulting array, and finally, enroll them.
Why not just use the external database enrolment plugin? Your staff wont even have to click on a link to run a script then, it just does it.