This is probably a fairly simple query, but I can't figure out to to get it to work as intended...
I have a database with 3 tables - copyRecord, loanRecord and borrowerRecord.
For simplicity sake, say the fields for each table are:
copyRecord: CopyID (unique, autonumber), Title, Status
loanRecord: LoanID (unique, autonumber), CopyID, RegistrationNo
borrowerRecord: BorrowerID (unique, autonumber), RegistrationNo (unique), FirstName, LastName
Now the query I'm trying to get to work is thus...
In a form the user can search the copyRecord table on the Title field (many results could be returned for a Title containing the word 'whatever'), or on the CopyID field (in which case there will be only 1 result returned). From the results page, the Status shows as either 'On Shelf' or 'On Loan'.
Now if the Status is 'On Loan' I want to display the name of the borrower - which is where I can't quite get it to work.
Code: Select all
$bookQuery = "SELECT * FROM copyRecord WHERE Title LIKE '%$searchkey%' ORDER By Title, CopyID ASC";
$result = mysql_query($bookQuery);Now, I've tried the following...
Code: Select all
while ($resultRow = mysql_fetch_array($result)) {
$CopyID = $resultRow['CopyID'];
$Status = $resultRow['Status'];
/* FIND BORROWER DETAILS OF BOOKS ON LOAN */
$regQuery = "SELECT loanRecord.RegistrationNo, borrowerRecord.FirstName, borrowerRecord.LastName
FROM loanRecord LEFT JOIN (borrowerRecord) ON (loanRecord.RegistrationNo = borrowerRecord.RegistrationNo)
WHERE loanRecord.CopyID = '$CopyID' AND loanRecord.Returned = 'N'";
$regResult = mysql_query($regQuery);
while ($resultRowReg = mysql_fetch_array($regResult)) {
$First = $resultRowReg['FirstName'];
$Last = $resultRowReg['LastName'];
echo "<tr><td>$CopyID</td><td>$Title</td><td>$Status</td><td>".$First.$Last"</td></tr>";
}
}
I'm getting a bit confused about the correct query to use, or if I need to create a temporary table or something? Is there anyone who can give me a pointer or two, please...?