NYCPHP Meetup

[Joseph Crawford]

Guys,

I have an sql query that uses one table to join another, the tables
are ci2_bizopps and ci2_companies, each company could have many
entries in the bizopps table and what i need to do is get each company
name only once.  However since we are searching the bizopps table it
returns a result for each bizopp found, this makes for duplicate
company names added to the array.  The following code works if i
remove the ORDER BY statement in the SQL query, however for some
reason with it in there it breaks the code.

$result = $db->query("
	SELECT 
		b.id as bizopp_id, b.contract_name, 
		c.id as company_id, c.name as company 
	FROM ci2_bizopps b 
	INNER JOIN ci2_companies c ON b.company_id=c.id  
	WHERE b.contract_type=".$type." 
	ORDER BY c.name ASC"
);
$results = $db->fetchAll($result);

$data = "<table border=0 cellpadding=0 cellspacing=0 width=100%
align='center'>\n";
$arr = array();
foreach($results as $result) {
	if(!array_search($result['company_id'], $arr)) {
		$data .= "<tr>\n";
		$data .= "<TD class='tabledata'><a
href='company.php?id=".$result['company_id']."'>".$result['company']."</a></td>\n";
		$data .= "</tr>\n";
		$arr[] = $result['company_id'];
	}
}
$data .= "</table>";
return $data;

as you can see all i did was for each record found, store the company
id into another array, and also use array_search to search the array
to see if that company_id has already been used.  If it has been used,
do nothing otherwise display the data for that company..  I have
printed out the value of company_id and $arr for each item and with
the order by statement, it says the company id is 2 but 2 is also in
the arr 3 times, so i am not sure why array_search is not finding it
in the array and just skipping it.  I guess one way of accomplishing
this is to not use the ORDER BY statement but create yet another
array, add the companies to that which will be displayed and then
sorting the array and then displaying but i think the order by would
be much easier to use if it can be done.

Here are the results shown with the ORDER BY in place
IPRO
PRO NJ Test Data
Romy's Widgets
The British Medicine Group
IPRO
IPRO

And here are the results without the ORDER BY in place
The British Medicine Group
Romy's Widgets
The British Medicine Group
IPRO
PRO NJ Test Data

This is really starting to get to me after working on this for a while
the other day to finally get it working and today just to break it
with an order by statement ;D

Anyone that can help i would greatly appreciate it.

-- 
Joseph Crawford Jr.
Codebowl Solutions
codebowl at gmail.com

For a GMail account
contact me OFF-LIST