sammyltk sammyltk - 5 months ago 10
MySQL Question

Issue to output mysql result

I create a form to register company and insert into DB. also created a form to insert each company cost and sell price. after all this has been inserted in DB, i try to output it on table but it was showing the company id instead of name... please bellow illustrate better

company DB

|id | company_name |
|1 | Oliva Ltd |
|2 | Bone Mill |


comp_product DB

|id | company_id | cp | sp |
|3 | 2 | 20000 | 18000 |
|4 | 1 | 3000 | 100 |


The help I need is to output all data from comp_product DB by representing the
company_id
with their
company_name
from company DB.

Ouput Table

| company_id | cp | sp |
| Bone Mill | 20000 | 18000 |
| Oliva Ltd | 3000 | 100 |


i tried bellow code, but i was showing only one row.

<table>
<thead>
<tr>
<th>company_id</th>
<th>cp</th>
<th>sp</th>
</tr>
<?php

$result = mysql_query("SELECT * FROM comp_product ORDER BY id DESC");
while($row = mysql_fetch_array($result))
{
$result = mysql_query("SELECT * FROM company WHERE id ='".$row['company_id']."'");
while($rowz = mysql_fetch_array($result))
{ $name = $rowz['company_name']; }
?>
<tr>
<td><?php echo $name ?></td>
<td><?php echo $row['cp'] ?></td>
<td><?php echo $row['cp'] ?></td>
</tr>
<?php
}
?>
</table>


Show can I do this. Thanks for understanding

Answer

You need a JOIN query

SELECT `company`.`company_name`, `comp_product`.`cp`, `comp_product`.`sp`
FROM `company`
LEFT JOIN `comp_cp`
ON `company`.`id` = `comp_product`.`company_id`

You do not need two queries, you can just loop through the results of one. If you need to limit this to one company you can add a WHERE clause.


In addition you need to stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

Comments