Single addressbook with different meanings...

[amof]

Hi All

 

Sorry my first post is a help one...

 

I'm developing a small fault logging application I need a little help with.

 

This is my code at the moment

 

Client Table Columns

contractid, company, branch, addressline1, addressline2, addressline3, addressline4, postcode, contactname, telephone, email

 

Addressbook2 Table Columns

 

nameid, namecompany, namecontactname, nameaddressline1, nameaddressline2, nameaddressline3, nameaddressline4, namepostcode, nametelephone1, nametelephone2, nameemail,

 

<?php

$NodeTitles = mysql_query("SELECT * FROM faults, users, clients, addressbook2 WHERE faults.client = clients.contractid AND faults.referredby = addressbook2.nameid AND faults.openedby = users.userid ORDER by dateopened DESC, timeopened DESC") or die (mysql_error()); 

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

?>

			<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 $row['faultid'];?></div></td>
				<td class="" colspan="1"><div title="Company"><?php echo $row['clientcompany'];?></div></td>
				<td class="" colspan="1"><div title="Referrer"><?php echo $row['referrercompany'];?></div></td>
				<td class="" colspan="1"><div title="Branch"><?php echo $row['clientbranch'];?></div></td>
				<td class="" colspan="1"><div title="Fault"><?php echo $row['fault'];?></div></td>
				<td class="" colspan="1"><div title="Opened By"><?php echo $row['fullname'];?></div></td>
				<td class="" colspan="1"><div title="Date Created"><b><?php $date = date_create($row['dateopened']); echo date_format($date, 'j F Y');?></b> <span><?php echo $row['timeopened'];?></span></div></td>
			</tr>

<?php
} 
?>

 

QUESTION TIME

 

I want to make this much cleaner and tidier (and also more future proof) by removing  the clients table so that I only have one 'addressbook' (this will contain clients, referrers, engineers ... and any other correspondents)

 

Naturally I will have a column in the address book to tell me what type of correspondent the name is. ie. 1 = Client, 2 = Referrer, 3 = Engineer.

 

So.......will I have to do many MYSQL queries within a while statement or will I be complicating the entire process by doing this? Can I write a single SQL statement to do this?

 

Any advise or help with my code would be great!

 

Thanks

 

Andrew

[amof]

Okay since no-one has replied I'm guessing no-one understood my question. So I've wrote the code in the only way I know how... its clearly not optomised and not very clean, there must be a better way to avoid doing so many queries...

 

<?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

}

?>