Join two database tables and output resutls to HTML table

For my current progress I need to create a table like the one below

pid cid eid name value

1    1   4   name  ab
2    1   5   amt    2
3    1   4   name   cd
4    1   5   amt    4

Instead of creating the table like this

pid cid  name amt

1    1    ab    22
2    1     cd    4

Anyhow created table as my wish with the below code

<table width="1204" height="100" border="1">
  <tr>
  <?php  $sqlname="Select * From  elements  where cat_id='1' order by e_id ";   
           $resname=mysql_query($sqlname);
           while($rowname=mysql_fetch_array($resname)){

          ?>
    <td colspan="2"><?php echo $rowname['lable_name']; ?></td>
  </tr>
  <tr>
  <?php $i=0; 
  $sqlprolist="select value from products_list where name='".$rowname['lable_name']."' and e_id='".$rowname['e_id']."'";
          $resprolist=mysql_query($sqlprolist);
          while($rowprolist=mysql_fetch_array($resprolist)){
              $i++;

         ?>
    <td><?php echo $rowprolist['value'];?></td>
    <?php if($i%8==0){
        ?>
        <tr></tr>
        <?php }?>
    <?php }?>
  </tr>
  <?php }?>
</table>

But I don't have any idea to retrieve data from the table for processing.

thanks

as by following martin the table created as like the below table

pid cid eid name  value

12   1  4  name    abc
1    1  4  name    cde
13   1  5  code    12
2    1  5  code    14

how to split up the data like

name code breeder quality size

abc   12   121     121     22
acfd  34   164     22      22

thanks

Solution

It's difficult to help you without seeing database structure. Please share it with us, you might get better answers.

Anyway, I suppose you have two tables, elements and products_list. It looks like you need to lookup the value column in the products_list for every row in the elements table. You can merge these table into one result set using a single SQL query:

SELECT e.p_id, e.cat_id, e.e_id, e.lable_name, p.value
FROM elements e, products_list p
WHERE e.cat_id='1' AND 
p.name = e.lable_name AND p.e_id = e.e_id
ORDER BY by e.e_id

Note that the e.p_id is just a guess, you have not shared with us, where the "pid" column value gets from. Also not sure, if you actually need to match the rows using the p.name = e.lable_name. If e_id is primary key, you might do with p.e_id = e.e_id only.

What's the point of cid column? If it is indeed the cat_id column in database, why do you need it in HTML table, if you filter the elements to cat_id=1 anyway? Was that intentional?

You can now take the results of the query and simply output it to a HTML table row by row like:

<table width="1204" height="100" border="1">
  <tr>
    <th>pid</th>
    <th>cid</th>
    <th>eid</th>
    <th>name</th>
    <th>value</th>
  </tr>
  <?php

    $sqlname =
        "SELECT e.p_id, e.cat_id, e.e_id, e.lable_name, p.value ".
        "FROM elements e, products_list p ".
        "WHERE e.cat_id='1' AND ".
        "p.name = e.lable_name AND p.e_id = e.e_id".
        "ORDER BY e.e_id";   
    $resname = mysql_query($sqlname);
    while ($row = mysql_fetch_array($resname))
    {
  ?>
  <tr>
    <td><?php echo $row['p_id'];?></td>
    <td><?php echo $row['cat_id'];?></td>
    <td><?php echo $row['e_id'];?></td>
    <td><?php echo htmlspecialchars($row['lable_name']);?></td>
    <td><?php echo $row['value'];?></td>
  </tr>
  <?php 
    }
  ?>
</table>

This is just a first step. It's unlikely this is correct answer. But I hope it helps you understand, what are you trying to do and to your question.

This is hardly production-grade code. But I wanted to do as little changes as possible, so you can see, what is the actual change from your code.

Also note that the mysql_ functions are deprecated. See the big red box on PHP documentation page.