Simple (hopefully!) PHP/MySQL syntax query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

Simple (hopefully!) PHP/MySQL syntax query

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

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

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
Post Reply