Display table row of data with query

PHP
1

I have a table of data that I want to display from a normalized MySQL DB.

Right now I have a an HTML output that shows names of clients from my clients table in MySQL. I have another MySQL table that lists Jobs from those clients. I know what I can use an inner join to join the clients client id to the jobs job id but what I’m trying to do is on that HTML output, I want it to list this:

Client A | # of Jobs
Client B | # of Jobs

Right now it’s just:
Client A
Client B

Here’s what I have for code:

<?
        // Request the text of all the info and sort it *****ASC*****
        $result = @mysql_query("SELECT * 
        FROM jropfinances.Clients 
        ORDER BY last_name, first_name ASC");
    
        if (!$result) {
        exit('<p>Error performing query: ' . mysql_error() . '</p>');
        }
        
        //creating the table w/ headers
        echo '
        <table cellpadding="3px">
            <tr>
                <td style="width:85px;font-weight:bold"><p><a href="?order=last_name" rel="nofollow">Last Name</a></p></td>
                <td style="width:125px;font-weight:bold"><p><a href="?order=first_name" rel="nofollow">First Name</a></p></td>

            </tr>';
        // Display each client
        while ($row = mysql_fetch_array($result)) {
        
        echo '
            <tr title="" class="tblhover">    
                <td style="width:85px;">' . $row['last_name'] . '</td>
                <td style="width:125px;">' . $row['first_name'] . '</td>
             
            </tr>
        ';
        }    
        echo '</table>';
        ?>

Now, if I run a SQL query I can get the # of sessions for each client when I specify their id, but I don’t know how to display it using the code above, here’s what I’ve used if it helps:

SELECT COUNT(*) FROM jropfinances.Jobs INNER JOIN jropfinances.Clients ON (Clients.id = client_id) WHERE Clients.id = "1"
2

You can actually return all the data you need in just one query:

SELECT jropfinances.Clients.*, count(jropfinances.Jobs.id) as total_jobs
FROM jropfinances.Clients
JOIN jropfinances.Jobs ON jropfinances.Clients.id = jropfinances.Jobs.client_id
GROUP BY jropfinances.Clients.id
ORDER BY last_name, first_name ASC

By the way, I notice you’re using the mysql_* functions, but be aware that the mysql extension has been deprecated and will eventually be removed from PHP. It would be a good idea to think about using the mysqli or PDO extension instead. There’s a thread here that covers converting your code to use the myqli_* functions.