Re: [PHP-DB] Basic JOIN tutorial - RE: [PHP-DB] Job interview test - I give up...
Bloody marvelous...!!! Many thanks.. I'll set myself a few challenges, and see what happens...! Cheers, Tris... "Gryffyn, Trevor" <[EMAIL PROTECTED]> 01/12/2004 15:47 To <[EMAIL PROTECTED]> cc <[EMAIL PROTECTED]> Subject [PHP-DB] Basic JOIN tutorial - RE: [PHP-DB] Job interview test - I give up... Looks like you got a lot of good responses. I downloaded the zip file but then got slammed with some priority stuff and havn't had a chance to take a look at this. So in the interest of not forgetting and of closing this reply window :) just a quicky response. I had a lot of trouble initially wrapping my brain around JOINs conceptually but they're really rather easy. Here's my 2 cent tour (because most of the documentation I read didn't really put it in "plain english"). Someone please correct me or add to what I've said if it's not accurate: Inner Join - This is when you have two tables and the items in the ON clause need to exist in both tables for you to get a row returned. Doing a " a INNER JOIN b ON somecriteria " is functionally the same as doing "from a, b where somecriteria" Maybe there's a performance difference, maybe only on certain database systems, not sure. They should return the same output: SELECT trucks.truckid, crates.crateid FROM trucks INNER JOIN crates ON trucks.truckid = crates.truckid Should be the same as... SELECT trucks.truckid, crates.crateid FROM trucks, crates WHERE trucks.truckid = crates.truckid This will return all trucks that have crates on them. If a truck is empty, it doesn't show up on the list. Outer Joins - This is where you know you have items in one table, but may or may not have items in another table. SELECT trucks.truckid, crates.crateid FROM trucks LEFT JOIN creates ON trucks.truckid = crates.truckid This should give you a list of all trucks, even empty ones, and their associated crates (if there are any.. If no crates are on the trucks, then an empty cell is returned for 'crateid'. SELECT trucks.truckid, crates.crateid FROM creates RIGHT JOIN trucks ON trucks.truckid = crates.truckid This should return the same thing. The LEFT or RIGHT "points" to the table that definitely has rows, the other table will show empty (NULL) cells if there are no matches. I believe there's a join that will show NULLs in either column if there's no match, but I don't know what it is off the top of my head. Anyone know this one? Also, some systems like Oracle, let you do shorthand like: SELECT trucks.truckid, crates.crateid FROM creates, trucks WHERE trucks.truckid = crates.truckid(+) (this syntax is probably wrong). It uses a (+) in the WHERE clause to indicate the join. Microsoft SQL-Server has some kind of shorthand way to do this as well, but it's documented as "never having worked properly" so I'd recommend not using it. To me, the Oracle shorthand is much more intuitive, but once you get the hang of the LEFT JOIN type syntax, it's not too bad. One last note, you can JOIN on multiple tables, you just need to wrap everything in parentheses. It gets a little sticky, but work from the inside out and keep an image in your head of what's going to be returned by each layer and join accordingly. If you have Microsoft Access or something else that graphically lets you create queries, you might try building a query that you know works and returns what you need, then looking at the SQL VIEW to see how the syntax goes. It can help you understand how to do the raw SQL yourself. Good luck! -TG > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 01, 2004 8:14 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Job interview test - I give up... > > > I recently saw a job that I thought I'd be good for, so they > sent me a > test... > All good.. but I realised that I couldn't do it.. > I got sooo damned close, but could not finish... :-( > > Anyhoo, here's the test: > http://www.beertastic.co.uk/test_problem.zip > > And the test parameters are below > If anyone can tell me the answer, I'd appreciate it, then I can > de-engineer it, and hopefully learn something. > I've already mailed them and said thanks, but I give up ;-) > > I my prob was in gettin ga distict list of all DVD > categories, from within > the final results page... > > Hmmm, any takers? > > TASK:- > > Modify the test.php file (attached) so a list of categories and the > number of DVDs in each category are displayed when t
Re: [PHP-DB] Basic JOIN tutorial - RE: [PHP-DB] Job interview test - I give up...
--- "Gryffyn, Trevor" <[EMAIL PROTECTED]> wrote: > If you have Microsoft Access or something else that > graphically lets you > create queries, you might try building a query that > you know works and > returns what you need, then looking at the SQL VIEW > to see how the > syntax goes. It can help you understand how to do > the raw SQL yourself. > Quick comment, most database tools these days have a graphic query builder. I have them for mySQL. It has helped me, because even in the graphic builders you still need to know whether it's a left right , outter inner, full join. So, the builder gets all the other stuff out of the way, all the code, and would let you focus on the join type. A click of the mouse and you can switch the join type, run the query and see the difference in results. Kind of neat. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Basic JOIN tutorial - RE: [PHP-DB] Job interview test - I give up...
Looks like you got a lot of good responses. I downloaded the zip file but then got slammed with some priority stuff and havn't had a chance to take a look at this. So in the interest of not forgetting and of closing this reply window :) just a quicky response. I had a lot of trouble initially wrapping my brain around JOINs conceptually but they're really rather easy. Here's my 2 cent tour (because most of the documentation I read didn't really put it in "plain english"). Someone please correct me or add to what I've said if it's not accurate: Inner Join - This is when you have two tables and the items in the ON clause need to exist in both tables for you to get a row returned. Doing a " a INNER JOIN b ON somecriteria " is functionally the same as doing "from a, b where somecriteria" Maybe there's a performance difference, maybe only on certain database systems, not sure. They should return the same output: SELECT trucks.truckid, crates.crateid FROM trucks INNER JOIN crates ON trucks.truckid = crates.truckid Should be the same as... SELECT trucks.truckid, crates.crateid FROM trucks, crates WHERE trucks.truckid = crates.truckid This will return all trucks that have crates on them. If a truck is empty, it doesn't show up on the list. Outer Joins - This is where you know you have items in one table, but may or may not have items in another table. SELECT trucks.truckid, crates.crateid FROM trucks LEFT JOIN creates ON trucks.truckid = crates.truckid This should give you a list of all trucks, even empty ones, and their associated crates (if there are any.. If no crates are on the trucks, then an empty cell is returned for 'crateid'. SELECT trucks.truckid, crates.crateid FROM creates RIGHT JOIN trucks ON trucks.truckid = crates.truckid This should return the same thing. The LEFT or RIGHT "points" to the table that definitely has rows, the other table will show empty (NULL) cells if there are no matches. I believe there's a join that will show NULLs in either column if there's no match, but I don't know what it is off the top of my head. Anyone know this one? Also, some systems like Oracle, let you do shorthand like: SELECT trucks.truckid, crates.crateid FROM creates, trucks WHERE trucks.truckid = crates.truckid(+) (this syntax is probably wrong). It uses a (+) in the WHERE clause to indicate the join. Microsoft SQL-Server has some kind of shorthand way to do this as well, but it's documented as "never having worked properly" so I'd recommend not using it. To me, the Oracle shorthand is much more intuitive, but once you get the hang of the LEFT JOIN type syntax, it's not too bad. One last note, you can JOIN on multiple tables, you just need to wrap everything in parentheses. It gets a little sticky, but work from the inside out and keep an image in your head of what's going to be returned by each layer and join accordingly. If you have Microsoft Access or something else that graphically lets you create queries, you might try building a query that you know works and returns what you need, then looking at the SQL VIEW to see how the syntax goes. It can help you understand how to do the raw SQL yourself. Good luck! -TG > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 01, 2004 8:14 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Job interview test - I give up... > > > I recently saw a job that I thought I'd be good for, so they > sent me a > test... > All good.. but I realised that I couldn't do it.. > I got sooo damned close, but could not finish... :-( > > Anyhoo, here's the test: > http://www.beertastic.co.uk/test_problem.zip > > And the test parameters are below > If anyone can tell me the answer, I'd appreciate it, then I can > de-engineer it, and hopefully learn something. > I've already mailed them and said thanks, but I give up ;-) > > I my prob was in gettin ga distict list of all DVD > categories, from within > the final results page... > > Hmmm, any takers? > > TASK:- > > Modify the test.php file (attached) so a list of categories and the > number of DVDs in each category are displayed when the script > is called > in a browser. (See resultspage.html for the EXACT output required) > > You must complete the getNumDvdsInCategories() function to query the > database and return a multidimensional array in the required format. > > You may use the PHP and MySQL manuals available online. > > > RESTRICTIONS:- > > You may only make ONE select query to the database. > Do not edit code outside the getNumD
Re: [PHP-DB] Job interview test - I give up...
Right Not married... Not as great a PHP/MySQL developer as I though... Alot of nots today... :-( I'll take a read or that link... This time next year, I'll be a professional...! (what, I don;t know..) Brent Baisley <[EMAIL PROTECTED]> 01/12/2004 15:10 To [EMAIL PROTECTED] cc [EMAIL PROTECTED] Subject Re: [PHP-DB] Job interview test - I give up... I code much better with drink than after drink. You never had to use JOIN before? Obviously you are not married. Although marriage tends to be a left join or right join as opposed to a straight join, which allows you to force an order. http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html On Dec 1, 2004, at 9:02 AM, [EMAIL PROTECTED] wrote: > No offence taken! > I've never had to use JOIN before... > and the test was supposed to take an hour.. I didn;t wanna cheat... > and it > took me ages to even realise what I was supposed to be doing... > (Plus [EMAIL PROTECTED] hungover :-( ) > > Anyhoo, I'll try what you've suggested... > Cheers.. we live, we learn... > > > > > > Brent Baisley <[EMAIL PROTECTED]> > 01/12/2004 13:44 > > To > [EMAIL PROTECTED] > cc > <[EMAIL PROTECTED]> > Subject > Re: [PHP-DB] Job interview test - I give up... > > > > > > > OK. No offense, but that seems fairly easy. > > Here's one possible answer: > > > select categories.*,count(dv_id) > from categories > left join dvds on dv_caid=ca_id > group by ca_name > having ca_name not like 'T%'; > > > On Dec 1, 2004, at 8:13 AM, [EMAIL PROTECTED] wrote: > >> I recently saw a job that I thought I'd be good for, so they sent me a >> test... >> All good.. but I realised that I couldn't do it.. >> I got sooo damned close, but could not finish... :-( >> >> Anyhoo, here's the test: >> http://www.beertastic.co.uk/test_problem.zip >> >> And the test parameters are below >> If anyone can tell me the answer, I'd appreciate it, then I can >> de-engineer it, and hopefully learn something. >> I've already mailed them and said thanks, but I give up ;-) >> >> I my prob was in gettin ga distict list of all DVD categories, from >> within >> the final results page... >> >> Hmmm, any takers? >> >> TASK:- >> >> Modify the test.php file (attached) so a list of categories and the >> number of DVDs in each category are displayed when the script is >> called >> in a browser. (See resultspage.html for the EXACT output required) >> >> You must complete the getNumDvdsInCategories() function to query the >> database and return a multidimensional array in the required format. >> >> You may use the PHP and MySQL manuals available online. >> >> >> RESTRICTIONS:- >> >> You may only make ONE select query to the database. >> Do not edit code outside the getNumDvdsInCategories() function. The >> results in the table must be in alphabetical order (category name). >> The >> Drama category MUST appear even though there are no DVDs in the >> category. Categories beginning with the letter "T" must not be >> displayed. Values in the database cannot be hardcoded anywhere in the >> script. >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Job interview test - I give up...
I code much better with drink than after drink. You never had to use JOIN before? Obviously you are not married. Although marriage tends to be a left join or right join as opposed to a straight join, which allows you to force an order. http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html On Dec 1, 2004, at 9:02 AM, [EMAIL PROTECTED] wrote: No offence taken! I've never had to use JOIN before... and the test was supposed to take an hour.. I didn;t wanna cheat... and it took me ages to even realise what I was supposed to be doing... (Plus [EMAIL PROTECTED] hungover :-( ) Anyhoo, I'll try what you've suggested... Cheers.. we live, we learn... Brent Baisley <[EMAIL PROTECTED]> 01/12/2004 13:44 To [EMAIL PROTECTED] cc <[EMAIL PROTECTED]> Subject Re: [PHP-DB] Job interview test - I give up... OK. No offense, but that seems fairly easy. Here's one possible answer: select categories.*,count(dv_id) from categories left join dvds on dv_caid=ca_id group by ca_name having ca_name not like 'T%'; On Dec 1, 2004, at 8:13 AM, [EMAIL PROTECTED] wrote: I recently saw a job that I thought I'd be good for, so they sent me a test... All good.. but I realised that I couldn't do it.. I got sooo damned close, but could not finish... :-( Anyhoo, here's the test: http://www.beertastic.co.uk/test_problem.zip And the test parameters are below If anyone can tell me the answer, I'd appreciate it, then I can de-engineer it, and hopefully learn something. I've already mailed them and said thanks, but I give up ;-) I my prob was in gettin ga distict list of all DVD categories, from within the final results page... Hmmm, any takers? TASK:- Modify the test.php file (attached) so a list of categories and the number of DVDs in each category are displayed when the script is called in a browser. (See resultspage.html for the EXACT output required) You must complete the getNumDvdsInCategories() function to query the database and return a multidimensional array in the required format. You may use the PHP and MySQL manuals available online. RESTRICTIONS:- You may only make ONE select query to the database. Do not edit code outside the getNumDvdsInCategories() function. The results in the table must be in alphabetical order (category name). The Drama category MUST appear even though there are no DVDs in the category. Categories beginning with the letter "T" must not be displayed. Values in the database cannot be hardcoded anywhere in the script. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Job interview test - I give up...
/*
NOTE: this is what I came up with though I don't have the time to test
it.
I think theres another way to solve this just by using a
different
sql statement.
*/
function getNumDvdsInCategories() {
$query = "SELECT ca_id , ca_name FROM categories, dvds WHERE dv_caid =
ca_id";
$results = mysql_query($query);
$counter = 0;
$counter2 = 0;
$oldflag = "";
$newflag = "";
while($row = mysql_fetch_assoc($results)) {
if(strcmp($row["ca_id"],$categories[$counter][ca_id]) == 0) {
//check if ca_id repeats
$counter2++; //if ca_id repeats it means we're still in
the same
category but different dvd
} else {
//if ca_id changes it means we changed categories now.
$categories[$counter][num] = $counter2; //counter2 is
the no. of
dvds in the category
$counter++; //goto the next array index for the next
category
$counter2 = 0; //reset counter2
}
//self explanatory
$categories[$counter][ca_id] = $row["ca_id"];
$categories[$counter][ca_name] = $row["ca_name"];
}
return $categories;
}
-- There's only one basic human right, the right to do as you damn
well please. And with it comes the only basic human duty, the duty to
take the consequences.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Job interview test - I give up...
No offence taken! I've never had to use JOIN before... and the test was supposed to take an hour.. I didn;t wanna cheat... and it took me ages to even realise what I was supposed to be doing... (Plus [EMAIL PROTECTED] hungover :-( ) Anyhoo, I'll try what you've suggested... Cheers.. we live, we learn... Brent Baisley <[EMAIL PROTECTED]> 01/12/2004 13:44 To [EMAIL PROTECTED] cc <[EMAIL PROTECTED]> Subject Re: [PHP-DB] Job interview test - I give up... OK. No offense, but that seems fairly easy. Here's one possible answer: select categories.*,count(dv_id) from categories left join dvds on dv_caid=ca_id group by ca_name having ca_name not like 'T%'; On Dec 1, 2004, at 8:13 AM, [EMAIL PROTECTED] wrote: > I recently saw a job that I thought I'd be good for, so they sent me a > test... > All good.. but I realised that I couldn't do it.. > I got sooo damned close, but could not finish... :-( > > Anyhoo, here's the test: > http://www.beertastic.co.uk/test_problem.zip > > And the test parameters are below > If anyone can tell me the answer, I'd appreciate it, then I can > de-engineer it, and hopefully learn something. > I've already mailed them and said thanks, but I give up ;-) > > I my prob was in gettin ga distict list of all DVD categories, from > within > the final results page... > > Hmmm, any takers? > > TASK:- > > Modify the test.php file (attached) so a list of categories and the > number of DVDs in each category are displayed when the script is called > in a browser. (See resultspage.html for the EXACT output required) > > You must complete the getNumDvdsInCategories() function to query the > database and return a multidimensional array in the required format. > > You may use the PHP and MySQL manuals available online. > > > RESTRICTIONS:- > > You may only make ONE select query to the database. > Do not edit code outside the getNumDvdsInCategories() function. The > results in the table must be in alphabetical order (category name). The > Drama category MUST appear even though there are no DVDs in the > category. Categories beginning with the letter "T" must not be > displayed. Values in the database cannot be hardcoded anywhere in the > script. > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Job interview test - I give up...
OK. No offense, but that seems fairly easy. Here's one possible answer: select categories.*,count(dv_id) from categories left join dvds on dv_caid=ca_id group by ca_name having ca_name not like 'T%'; On Dec 1, 2004, at 8:13 AM, [EMAIL PROTECTED] wrote: I recently saw a job that I thought I'd be good for, so they sent me a test... All good.. but I realised that I couldn't do it.. I got sooo damned close, but could not finish... :-( Anyhoo, here's the test: http://www.beertastic.co.uk/test_problem.zip And the test parameters are below If anyone can tell me the answer, I'd appreciate it, then I can de-engineer it, and hopefully learn something. I've already mailed them and said thanks, but I give up ;-) I my prob was in gettin ga distict list of all DVD categories, from within the final results page... Hmmm, any takers? TASK:- Modify the test.php file (attached) so a list of categories and the number of DVDs in each category are displayed when the script is called in a browser. (See resultspage.html for the EXACT output required) You must complete the getNumDvdsInCategories() function to query the database and return a multidimensional array in the required format. You may use the PHP and MySQL manuals available online. RESTRICTIONS:- You may only make ONE select query to the database. Do not edit code outside the getNumDvdsInCategories() function. The results in the table must be in alphabetical order (category name). The Drama category MUST appear even though there are no DVDs in the category. Categories beginning with the letter "T" must not be displayed. Values in the database cannot be hardcoded anywhere in the script. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Job interview test - I give up...
I recently saw a job that I thought I'd be good for, so they sent me a test... All good.. but I realised that I couldn't do it.. I got sooo damned close, but could not finish... :-( Anyhoo, here's the test: http://www.beertastic.co.uk/test_problem.zip And the test parameters are below If anyone can tell me the answer, I'd appreciate it, then I can de-engineer it, and hopefully learn something. I've already mailed them and said thanks, but I give up ;-) I my prob was in gettin ga distict list of all DVD categories, from within the final results page... Hmmm, any takers? TASK:- Modify the test.php file (attached) so a list of categories and the number of DVDs in each category are displayed when the script is called in a browser. (See resultspage.html for the EXACT output required) You must complete the getNumDvdsInCategories() function to query the database and return a multidimensional array in the required format. You may use the PHP and MySQL manuals available online. RESTRICTIONS:- You may only make ONE select query to the database. Do not edit code outside the getNumDvdsInCategories() function. The results in the table must be in alphabetical order (category name). The Drama category MUST appear even though there are no DVDs in the category. Categories beginning with the letter "T" must not be displayed. Values in the database cannot be hardcoded anywhere in the script. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
