Friday, 28 December 2018

get data from sql by join three table

Using This code you can join three table for fetching data from mysql.
mysql data and table details :
hostname : localhost
dbusername : dbusername
dbpassword : yourpassword
dbname : dbname
Table Details :
Table 1 : wp_users ( column name : ID , user_login )
Table 2 : user_transactions ( column : user_id , referral_amnt )
Table 3 : user_plans ( column : user_id , amount_paid )

Just copy and paste this code and modify mysql and table details as per your details

<?php
 $connect = mysqli_connect("localhost", "dbusername", "yourpassword", "dbname");
 $sql = "SELECT * FROM wp_users INNER JOIN user_transactions ON wp_users.ID = user_transactions.user_id 
JOIN user_plans ON wp_users.ID = user_plans.user_id";
 $result = mysqli_query($connect, $sql);
 ?>


 <!DOCTYPE html>
 <html>
      <head>
           <title>Get Data from Join Three Table</title>
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
      </head>
      <body>
           <br />
           <div class="container" style="width:980px;">
<Img src="https://yourdomain.com/wp-content/uploads/2018/12/transparent-logo-1-1.png"width="150px" height="150px"><br>
                <h3 align="">Join Three Table</h3><br />               
                <div class="table-responsive">
                     <table class="table table-striped">
                          <tr>
                               <th>User Name</th>
                               <th>Total Invested Amount</th>
                               <th>Referral Amount</th>
                             
                          </tr>
                          <?php
                          if(mysqli_num_rows($result) > 0)
                          {
                               while($row = mysqli_fetch_array($result))
                               {
                          ?>
                          <tr>
                               <td><?php echo $row["user_login"];?></td>
<td><?php echo $row["amount_paid"]; ?></td>
                               <td><?php echo $row["referral_amnt"]; ?></td>
                             
                          </tr>
                          <?php
                               }
                          }
                          ?>
                     </table>
                </div>
           </div>
           <br />
      </body>
 </html>

get data from sql by join two table

Using This code you can join two table for fetching data from mysql.
mysql data and table details :
hostname : localhost
dbusername : dbusername
dbpassword : yourpassword
dbname : dbname
Table Details :
Table 1 : wp_users ( column name : ID , user_login )
Table 2 : user_transactions ( column : user_id , referral_amnt )

Just copy and paste this code and modify mysql and table details as per your details

<?php
 $connect = mysqli_connect("localhost", "investment_new", "yourpassword", "investment_wp");
 $sql = "SELECT * FROM wp_users INNER JOIN user_transactions ON wp_users.ID = user_transactions.user_id";
 $result = mysqli_query($connect, $sql);
 ?>
 <!DOCTYPE html>
 <html>
      <head>
           <title>Get Data from Join Two Table</title>
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
      </head>
      <body>
           <br />
           <div class="container" style="width:980px;">
<Img src="https://www.yourdomain.com/wp-content/uploads/2018/12/transparent-logo-1-1.png"width="150px" height="150px"><br>
                <h3 align="">Join Two Table</h3><br />               
                <div class="table-responsive">
                     <table class="table table-striped">
                          <tr>
                               <th>User Name</th>
                               <th>Referral Amount</th>
                             
                          </tr>
                          <?php
                          if(mysqli_num_rows($result) > 0)
                          {
                               while($row = mysqli_fetch_array($result))
                               {
                          ?>
                          <tr>
                               <td><?php echo $row["user_login"];?></td>
                               <td><?php echo $row["referral_amnt"]; ?></td>
                             
                          </tr>
                          <?php
                               }
                          }
                          ?>
                     </table>
                </div>
           </div>
           <br />
      </body>
 </html>

Simple search form in php

Using This code you can fetching data from mysql.
mysql data and table details :
hostname : localhost
dbusername : dbusername
dbpassword : yourpassword
dbname : dbname
Table Details :
Table 1 : table_users ( column name : pk_i_id , name , username , password  )


Just copy and paste this code and modify mysql and table details as per your details

<?php

if(isset($_POST['search']))
{
    $valueToSearch = $_POST['valueToSearch'];
    // search in all table columns
    // using concat mysql function
    $query = "SELECT * FROM `table_user` WHERE CONCAT(`pk_i_id`, `s_name`, `s_username`, `s_password`) LIKE '%".$valueToSearch."%'";
    $search_result = filterTable($query);
 
}
 else {
    $query = "SELECT * FROM `osoh_t_user`";
    $search_result = filterTable($query);
}

// function to connect and execute the query
function filterTable($query)
{
    $connect = mysqli_connect("localhost", "dbusername", "yourpassword", "dbname");
    $filter_Result = mysqli_query($connect, $query);
    return $filter_Result;
}

?>

<!DOCTYPE html>
<html>
    <head>
        <title>PHP HTML TABLE DATA SEARCH</title>
        <style>
            table,tr,th,td
            {
                border: 1px solid black;
            }
        </style>
    </head>
    <body>
     
        <form action="1d.php" method="post">
            <input type="text" name="valueToSearch" placeholder="Value To Search"><br><br>
            <input type="submit" name="search" value="Filter"><br><br>
         
            <table>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Username</th>
                    <th>Password</th>
                </tr>

      <!-- populate table from mysql database -->
                <?php while($row = mysqli_fetch_array($search_result)):?>
                <tr>
                    <td><?php echo $row['pk_i_id'];?></td>
                    <td><?php echo $row['s_name'];?></td>
                    <td><?php echo $row['s_username'];?></td>
                    <td><?php echo $row['s_password'];?></td>
                </tr>
                <?php endwhile;?>
            </table>
        </form>
     
    </body>
</html>