mysqli database query for count views, join and select some rows

 Here I am giving some useful mysql queries. If you check it, you will understand, which query is suitable for your project.

function getChatUserNames(){

global $db_conx;

$sql = "SELECT 

    c1.fname AS username_first_name,

    c1.mname AS username_m_name,

    c1.lname AS username_last_name,

    c1.custid AS uchatid,

    c1.uid,

    GROUP_CONCAT(DISTINCT CONCAT(c2.custid, ' - ', c2.fname, ' ', c2.lname) SEPARATOR '<br> ') AS chat_with_list

FROM 

    candidate c1

LEFT JOIN 

    (

        SELECT from_user_id AS user_id, to_user_id AS chat_with_user

        FROM chat_message

        UNION

        SELECT to_user_id AS user_id, from_user_id AS chat_with_user

        FROM chat_message

    ) AS cm_combined

    ON c1.custid = cm_combined.user_id

LEFT JOIN 

    candidate c2 ON c2.custid = cm_combined.chat_with_user

GROUP BY 

    c1.custid

";

$select = mysqli_query($db_conx,$sql);

while($row = mysqli_fetch_assoc($select)){

$ret[] = $row;

}

return $ret;

}

function getAllChatDataAdmin($user1, $user2){ 

global $db_conx; 

$ret = array();

//check block

$sql = "SELECT id FROM block_table WHERE user_id = '".$user1."' AND blocked_user_id = '". $user2."'  ";

    $select = mysqli_query($db_conx, $sql);

    $blockCheck = (mysqli_num_rows($select) == 0) ? '0' : '1';

    //get chat data

$query = "

    SELECT a.fname as from_user_name, b.fname as to_user_name, cm.chat_message, cm.timestamp,  cm.to_user_id, cm.from_user_id, cm.status

    FROM chat_message cm

    INNER JOIN candidate a

    ON cm.from_user_id = a.custid

    INNER JOIN candidate b

    ON cm.to_user_id = b.custid

    WHERE (cm.from_user_id = $user2 AND cm.to_user_id = $user1 ) 

    OR (cm.from_user_id = $user1 AND cm.to_user_id = $user2 )

    ";

    $chatdata = mysqli_query($db_conx,$query);

    while($row = mysqli_fetch_assoc($chatdata)){

    //$row['blockCheck'] = $blockCheck; //insert blockcheck variable

$ret[] = $row;

}

return $ret;

}

//15-06-2025

function getInterestMsgText($tocid){

global $db_conx;

$fromid = $_SESSION['cid'];

$sql = "SELECT * FROM interestmsg WHERE toid = '".$tocid."' AND fromid = '".$fromid."' AND status = 1 ";

$result = mysqli_query($db_conx, $sql);

return $row = mysqli_fetch_assoc($result);

}

function getInterestMsgs(){

global $db_conx;

$tocid = $_SESSION['cid'];

$sql = "SELECT i.*, c.uid as cuid, c.fname, c.lname 

FROM interestmsg i

LEFT JOIN candidate c On i.fromid = c.custid

WHERE i.toid = '".$tocid."' AND i.status = 1 

ORDER BY i.updateddate ASC";

$select = mysqli_query($db_conx, $sql);

while($row = mysqli_fetch_assoc($select)){

$ret[] = $row;

}

return $ret;

}

function sentInterestMsgs(){

global $db_conx;

$fromid = $_SESSION['cid'];

$sql = "SELECT i.*, c.uid as cuid, c.fname, c.lname 

FROM interestmsg i

LEFT JOIN candidate c On i.toid = c.custid

WHERE i.fromid = '".$fromid."' AND i.status = 1 

ORDER BY i.updateddate ASC";

$select = mysqli_query($db_conx, $sql);

while($row = mysqli_fetch_assoc($select)){

$ret[] = $row;

}

return $ret;

}