How to retrieve data from a table and count number rows from another table then show them together in a while loop or HTML table?

php select join mysqli subquery

132 观看

2回复

411 作者的声誉

I need to retrieve 'projects' details from table 'projects'.

I need to get number of bids from 'projectsBids'.

Then show them all in a HTML table based on project IDs

This is my 'projectsBids' table:

pID  | bidder      
-----+----------------
AAAA | mark 
AAAA | pete 
AAAA | dave 
BBBB | mason
BBBB | simon

Following code shows number of bids for a particular project ID (pID)

<?php
$pID = $_GET["pID"];
$query = "SELECT pID FROM projectsBids WHERE pID='$pID'";
$result = mysqli_query($con, $query) or die('error');
echo mysqli_num_rows($result);
?>

Now I want to show this count using it's 'pID' in a HTML summary table where I am retrieve data from another table 'projects'

'projects' table:

pID   |     pName      | pBudget 
--------------------------------
AAAA | Create Website | 250      
BBBB | Create an App  | 550

I am using the following code to retrieve the data..

Check 4th <td>(in following code) where I want to display the number of bids for that project ID.

<?php
$sql="SELECT pID,pName,pBudget FROM projects ORDER BY id";
    $result=mysqli_query($con,$sql);
    while($row = mysqli_fetch_assoc($result))
    {
        echo "<tr>";
        echo "<td>".$row['pID']."</td>";
        echo "<td>".$row['pName']."</td>";
        echo "<td>".$row['pBudget']."</td>";
        echo "<td>"**HERE I WANT TO SHOW 'Number of BIDS of PROJECT ID(pID)'**"</td>";
        echo "</tr>";
    }
?>

Whenever I try with subquery or join it does not work..

What is the correct query to do this?

作者: Riffaz Starr 的来源 发布者: 2017 年 12 月 27 日

回应 (2)


-1

315 作者的声誉

Try this:

select p.pid,p.pname,p.pbudget,count(pb.bidder) as totbids from projects p left join projectsBids pb on p.pid=pb.pid group by p.pid,p.pname,p.pbudget
作者: maddy23285 发布者: 27.12.2017 05:06

1

104 作者的声誉

决定

Try this I have tested it on your data.

SELECT *,( SELECT COUNT(pID) FROM projectsBids WHERE projectsBids.pID = projects.pID) as bids FROM projects use this query you will get the result.

作者: Nilesh Lathe 发布者: 27.12.2017 05:53
32x32