George
Have you tried changing " and transactions.ExtractID=bib_extract.E_ID "
to
" and bib_extract.E_ID = transactions.ExtractID "? You have the join, but
it's reversed.
The transactions table is the primary determinant of the query,
"Transactions.Course_ID = '210' " , thus fields returned from it provide
the join fields for the other tables. As written, the query requests
records where the transactions.ExtractID matches whatever record the record
pointer is sitting on in the bib_extract table, which could easily be end
of file or beginning of file.
I'd be inclined to rewrite the WHERE condition this way ....
where bib_extract.E_ID = transactions.ExtractID and
scanrates.rate = transactions.finrate and
transactions.CourseID = '210'
order by etc...
but that's just a personal quirk, I like to have the most specific criteria
at the end of the WHERE.
To help with diagnosis you can add this to the mysql_query():
or die( mysql_errno() ." : ". Mysql_error() )
which may just return something helpful like "Error in select near blah .."
The other requirement is that the joined fields must be of same type and
length.
Regards - Miles
At 12:14 PM 1/17/2002 +0000, George Pitcher wrote:
>If I perform the following query in MySQL_Front, It works but I get no
>records returned (there should be 10 records).
>
>If I use it with php on my web page, I get an error: "Supplied argument is
>not a valid MySQL result resource in [script name]" which is probably
>because of the zero return.
>
>The script works fine without the 'scanrates'**' references but not with
>them. Should I be putting a JOIN instruction in here somwehere to make it
>work properly?
>
>George
>
>
>The select query:
>
>Select bib_extract.E_Author, bib_extract.E_Title, bib_extract.Vol,
>bib_extract.Issue, bib_extract.Page_End_1, bib_extract.Page_End_2,
>bib_extract.Page_Start_1, bib_extract.Page_Start_2, bib_extract.PDF_Stored,
>scanrates.price_per_unit,
>scanrates.pub_digi_fee,
>transactions.RecID, transactions.CourseID, transactions.SourceID,
>transactions.ExtractID, transactions.CLClearanceCLA,
>transactions.CLClearanceAlt, transactions.CLClearanceRoute,
>transactions.CLClearanceTypeCLA, transactions.CLClearanceTypeAlt,
>transactions.CLCourseEndAlt, transactions.CLCourseStartAlt,
>transactions.CLItemOwnedbyHEI, transactions.CLReqNotes,
>transactions.CLReqType, transactions.CLSubscribe, transactions.CLCFeeWaived,
>transactions.FinBLDSCCopyfee, transactions.FinCfeeCLA,
>transactions.FinCfeealt, transactions.FinCurrencyCLA,
>transactions.FinCurrencyalt, transactions.FinExchangerate,
>transactions.FinExchangeratealt, transactions.FinFeetype,
>transactions.FinFeetypealt, transactions.FinFlatfee,
>transactions.FinFlatfeealt, transactions.FinPaperItemCost,
>transactions.FinPriceperpage, transactions.FinPubVAT,
>transactions.FinPubDigiFee, transactions.FinPubDigiFeealt,
>transactions.FinRate, transactions.FinRateType,
>transactions.FinSupplementalFee, transactions.HEI_ID,
>transactions.HEICustNotes, transactions.HEIFailureReason,
>transactions.PAltsenttopub, transactions.PBLDSCinformed,
>transactions.PCleardate, transactions.PCleardatealt, transactions.PComplete,
>transactions.PDocfromBureau, transactions.PDocreceived,
>transactions.PDocsupplied, transactions.PDoctoBureau,
>transactions.PHEIaccepts, transactions.PHEIAction,
>transactions.PHEIInformedofClearance, transactions.PHEIinformedofcompletion,
>transactions.PHEIRemindedofDocReq, transactions.POriginalreceivedfromHEI,
>transactions.PRemindersent, transactions.PRequestsenttoCLA,
>transactions.PRequestsenttoPub, transactions.PRequestsubmitted,
>transactions.PRqsforOriginaltoHEI, transactions.PTUComplete,
>transactions.RecCreatedby, transactions.RecCreatedon,
>transactions.TUResupply, transactions.TUStoragepermitted,
>transactions.WWHeroninfo1, transactions.WWPrevClear,
>transactions.WWPrevClearinfo, transactions.PDocmounted,
>transactions.Pdownload from
>bib_extract,scanrates,transactions where
>(transactions.CourseID = '210'
>and transactions.ExtractID=bib_extract.E_ID
>and scanrates.rate=transactions.finrate
>) order by bib_extract.E_Author, bib_extract.E_Title
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.314 / Virus Database: 175 - Release Date: 11/01/02
>
>
>_________________________________________________________
>Do You Yahoo!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
