Simple (hopefully!) PHP/MySQL syntax query
Posted: Mon May 19, 2008 5:52 am
Hey all
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.
^^ is the code to perform the search on the copyRecord table (this example searching on the Title field)
Now, I've tried the following...
However, this query then only displays the details of the books out on loan (together with borrower details), when I want to see ALL books from the original query, with details of the borrower against the books on loan.
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...?
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...?