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 观看


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)

$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.

$sql="SELECT pID,pName,pBudget FROM projects ORDER BY id";
    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)


315 作者的声誉

Try this:

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


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