[PHP-DB] Problem with BIG MySQL query
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-DB] Problem with BIG MySQL query
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]
Re: [PHP-DB] Problem with BIG MySQL query
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 +, 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]
