0

I have two tables in a database called categories and products, I need to interconnect them and show all related products under each category and following is my approach. 

<table class="table table-striped">                         
<tr>
<th>product id</th>
<th>product name</th>
<th>category name</th>
</tr>

<?php
mysql_connect("localhost","root");
mysql_select_db("inventory");
$res=mysql_query("SELECT c.* , p.* FROM categories c,products p WHERE c.id=p.category_id");
while($row=mysql_fetch_array($res))
{
 ?>                          

<tr>
    <td><p><?php echo $row['name']; ?></p></td>
    <td><p><?php echo $row['name']; ?></p></td>
    </tr> 

<?php
}
?>
</table>

But it throws an error

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/index.php on line 27
Ganidu Ranasinghe
  • 223
  • 6
  • 21

2 Answers2

1

You really should upgrade to a more recent version of PHP.

There is no error checking in your code. There is no error handling in your code. That you have reported an error message here suggests that the error logging is at least working.

Your query failed.

The reason your query failed is at least one of the following failed:

mysql_connect("localhost","root");
mysql_select_db("inventory");
$res=mysql_query("SELECT c.* , p.* FROM categories c,products p WHERE c.id=p.category_id");

Consider:

$dbh=mysql_connect("localhost","root") || die (mysql_error());
mysql_select_db("inventory", $dbh) || die (mysql_error($dbh));
$qry="SELECT c.* , p.* FROM categories c,products p WHERE c.id=p.category_id";
$res=mysql_query($qry, $dbh) || die (mysql_error($dbh));
symcbean
  • 46,644
  • 6
  • 56
  • 89
  • and then this error message. Warning: mysql_select_db() expects parameter 2 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/index.php on line 25 – Ganidu Ranasinghe Sep 28 '18 at 11:09
  • You didn't get that from the code above (unless you really are using somethnig other than the standard mysql extension). That error implies the `mysql_conect()` call failed - in which case the execution would have stopped before it got to `mysql_select_db()` – symcbean Sep 28 '18 at 12:38
0

I would expect the SELECT statement may also be an issue asside from the error you are getting, you will need to Join the two tables together to get the data you require.

JOIN: https://dev.mysql.com/doc/refman/8.0/en/join.html

Without knowing what columns are within your two tables, something like the following should work:

"SELECT p.*, c.* FROM products p JOIN categories c ON c.id = p.category_id"

As you are selecting all (*) from both tables this might become messy so i would suggest only selecting the columns you are going to use.

CamBarker
  • 41
  • 7