Re: [PHP] formulate nested select
On 31 Mar 2009 at 18:15, PJ wrote: snip But I see that I may be trying to do too much - I thought of showing how many books were listed under each letter of the alphabet but I don't see how it can be done in any simiple way as it would mean that I would have to do the select once with the ORDER BY and a second time without it just to get the number of listing. If there are a lot of books, like thousands, it might slow down things. I suppose I could live with ORDER BY title as that does not require another effort. Any thoughts or suggestions? Hi, Sounds like you need to use the GROUP BY functions of MySQL This SQL is probably wrong because I don't remember seeing your schema (and am too busy here to go looking!) SELECT LEFT(last_name, 1 ) as Letter, Count(bookID) as NumberOfBooks FROM books INNER JOIN tables that join them... GROUP BY Letter ORDER BY Letter ASC You will have to play around with that to get the right results. But it should give you something like: Letter,NumberOfBooks A,47 B,21 C,8 ... The MySQL manual has more info: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html Regards Ian -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors' names must be written with the first letter in uppercase, even Anonymous or Unknown I assume I don't need to specify uppercase. Or does it really make a difference? Glad to learn of the option, though. I'm just starting on the listing of the books by author and just realized that the sorting should be by author (last name). Can I foresee a problem in that since the last_name is in associative tables and not in the book table? Or does the JOIN incorporate the last_name in the results? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
I must be doing something wrong. Can't figure it out even though I've
been searching the manuals tutorials, it still does not work. Here is
the exact code that I have tried; the first version is commented out and
obviously does not work either (the spelling, the table names and column
names are correct):
$books = array();
/*$SQL = SELECT * FROM book b
JOIN book_author c
ON b.id = c.bookID
JOIN author a ON a.id = c.authID
WHERE LEFT(a.last_name,1) = $Auth
ORDER BY $sort $dir
LIMIT $offset, $records_per_page; */
$SQL = SELECT * FROM book b
INNER JOIN book_author c
ON b.id = c.bookID
WHERE c.authID = (SELECT id FROM author a WHERE LEFT(a.last_name, 1
) = $Auth )
ORDER BY $sort $dir
LIMIT $offset, $records_per_page;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row;
}
}
echo auth = :, $Auth ; --- returns auth = :A (my quotes)
var_dump($results); --- returns boolean false (my quotes)
Now, this:
$SQL = select *
from book b
inner join book_author c
on (b.id = c.bookID)
inner join author a on (a.id = c.authID)
where left(a.last_name, 1 ) = $Auth;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row; --- returns Parse error:
syntax error, unexpected '[', expecting ']' in this line
There seems to be something odd in the query... and yet, it all three
look right to me... could there be some inconsistency in the tables,
like skipped records or a record in a column that is non-existent in a
corresponding column?
Jim Lucas wrote:
Chris wrote:
PJ wrote:
I cannot find anything on google or the manuals/tutorials that gives
some kin of clear explanation of how to so nested selects with where or
whatever.
I have three tables: books, authors and book-authors.
I need to retrieve only those books whose author's names begin with A.
I have tried several maniipulations of where and select with select
subqueries and I cannot get results from the queries.
For example
SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE
LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id =
c.bookID snip
Not really a php question :P
You don't need a subquery for this. You can join all of the tables
together and just use the where clause to cut down your results, but
I'll give an example of both.
select *
from
books b inner join book_authors c on (b.id=c.bookId)
inner join authors a on (a.id=c.authorId)
where
left(a.last_name = 'A');
correct me if I'm wrong, but did you use the left() function
in-correctly?
The documentation shows a different way to use it then you describe.
Something more like the following:
WHERE
LEFT(a.last_name, 1) = 'A';
But that would be case-sensitive...
So, something like this would work better IMHO
WHERE
UPPER(LEFT(a.last_name, 1)) = 'A';
or
WHERE
a.last_name ILIKE 'A%';
would do the trick
or
select *
from
books b inner join book_authors c on (b.id=c.bookId)
where
c.authorId in (
select id from authors where left(last_name='A')
Again...
SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A')
but yet again, case-sensitive...
SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A')
or
SELECT id FROM authors WHERE last_name ILIKE 'A%'
would do the trick
);
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
On 31 Mar 2009 at 9:08, PJ wrote:
I must be doing something wrong. Can't figure it out even though I've
been searching the manuals tutorials, it still does not work. Here is
the exact code that I have tried; the first version is commented out and
obviously does not work either (the spelling, the table names and column
names are correct):
$books = array();
/*$SQL = SELECT * FROM book b
JOIN book_author c
ON b.id = c.bookID
JOIN author a ON a.id = c.authID
WHERE LEFT(a.last_name,1) = $Auth
ORDER BY $sort $dir
LIMIT $offset, $records_per_page; */
$SQL = SELECT * FROM book b
INNER JOIN book_author c
ON b.id = c.bookID
WHERE c.authID = (SELECT id FROM author a WHERE LEFT(a.last_name, 1
) = $Auth )
Hi,
I think this should be '$Auth' as MySQL will be expecting a string.
ORDER BY $sort $dir
LIMIT $offset, $records_per_page;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row;
}
}
echo auth = :, $Auth ; --- returns auth = :A (my quotes)
var_dump($results); --- returns boolean false (my quotes)
Now, this:
$SQL = select *
from book b
inner join book_author c
on (b.id = c.bookID)
inner join author a on (a.id = c.authID)
where left(a.last_name, 1 ) = $Auth;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row; --- returns Parse error:
syntax error, unexpected '[', expecting ']' in this line
There seems to be something odd in the query... and yet, it all three
look right to me... could there be some inconsistency in the tables,
like skipped records or a record in a column that is non-existent in a
corresponding column?
Looks like there is a double quote ( ) missing from the end of the $SQL
variable.
When building complex SQL queries for use in PHP I usually test them in the
mysql
command line client or a gui such as HeidiSQL first to make sure I am getting
the
expected results.
Also you could try printing the SQL statement to the web page to make sure it
looks as
expected.
You can try checking for a MySQL error within PHP by using these functions:
mysql_errno()
mysql_error()
Regards
Ian
--
Jim Lucas wrote:
Chris wrote:
PJ wrote:
I cannot find anything on google or the manuals/tutorials that gives
some kin of clear explanation of how to so nested selects with where or
whatever.
I have three tables: books, authors and book-authors.
I need to retrieve only those books whose author's names begin with A.
I have tried several maniipulations of where and select with select
subqueries and I cannot get results from the queries.
For example
SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE
LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id =
c.bookID snip
Not really a php question :P
You don't need a subquery for this. You can join all of the tables
together and just use the where clause to cut down your results, but
I'll give an example of both.
select *
from
books b inner join book_authors c on (b.id=c.bookId)
inner join authors a on (a.id=c.authorId)
where
left(a.last_name = 'A');
correct me if I'm wrong, but did you use the left() function
in-correctly?
The documentation shows a different way to use it then you describe.
Something more like the following:
WHERE
LEFT(a.last_name, 1) = 'A';
But that would be case-sensitive...
So, something like this would work better IMHO
WHERE
UPPER(LEFT(a.last_name, 1)) = 'A';
or
WHERE
a.last_name ILIKE 'A%';
would do the trick
or
select *
from
books b inner join book_authors c on (b.id=c.bookId)
where
c.authorId in (
select id from authors where left(last_name='A')
Again...
SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A')
but yet again, case-sensitive...
SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A')
or
SELECT id FROM authors WHERE last_name ILIKE 'A%'
would do the trick
);
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
Ian wrote:
On 31 Mar 2009 at 9:08, PJ wrote:
I must be doing something wrong. Can't figure it out even though I've
been searching the manuals tutorials, it still does not work. Here is
the exact code that I have tried; the first version is commented out and
obviously does not work either (the spelling, the table names and column
names are correct):
$books = array();
/*$SQL = SELECT * FROM book b
JOIN book_author c
ON b.id = c.bookID
JOIN author a ON a.id = c.authID
WHERE LEFT(a.last_name,1) = $Auth
ORDER BY $sort $dir
LIMIT $offset, $records_per_page; */
$SQL = SELECT * FROM book b
INNER JOIN book_author c
ON b.id = c.bookID
WHERE c.authID = (SELECT id FROM author a WHERE LEFT(a.last_name, 1
) = $Auth )
Hi,
I think this should be '$Auth' as MySQL will be expecting a string.
Hmmm That was it. I'm not clear on this... what was MySQL getting?
if not a string...
sorry for my ignorance...
ORDER BY $sort $dir
LIMIT $offset, $records_per_page;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row;
}
}
echo auth = :, $Auth ; --- returns auth = :A (my quotes)
var_dump($results); --- returns boolean false (my quotes)
Now, this:
$SQL = select *
from book b
inner join book_author c
on (b.id = c.bookID)
inner join author a on (a.id = c.authID)
where left(a.last_name, 1 ) = $Auth;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row; --- returns Parse error:
syntax error, unexpected '[', expecting ']' in this line
There seems to be something odd in the query... and yet, it all three
look right to me... could there be some inconsistency in the tables,
like skipped records or a record in a column that is non-existent in a
corresponding column?
Looks like there is a double quote ( ) missing from the end of the $SQL
variable.
When building complex SQL queries for use in PHP I usually test them in the
mysql
command line client or a gui such as HeidiSQL first to make sure I am getting
the
expected results.
Also you could try printing the SQL statement to the web page to make sure it
looks as
expected.
You can try checking for a MySQL error within PHP by using these functions:
mysql_errno()
mysql_error()
Regards
Ian
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
Gentlemen all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth'); I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: it sure don't look too good (American, I believe...) :-) Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors' names must be written with the first letter in uppercase, even Anonymous or Unknown I assume I don't need to specify uppercase. Or does it really make a difference? Glad to learn of the option, though. I'm just starting on the listing of the books by author and just realized that the sorting should be by author (last name). Can I foresee a problem in that since the last_name is in associative tables and not in the book table? Or does the JOIN incorporate the last_name in the results? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] formulate nested select
What about using IN, something like: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE authID IN (SELECT author.id FROM authors WHERE last_name LIKE $Auth%)); You could use LEFT instead of LIKE, too. -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Tuesday, March 31, 2009 2:06 PM To: Jim Lucas Cc: Chris; php-general@lists.php.net Subject: Re: [PHP] formulate nested select Gentlemen all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth'); I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: it sure don't look too good (American, I believe...) :-) Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors' names must be written with the first letter in uppercase, even Anonymous or Unknown I assume I don't need to specify uppercase. Or does it really make a difference? Glad to learn of the option, though. I'm just starting on the listing of the books by author and just realized that the sorting should be by author (last name). Can I foresee a problem in that since the last_name is in associative tables and not in the book table? Or does the JOIN incorporate the last_name in the results? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
kyle.smith wrote: What about using IN, something like: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE authID IN (SELECT author.id FROM authors WHERE last_name LIKE $Auth%)); You could use LEFT instead of LIKE, too. Well, I learned something here... but that also revealed another, hidden, problem which I had not considered - the order by clause which I had stupidly not included in my example: $SQL = SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) ORDER BY $sort $dir LIMIT $offset, $records_per_page ; I now realize that to keep things as simple as possible in the rest of the code, I must join 1 column to the query result and that is last_name from the author table. the spelling, column and table names are spelled correctly. Without the ORDER BY I get the right results. I'll try to JOIN the author table ??? But I see that I may be trying to do too much - I thought of showing how many books were listed under each letter of the alphabet but I don't see how it can be done in any simiple way as it would mean that I would have to do the select once with the ORDER BY and a second time without it just to get the number of listing. If there are a lot of books, like thousands, it might slow down things. I suppose I could live with ORDER BY title as that does not require another effort. Any thoughts or suggestions? -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Tuesday, March 31, 2009 2:06 PM To: Jim Lucas Cc: Chris; php-general@lists.php.net Subject: Re: [PHP] formulate nested select Gentlemen all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth'); I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: it sure don't look too good (American, I believe...) :-) Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors' names must be written with the first letter in uppercase, even Anonymous or Unknown I assume I don't need to specify
Re: [PHP] formulate nested select
On Tue, Mar 31, 2009 at 5:15 PM, PJ af.gour...@videotron.ca wrote: kyle.smith wrote: What about using IN, something like: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE authID IN (SELECT author.id FROM authors WHERE last_name LIKE $Auth%)); You could use LEFT instead of LIKE, too. Well, I learned something here... but that also revealed another, hidden, problem which I had not considered - the order by clause which I had stupidly not included in my example: $SQL = SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) ORDER BY $sort $dir LIMIT $offset, $records_per_page ; I now realize that to keep things as simple as possible in the rest of the code, I must join 1 column to the query result and that is last_name from the author table. the spelling, column and table names are spelled correctly. Without the ORDER BY I get the right results. I'll try to JOIN the author table ??? But I see that I may be trying to do too much - I thought of showing how many books were listed under each letter of the alphabet but I don't see how it can be done in any simiple way as it would mean that I would have to do the select once with the ORDER BY and a second time without it just to get the number of listing. If there are a lot of books, like thousands, it might slow down things. I suppose I could live with ORDER BY title as that does not require another effort. Any thoughts or suggestions? -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Tuesday, March 31, 2009 2:06 PM To: Jim Lucas Cc: Chris; php-general@lists.php.net Subject: Re: [PHP] formulate nested select Gentlemen all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth'); I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: it sure don't look too good (American, I believe...) :-) Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors' names must be written
Re: [PHP] formulate nested select
PJ wrote: Ian wrote: On 31 Mar 2009 at 9:08, PJ wrote: I must be doing something wrong. Can't figure it out even though I've been searching the manuals tutorials, it still does not work. Here is the exact code that I have tried; the first version is commented out and obviously does not work either (the spelling, the table names and column names are correct): $books = array(); /*$SQL = SELECT * FROM book b JOIN book_author c ON b.id = c.bookID JOIN author a ON a.id = c.authID WHERE LEFT(a.last_name,1) = $Auth ORDER BY $sort $dir LIMIT $offset, $records_per_page; */ $SQL = SELECT * FROM book b INNER JOIN book_author c ON b.id = c.bookID WHERE c.authID = (SELECT id FROM author a WHERE LEFT(a.last_name, 1 ) = $Auth ) Hi, I think this should be '$Auth' as MySQL will be expecting a string. Hmmm That was it. I'm not clear on this... what was MySQL getting? if not a string... sorry for my ignorance... You have to quote strings in sql, only numbers (and booleans) don't have quotes. ie $sql = .. where left(a.last_name, 1) = ' . mysql_real_escape_string($Auth) . '; single quotes around the value and it's properly escaped. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
haliphax wrote: On Tue, Mar 31, 2009 at 5:15 PM, PJ af.gour...@videotron.ca wrote: kyle.smith wrote: What about using IN, something like: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE authID IN (SELECT author.id FROM authors WHERE last_name LIKE $Auth%)); You could use LEFT instead of LIKE, too. Well, I learned something here... but that also revealed another, hidden, problem which I had not considered - the order by clause which I had stupidly not included in my example: $SQL = SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id     FROM author WHERE LEFT(last_name, 1 ) = '$Auth'))     ORDER BY $sort $dir     LIMIT $offset, $records_per_page ; I now realize that to keep things as simple as possible in the rest of the code, I must join 1 column to the query result and that is last_name from the author table. the spelling, column and table names are spelled correctly. Without the ORDER BY I get the right results. I'll try to JOIN the author table ??? But I see that I may be trying to do too much - I thought of showing how many books were listed under each letter of the alphabet but I don't see how it can be done in any simiple way as it would mean that I would have to do the select once with the ORDER BY and a second time without it just to get the number of listing. If there are a lot of books, like thousands, it might slow down things. I suppose I could live with ORDER BY title as that does not require another effort. Any thoughts or suggestions? -Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Tuesday, March 31, 2009 2:06 PM To: Jim Lucas Cc: Chris; php-general@lists.php.net Subject: Re: [PHP] formulate nested select Gentlemen all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth'); I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: it sure don't look too good (American, I believe...) :-) Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); Thank you for the suggestions, gentlemen. As to the case sensitivity, since the authors
Re: [PHP] formulate nested select
PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') ); -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in ( select id from authors where left(last_name='A') Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick ); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] formulate nested select
Jim Lucas wrote: Chris wrote: PJ wrote: I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID b.id = c.bookID snip Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A'); correct me if I'm wrong, but did you use the left() function in-correctly? Probably, I didn't look at the doco - I (stupidly I know) assumed it was correct in the original example. Thanks for the correction. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
