[RESOLVED] displaying results of one to many relationship

[ptaylor2005]

Having trouble getting this to work properly:

I have a query that displays the results of a search, showing employers and their contact info, and also the cities they are located in. They may be only in one city, or several. The results are written out to the webpage using a table row per result, but if there are multiple cities, it will list them one per line within that same row, just expanding the height of the row.

My problem is I don't know how to code the sql query properly, to group the employer info so that it only appears once, but with the associated cities. Right now I get results that looks like this, if I run it in query analyzer;

1 ABC Co. 555-1212 [e-mail]
Bo Smith New York

2 ABC Co. 555-1212 [e-mail]
Bo Smith Las Vegas

3 ABC Co. 555-1212 [e-mail]
Bo Smith Seattle

---

it is displaying the complete company info, once for every city that company is linked to. I was hoping to group it somehow so that the company info only appears once, and it would list the cities grouped with that company:

1 ABC Co. 555-1212 [e-mail]
Bo Smith New York
Las Vegas
Seattle

2 XYZ Co. 555-1234 [e-mail]
Jo Bob Portland
Boston
LA
etc....

I know there is a way to use PHP code to filter the results and display it like I need it to, but I am hoping there is a better way to write the query, so that I get a true number of records returned etc. as I need to use recordset paging as well, and there will be hundreds of results.

Here is the query I currently have:

Select c.company_id, q.region_name, c.firstname, c.lastname, c.title, c.company_name, c.address, c.company_email, c.areacode, c.phone1, c.phone2, j.country, i.industry From smi_companies As c, jupiter_countries As j, smi_employer_industries As i, smi_regions As q, smi_company_regions As r Where c.company_name like '%$q%' And j.country_id = c.country_id And r.company_id = c.company_id And r.region_id = q.region_id And i.industry_id = c.industry Group By c.company_id, c.firstname, c.lastname, c.title, c.company_name, c.address, c.company_email, c.areacode, c.phone1, c.phone2, q.region_name, j.country, i.industry Order By c.company_name

Thanks very much,
Paul

[Sxooter]

Generally, the way you do this is in code, something like this:

$header="";
while ($row = pgsql_fetch_assoc($res)){
    if ($header!=$row['header']){
        $header=$row['header'];
        print_header($header);
    }
    print_company_info($row);
}

function print_header($headerstring){
    //do something to print the company header here
}

function print_company_info($info){
    // do something to print the info rows here
}

[ptaylor2005]

I basically have something like that working already, but unfortunately it doesnt' solve the problem of the query returning an "incorrect" number of records.

So I do a search and it returns 27 records, and the recordset paging relies on that figure to set up the number of records per page, along with the statement at the top of the page, "your search returned 27 records" - it would be 27 if it was writing out 27 rows, one for each city bound to the corresponding company, but because I am grouping the cities to a company programatically, there are only say 14 rows written to the page (some companies will have 5 or 6 cities associated with them).

That's where I am stumped - with the query, trying to get it to return the proper number of records. I'm afraid I am not an SQL guru but I was hoping there would be a Group BY query or similar that would do the grouping for me and return the proper number of records.

Thanks,
Paul

[Sxooter]

You'll likely have to use two queries then, one to get the count, and one to get the data. You could do some kind of union or join on them, if you want just one query, but that's kind of ugly and hackish.

That said, I've done many ugly and hackish things in my life...

[ptaylor2005]

Thanks... I think that's what I will do, use two queries, and just use the second one to get the count and see if I can get it to work with the recordset paging / navigation links at the bottom of the page, although that might be tricky, since the paging of course is used to manipulate the results of the other "data" query...

[Roger_Ramjet]

If you are paginating this then you are going to be running multiple queries anyway. The usual way is to get the count first,as you say, and then to run the query for each page using a LIMIT clause; and this should apply to the first page of results as well as all the rest.

(first, your query in a legible form)

"SELECT c.company_id, q.region_name, c.firstname, c.lastname, 
   c.title, c.company_name, c.address, c.company_email, c.areacode, 
   c.phone1, c.phone2, j.country, i.industry 
FROM smi_companies As c, jupiter_countries As j, smi_employer_industries As i,
    smi_regions As q, smi_company_regions As r 
WHERE c.company_name like '%$q%' And j.country_id = c.country_id 
   And r.company_id = c.company_id And r.region_id = q.region_id 
   And i.industry_id = c.industry 
  GROUP BY c.company_id, c.firstname, c.lastname, c.title, c.company_name, 
   c.address, c.company_email, c.areacode, c.phone1, c.phone2, q.region_name, 
   j.country, i.industry 
ORDER BY c.company_name";

