Arraying results within an array

I have a form which displays all car dealers for a particular county. I also need it to show a count of the number of cars in this dealer's premises. The code below works perfectly except for the fact that the car totals per dealer are showing the same value for all (total) instead of individual ones. I know this is to do with the placement of the 2nd part of this line below but I dont know where to put it. Can someone please help? Thanks:

while (($row = mysql_fetch_array($result)) AND ($row1 = mysql_num_rows($result1)))


*********Full code*********

<?
session_start();
$linkcounty= $_SERVER['QUERY_STRING'];

include('connect.php');
$dbLinkID = mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM customers WHERE (((County)='$linkcounty') AND ((AccountType)='Dealer'))";
$result = mysql_query($query);

$query1 = "SELECT advertisements.Customer_No FROM advertisements INNER JOIN customers ON advertisements.Customer_No = customers.Customer_No WHERE (((customers.County)='$linkcounty') AND ((advertisements.Status)='Active') AND ((customers.AccountType) ='Dealer'))";

$result1 = mysql_query($query1);
$num_results = mysql_num_rows($result1);
?>

<HTML>
<TITLE>Search</TITLE>
<body BGCOLOR="#FFFFFF">
<FORM>

<?
echo '<P>';
ECHO '<TABLE BORDER="0" WIDTH=80%>';

while (($row = mysql_fetch_array($result)) AND ($row1 = mysql_num_rows($result1)))
{

$linkTemplate = '<FONT SIZE="2"><a href="edit.php?Customer_No='.intval($row
['Customer_No']).'">%s</a>';

ECHO '<TR>';
ECHO '<TD VALIGN=TOP width=100><FONT SIZE="2">';
printf('-->');
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=300><FONT SIZE="2">';
printf($linkTemplate, htmlspecialchars (stripslashes($row['FirstName'])));
printf($linkTemplate, htmlspecialchars (stripslashes(' ')));
printf($linkTemplate, htmlspecialchars (stripslashes($row['LastName'])));
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=150><FONT SIZE="2">';
printf('has');
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=150><FONT SIZE="2">';
printf($linkTemplate, htmlspecialchars (stripslashes($row1)));
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=300><FONT SIZE="2">';
printf('car(s) in stock');
ECHO '</TR>';
}

ECHO '</TABLE>';

?>

</FORM>
</BODY>
</HTML>
LVL 2
andrewf10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
You ae not even selecting the total number of cars with the dealer from database. The total you are displaying now if numnber of dealers rather than number of cars with the dealer or all the dealers. What table has this information? You need to query that table.
andrewf10Author Commented:
Made the tweak but it wont affect the count. Revised code (only change is the selected fieldname):

<?
session_start();
$linkcounty= $_SERVER['QUERY_STRING'];

include('connect.php');
$dbLinkID = mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM customers WHERE (((County)='$linkcounty') AND ((AccountType)='Dealer'))";
$result = mysql_query($query);

$query1 = "SELECT advertisements.Advertisement_No FROM advertisements INNER JOIN customers ON advertisements.Customer_No = customers.Customer_No WHERE (((customers.County)='$linkcounty') AND ((advertisements.Status)='Active') AND ((customers.AccountType) ='Dealer'))";

$result1 = mysql_query($query1);
$num_results = mysql_num_rows($result1);
?>

<HTML>
<TITLE>Search</TITLE>
<body BGCOLOR="#FFFFFF">
<FORM>

<?
echo '<P>';
ECHO '<TABLE BORDER="0" WIDTH=80%>';

while (($row = mysql_fetch_array($result)) AND ($row1 = mysql_num_rows($result1)))
{

$linkTemplate = '<FONT SIZE="2"><a href="edit.php?Customer_No='.intval($row
['Customer_No']).'">%s</a>';

ECHO '<TR>';
ECHO '<TD VALIGN=TOP width=100><FONT SIZE="2">';
printf('-->');
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=300><FONT SIZE="2">';
printf($linkTemplate, htmlspecialchars (stripslashes($row['FirstName'])));
printf($linkTemplate, htmlspecialchars (stripslashes(' ')));
printf($linkTemplate, htmlspecialchars (stripslashes($row['LastName'])));
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=150><FONT SIZE="2">';
printf('has');
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=150><FONT SIZE="2">';
printf($linkTemplate, htmlspecialchars (stripslashes($row1)));
ECHO '</TD>';
ECHO '<TD VALIGN=TOP width=300><FONT SIZE="2">';
printf('car(s) in stock');
ECHO '</TR>';
}

ECHO '</TABLE>';

?>

</FORM>
</BODY>
</HTML>
amit_gCommented:
Which table and column contains the car count with a dealer? You still don't have that. Does table advertisements has this information per dealer in column Advertisement_No?
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

andrewf10Author Commented:
Apologies, the query is a bit dodgy alright because the results are not grouped by dealer.

Advertisement_No is the primary key of advertisements
Customer_No is the primary key of customers
Customer_No is also on the advertisements table

I'm trying to get the count of records from the two joined tables where:
advertisement.Status='Active'
customers.County='$linkcounty'
customers.AccountType='Dealer'

The second query I posted above is not breaking the results down by dealer. Should I feed the Customer_No returned by the first query into the second query I wonder?!
amit_gCommented:
Change the first query to ...

$query = "SELECT customers.FirstName, customers.LastName, Count(*) as Num FROM advertisements INNER JOIN customers ON advertisements.Customer_No = customers.Customer_No WHERE (((customers.County)='$linkcounty') AND ((advertisements.Status)='Active') AND ((customers.AccountType) ='Dealer'))";

Change

printf($linkTemplate, htmlspecialchars (stripslashes($row1)));

to

printf($linkTemplate, htmlspecialchars (stripslashes($row['Num'])));
andrewf10Author Commented:
Thanks but Im getting this message:   "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

for this line:    while ($row = mysql_fetch_array($result))

Ive removed the second query so first PHP part looks as below. Made the other change too

<?
session_start();
$linkcounty= $_SERVER['QUERY_STRING'];

include('connect.php');
$dbLinkID = mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT customers.FirstName, customers.LastName, Count(*) as Num FROM advertisements INNER JOIN customers ON advertisements.Customer_No = customers.Customer_No WHERE (((customers.County)='$linkcounty') AND ((advertisements.Status)='Active') AND ((customers.AccountType) ='Dealer'))";

$result = mysql_query($query);
?>
andrewf10Author Commented:
Finally got it to work by creating it in Access, then bringing it into a 'proper' database like MySQL and correcting all the errors mesages it gave me (in English!):

$query = "SELECT customers.Customer_No, customers.County, customers.AccountType, advertisements.Status,
customers.LastName, customers.FirstName, Count(advertisements.Advertisement_No) AS CountOfAdvertisement_No
FROM advertisements INNER JOIN customers ON advertisements.Customer_No = customers.Customer_No
GROUP BY customers.Customer_No, customers.County, customers.LastName, customers.FirstName, customers.County,
advertisements.Status, customers.AccountType HAVING (((customers.County)='Waterford') AND ((advertisements.Status)='Active') AND ((customers.AccountType)='Dealer'))";
amit_gCommented:
You seems to have gotten it. I forgot to put the group by clause in the query I posted.
RomModCommented:
Closed, 500 points refunded.
RomMod
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.