Re: [PHP-DB] Unexplained MySQL Error..
Thanks for your attention to this matter Christopher, Christopher Ostmo wrote: > If you do not list a table in the UPDATE parameter, you cannot > reference that table from anywhere else within your query. The same is > true of SELECT statements - you cannot reference a table that is not > listed in the FROM clause. The same is true of UPDATE and many > other commands. There's a table selection parameter for most SQL > queries (at least those that actually query), and you cannot point to > tables later in your query that are not listed. Good explaination, thanks! But can you UPDATE or INSERT into multiple tables using the same query? You can SELECT multiple fields FROM multiple tables, but I ran into an error when I tried to use UPDATE with this same logic. > In other words, you cannot tell it to "update table1 where table2 = ..." If > you haven't told it to do an action (update in this case) on table2, it's > going to wonder what the heck those tables have to do with each other > and exit with an error. This is an oversimplification of what "really" > happens, but this message is way too short to go into that in any detail. You've given lots of detail, and I just want the script to work, rather than learning what "really" happens. :) > You should either pour through the documentation section at > mysql.com or get the book "MySQL" by Paul DuBois. The docs may > be difficult to get through for newbies, but they are precise and contain a > lot of info. The book on the otherhand starts at the beginning and holds > your hand into the more difficult aspects. The book even goes into > details about how MySQL works that makes it so that you can't do what > you are attempting above. I'll look for the book. I've found a number of MySQL Tutorials online that have been useful. However I keep running into these gaps in my understanding. I will look for Paul's book. Likely tomorrow. Thanks for the led. > Open Source Applications: > http://open.AppIdeas.com/ I think I've email you a about your calendar script. Thanks again for your help! Mike -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- 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] Unexplained MySQL Error..
Mike Gifford pressed the little lettered thingies in this order... > Hi Chris, > > I think you are onto something here.. However, this has produced another > problem. > > Boget, Chris wrote: > > > > UPDATE > > > WLPbib, WLPpublisher,WLPprofile,WLPbib2profile > > I think you can only update one table at a time... > > I could be wrong, though... > > I seperated this out into 3 update commands. However, the WHERE statement > is hanging now: > > > Database error: > Invalid SQL: > UPDATE > WLPpublisher > SET > WLPpublisher.publisherID = '' > WHERE > WLPbib.bibID = '32' AND > WLPbib.publisherID = WLPpublisher.publisherID > MySQL Error: 1109 (Unknown table 'WLPbib' in where clause) > Please contact the webmaster and report the exact error message. > Session halted. > > > I can understand that MySQL is getting a bit upset because I am referring > to fields like WLPbib.bibID when the table hasn't been specifically > referenced in the query. > > If you do not list a table in the UPDATE parameter, you cannot reference that table from anywhere else within your query. The same is true of SELECT statements - you cannot reference a table that is not listed in the FROM clause. The same is true of UPDATE and many other commands. There's a table selection parameter for most SQL queries (at least those that actually query), and you cannot point to tables later in your query that are not listed. In other words, you cannot tell it to "update table1 where table2 = ..." If you haven't told it to do an action (update in this case) on table2, it's going to wonder what the heck those tables have to do with each other and exit with an error. This is an oversimplification of what "really" happens, but this message is way too short to go into that in any detail. You should either pour through the documentation section at mysql.com or get the book "MySQL" by Paul DuBois. The docs may be difficult to get through for newbies, but they are precise and contain a lot of info. The book on the otherhand starts at the beginning and holds your hand into the more difficult aspects. The book even goes into details about how MySQL works that makes it so that you can't do what you are attempting above. Christopher Ostmo a.k.a. [EMAIL PROTECTED] AppIdeas.com Innovative Application Ideas Meeting cutting edge dynamic web site needs since the dawn of Internet time (1995) Business Applications: http://www.AppIdeas.com/ Open Source Applications: http://open.AppIdeas.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] Unexplained MySQL Error..
Hi Chris, I think you are onto something here.. However, this has produced another problem. Boget, Chris wrote: > > UPDATE > > WLPbib, WLPpublisher,WLPprofile,WLPbib2profile > I think you can only update one table at a time... > I could be wrong, though... I seperated this out into 3 update commands. However, the WHERE statement is hanging now: Database error: Invalid SQL: UPDATE WLPpublisher SET WLPpublisher.publisherID = '' WHERE WLPbib.bibID = '32' AND WLPbib.publisherID = WLPpublisher.publisherID MySQL Error: 1109 (Unknown table 'WLPbib' in where clause) Please contact the webmaster and report the exact error message. Session halted. I can understand that MySQL is getting a bit upset because I am referring to fields like WLPbib.bibID when the table hasn't been specifically referenced in the query. So how how do I work bibID (which isn't defined in WLPpublisher) into the following query: $q = " UPDATE WLPpublisher SET WLPpublisher.publisherID = '$ary[publisherID]' WHERE WLPbib.bibID = '$ary[bibID]' AND WLPbib.publisherID = WLPpublisher.publisherID"; I'm going to run into a similar problem in the next query: $q = " UPDATE WLPprofile SET WLPprofile.firstName = '$ary[firstName]', WLPprofile.middleName = '$ary[middleName]', WLPprofile.lastName = '$ary[lastName]' WHERE bibID = '$ary[bibID]' AND WLPbib2profile.bibID = WLPbib.bibID AND WLPbib2profile.profileID = WLPbib.profileID"; so any sugestions to get over this next hurdle would be great. Mike -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- 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] Unexplained MySQL Error..
Chris, My first sentance was in response to your "I think you can only update one table at a time..." statement, not "I could be wrong, though..." Supported by the example I gave. Sorry for the confusion... My 2 ¢ John Pickett http://www.bvstudios.com/ Co-Author: Inside Dreamweaver 4 http://www.amazon.com/exec/obidos/ASIN/0735710848/xtremist - Original Message - From: "Boget, Chris" <[EMAIL PROTECTED]> To: "'John Pickett'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 2:35 PM Subject: RE: [PHP-DB] Unexplained MySQL Error.. > > Chris: The MySQL documentation would make you think so... > > I don't think so > > > UPDATE [LOW_PRIORITY] [IGNORE] tbl_name > > Note there isn't a tbl_name [, tbl_name2, ...]. It's just tbl_name... > > Right... that means just *one* table. Your query is updating > 4 tables. > > Chris > -- 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] Unexplained MySQL Error..
> Chris: The MySQL documentation would make you think so... I don't think so > UPDATE [LOW_PRIORITY] [IGNORE] tbl_name > Note there isn't a tbl_name [, tbl_name2, ...]. It's just tbl_name... Right... that means just *one* table. Your query is updating 4 tables. Chris
Re: [PHP-DB] Unexplained MySQL Error..
Chris: The MySQL documentation would make you think so... UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1, [col_name2=expr2, ...] [WHERE where_definition] [LIMIT #] Note there isn't a tbl_name [, tbl_name2, ...]. It's just tbl_name... Cheers! My 2 ¢ John Pickett http://www.bvstudios.com/ Co-Author: Inside Dreamweaver 4 http://www.amazon.com/exec/obidos/ASIN/0735710848/xtremist - Original Message - From: "Boget, Chris" <[EMAIL PROTECTED]> To: "'Mike Gifford'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 2:25 PM Subject: RE: [PHP-DB] Unexplained MySQL Error.. > > UPDATE > > WLPbib, WLPpublisher,WLPprofile,WLPbib2profile > > I think you can only update one table at a time... > I could be wrong, though... > > Chris > -- 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] Unexplained MySQL Error..
> UPDATE > WLPbib, WLPpublisher,WLPprofile,WLPbib2profile I think you can only update one table at a time... I could be wrong, though... Chris
[PHP-DB] Unexplained MySQL Error..
Hello, I'm hoping that this error is clear to someone who is familiar with MySQL errors (cause it sure doesn't make sense to me): Database error: Invalid SQL: UPDATE WLPbib, WLPpublisher,WLPprofile,WLPbib2profile SET WLPprofile.firstName = 'asdf', WLPprofile.middleName = '', WLPprofile.lastName = 'asdfasdf', WLPbib.title = 'asdf', WLPbib.publisherID = '', WLPpublisher.publisherID = '' WHERE bibID = '32' AND WLPbib2profile.bibID = WLPbib.bibID AND WLPbib2profile.profileID = WLPbib.profileID AND WLPbib.publisherID = WLPbib.publisherID MySQL Error: 1064 (You have an error in your SQL syntax near ' WLPpublisher,WLPprofile,WLPbib2profile SET WLPprofile.firstName = ' at line 3) Please contact the webmaster and report the exact error message. Session halted. Which in the code looks like: $q = " UPDATE WLPbib, WLPpublisher,WLPprofile,WLPbib2profile SET WLPprofile.firstName = '$ary[firstName]', WLPprofile.middleName = '$ary[middleName]', WLPprofile.lastName = '$ary[lastName]', WLPbib.title = '$ary[title]', WLPbib.publisherID = '$ary[publisherID]', WLPpublisher.publisherID = '$ary[publisherID]' WHERE bibID = '$ary[bibID]' AND WLPbib2profile.bibID = WLPbib.bibID AND WLPbib2profile.profileID = WLPbib.profileID AND WLPbib.publisherID = WLPbib.publisherID"; And in the DB looks like: CREATE TABLE WLPbib ( bibID mediumint(9) NOT NULL, languageID varchar(5), publisherID mediumint(9), categoryID smallint(6), type varchar(55), title varchar(255), pageNumber varchar(55), source_bibID varchar(55), publicationDate varchar(5), dateAdded date, publishedLanguage varchar(5), URL varchar(100), status varchar(5), PRIMARY KEY (bibID) ); CREATE TABLE WLPpublisher ( publisherID mediumint(9) NOT NULL, languageID varchar(5), addressID mediumint(9), name varchar(255), alias4publisherID mediumint(9), PRIMARY KEY (publisherID) ); CREATE TABLE WLPprofile ( profileID mediumint(9) NOT NULL, languageID varchar(5), addressID mediumint(9), firstName varchar(255), middleName varchar(255), lastName varchar(255), organization varchar(255), nationality varchar(255), professionID smallint(3), bio text, status varchar(5), PRIMARY KEY (profileID) ); CREATE TABLE WLPbib2profile ( bibID mediumint(9), profileID mediumint(9) ); What the heck am I missing? Any help would be appreciated. Mike ps. I fixed the duplication error I was getting previously (and brought to the list at that point) with the following: $q = " SELECT WLPbib.bibID, WLPbib.title, WLPbib.publicationDate, WLPpublisher.name, WLPaddress.city, WLPaddress.state, WLPcountry.name, WLPprofile.firstName, WLPprofile.middleName, WLPprofile.lastName, WLPprofile.organization "; $q .= " FROM WLPbib LEFT JOIN WLPpublisher USING(publisherID), WLPaddress LEFT JOIN WLPcountry USING(countryID), WLPprofile LEFT JOIN WLPbib2profile USING(profileID) "; $q .= " WHERE WLPpublisher.addressID = WLPaddress.addressID AND WLPbib2profile.bibID = WLPbib.bibID "; Seems to be working so far. -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- 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]
