Issue to output mysql result

Question

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

[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 03 '16 at 12:53
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 03 '16 at 12:53
ok.. thanks for the advise.... how can i use PDO to determine the result — sammyltk, Jun 03 '16 at 12:57

score 0 · Answer 1 · answered Jun 03 '16 at 12:57

0

Try this

 "SELECT company.company_name as company_id,comp_product.cp 
 ,comp_product.sp  FROM company inner join comp_product on 
 company.id = comp_product.company_id
 ORDER BY company.id DESC"

You will get desired result .

answered Jun 03 '16 at 12:57

Web Artisan

1,870
3
23
33

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

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.

edited May 23 '17 at 10:29

Community

1
1

answered Jun 03 '16 at 12:57

Jay Blanchard

34,243
16
77
119

Thanks... can I also JOIN query in PDO to get the result, moving to PDO now – sammyltk Jun 03 '16 at 13:02
I have started with PDO, it works but joining the query, but i added a company to company DB only making it 3 data... but I still have 2 data in comp_product DB. problem is that it show all the 3 data and set to a blank the third line... – sammyltk Jun 03 '16 at 14:03
You may want to post a new question for that @sammyltk, showing your new code, etc. – Jay Blanchard Jun 03 '16 at 14:14

score 0 · Answer 3 · answered Jun 03 '16 at 13:00

0

You need to use this query to properly show the data:

mysql_query ("SELECT company.company_name as company_id,comp_product.cp 
 ,comp_product.sp  FROM company where 
 company.id = comp_product.company_id
 ORDER BY company.id DESC");

answered Jun 03 '16 at 13:00

Shahzeb Akram

156
10

score 0 · Answer 4 · answered Jun 03 '16 at 13:05

The problem with your code outputting one row is simply because you're reusing the $result variable for both queries.

The name query result overwrites the price query.

Your code looks like this right now:

$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']; 
}

Change it to:

$result = mysql_query("SELECT * FROM comp_product ORDER BY id DESC");
while($row = mysql_fetch_array($result)){

    $name_query_result = mysql_query("SELECT * FROM company WHERE id ='".$row['company_id']."'");
    while($rowz = mysql_fetch_array($name_query_result)){ 

      $name = $rowz['company_name']; 
}

Issue to output mysql result

4 Answers4