Bitrix - Forums

Nishant Bhatt User Posts: 109 Joined: 02/22/2012	#1 07/06/2012 15:24:55 Hello, I need iblock Custom Fields value from database... How to get user custom fields value via SQL Query? what write like "Select * From .... Where ..." - Nishant
	Quote Name

Anna Slyshkina Administrator Posts: 611 Joined: 03/13/2008	#2 07/06/2012 15:46:00 Hi, Of course, I won't provide you with the SQL example:) Because we do not recommend to perform queries to a DB directly (please find more info here). Please use API: CIBlockElement::GetProperty() or CIBlockElement::GetList()
	Quote Name

brown2

User

Posts: 17 Joined: 01/07/2010

07/06/2012 16:22:28

Nishant,

The Custom Field code can be a little tricky. Here is the code I use.

Code

$arFilter = array("IBLOCK_ID"=>"5", "ACTIVE"=>"Y");


$arParams = array("*"); //This will give you every parameter/field, including custom ones, in the CIBlockSection. 


$arPcList = CIBlockSection::GetList(Array("CODE"=>"ASC"), $arFilter, false, $arParams);

while($arList->NavNext(true, "f_")) :


// Do your stuff here


endwhile;

You can also list the fields/parameters individually by including the name separated by commas. If you have a prefix before your custom fields, instead of just a *, lets say your prefix is CF_, then you can use "CF_*" which will pull all fields that start with CF_.
I hope this helps.
I'm also with Anna, do not directly query the database. The API is much easier once you get used to it.

-Jeff

Edited: brown2 - 07/06/2012 16:26:14

Quote Name

Nishant Bhatt

User

Posts: 109 Joined: 02/22/2012

07/06/2012 16:38:01

Hello,

I am trying to fetch new user list with descending order of custom fields.. that i can;t implement inside Iblock so i tried with direct Query...

Look my code which returns new employee list..

Code

 $dbEntries = CIBlockElement::GetList(
      array('active_from' => 'desc'),
      $arFilter,
      false,
      array('nTopCount' => $arParams['NUM_USERS']),
      array('IBLOCK_ID', 'NAME', 'DATE_ACTIVE_FROM', 'PROPERTY_USER')
   );

   while ($arRes = $dbEntries->Fetch())
   {
      $arUserIDs[] = $arRes['PROPERTY_USER_VALUE'];
      $arResult['ENTRIES'][] = $arRes;
   }

now i want to fetch data as per newly created custom fields with name of "EMP_HIRED_DATE"...

Could you please tell me inside above code how may i get EMP_HIRED_DATE descending order of users?

Please advice

- Nishant

Quote Name

Anna Slyshkina

Administrator

Posts: 611 Joined: 03/13/2008

07/06/2012 16:49:05

Code
$dbEntries = CIBlockElement::GetList( array('PROPERTY_EMP_HIRED_DATE' => 'desc'),

Quote Name

Nishant Bhatt

User

Posts: 109 Joined: 02/22/2012

07/06/2012 17:13:39

not working...output not meet our requirement!

please check screen shot i given date in hiring date..

http://www.diigo.com/item/image/2fa8u/svxp

i created custom fields as "UF_HIRE_DATE" with property of ENTITY_ID => USER & FIELD_NAME => UF_HIRE_DATE

is there any made change?

new code:

Code

$dbEntries = CIBlockElement::GetList(
      array('PROPERTY_UF_HIRE_DATE' => 'desc'),
      $arFilter,
      false,
      array('nTopCount' => $arParams['NUM_USERS']),
      array('IBLOCK_ID', 'NAME', 'DATE_ACTIVE_FROM', 'PROPERTY_USER')
   );

   while ($arRes = $dbEntries->Fetch())
   {
      $arUserIDs[] = $arRes['PROPERTY_USER_VALUE'];
      $arResult['ENTRIES'][] = $arRes;
   }

Edited: Nishant Bhatt - 07/06/2012 17:25:48

- Nishant

Quote Name

Nishant Bhatt

User

Posts: 109 Joined: 02/22/2012

07/06/2012 17:34:46

Hello Anna

I checked inside SQL Query Performance...

that not consider PROPERTY_UF_HIRE_DATE field inside Query.

Original Query which include "ORDER BY BE.ACTIVE_FROM desc"

Code

SELECT BE.IBLOCK_ID as IBLOCK_ID,BE.NAME as NAME,IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y')) as DATE_ACTIVE_FROM, FPV0.VALUE as PROPERTY_USER_VALUE, FPV0.ID as PROPERTY_USER_VALUE_ID,IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y')) as ACTIVE_FROM
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='USER'
LEFT JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='STATE'
LEFT JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
WHERE
1=1 AND ( ((((BE.IBLOCK_ID = '12')))) AND ((((BE.ACTIVE='Y')))) AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL))) AND ( ((((BE.PREVIEW_TEXT LIKE '%Employed%')))) OR ((((FPV1.VALUE_ENUM = '265')))) ) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
ORDER BY
BE.ACTIVE_FROM desc
LIMIT 5

