Page 1 of 1

Simple (hopefully!) PHP/MySQL syntax query

Posted: Mon May 19, 2008 5:52 am
by Eric Praline
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.

Code: Select all

$bookQuery = "SELECT * FROM copyRecord WHERE Title LIKE '%$searchkey%' ORDER By Title, CopyID ASC";
$result = mysql_query($bookQuery);
^^ is the code to perform the search on the copyRecord table (this example searching on the Title field)

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>";
}
}
 
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...? :D

Re: Simple (hopefully!) PHP/MySQL syntax query

Posted: Mon May 19, 2008 1:20 pm
by califdon
I'll take a stab at it. To summarize your schema:

Code: Select all

copyRecord:
   CopyID
   Title
   Status
 
loanRecord:
   LoanID
   CopyID
   RegistrationNo
   Returned
 
borrowerRecord:
   BorrowerID
   RegistrationNo
   FirstName
   LastName
Right? And you want to:

Code: Select all

Search either by Title (multiple results) or CopyID (unique result).
 
In both cases, display results, and if Status='On Loan', display name of borrower.
I *think* you could do it with one query, although I haven't tried this:

Code: Select all

SELECT loanRecord.RegistrationNo,Title,CopyID,Status,FirstName,LastName 
  FROM copyRecord 
  LEFT JOIN loanRecord ON loanRecord.CopyID = copyRecord.CopyID 
  LEFT JOIN borrowerRecord ON borrowerRecord.RegistrationNo = loanRecord.RegistrationNo 
  WHERE Title LIKE '%$searchkey%' OR copyRecord.CopyID = '$searchkey' 
  ORDER BY Title, CopyID ASC

Re: Simple (hopefully!) PHP/MySQL syntax query

Posted: Wed May 21, 2008 4:08 am
by Eric Praline
Brilliant! That worked a treat - although I obviously had to add the correct table names to the fields in the SELECT query.

Although after a little bit of testing it appears that it still displays the name of the last borrower of a book, even if it is not on loan anymore.
However, I cured this with a simple PHP switch command to only show the FirstName and LastName if the Status is 'On Loan' or 'Loan Overdue'. Maybe not the most elegant solution, but it works!

Cheers dude, that's saved me a big headache! :D

Re: Simple (hopefully!) PHP/MySQL syntax query

Posted: Wed May 21, 2008 3:22 pm
by califdon
Glad that worked for you. Side note: you only have to add the table names IF there are fields with the same name in different tables. MySQL is smart enough to find the fields if the names occur in only one of the tables in your query.