MySQL DAYNAME Date and Time function, translated in Portuguese

Discussion in 'PHP' started by afonseca, Oct 17, 2006.

0
  1. #1
    Hi all!

    I'm using an opensource in PHP for digital libraries.
    I'm trying to put the day names, that are generated by a SQL query in MySQL using the MySQL DAYNAME Date and Time function, in Portuguese.
    So when the query says "Monday" the system print "segunda-feira".
    The SQL query is generated by the 'browseListing' function.

    Does anyone can help me doing that?
    Someting using the switch statement ?
    My PHP is not that sofisticated...

    Thanks in advance
    António Fonseca


    /**
    * Method used to get the list of records in browse view by a browsing category available in the
    * system.
    *
    * @access public
    * @param integer $current_row The point in the returned results to start from.
    * @param integer $max The maximum number of records to return
    * @param string $searchKey The search key the records are being browsed by eg Subject, Created Date (latest additions).
    * @param string $getCount If 1 the query will get all the records to it can get a count, otherwise it will be restricted earlier.
    * @return array The list of records
    */
    function browseListing($current_row = 0, $max = 25, $searchKey="Subject", $order_by=null, $getCount=1)
    {
    // return array();
    if (empty($order_by)) {
    $order_by = $searchKey;
    if (empty($order_by)) {
    $order_by = 'Title';
    }
    }
    if ($max == "ALL") {
    $max = 9999999;
    }
    $start = $current_row * $max;
    $sekdet = Search_Key::getDetailsByTitle($order_by);
    $data_type = $sekdet['xsdmf_data_type'];
    $order_dir = ' asc ';
    $restrictSQL = "";
    $middleStmt = "";
    $extra_order = "";
    $internal_extra_order = "";
    $termCounter = 5;
    $dbtp = APP_DEFAULT_DB . "." . APP_TABLE_PREFIX;


    $joinNum = $termCounter + 1;
    // $joinNum = 2;
    $extra = '';
    if ($searchKey == "Subject") {
    $terms = $_GET['parent_id'];
    $search_data_type = "varchar";
    $restrictSQL = "AND r".$termCounter.".rmf_".$search_data_type." = '".$terms."'";
    } elseif ($searchKey == "Created Date") {
    $search_data_type = "date";
    $restrictSQL = "AND DATE_SUB(CURDATE(), INTERVAL 6 DAY) < r".$termCounter.".rmf_".$search_data_type."";
    $extra = ", DAYNAME(display.preorder) as day_name";
    // $extra_order = "r".$termCounter.".rmf_".$search_data_type.", ";
    $subqueryExtra = ", r".$termCounter.".rmf_".$search_data_type." as preorder";

    $extra_order = "date(display.preorder) DESC, ";
    $order_dir = " ASC ";
    $internal_extra_order = "date(r5.rmf_date) desc, ";
    if ($order_by == 'Created Date') {
    /* $order_dir = " DESC ";
    $internal_extra_order = "preorder desc, ";*/
    $extra = ", DAYNAME(display.sort_column) as day_name";
    $extra_order = "";
    $subqueryExtra = "";
    $internal_extra_order = "";
    $joinNum = $termCounter;
    } else {

    }
    } elseif ($searchKey == "Date") {
    $search_data_type = "date";
    $subqueryExtra = ", r".$termCounter.".rmf_".$search_data_type;
    $terms = $_GET['year'];
    $restrictSQL = "AND YEAR(r".$termCounter.".rmf_".$search_data_type.") = ".$terms."";
    } elseif ($searchKey == "Author") {
    $search_data_type = "varchar";
    $subqueryExtra = ", r".$termCounter.".rmf_".$search_data_type;
    $terms = mysql_escape_string($_GET['author']);

    $restrictSQL = "AND r".$termCounter.".rmf_".$search_data_type." = '".$terms."'";
    } else {
    $search_data_type = "varchar";
    $subqueryExtra = ", r".$termCounter.".rmf_".$search_data_type;

    }

    $mainJoin = 2;
    $middleStmt .= "
    inner join {$dbtp}record_matching_field r".$termCounter." on r".$termCounter.".rmf_rec_pid = r".($mainJoin).".rmf_rec_pid
    inner join {$dbtp}xsd_display_matchfields x".$termCounter." on r".$termCounter.".rmf_xsdmf_id = x".$termCounter.".xsdmf_id
    inner join {$dbtp}search_key AS s".$termCounter." on s".$termCounter.".sek_id = x".$termCounter.".xsdmf_sek_id
    and s".$termCounter.".sek_title = '".$searchKey."' ".$restrictSQL;
    $termCounter++;
    /*
    inner join {$dbtp}record_matching_field r3 on r3.rmf_rec_pid = r2.rmf_rec_pid
    inner join {$dbtp}xsd_display_matchfields x3 on r3.rmf_xsdmf_id = x3.xsdmf_id
    inner join {$dbtp}search_key s3 on s3.sek_id = x3.xsdmf_sek_id and s3.sek_title = '$order_by'
    */

    if ($searchKey == "Created Date") {
    $search_data_type = "date";
    }
    $authArray = Collection::getAuthIndexStmt();
    $authStmt = $authArray['authStmt'];
    $joinStmt = $authArray['joinStmt'];

    $bodyStmt = " FROM {$dbtp}record_matching_field r2
    inner join {$dbtp}xsd_display_matchfields AS x2 on r2.rmf_xsdmf_id = x2.xsdmf_id and r2.rmf_varchar='2' and x2.xsdmf_element='!sta_id' $joinStmt

    ";

    if ((($searchKey == 'Created Date') && ($order_by != $searchKey)) || ($searchKey != 'Created Date')) {
    $bodyStmt .= "
    inner join " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "record_matching_field r".$termCounter."
    on r".$termCounter.".rmf_rec_pid = r".$mainJoin.".rmf_rec_pid
    inner join " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "xsd_display_matchfields x".$termCounter."
    on r".$termCounter.".rmf_xsdmf_id = x".$termCounter.".xsdmf_id
    inner join " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "search_key s".$termCounter."
    on s".$termCounter.".sek_id = x".$termCounter.".xsdmf_sek_id
    and s".$termCounter.".sek_title = '$order_by' ";
    } else {
    $joinNum = 2;
    }
    $bodyStmt .= "

    $authStmt

    $middleStmt ";



    $stmtCount = "SELECT count(distinct r2.rmf_rec_pid) as display_count
    $bodyStmt";
    /* FROM {$dbtp}record_matching_field r2
    inner join {$dbtp}xsd_display_matchfields x2 on r2.rmf_xsdmf_id = x2.xsdmf_id $joinStmt
    inner join {$dbtp}search_key s2 on (s2.sek_id = x2.xsdmf_sek_id AND s2.sek_title = 'Display Type')

    $authStmt

    $middleStmt

    inner join {$dbtp}record_matching_field AS r4 on r4.rmf_rec_pid = r2.rmf_rec_pid
    inner join {$dbtp}xsd_display_matchfields AS x4 on r4.rmf_xsdmf_id = x4.xsdmf_id and r4.rmf_varchar='2' and x4.xsdmf_element='!sta_id'";
    */
    // echo $stmtCount;
    $res = $GLOBALS["db_api"]->dbh->getOne($stmtCount);
    if (PEAR::isError($res)) {
    Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
    $res = array();
    }
    $total_rows = $res;


    $stmt = "SELECT r1.*, x1.*, s1.*, k1.*, d1.* ".$extra."
    FROM {$dbtp}record_matching_field r1
    INNER JOIN {$dbtp}xsd_display_matchfields x1
    ON r1.rmf_xsdmf_id = x1.xsdmf_id
    INNER JOIN (
    SELECT r2.rmf_rec_pid, r".$joinNum.".rmf_$data_type as sort_column $subqueryExtra
    $bodyStmt
    order by $internal_extra_order sort_column $order_dir, r2.rmf_rec_pid desc

    ";
    // if ($getCount == 0) {
    $stmt .= " limit $start, $max ";
    // }
    $stmt .=
    "


    ) as display on display.rmf_rec_pid = r1.rmf_rec_pid

    LEFT JOIN {$dbtp}xsd_loop_subelement s1
    ON (x1.xsdmf_xsdsel_id = s1.xsdsel_id)
    LEFT JOIN {$dbtp}search_key k1
    ON (k1.sek_id = x1.xsdmf_sek_id)
    LEFT JOIN {$dbtp}xsd_display d1
    ON (d1.xdis_id = r1.rmf_varchar and k1.sek_title = 'Display Type')
    ORDER BY $extra_order display.sort_column $order_dir, r1.rmf_rec_pid DESC ";

    $res = $GLOBALS["db_api"]->dbh->getAll($stmt, DB_FETCHMODE_ASSOC);

    // echo $stmt; //return array();
    $securityfields = Auth::getAllRoles();
    if (PEAR::isError($res)) {
    Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
    return "";
    } else {

    $return = array();
    $return = Collection::makeReturnList($res);

    $return = Collection::makeSecurityReturnList($return);
    // $hidden_rows = count($return);
    $hidden_rows = $total_rows;
    $return = Auth::getIndexAuthorisationGroups($return);
    $return = Misc::cleanListResults($return);
    // print_r($return);
    // $total_rows = count($return);
    if (($start + $max) < $total_rows) {
    $total_rows_limit = $start + $max;
    } else {
    $total_rows_limit = $total_rows;
    }
    $total_pages = ceil($total_rows / $max);
    $last_page = $total_pages - 1;
    // $return = Misc::limitListResults($return, $start, ($start + $max));
    return array(
    "list" => $return,
    "info" => array(
    "current_page" => $current_row,
    "start_offset" => $start,
    "end_offset" => $total_rows_limit,
    "total_rows" => $total_rows,
    "total_pages" => $total_pages,
    "previous_page" => ($current_row == 0) ? "-1" : ($current_row - 1),
    "next_page" => ($current_row == $last_page) ? "-1" : ($current_row + 1),
    "last_page" => $last_page,
    "hidden_rows" => $hidden_rows - $total_rows
    )
    );
    }
    }
     
    afonseca, Oct 17, 2006 IP
  2. pondlife

    pondlife Peon

    Messages:
    898
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    0
    #2
    The above code is too complicated for me but if you're just after translating the 7 day names couldn't you load them into an array and have the keys in Portuguese and just display the matched key value? You'd then build your date-time output from the variables...

    Sorry if I've missed the point :)
     
    pondlife, Oct 19, 2006 IP