Single addressbook with different meanings

PHP
1

Can anyone help me optimize my code here?

Surely there is a way of doing this without doing hundreds of queries?

Thanks

Andrew



<?php

$NodeTitles = mysql_query("SELECT * FROM faults, users WHERE faults.openedby = users.userid ".implode(" ", $whereclause)." ORDER by dateopened DESC, timeopened DESC") or die (mysql_error()); 
while ($row = mysql_fetch_assoc($NodeTitles)) { 

	$faultid = $row['faultid'];
	$clientid = $row['client'];
	$referrerid = $row['referredby'];
	$fault = $row['fault'];
	$openedby = $row['fullname'];
	$dateopened = $row['dateopened'];
	$timeopened = $row['timeopened'];

		//RETRIEVE CLIENT INFO
		$client = mysql_query("SELECT * FROM addressbook2 WHERE nameid = '$clientid'") or die (mysql_error()); 
		$row = mysql_fetch_assoc($client);
		
		$clientname = $row['namecompany'];
		$clientbranch = $row['namecontactname'];

		//RETREIVE REFERRER INFO
		$referrer = mysql_query("SELECT * FROM addressbook2 WHERE nameid = '$referrerid'") or die (mysql_error()); 
		$row = mysql_fetch_assoc($referrer);
		
		$refferername = $row['namecompany'];

?>

				<tr id="entryRow2358" onMouseOver="this.style.cursor='pointer'" onclick="window.location.href='job.php?faultid=<?php echo $row['faultid'];?>';">
					<td class="n" colspan="1"><div title=""><?php echo $faultid;?></div></td>
					<td class="" colspan="1"><div title="Company"><?php echo $clientname;?></div></td>
					<td class="" colspan="1"><div title="Branch"><?php echo $clientbranch;?></div></td>
					<td class="" colspan="1"><div title="Referrer"><?php echo $refferername;?></div></td>
					<td class="" colspan="1"><div title="Fault"><?php echo $fault;?></div></td>
					<td class="" colspan="1"><div title="Opened By"><?php echo $openedby;?></div></td>
					<td class="" colspan="1"><div title="Date Created"><b><?php $date = date_create($dateopened); echo date_format($date, 'j F Y');?></b> <span><?php echo $timeopened;?></span></div></td>
				</tr>
				
<?php

}

?>
2

Join the two addressbook tables in your first query as well.

3

I could do that if it was only one name from the addressbook but since I’m extracting two different names from the addressbook what sort of query would i run? Sorry i dont follow - i only have ONE addressbook

thanks

Andrew

4

Join the same table twice, using aliasses.

5

are you able to write this statement for me? head is about to explode thinking about this, also how will i output the samecolumn twice using php?

6

Don’t use the *, but specify all columns you want to extract. And use aliasses for the double column names as well.


SELECT 
    faultid
  , fault
  , client
  , referredby
  , fullname
  , dateopened
  , timeopened
  , a1.namecompany AS clientnamecompany
  , a1.namecontactname AS clientnamecontactname
  , a2.namecompany AS referrernamecompany
FROM faults
INNER JOIN users 
ON faults.openedby = users.userid 
INNER JOIN addressbook2 AS a1
ON a1.nameid = client
INNER JOIN addressbook2 AS a2
ON a2.nameid = referredby
WHERE ".implode(" ", $whereclause)." 
ORDER BY 
    dateopened DESC
  , timeopened DESC
7

Thanks for all your help, managed to get it going using this code

<?php

$NodeTitles = mysql_query("

SELECT 

ab1.company 		AS 	clientname, 
ab1.branch			AS	clientbranch, 
ab2.company 		AS 	referrername, 
f.*,
u.*

FROM faults AS f

LEFT JOIN users 		AS u		ON f.openedby = u.userid 
LEFT JOIN addressbook 	AS ab1		ON ab1.contractid = f.client 
LEFT JOIN addressbook 	AS ab2		ON ab2.contractid = f.referredby 

ORDER by dateopened DESC, timeopened DESC

") or die (mysql_error()); 

while ($row = mysql_fetch_assoc($NodeTitles)) { 

	$faultid = $row['faultid'];
    $clientname = $row['clientname'];
	$clientbranch = $row['clientbranch'];
	$referrername = $row['referrername'];
	$fault = $row['fault'];
	$fullname = $row['fullname'];
	$dateopened = $row['dateopened'];
	$timeopened = $row['timeopened'];
?>

				<tr id="entryRow2358" onMouseOver="this.style.cursor='pointer'" onclick="window.location.href='job.php?faultid=<?php echo $row['faultid'];?>';">
					<td class="n" colspan="1"><div title=""><?php echo $faultid;?></div></td>
					<td class="" colspan="1"><div title="Company"><?php echo $clientname;?></div></td>
					<td class="" colspan="1"><div title="Branch"><?php echo $clientbranch;?></div></td>
					<td class="" colspan="1"><div title="Referrer"><?php echo $referrername;?></div></td>
					<td class="" colspan="1"><div title="Fault"><?php echo $fault;?></div></td>
					<td class="" colspan="1"><div title="Opened By"><?php echo $fullname;?></div></td>
					<td class="" colspan="1"><div title="Date Created"><b><?php $date = date_create($dateopened); echo date_format($date, 'j F Y');?></b> <span><?php echo $timeopened;?></span></div></td>
				</tr>

<?php
} 
?>