[PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello I've got the following SQL Query, which consistently pulls up only 3 out of 4 months from the database: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC; This results in: Month: November Number of Invoices: 17 Month: December Number of Invoices: 22 Month: January Number of Invoices: 15 But when I do change the order of the query from ASC to DESC like this: $sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled) ORDER BY DateBilled DESC; I get: Month: December Number of Invoices: 22 Month: November Number of Invoices: 17 Month: October Number of Invoices: 21 (I gained October and Lost January) The date format in the DB is like this: 2001-12-05 I've tried a whole stack of variations on the above query, but I still seem to be coming up one short. Any idea why I'm not getting a display of all of the months? Thanks. Mike -- Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca Supporting progressive organizations in online campaigns and tools. Feature: Women's Learning Partnership http://learningpartnership.org Truth is that which confirms what we already believe. Northrop Frye -- 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] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello,
Thanks for your quick reply.. I'm trying to improve the stats feature
for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net)
On Fri, 2002-01-11 at 05:24, DL Neil wrote:
Have you posted all of the relevant code - for example, how the 'result' is limited
to three month's worth of
data???
I didn't provide all of the code in the initial response as it was using
a wrapper so I didn't know how relevant it would be.. However, your
note made me realize that I could rewrite the code without the
wrapper.. It still worked the same way. The code stands as:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled DESC;
$monthly_result = mysql_query($sql, $db);
$monthly_row = DBfetch_array($monthly_result);
while($monthly_row = mysql_fetch_array($monthly_result)) {
++$i;
$InvMonth[$i] = $monthly_row[BilledMonth];
$InvCount[$i] = $monthly_row[count];
echo strongMonth: . date (F,
mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number
of Invoices: . $InvCount[$i] . /strongbr;
}
RESULTS:
Month: December Number of Invoices: 22
Month: November Number of Invoices: 17
Month: October Number of Invoices: 21
Have you extracted the SQL from the PHP and applied it directly to the command line
or used it in a MySQL
Management package? Was the result any different?
Also a damn good idea (I haven't had root access to MySQL until
recently, hadn't thought of that either)
mysql SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled DESC;
+---+-++
| count | BilledMonth | BilledYear |
+---+-++
|15 | 1 | 2002 |
|22 | 12 | 2001 |
|17 | 11 | 2001 |
|21 | 10 | 2001 |
+---+-++
4 rows in set (0.00 sec)
Ok.. So the problem seems to be with my code..
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;
$monthly_result = mysql_query($sql, $db);
$monthly_row = DBfetch_array($monthly_result);
$i=0;
while($monthly_row = mysql_fetch_array($monthly_result)) {
$InvMonth[$i] = $monthly_row[BilledMonth];
$InvCount[$i] = $monthly_row[count];
echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002))
. !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] .
/strongbr;
++$i;
}
RESULTS:
Month: November Number of Invoices: 17
Month: December Number of Invoices: 22
Month: January Number of Invoices: 15
I can't see the bug in the PHP I've got, but there certainly must be
one..
Any suggestions would be appreciated!
Mike
- Original Message -
From: Mike Gifford [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 11 January 2002 08:12
Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello
I've got the following SQL Query, which consistently pulls up only 3 out
of 4 months from the database:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;
This results in:
Month: November Number of Invoices: 17
Month: December Number of Invoices: 22
Month: January Number of Invoices: 15
But when I do change the order of the query from ASC to DESC like this:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT
COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth,
YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled)
ORDER BY DateBilled DESC;
I get:
Month: December Number of Invoices: 22
Month: November Number of Invoices: 17
Month: October Number of Invoices: 21
(I gained October and Lost January)
The date format in the DB is like this:
2001-12-05
I've tried a whole stack of variations on the above query, but I still
seem to be coming up one short. Any idea why I'm not getting a display
of all of the months?
Thanks.
Mike
--
Mike Gifford, OpenConcept Consulting, http://www.openconcept.ca
Supporting progressive organizations in online campaigns and tools.
Feature: Women's Learning Partnership http://learningpartnership.org
Truth is that which confirms what we already believe. Northrop Frye
--
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]
--
Mike
Re: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
It may have been obvious to many, but I stumbled across the solution
(eventually)..
Changing the while statement to a do statement did the trick:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled DESC;
$monthly_result = mysql_query($sql, $db);
$monthly_row = DBfetch_array($monthly_result);
$i=0;
do {
$InvMonth[$i] = $monthly_row[BilledMonth];
$InvCount[$i] = $monthly_row[count];
echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002))
. !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] .
/strongbr;
++$i;
} while($monthly_row = mysql_fetch_array($monthly_result));
On Fri, 2002-01-11 at 11:20, Mike Gifford wrote:
Hello,
Thanks for your quick reply.. I'm trying to improve the stats feature
for gcdb (a pretty decent little PHP/MySQL accounting package on SF.net)
On Fri, 2002-01-11 at 05:24, DL Neil wrote:
Have you posted all of the relevant code - for example, how the 'result' is
limited to three month's worth of
data???
I didn't provide all of the code in the initial response as it was using
a wrapper so I didn't know how relevant it would be.. However, your
note made me realize that I could rewrite the code without the
wrapper.. It still worked the same way. The code stands as:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled DESC;
$monthly_result = mysql_query($sql, $db);
$monthly_row = DBfetch_array($monthly_result);
while($monthly_row = mysql_fetch_array($monthly_result)) {
++$i;
$InvMonth[$i] = $monthly_row[BilledMonth];
$InvCount[$i] = $monthly_row[count];
echo strongMonth: . date (F,
mktime(0,0,0,$InvMonth[$i],1,2002)) . !-- ($InvMonth) -- Number
of Invoices: . $InvCount[$i] . /strongbr;
}
RESULTS:
Month: December Number of Invoices: 22
Month: November Number of Invoices: 17
Month: October Number of Invoices: 21
Have you extracted the SQL from the PHP and applied it directly to the command
line or used it in a MySQL
Management package? Was the result any different?
Also a damn good idea (I haven't had root access to MySQL until
recently, hadn't thought of that either)
mysql SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled DESC;
+---+-++
| count | BilledMonth | BilledYear |
+---+-++
|15 | 1 | 2002 |
|22 | 12 | 2001 |
|17 | 11 | 2001 |
|21 | 10 | 2001 |
+---+-++
4 rows in set (0.00 sec)
Ok.. So the problem seems to be with my code..
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;
$monthly_result = mysql_query($sql, $db);
$monthly_row = DBfetch_array($monthly_result);
$i=0;
while($monthly_row = mysql_fetch_array($monthly_result)) {
$InvMonth[$i] = $monthly_row[BilledMonth];
$InvCount[$i] = $monthly_row[count];
echo strongMonth: . date (F, mktime(0,0,0,$InvMonth[$i],1,2002))
. !-- ($InvMonth) -- Number of Invoices: . $InvCount[$i] .
/strongbr;
++$i;
}
RESULTS:
Month: November Number of Invoices: 17
Month: December Number of Invoices: 22
Month: January Number of Invoices: 15
I can't see the bug in the PHP I've got, but there certainly must be
one..
Any suggestions would be appreciated!
Mike
- Original Message -
From: Mike Gifford [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 11 January 2002 08:12
Subject: [PHP-DB] GROUP BY MONTH(DateBilled) misses an entry consistently
Hello
I've got the following SQL Query, which consistently pulls up only 3 out
of 4 months from the database:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;
This results in:
Month: November Number of Invoices: 17
Month: December Number of Invoices: 22
Month: January Number of Invoices: 15
But when I do change the order of the query from ASC to DESC like this:
$sql = SELECT COUNT(DateBilled) AS count, MONTH(DateBilled) AS
BilledMonth, YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY
MONTH(DateBilled) ORDER BY DateBilled ASC;$sql = SELECT
COUNT(DateBilled) AS count, MONTH(DateBilled) AS BilledMonth,
YEAR(DateBilled) AS BilledYear FROM Invoices GROUP BY MONTH(DateBilled)
ORDER BY DateBilled DESC;
I get:
Month: December Number of Invoices
[PHP-DB] Re: Query displays one
Hi Hugh,
Hugh Bothwell wrote:
Mike Gifford [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
The problem is that I am only getting one response from this query rather
than
5. I can't figure this out. What did I do to only get one response?
Try removing the ORDER BY and LIMIT and run the query by
hand - how many responses do you get? The search may only
return one article.
I tried removing these and I got a similarly disappoinging response (one reply),
so I decided to look again for code that is actually working and copy it from
scratch. I hit upon this and it is working now.
Thanks for your help.
function latest_articles() {
global $id,$main_file,$front_end_url,$alt, $articlestable, $categorytable;
$alt_var=;
$contentquery4 = mysql_query(SELECT a.*,s.URLname FROM $articlestable
a,
$categorytable s WHERE a.articleSectionID = s.articleSectionID ORDER BY a.date
DESC) or mysql_die();
if ($result = mysql_num_rows($contentquery4)) {
while ($contentarray4 = mysql_fetch_array($contentquery4)) {
$category = $contentarray4[URLname];
if ($alt) {
if ($contentarray4[alt_title]) {
$link_name =
$contentarray4[alt_title];
$alt_var = ?alt=french;
} else {
$link_name = $contentarray4[title];
$alt_var = ?alt=french;
}
} else {
$link_name = $contentarray4[title];
$alt_var = ;
}
$content .= \nlia
href=\.$front_end_url./.$main_file./.urlencode($category)./.$contentarray4[articleID]./
. $alt_var .\ class=\articleslink\.stripslashes($link_name)./a;
$i++;
if ($i==5) {
break;
}
}
}
return $content;
}
--
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]
[PHP-DB] Query displays one
Hello,
I've got a pretty basic query which I am trying to run within a function.
The query looks like this:
SELECT
a.title,a.alt_title,a.articleID,a.articleSectionID,sect.URLname
FROM
$articlestable a, $categorytable sect
WHERE
a.articleSectionID = sect.articleSectionID
ORDER BY
a.date
DESC
limit 5
The problem is that I am only getting one response from this query rather than
5. I can't figure this out. What did I do to only get one response?
The function is here:
function latest_articles() {
global $id,$main_file,$front_end_url,$alt, $articlestable, $categorytable;
$alt_var=;
$contentquery4 = mysql_query(SELECT
a.title,a.alt_title,a.articleID,a.articleSectionID,sect.URLname FROM
$articlestable a, $categorytable sect WHERE a.articleSectionID =
sect.articleSectionID ORDER BY a.date DESC limit 5) or mysql_die();
if ($result = mysql_num_rows($contentquery4)) {
while ($contentarray4 = mysql_fetch_array($contentquery4)) {
$category = $contentarray4[URLname];
if ($alt) {
if ($contentarray4[alt_title]) {
$link_name = $contentarray4[alt_title];
$alt_var = ?alt=french;
} else {
$link_name = $contentarray4[title];
$alt_var = ?alt=french;
}
} else {
$link_name = $contentarray4[title];
}
$content .= \nlia
href=\.$front_end_url./.$main_file./.urlencode($category)./.$contentarray4[articleID]./
. $alt_var .\ class=\articleslink\.stripslashes($link_name)./a;
}
}
return $content;
}
And Table below:
CREATE TABLE articles (
articleID bigint(21) unsigned NOT NULL auto_increment,
articleSectionID bigint(21),
uid bigint(21),
title varchar(255),
content mediumtext,
image_suffix varchar(4),
date date,
alt_title varchar(255),
alt_content BLOB,
meta_keywords varchar(255),
meta_description varchar(255),
blerb TEXT,
alt_blerb TEXT,
PRIMARY KEY (articleID)
);
The missing results can (not) be seen here (under new postings):
http://airdiv.cupe.ca/union.php/Division/
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]
[PHP-DB] Counting Number of Instances in a One2Many Relationships
Hello All,
I'd like a simple query to determine how MANY books in a database are released
by A publisher. This shouldn't be a big deal, but I've looked at a number of
solutions and am coming up short. MySQL is being accessed via phplib, but I'm
sure that it's just how I'm expressing the general MySQL query.
The bookID publisherID are both listed in the bookDB. I just want a query
which will go through the database and return the publisherID and a count of the
number of books.
I'd like to end up with an array that expresses $publisherID = Number of books
so that I can tap this later...
I first tried to approach it by doing this which fell because of @@IDENTITY
$q2 = SELECT publisherID FROM WLPpublisher;
$q2 = SELECT @@IDENTITY AS publisherID FROM WLPpublisher;
echo $q2 . br;
$lastPublisherID = $this-db-query($q2);
$q3 = SELECT bibID,publisherID FROM WLPbib WHERE publisherID='$i';
echo $q3 . br;
for ($i=0; ($i $lastPublisherID); $i++) {
$pub_count_ary[$i] = $this-db-num_rows($q3);
}
I next tried to use a query like this, but couldn't figure out how to express
the relationship between the two:
$q2 = SELECT bibID,publisherID,COUNT(*)
FROM WLPbib
GROUP BY publisherID;
$this-db-query($q2);
while ($this-db-next_record()) {
$pub_count_ary[$i] = $this-db-Record[publisherID];
$i++;
}
Finally, I tried this which didn't work either:
$q2 = SELECT bibID,publisherID FROM WLPbib;
$this-db-query($q2);
$i = 0;
while ($this-db-next_record()) {
$pub_count_ary[$i] = $this-db-Record[publisherID];
$i++;
}
The relevant table's here:
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)
);
Suggestions are appreciated!
Mike
--
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] 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]
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..
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] Left Join is producing duplicate results - MySQL relational tables
Hi Matthew, Matthew Loff wrote: What about... SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPprofile, WLPaddress, WLPcountry WHERE WLPprofile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I think that this is working now. I had a similar problems with a database I was working with... It had a main table with 29,000 listings, and I decided to normalize it to improve query times (split the records into diff. tables, one for each attribute of the record, associate the records back together by a common ID than spanned all the tables)... This one will likely have 10,000 to start with, so it is good to build in normalization... Didn't even know what the term normalization referred to earlier today. However, that is what I was doing with the table. I ended up with an SQL query that spanned like 10 tables-- but it was -way- faster than one big table. There's an excellent article on Normalization on PHPbuilder -- http://www.phpbuilder.com/columns/barry2731.php3 This is a good article. Even addressed the question that I fired off to Dobromir about linking various profiles to the same bibliography. One person can write many articles an article can have many authors, so I need to create a many-many table to link the articles to profiles. I think that this wil work: CREATE TABLE WLParticle2profile ( a2pID mediumint(9) NOT NULL auto_increment, bibID mediumint(9), profileID mediumint(9), PRIMARY KEY (a2pID) ) I'll then need to Re-jig the WHERE command to limit the number of returns... This would become SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROMWLPprofile, WLPaddress, WLPcountry, WLParticle2profile WHERE WLParticle2profile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I'm not sure this will work to tie in the relational table and then we come back to the problem with duplicate entries again... If your WLP tables are very large, you may want to try using mySQL's EXPLAIN SELECT [rest of select query]... function to figure out the best (read: efficient) ways of performing this query... Indexes are definitely a must if you are dealing with a lot of rows... If not, you should be just fine with the above query-- which I -think- is equivilent... I didn't know about this option: http://www.mysql.com/doc/E/X/EXPLAIN.html I find the MySQL.com site to be hard to read through.. php.net is much easier to understand in my experience. I don't have a ton of experience with SQL, so perhaps someone can better elaborate. I think that you did a good job.. However I think I'm still stuck with the same duplicate error now (well when I've expanded the code. Mike -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 3:01 AM To: [EMAIL PROTECTED]; Mike Gifford Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hi, Did you try to use something like this. mysql_query(SELECT DISTINCT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM ((WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID) LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID) LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); The other thing that may help is to rearange the order of the tables in the FROM clause. Please check if some of the joins return more than one result - if you have more than one address for a publisher the query will return one row for every address. Hope this helps Dobromir Velev -Original Message- From: Mike Gifford [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, July 12, 2001 4:31 AM Subject: [PHP-DB] Left Join is producing duplicate results - MySQL relational tables Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables
[PHP-DB] Left Join is producing duplicate results - MySQL relational tables
Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables. However, when I run this query: mysql_query(SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); I now get results in triplicate. ie. I'm getting three copies of the same title, firstName, organization, etc I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure how to return a query without duplication. This is far better than what I had this morning (which was no response from the server). Thanks. I'm new to joining tables... Someone wrote back suggesting that SELECT DISTINCT could be used to to the job. Another person suggested that using UNIQUE(profileID) would make it look nicer. I wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to WLPbib.. Any suggestions would be useful. 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] Left Join is producing duplicate results - MySQL relational tables
Sorry Ken, I'm trying to learn a few too many things at once. Switching it around eliminated the error message but not the duplication. I'm eliminating as much of the code as I can to see that it isn't coming from PHP. Thanks again for your help. Mike Ken wrote: In general, you should always be reading the manual first. From the SELECT syntax at http://www.mysql.com/doc/S/E/SELECT.html GROUP BY must be indicated before ORDER BY. So reverse the order of those portions. - Ken At 10:35 PM 7/11/01 -0400, Mike Gifford wrote: It looked good, but it gave me an error.. Sorry ... WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY WLPbib.bibID ' at line 10) Please contact the webmaster and report the exact error message. Session halted. Ken wrote: Mike - I'm not certain but it sounds like you might be looking for GROUP BY. Do GROUP BY and then the columns that are identical in your results. - Ken At 09:46 PM 7/11/01 -0400, Mike Gifford wrote: Hello, I posted this to the general list this morning got a couple of good leads, but they weren't able to actually fix the problem, so I'm posting here to the db list. I'm making some headway on joining three MySQL tables. However, when I run this query: mysql_query(SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPbib LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID); I now get results in triplicate. ie. I'm getting three copies of the same title, firstName, organization, etc I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure how to return a query without duplication. This is far better than what I had this morning (which was no response from the server). Thanks. I'm new to joining tables... Someone wrote back suggesting that SELECT DISTINCT could be used to to the job. Another person suggested that using UNIQUE(profileID) would make it look nicer. I wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to WLPbib.. Any suggestions would be useful. 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] -- 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] Left Join is producing duplicate results - MySQL relational tables
phpMyAdmin is great! And I hear that they are coming out with a new version pretty soon (or is it here - haven't checked).. However I don't think that you can join tables in phpMyAdmin.. Perhaps I don't know the app well enough though. Mike Beau Lebens wrote: altho this may not help at all, rather than bashing away with php+SQL statements, i usually just pilot my sql in phpmyadmin, then once it works with set values (ie SELECT * FROM people WHERE personID='3' rahter than personID='$personID' or something) i can then drop that SQL into my script. HTH Beau // -Original Message- // From: Mike Gifford [mailto:[EMAIL PROTECTED]] // Sent: Thursday, 12 July 2001 11:01 AM // To: Ken // Cc: [EMAIL PROTECTED] // Subject: Re: [PHP-DB] Left Join is producing duplicate // results - MySQL // relational tables // // // Sorry Ken, // // I'm trying to learn a few too many things at once. // Switching it around // eliminated the error message but not the duplication. // // I'm eliminating as much of the code as I can to see that it // isn't coming from PHP. // // Thanks again for your help. // // Mike // // Ken wrote: // // In general, you should always be reading the manual first. // From the SELECT syntax at // http://www.mysql.com/doc/S/E/SELECT.html // GROUP BY must be indicated before ORDER BY. So reverse // the order of those portions. // // - Ken // // At 10:35 PM 7/11/01 -0400, Mike Gifford wrote: // // It looked good, but it gave me an error.. Sorry // ... // WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID // // MySQL Error: 1064 (You have an error in your SQL syntax // near 'GROUP BY WLPbib.bibID ' at line 10) // Please contact the webmaster and report the exact error message. // Session halted. // // Ken wrote: // // // Mike - // I'm not certain but it sounds like you might be looking // for GROUP BY. Do GROUP BY and then the columns that are // identical in your results. // - Ken // At 09:46 PM 7/11/01 -0400, Mike Gifford wrote: // // // Hello, // // I posted this to the general list this morning got a // couple of good leads, but they weren't able to actually fix // the problem, so I'm posting here to the db list. // // I'm making some headway on joining three MySQL tables. // // However, when I run this query: // // mysql_query(SELECT // WLPbib.bibID, // WLPbib.title, // WLPbib.publisher, // WLPbib.publicationDate, // WLPaddress.city, // WLPaddress.state, // WLPprofile.firstName, // WLPprofile.lastName, // WLPprofile.organization, // WLPcountry.languageName // // FROM WLPbib // // LEFT JOIN WLPprofile ON WLPprofile.profileID = // WLPbib.profileID // LEFT JOIN WLPaddress ON WLPaddress.publisherID = // WLPbib.publisherID // LEFT JOIN WLPcountry ON WLPcountry.countryID = // WLPaddress.countryID); // // I now get results in triplicate. ie. I'm getting three // copies of the same title, firstName, organization, etc // // I somehow suspected that this should be the result with // LEFT JOIN, but I'm not sure how to return a query without // duplication. // // This is far better than what I had this morning (which // was no response from the server). // // Thanks. I'm new to joining tables... // // Someone wrote back suggesting that SELECT DISTINCT could // be used to to the job. // // Another person suggested that using UNIQUE(profileID) // would make it look nicer. I wasn't sure how to use UNIQUE // with the last JOIN as it isn't directly linked to WLPbib.. // // Any suggestions would be useful. // // 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] // // // // // // // -- // 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] // -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything
