CODEWITHSUNDEEP
phpmysqlhtml
user1110409
user1110409

Reputation:

mysql query to html table

I want to show the query results of mysql query in a table,

There are two tables, and some information has to be retrieved from the second table

I want to retrieve title, borrower name and borrower surname from other tables,

I tried to retrieve title, but I was unsuccessful, Then I want to retrieve the borrower name and borrower surname from the users table

You may also offer a new way to put the results into a table

Here is the code 

for($i=0;$i<999999999;$i++){
$sql="SELECT * FROM products WHERE product_id='$i' LIMIT 1";
//echo $sql;
$moviename="SELECT dbid FROM products WHERE product_id='$i'";
$dbid=mysql_query($moviename);
$movname="SELECT title FROM titles WHERE dbid='$dbid' ";
$name=mysql_query($movname);
$title=mysql_fetch_array($name);

print "<table border=1>
<tr><th>Product_ID</th>
<th>dbid</th>
<th>Title</th>  
<th>Status</th> 
<th>Date</th> 
<th>Borrowe_id</th> 
<th>Borrower Name</th>
<th>Borrower Surname</th></tr>";
$result=mysql_query($sql);
if($result==NULL){
continue ;
}
while ($row = mysql_fetch_array($result)) 
{ 
  print "<tr><td>{$row[1]}</td> 
   <td>{$row[2]}</td> 
   <td>{$title[0]}</td> 
  <td>{$row[12]}</td>
  <td>{$row[13]}</td> 
  <td>{$row[14]}</td>
  <td>{$borrowername[0]}</td>  
  <td>{$borrowesurname[0]}</td></tr>"; 
}  
print "</table>"; 
}
?>

Upvotes: 0

Views: 4105

Answers (4)

M_R_K
M_R_K

Reputation: 6350

Here is a function for you to convert any MySQL select statement to a HTML table with table headings.

function createTable_from_sql_select_query($query) {
    $sql_link = Connect_MySQLi_DB(); // Your Database Connection
    $sql_link->set_charset("utf8");
    $result = $sql_link->query($query);

    // Adding Missing Array Column Function for Old PHP Versions (<5.5)
    if (!function_exists('array_column')) {

        function array_column($array, $column) {
            $ret = array();
            foreach ($array as $row)
                $ret[] = $row[$column];
            return $ret;
        }

    }

    $headings = json_decode(json_encode($result->fetch_fields()), true);
    $headings = array_column($headings, 'name');

      $return = '<table>';
      $return .= '<thead><tr>';
      for ($x = 0; $x <= (count($headings) - 1); $x++) {
      $return .= '<th>' . ucwords(str_replace('_', ' ', (strtolower($headings[$x])))) . '</th>';
      }
      $return .= '</tr></thead><tbody>';
      while ($row = $result->fetch_object()) {
      $return .= '<tr>';
      for ($x = 0; $x <= (count($headings) - 1); $x++) {
      $return .= '<td>' . $row->$headings[$x] . '</td>';
      }
      $return .= '</tr>';
      }
      $return .= '</tbody></table>';

      return $return;
}

Upvotes: 0

afuzzyllama
afuzzyllama

Reputation: 6548

There are a few things going on here which you should definitely not be doing.

Super insane loop with a query inside

for($i=0;$i<999999999;$i++){
    // SELECT STATEMENT HERE!
}

Why are you looping potentially 999,999,999 times? And if you do you are going to hit your database 999,999,999 times? That is going to kill your database and it can be done much more efficiently.

Use joins over individual selects

$sql="SELECT * FROM products WHERE product_id='$i' LIMIT 1";
$moviename="SELECT dbid FROM products WHERE product_id='$i'";
$movname="SELECT title FROM titles WHERE dbid='$dbid' ";

Note:

product_id='$i' LIMIT 1

Usually you want your ids to be unique. Why do you need to LIMIT your query?

The query can be simplified to:

select
    * 
from
    products p
    left outer join titles t
        on p.dbid = t.dbid

Note: SELECTING * IS BAD PRACTICE, BUT I DON'T KNOW WHAT YOUR STRUCT IS

Illogical logic

if($result==NULL){
    continue ;
}

while ($row = mysql_fetch_array($result)) {...}

$result is null at this point, so your while loop will never happen.

So after killing that loop, simplifying the query, and revising logic... we can do:

$result_resource = mysql_query($sql);

echo"
    <table border=1>
        <tr>
            <th>Product_ID</th>
            <th>dbid</th>
            <th> Title</th>  
            <th>Status</th> 
            <th>Date</th> 
            <th>Borrowe_id</th> 
            <th>Borrower Name</th>
            <th>Borrower Surname</th>
        </tr>";

while ($row = mysql_fetch_array($result_resource))
{
    echo "
        <tr>
            <td>{$row['ProductID']}</td> 
            <td>{$row['dbid']}</td> 
            <td>{$row['Title']}</td> 
            <td>{$row['Status']}</td>
            <td>{$row['Date']}</td> 
            <td>{$row['Borrowe_id']}</td>
            <td>{$borrowername[0]}</td>  
            <td>{$borrowesurname[0]}</td>
        </tr>"; 
}

echo "</table>";

Looking at $borrower.... I have a feeling you can also join your borrow table to the query to bring that in too.

I would also look into templating. It would definitely separate your data from your display and make your code a lot easier to read.

Upvotes: 4

Nettogrof
Nettogrof

Reputation: 2136

(Maybe your question is "too localize")

First I don't recommend this :

  for($i=0;$i<999999999;$i++)

because it will take long for the server to compute, and generate a lot of mysql query.

Second, most (all?) of your mysql queries could be merge in only one query. You can about query on more then one table here (dev.mysql.com) There's several way to join multiples tables, you could read about it

Third, in your code the table header will be repeated each time, because it's in your For loop.

here my suggest to improve your code :

 //Print the header
 print "<table border=1>
<tr><th>Product_ID</th>
<th>dbid</th>
<th>Title</th>  
<th>Status</th> 
<th>Date</th> 
<th>Borrowe_id</th> 
<th>Borrower Name</th>
<th>Borrower Surname</th></tr>";

//adapt the following sql with your tables name / fields
$sql="SELECT products.product_id, products.dbid, title.tiles, product.status, product.date, borrow.id , borrow.name, borrow.surname FROM products,titles,borrow WHERE product.dbid = titles.dbid && borrow.id = product.prodict_id LIMIT 1";


$result=mysql_query($sql);

while ($row = mysql_fetch_array($result)) 
{ 
 print "<tr><td>{$row[1]}</td> 
  <td>{$row[2]}</td> 
  <td>{$row[3]}</td> 
 <td>{$row[4]}</td>
 <td>{$row[5]}</td> 
 <td>{$row[6]}</td>
 <td>{$row[7]}</td>  
 <td>{$row[8]}</td></tr>"; 
}  
print "</table>"; 

?>

I know that the sql query isn't the best one, but I think those suggestions could help you.

Upvotes: 0

Nir Alfasi
Nir Alfasi

Reputation: 53525

First,you should debug your code

one thing that pops up is the following error:

you should change the following from:

print "<tr><td>{$row[1]}</td> 
   <td>{$row[2]}</td> ..."

to:

print "<tr><td>{" . $row[1] . "}</td> 
   <td>{" . $row[2] . "}</td> ..."

Second, when you say " I was unsuccessful" - please add details, did you get an error ? what was the output ?

Upvotes: 0

Related Questions