View SQL table on HTML page(SQL SERVER MANAGEMENT STUDIO)

Question

I am creating a website that will be connected with the database. I have created a database in SQL Server.

I want to connect one table with one html page, so that I can view the table on that page. In this way I will connect all the SQL tables with each html page.

I know I have to use php/javaquery!

Please help me with appropriate codes!

Also I am new to SQL. I am learning!

Please explain the changes that I will have to do in the codes you provide to enable it in my system.

i got these 2 codes:which will work for my requirement? code1:

  $result = mysqli_query($con,"SELECT * FROM Orders");

 echo "<table>";
 echo "<table border='1'>
 <tr>
 <th>ID</th>
 <th>orderNumber</th>
 <th>Price</th>
 <th>customerName</th>
 <th>salesRep</th>
 <th>DatePicker</th>
 <th>shipMethod</th>
 <th>trackingNumber</th>
 <th>Statuscheck</th>
 <th>Edit</th>
 </tr>";

 while($row = mysqli_fetch_array($result))
   {
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['orderNumber'] . "</td>";
echo "<td>" . $row['Price'] . "</td>";
echo "<td>" . $row['customerName'] . "</td>";
echo "<td>" . $row['salesRep'] . "</td>";
echo "<td>" . $row['DatePicker'] . "</td>";
echo "<td>" . $row['shipMethod'] . "</td>";
echo "<td>" . $row['trackingNumber'] . "</td>";
echo "<td>" . $row['Statuscheck'] . "</td>";
echo "<td>" . $row['Edit'] . "</td>";
echo "</tr>";
  }
echo "</table>";

?>

code2:

 <?php

   $host=""; // Host name 
   $username=""; // Mysql username 
   $password=""; // Mysql password 
   $db_name=""; // Database name 
   $tbl_name=""; // Table name 

  // Connect to server and select databse.
     mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
     mysql_select_db("$db_name")or die("cannot select DB");
     $sql="SELECT * FROM $tbl_name ORDER BY id DESC";
  // OREDER BY id DESC is order result by descending
  }
      $result=mysql_query($sql);

    ?>

  <html>

  <head>

   <script type="text/javascript" charset="utf-8" src="jquery.js"></script>

  </head>

   <table id="forum" width="90%" border="0" align="center"          cellpadding="3"      cellspacing="1" bgcolor="#CCCCCC">

 <thead>

  <tr>
   <th width="6%" align="center" bgcolor="#E6E6E6"><strong>#</strong></td>
    <th width="43%" align="center" bgcolor="#E6E6E6"><strong>Topic</strong></td>
   <th width="10%" align="center" bgcolor="#E6E6E6"><strong>Author</strong></td>
   <th width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
    <th width="13%" align="center" bgcolor="#E6E6E6"><strong>Replies</strong></td>
     <th width="13%" align="center"  bgcolor="#E6E6E6"><strong>Date/Time</strong></td>

     </tr>

    </thead>

    <?php 
  // Start looping table row
 while($rows=mysql_fetch_array($result)){
     ?>

  <tbody>

  <tr>
  <td bgcolor="#FFFFFF"><? echo $rows['threadtype']; ?></td>
  <td bgcolor="#FFFFFF"><a href="view_topic.php?id=<? echo $rows['id']; ?>">
  <? echo $rows['topic']; ?></a><BR></td>
 <td align="center" bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
  <td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
   <td align="center" bgcolor="#FFFFFF"><? echo $rows['reply']; ?></td>
   <td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
  </tr>

  </tbody>

   <?php

   // Exit looping and close connection 
  }
   mysql_close();

  ?>

 <tfoot>
 <tr>
 <td colspan="6" align="right" bgcolor="#E6E6E6"><strong>Create New  Topic</strong> td>
</tr>
  </tfoot>
</table>

 </html>

You can use `mysqli` extension http://php.net/manual/en/mysqli.quickstart.connections.php — newage, Jun 28 '16 at 10:06
It is too broad for SO. There are many web-servers to run you site (XAMPP, IIS, Apache, NGINX etc.) which one you will use? If you will use PHP, which drivers you will use to get data from DB (PDO, sqlsrv etc)? When you determine what you will use - you will find out that there are many manuals about retrieving/writing or whatever from/to DB. — gofr1, Jun 28 '16 at 10:46
i am using php,html & sqlsrv . n i want to link sql datatable with html using php.i have mentioned some codes provided to me above — Neha Shetty, Jun 28 '16 at 11:12

score 1 · Answer 1 · answered Jun 28 '16 at 12:28

If I understood your questions correctly, you want to display the data that's retrieved from database. I am not a php expert, but based on the code, let me try to explain.

1) First connect to the database using the connections string provided and fetch the data

     <?php

   $host=""; // Host name 
   $username=""; // Mysql username 
   $password=""; // Mysql password 
   $db_name=""; // Database name 
   $tbl_name=""; // Table name 

  // Connect to server and select databse.
     mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
     mysql_select_db("$db_name")or die("cannot select DB");
     $sql="SELECT * FROM $tbl_name ORDER BY id DESC";
  // OREDER BY id DESC is order result by descending
  }
      $result=mysql_query($sql);

    ?>

2) Create an HTML table to display the data. (First create the table header)

<table id="forum" width="90%" border="0" align="center"          cellpadding="3"      cellspacing="1" bgcolor="#CCCCCC">

 <thead>

  <tr>
   <th width="6%" align="center" bgcolor="#E6E6E6"><strong>#</strong></td>
    <th width="43%" align="center" bgcolor="#E6E6E6"><strong>Topic</strong></td>
   <th width="10%" align="center" bgcolor="#E6E6E6"><strong>Author</strong></td>
   <th width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
    <th width="13%" align="center" bgcolor="#E6E6E6"><strong>Replies</strong></td>
     <th width="13%" align="center"  bgcolor="#E6E6E6"><strong>Date/Time</strong></td>

     </tr>

    </thead>

3) Now dynamically add the data returned by the php code to this HTML table

while($row = mysqli_fetch_array($result))
   {
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['orderNumber'] . "</td>";
echo "<td>" . $row['Price'] . "</td>";
echo "<td>" . $row['customerName'] . "</td>";
echo "<td>" . $row['salesRep'] . "</td>";
echo "<td>" . $row['DatePicker'] . "</td>";
echo "<td>" . $row['shipMethod'] . "</td>";
echo "<td>" . $row['trackingNumber'] . "</td>";
echo "<td>" . $row['Statuscheck'] . "</td>";
echo "<td>" . $row['Edit'] . "</td>";
echo "</tr>";
  }
echo "</table>";

?>

4) Complete the HTML table

<tfoot>
 <tr>
 <td colspan="6" align="right" bgcolor="#E6E6E6"><strong>Create New  Topic</strong> td>
</tr>
  </tfoot>
</table>

5) Finally as best practice to close the connection to the database to avoid any unwanted issues + performance gain.

<?php

   // Exit looping and close connection 
  }
   mysql_close();

  ?>

View SQL table on HTML page(SQL SERVER MANAGEMENT STUDIO)

1 Answers1