我正在使用Moodle 2.2.1，但是有一个基本的php-mysql问题。我正在尝试在WHERE子句中返回带有条件的字段的计数。 WHERE子句条件来自一个foreach循环。这是我的代码：
foreach($allunits as $allunit)
{
$countquery = $DB->get_records_sql("SELECT COUNT(uid.userid) FROM {user_info_data} uid WHERE uid.data = '".$allunit."' OR uid.data = '".$allunit."\r'");
$ucount = $ucount + $countquery;
}
这将引发不受支持的操作数类型错误。由于“数据”字段可以添加垃圾线/空格，因此我需要使用\ r搜索此类值。如果我直接在wHERE子句中传递值，则可以正常工作，像这样，
SELECT COUNT(uid.data) FROM {user_info_data} WHERE uid.data = 'Marketing' OR uid.data = 'Marketing\r'
但我的wHERE子句值需要来自数组。
有人可以帮我解决这个问题吗？
谢谢
编辑：我的数组'$ allunits'的var_dump
array(118) {
[0] => string(9) "PBS Group"
[1] => string(15) "Admin-PBS Group"
[2] => string(15) "Sales Functions"
[3] => string(17) "Support Functions"
[4] => string(10) "Executives"
[5] => string(4) "Nemo"
[6] => string(7) "Prop Co"
[7] => string(21) "Admin-Sales Functions"
[8] => string(7) "Connect"
[9] => string(12) "Intermediary"
[10] => string(6) "Retail"
[11] => string(13) "Sales Support"
[12] => string(15) "Business Change"
[13] => string(13) "Group Finance"
[14] => string(2) "HR"
[15] => string(16) "IT and eChannels"
[16] => string(9) "Marketing"
[17] => string(15) "Member Services"
[18] => string(9) "Oversight"
[19] => string(24) "Executives and Directors"
[20] => string(19) "Personal Assistants"
[21] => string(19) "Corporate Property "
[22] => string(10) "Peter Alan"
[23] => string(8) "Premises"
[24] => string(18) "Property Solutions"
[25] => string(9) "Surveyors"
[26] => string(13) "Connect Sales"
[27] => string(15) "Connect Service"
[28] => string(12) "Retail Sales"
[29] => string(15) "Regulated Sales"
[30] => string(21) "Administration-Retail"
[31] => string(19) "Business Partnering"
[32] => string(21) "Capital and Liquidity"
[33] => string(18) "Finance Operations"
[34] => string(11) "Procurement"
[35] => string(15) "Risk Management"
[36] => string(8) "Treasury"
[37] => string(8) "Group HR"
[38] => string(9) "Group LnD"
[39] => string(9) "eChannels"
[40] => string(29) "eChannels Sales and Marketing"
[41] => string(15) "IT Architecture"
[42] => string(11) "IT Delivery"
[43] => string(11) "IT Services"
[44] => string(19) "Business Operations"
[45] => string(11) "Collections"
[46] => string(23) "Mortgage Administration"
[47] => string(3) "MSC"
[48] => string(8) "Registry"
[49] => string(7) "Savings"
[50] => string(22) "Group Business Conduct"
[51] => string(32) "Group Financial Crime Prevention"
[52] => string(26) "Group Information Security"
[53] => string(14) "Group Internal"
[54] => string(14) "Legal Services"
[55] => string(11) "Secretariat"
[56] => string(25) "Administration-Peter Alan"
[57] => string(7) "Agency "
[58] => string(8) "Lettings"
[59] => string(17) "Retail Sales City"
[60] => string(17) "Retail Sales East"
[61] => string(18) "Retail Sales North"
[62] => string(18) "Retail Sales South"
[63] => string(17) "Retail Sales West"
[64] => string(21) "Regulated Sales South"
[65] => string(20) "Regulated Sales West"
[66] => string(29) "Albany Road-Retail Sales City"
[67] => string(23) "Barry-Retail Sales City"
[68] => string(28) "Birchgrove-Retail Sales City"
[69] => string(24) "Canton-Retail Sales City"
[70] => string(26) "Llandaff-Retail Sales City"
[71] => string(25) "Penarth-Retail Sales City"
[72] => string(27) "Llanishen-Retail Sales City"
[73] => string(30) "Queen Street-Retail Sales City"
[74] => string(24) "Rumney-Retail Sales City"
[75] => string(31) "St Davids two-Retail Sales City"
[76] => string(28) "Whitchurch-Retail Sales City"
[77] => string(26) "Aberdare-Retail Sales East"
[78] => string(29) "Abergavenny-Retail Sales East"
[79] => string(27) "Blackwood-Retail Sales East"
[80] => string(28) "Caerphilly-Retail Sales East"
[81] => string(27) "Ebbw Vale-Retail Sales East"
[82] => string(26) "Hereford-Retail Sales East"
[83] => string(35) "Llandrindod Wells-Retail Sales East"
[84] => string(32) "Merthyr Tydfil-Retail Sales East"
[85] => string(26) "Monmouth-Retail Sales East"
[86] => string(25) "Newport-Retail Sales East"
[87] => string(27) "Pontypool-Retail Sales East"
[88] => string(25) "Bangor-Retail Sales North"
[89] => string(26) "Chester-Retail Sales North"
[90] => string(26) "Denbigh-Retail Sales North"
[91] => string(28) "Llandudno-Retail Sales North"
[92] => string(23) "Mold-Retail Sales North"
[93] => string(28) "Prestatyn-Retail Sales North"
[94] => string(27) "Pwllheli-Retail Sales North"
[95] => string(29) "Shrewsbury-Retail Sales North"
[96] => string(26) "Wrexham-Retail Sales North"
[97] => string(27) "Bridgend-Retail Sales South"
[98] => string(28) "Cowbridge-Retail Sales South"
[99] => string(33) "Llantwit Major-Retail Sales South"
[100] => string(26) "Maesteg-Retail Sales South"
[101] => string(24) "Neath-Retail Sales South"
[102] => string(29) "Pontypridd-Retail Sales South"
[103] => string(28) "Porthcawl-Retail Sales South"
[104] => string(30) "Port Talbot-Retail Sales South"
[105] => string(28) "Tonypandy-Retail Sales South"
[106] => string(27) "Treorchy-Retail Sales South"
[107] => string(29) "Aberystwyth-Retail Sales West"
[108] => string(28) "Carmarthen-Retail Sales West"
[109] => string(27) "Fishguard-Retail Sales West"
[110] => string(27) "Gorseinon-Retail Sales West"
[111] => string(31) "Haverfordwest-Retail Sales West"
[112] => string(27) "Llampeter-Retail Sales West"
[113] => string(27) "Llandeilo-Retail Sales West"
[114] => string(26) "Llanelli-Retail Sales West"
[115] => string(27) "Morriston-Retail Sales West"
[116] => string(25) "Mumbles-Retail Sales West"
[117] => string(25) "Swansea-Retail Sales West"
}
最佳答案
您传递的是原义的回车符，而不是必需的字符串\r。这是因为您的字符串用双引号引起来，并且PHP在将转义序列传递给MySQL之前对其进行了解释。
解决方案很简单-将SQL字符串用单引号引起来，或更简单的方法是，用另一个反斜杠转义反斜杠：
$countquery = $DB->get_records_sql("SELECT COUNT(uid.userid) FROM {user_info_data} uid WHERE uid.data = '".$allunit."' OR uid.data = '".$allunit."\\r'");
编辑
我认为这将在没有循环的单个查询中完成您想要的操作：
$query = "
SELECT COUNT(uid.data) AS users
FROM {user_info_data}
WHERE uid.data IN ('".implode("','", $allunits)."','".implode("\\r','", $allunits)."\\r')
";
$countquery = $DB->get_records_sql($query);
$ucount = $countquery->users;