php - 不支持的操作数类型错误/回车 - PHP

Question

我正在使用 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"
} 

Answer 1

您正在传递一个文字回车符，而不是所需的字符串 \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;