Now, as it should be written (and I'm sure Sxooter agrees)

"SELECT c.company_id, q.region_name, c.firstname, c.lastname, 
   c.title, c.company_name, c.address, c.company_email, c.areacode, 
   c.phone1, c.phone2, j.country, i.industry 
FROM smi_companies As c 
   INNER JOIN jupiter_countries As j ON (c.country_id=j.country_id)
   INNER JOIN smi_employer_industries As i ON (c.industry=i.industry_id)
   INNER JOIN smi_company_regions As r ON (c.company_id=r.company_id)
   INNER JOIN smi_regions As q ON (r.region_id = q.region_id)
WHERE c.company_name like '%$q%' 
GROUP BY c.company_id, q.region_name, j.country, i.industry 
ORDER BY c.company_name";

Much easier to take in at a glance. Of course, the simplified group by is mysql specific.

Now nowhere in all of that is there anything to indicate the city? Unless it is the c.address field. So I'm just going to use that in the count query you need to setup the pagination

"SELECT company_name, COUNT(address) AS city_count 
FROM smi_companies
WHERE company_name like '%$q%'
GROUP BY company_name, address
ORDER BY company_name";

That will return each matching company once, with the count of different addresses for each company, hence the count of cities. So the num-rows will give you your result count to print to the user and your total results for the pagination. You store that in a session var in the usual way, along with the query string I hope.

If you also save the city_counts in a session array you can use them to also control the pagination so that you do not get orphan cities at the top of a page. I'll leave that to you to work out how you do that.

[ptaylor2005]

Thanks for the reply - much appreciated.

Sorry I should have clarified; the cities are actually identified by the region_name field.

I have pasted in your modified query, and this is what I have also included to get the subset of cities/regions that are linked to each company, as well as the count of the parent records:

$sqlnum = mysql_query("Select c.company_id From smi_companies
Where c.company_name like '%$q%'");
$num_records = mysql_num_rows($sqlnum);


$sql = mysql_query("SELECT c.company_id, q.region_name, c.firstname, c.lastname, c.title, c.company_name, c.address, c.company_email, c.areacode, c.phone1, c.phone2, j.country, i.industry 
                        FROM smi_companies As c 
                        INNER JOIN jupiter_countries As j ON (c.country_id=j.country_id) 
                        INNER JOIN smi_employer_industries As i ON (c.industry=i.industry_id) 
                        INNER JOIN smi_company_regions As r ON (c.company_id=r.company_id) 
                        INNER JOIN smi_regions As q ON (r.region_id = q.region_id) 
                        WHERE c.company_name like '%$q%' 
                        GROUP BY c.company_id, q.region_name, j.country, i.industry ORDER BY c.company_name"); 

$num_rows = mysql_num_rows($sql);
 for($j=0;$j<$num_rows;$j++) {
$row = mysql_fetch_array($sql);
$results[] = array($row[0],$row[1]);
}

Then further down, where the html is written out, I have this:

    $a =& new Paginator_html($_GET['page'],$num_records);
	$a->set_Links(18);
	$a->set_Limit(10); 

 $limit1 = $a->getRange1();  
 //gets number of items displayed on page.
 $limit2 = $a->getRange2(); 
 $priorlist = "";

 for($i=$limit1; $i < $limit1 + $limit2; $i++)
 {
 $comp_id = mysql_result($sql,$i,"company_id");
             $company = mysql_result($sql,$i,"company_name");
 $address = mysql_result($sql,$i,"address");
             $title = mysql_result($sql,$i,"title");
              $fname = mysql_result($sql,$i,"firstname");
 $lname = mysql_result($sql,$i,"lastname");
 $email = mysql_result($sql,$i,"company_email");
 $ac = mysql_result($sql,$i,"areacode");
 $phone1 = mysql_result($sql,$i,"phone1");
 $phone2 = mysql_result($sql,$i,"phone2");
             $country = mysql_result($sql,$i,"country");
 $industry = mysql_result($sql,$i,"industry");
 $phone = $ac." ".$phone1." ".$phone2;

            if ($comp_id <> $priorlist){
 echo "<tr>\n";
            // etc...

     echo "<td width=\"97\" style=\"border-bottom: 1px solid #478DF8\">\n";
     for($k=0;$k<count($results);$k++) {	
     if($results[$k][0] == $comp_id) echo $results[$k][1]."<br />\n";
       }

    echo "</td>\n";
// etc..

 $priorlist = $comp_id;
   }

So I will try what you suggested, modifying it for the schema I have, and see how it goes.

Many thanks,
Paul

[ptaylor2005]

I managed to get it working... I used some of the theory put forth here, so thank you for that. It helped me see the light.

This is how it turned out:

Two queries - first one:

$sql = mysql_query("SELECT c.company_id, c.firstname, c.lastname, c.title, c.company_name, c.address, c.company_email, c.areacode, c.phone1, c.phone2, j.country, i.industry 

FROM smi_companies As c 
                    INNER JOIN jupiter_countries As j ON (c.country_id=j.country_id) 
                    INNER JOIN smi_employer_industries As i ON (c.industry=i.industry_id) 
                    WHERE c.company_name like '%$q%' 
                    GROUP BY c.company_id, j.country, i.industry ORDER BY c.company_name"); 
$num_rows = mysql_num_rows($sql);

Second one to pull the cities/regions for each company:

$sql_reg = mysql_query("Select c.company_id, r.region_name FROM smi_company_regions As c, smi_companies As d, smi_regions As r Where d.company_name like '%$q%' And c.company_id = d.company_id And r.region_id = c.region_id");
$num_records = mysql_num_rows($sql_reg);

Then there is the php code to write out the <table> and parent results, then this is inserted in the appropriate place to write out the cities:

 echo "<td width=\"97\" style=\"border-bottom: 1px solid #478DF8\">\n";
	for($k=0;$k<$num_records;$k++){
	 $companyid = mysql_result($sql_reg,$k,"company_id");
	 $reg_name = mysql_result($sql_reg,$k,"region_name");
	 if($companyid==$comp_id){
	 echo $reg_name."<br />\n";
	  }
	 }
 echo "</td>\n";
//etc...

It seems to work nicely; I have compared the results to the database and it looks good.
The pagination works using the $num_rows from the first query.

Thanks again for your help - much appreciated!
Paul