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;

}

How to backup and download Database using PHP

Hello

Today I saw you How to backup and download Database using PHP.

dbexport.php

<?php

    

    $mysqlUserName      = 'root';

    $mysqlPassword      = 'password';

    $mysqlHostName      = 'localhost';

    $DbName             = 'wordpress63';  

    

   

    Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,  $tables=false, $backup_name=false );

    function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name=false )

    {

        $mysqli = new mysqli($host,$user,$pass,$name); 

        $mysqli->select_db($name); 

        $mysqli->query("SET NAMES 'utf8'");

        $queryTables    = $mysqli->query('SHOW TABLES'); 

        while($row = $queryTables->fetch_row()) 

        { 

            $target_tables[] = $row[0]; 

        }   

        if($tables !== false) 

        { 

            $target_tables = array_intersect( $target_tables, $tables); 

        }

        foreach($target_tables as $table)

        {

            $result         =   $mysqli->query('SELECT * FROM '.$table);  

            $fields_amount  =   $result->field_count;  

            $rows_num=$mysqli->affected_rows;     

            $res            =   $mysqli->query('SHOW CREATE TABLE '.$table); 

            $TableMLine     =   $res->fetch_row();

            $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

            for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 

            {

                while($row = $result->fetch_row())  

                { //when started (and every after 100 command cycle):

                    if ($st_counter%100 == 0 || $st_counter == 0 )  

                    {

                            $content .= "\nINSERT INTO ".$table." VALUES";

                    }

                    $content .= "\n(";

                    for($j=0; $j<$fields_amount; $j++)  

                    { 

                        $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 

                        if (isset($row[$j]))

                        {

                            $content .= '"'.$row[$j].'"' ; 

                        }

                        else 

                        {   

                            $content .= '""';

                        }     

                        if ($j<($fields_amount-1))

                        {

                                $content.= ',';

                        }      

                    }

                    $content .=")";

                    //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler

                    if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 

                    {   

                        $content .= ";";

                    } 

                    else 

                    {

                        $content .= ",";

                    } 

                    $st_counter=$st_counter+1;

                }

            } $content .="\n\n\n";

        }

        

        $backup_name = $name.".sql";

        header('Content-Type: application/octet-stream');   

        header("Content-Transfer-Encoding: Binary"); 

        header("Content-disposition: attachment; filename=\"".$backup_name."\"");  

        echo $content; exit;

    }

?>

Display database tables and its data dynamically in php mysql

Dear Friends,

Today I face problem with MySQL Workbench. PhpMyAdmin is not running in my system. I saw that database is updated but not displayed effect in Mysql Workbench. So this code will help you to see table's data. All tables with data displayed in tab.

<!DOCTYPE html>

<html>

<head>

<title>Display Database Tables</title>

<style>

/* Style the tab */

.tab {

overflow: hidden;

border: 1px solid #ccc;

background-color: #f1f1f1;

}

/* Style the buttons inside the tab */

.tab button {

background-color: inherit;

float: left;

border: none;

outline: none;

cursor: pointer;

padding: 14px 16px;

transition: 0.3s;

font-size: 17px;

}

/* Change background color of buttons on hover */

.tab button:hover {

background-color: #ddd;

}

/* Create an active/current tablink class */

.tab button.active {

background-color: #ccc;

}

/* Style the tab content */

.tabcontent {

display: none;

padding: 6px 12px;

border: 1px solid #ccc;

border-top: none;

}

</style>

</head>

<body>

<div class="tab">

<?php

// MySQLi connection

$mysqli = new mysqli("localhost", "Database username", "Database password", "Database name");

// Check connection

if ($mysqli->connect_errno) {

echo "Failed to connect to MySQL: " . $mysqli->connect_error;

exit();

}

// Get all table names in the database

$tables = array();

$result = $mysqli->query("SHOW TABLES");

while ($row = $result->fetch_array(MYSQLI_NUM)) {

$tables[] = $row[0];

}

// Loop through each table and display its name, column names, and data

foreach ($tables as $table) {

echo "<button class='tablinks' onclick='openTab(event, \"$table\")'>$table</button>";

}

?>

<!-- Create tab content for each table -->

<?php

foreach ($tables as $table) {

echo "<div id='$table' class='tabcontent'>";

echo "<h2>Table: $table</h2>";

echo "<table border='1'>";

$result = $mysqli->query("SELECT * FROM $table");

$fields = $result->fetch_fields();

echo "<tr>";

foreach ($fields as $field) {

echo "<th>$field->name</th>";

}

echo "</tr>";

while ($row = $result->fetch_assoc()) {

echo "<tr>";

foreach ($row as $value) {

echo "<td>$value</td>";

}

echo "</tr>";

}

echo "</table>";

echo "</div>";

}

// Close MySQLi connection

$mysqli->close();

?>

</div>

<script>

// Show default table

document.getElementById("<?php echo $tables[0]; ?>").style.display = "block";

// Function to open a specific tab content

function openTab(evt, tabName) {

  var i, tabcontent, tablinks;

  // Hide all tab content

  tabcontent = document.getElementsByClassName("tabcontent");

  for (i = 0; i < tabcontent.length; i++) {

    tabcontent[i].style.display = "none";

  }

  // Remove active class from all tab links

  tablinks = document.getElementsByClassName("tablink");

  for (i = 0; i < tablinks.length; i++) {

    tablinks[i].className = tablinks[i].className.replace(" active", "");

  }

  // Show the selected tab content and add active class to the selected tab link

  document.getElementById(tabName).style.display = "block";

  evt.currentTarget.className += " active";

}

</script>

</body>

</html>

import database by php code

 Here I explain you how to import database by PHP code.

<?php
// Name of the data file
$filename = 'u6357583.zip';
// MySQL host
$mysqlHost = 'localhost';
// MySQL username
$mysqlUser = 'root';
// MySQL password
$mysqlPassword = '';
// Database name
$mysqlDatabase = 'u635758';
// Connect to MySQL server
$link = mysqli_connect($mysqlHost, $mysqlUser, $mysqlPassword, $mysqlDatabase) or die('Error connecting to MySQL Database: ' . mysqli_error());

$tempLine = '';
// Read in the full file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line) {
    // Skip it if it's a comment
    if (substr($line, 0, 2) == '--' || $line == '')
        continue;
    // Add this line to the current segment
    $tempLine .= $line;
    // If its semicolon at the end, so that is the end of one query
    if (substr(trim($line), -1, 1) == ';')  {
        // Perform the query
        mysqli_query($link, $tempLine) or print("Error in " . $tempLine .":". mysqli_error());
        // Reset temp variable to empty
        $tempLine = '';
    }
}
 echo "Tables imported successfully";
?>

database upload with cmd

Here I explain how to upload database with cmd.

First create database in phpmyadmin. For example ahd_db1

Now copy your sql file and paste in this path : MySql/bin

for example your sql file is db.sql so paste it xampp/MySql/bin/db.sql

Now run this command :

D:\xampp\mysql\bin>mysql -uroot ahd_db1< db.sql

Wait for some time. If execution ncompleted than it will display noraml cmd. For cross check go to phpmyadmin and check your database.