after change array('PROPERTY_UF_HIRE_DATE' => 'desc')

which not consider PROPERTY_UF_HIRE_DATE field inside query ???

Code

SELECT BE.IBLOCK_ID as IBLOCK_ID,BE.NAME as NAME,IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%m/%d/%Y')) as DATE_ACTIVE_FROM, FPV0.VALUE as PROPERTY_USER_VALUE, FPV0.ID as PROPERTY_USER_VALUE_ID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
LEFT JOIN b_iblock_property FP0 ON FP0.IBLOCK_ID = B.ID AND FP0.CODE='USER'
LEFT JOIN b_iblock_property FP1 ON FP1.IBLOCK_ID = B.ID AND FP1.CODE='STATE'
LEFT JOIN b_iblock_element_property FPV0 ON FPV0.IBLOCK_PROPERTY_ID = FP0.ID AND FPV0.IBLOCK_ELEMENT_ID = BE.ID
LEFT JOIN b_iblock_element_property FPV1 ON FPV1.IBLOCK_PROPERTY_ID = FP1.ID AND FPV1.IBLOCK_ELEMENT_ID = BE.ID
WHERE
1=1 AND ( ((((BE.IBLOCK_ID = '12')))) AND ((((BE.ACTIVE='Y')))) AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL))) AND ( ((((BE.PREVIEW_TEXT LIKE '%Employed%')))) OR ((((FPV1.VALUE_ENUM = '265')))) ) ) AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
LIMIT 5

then why that field not appear in SQL query?

Edited: Nishant Bhatt - 07/06/2012 17:36:20

- Nishant

Quote Name

Anna Slyshkina Administrator Posts: 611 Joined: 03/13/2008	#8 07/09/2012 12:03:08 As I see from the attached image, UF_HIRE_DATE is the user custom field, not the iblock property. The CIBlockElement::GetList() does not work with user fields. Please use the CUser::GetList() method to get the user field value and sort by it.
	Quote Name

Cracker

Guest

12/04/2012 10:22:22

у меня такая ошибка есть. Есть решения? помогите пожалуйста

Fatal error: Class 'CUser' not found in /var/www/clients/client5/web17/web/ru/test_api.php on line 11

КОД

<?
$login_password_correct = false;

if (
isset( $_REQUEST['login'] ) && strlen( $_GET['password'] ) > 0
&&
isset( $_REQUEST['login'] ) && strlen( $_GET['password'] ) > 0
)
{

$rsUser = CUser::GetByLogin( $_GET['login'] );
if ($arUser = $rsUser->Fetch())
{
if(strlen($arUser["PASSWORD"]) > 32)
{
$salt = substr($arUser["PASSWORD"], 0, strlen($arUser["PASSWORD"]) - 32);
$db_password = substr($arUser["PASSWORD"], -32);
}
else
{
$salt = "";
$db_password = $arUser["PASSWORD"];
}

$user_password = md5($salt.$_GET['password']);

if ( $user_password == $db_password )
{
$login_password_correct = true;
}
}
}
?>

Quote Name

Anna Slyshkina Administrator Posts: 611 Joined: 03/13/2008	#10 12/04/2012 13:30:12 Try to include the Kernel module with the CModule::IncludeModule() method before the code. If the error persists, please contact our Helpdesk or our Russian speaking community at dev.1c-bitrix.ru.
	Quote Name

Login:
Password:
	Remember me on this computer

Forgot your password?
Register