Re: [PHP-DB] Join on single table
* Micah Stevens [EMAIL PROTECTED]: What kind of speed do you need? I was aiming at something that would run in 2 hours or so. Currently, it takes around 3 - 4. I had to go to temp tables for a logging application, but not until my table got upwards of a million records or so.. For the numbers your quoting, it should be pretty quick unless your engine needs optimization.. Well, actually... The index currently has around a million records, with approximately 350 words per resource id, several thousand resource ids per app_id you get the idea. I've optimized my indices, and I've got appropriate indices for the selection criteria I'm using. As I've noted, I also realize that this is a one shot deal -- after the initial link table is created, I won't be needing to recreate it every time I have data that changes -- I'll only be operating on deltas from here out, and those can be done quite quickly. Anyways, it's a moot question now -- I've got the data in, and I'm pretty happy with the results I've seen. On Friday 11 February 2005 11:56 am, Matthew Weier O'Phinney wrote: * Micah Stevens [EMAIL PROTECTED]: Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? Speed. :-) I had tried this as well. The temporary tables really do offer much better performance. The issues I was having were (1) bad resultsets (I've now got that fixed) and (2) speed. I still don't have (2) completely fixed, and it may be something I can't fix. On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. I have created a temporary table 'tmp1' that contains all resource_ids from the second app_id (the one whose resources I wish to retrieve). I am then looping through all resource_ids in the main table with the first app_id, and doing the following: * Creating a temporary table tmp2 with a single column 'word' populated by the words associated with resource_id in the main table * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 on the word field The issues I'm running into are that (1) each resource_id cycle takes a good amount of time, and (2) I seem to be getting either too many resource_ids or not enough. (1) may be something I just have to deal with. As it is, I'm planning on running the full indexing once, and then doing incremental updates, so it may not be that big of an issue (unless it takes too much time to create the initial index). As for (2), unfortunately, I'm not sur ehow to really trouble shoot the issue. I know, for instance, that in once case, I have a list of 343 words that generates a list of ~12,000 resource_ids (of a possible 18,000) -- but I don't quite know how to spot check 300 values to be certain that this is reasonable. In a previous incarnation of the script, I was looping through each word of each resource_id and then selecting out of tmp1 based on the single word value. The results were very different (very few matches), and, again, the script ran long. Any ideas? -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. Is this at or near what you're looking for? I don't see any need for killing yourself making the temporary tables/etc. - but maybe I'm missing something. (obviously replace both 'tablename's with your tables name) SELECT b.app_id, b.resource_id, b.word, b.score FROM tablename AS a, tablename AS b WHERE a.app_id != b.app_id AND a.word = b.word; Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
* Martin Norland [EMAIL PROTECTED]: Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. Is this at or near what you're looking for? I don't see any need for killing yourself making the temporary tables/etc. - but maybe I'm missing something. (obviously replace both 'tablename's with your tables name) SELECT b.app_id, b.resource_id, b.word, b.score FROM tablename AS a, tablename AS b WHERE a.app_id != b.app_id AND a.word = b.word; I'd tried a similar query already. The issue with this particular approach is that the query takes forever to execute, even with good indexing. The temporary table approach speeds things up tremendously -- but it's still slower than I'd like. I finally hit on a solution last night, and did finally end up with a good index -- took several hours to run, but from now on I only have to do deltas. Basically, I do the following: * foreach resource_id in a single app_id, put a list of words into a temporary table * select resource_ids from another app_id by joining on the temporary table -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
* Micah Stevens [EMAIL PROTECTED]: Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? Speed. :-) I had tried this as well. The temporary tables really do offer much better performance. The issues I was having were (1) bad resultsets (I've now got that fixed) and (2) speed. I still don't have (2) completely fixed, and it may be something I can't fix. On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. I have created a temporary table 'tmp1' that contains all resource_ids from the second app_id (the one whose resources I wish to retrieve). I am then looping through all resource_ids in the main table with the first app_id, and doing the following: * Creating a temporary table tmp2 with a single column 'word' populated by the words associated with resource_id in the main table * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 on the word field The issues I'm running into are that (1) each resource_id cycle takes a good amount of time, and (2) I seem to be getting either too many resource_ids or not enough. (1) may be something I just have to deal with. As it is, I'm planning on running the full indexing once, and then doing incremental updates, so it may not be that big of an issue (unless it takes too much time to create the initial index). As for (2), unfortunately, I'm not sur ehow to really trouble shoot the issue. I know, for instance, that in once case, I have a list of 343 words that generates a list of ~12,000 resource_ids (of a possible 18,000) -- but I don't quite know how to spot check 300 values to be certain that this is reasonable. In a previous incarnation of the script, I was looping through each word of each resource_id and then selecting out of tmp1 based on the single word value. The results were very different (very few matches), and, again, the script ran long. Any ideas? -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
What kind of speed do you need? I had to go to temp tables for a logging application, but not until my table got upwards of a million records or so.. For the numbers your quoting, it should be pretty quick unless your engine needs optimization.. On Friday 11 February 2005 11:56 am, Matthew Weier O'Phinney wrote: * Micah Stevens [EMAIL PROTECTED]: Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? Speed. :-) I had tried this as well. The temporary tables really do offer much better performance. The issues I was having were (1) bad resultsets (I've now got that fixed) and (2) speed. I still don't have (2) completely fixed, and it may be something I can't fix. On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. I have created a temporary table 'tmp1' that contains all resource_ids from the second app_id (the one whose resources I wish to retrieve). I am then looping through all resource_ids in the main table with the first app_id, and doing the following: * Creating a temporary table tmp2 with a single column 'word' populated by the words associated with resource_id in the main table * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 on the word field The issues I'm running into are that (1) each resource_id cycle takes a good amount of time, and (2) I seem to be getting either too many resource_ids or not enough. (1) may be something I just have to deal with. As it is, I'm planning on running the full indexing once, and then doing incremental updates, so it may not be that big of an issue (unless it takes too much time to create the initial index). As for (2), unfortunately, I'm not sur ehow to really trouble shoot the issue. I know, for instance, that in once case, I have a list of 343 words that generates a list of ~12,000 resource_ids (of a possible 18,000) -- but I don't quite know how to spot check 300 values to be certain that this is reasonable. In a previous incarnation of the script, I was looping through each word of each resource_id and then selecting out of tmp1 based on the single word value. The results were very different (very few matches), and, again, the script ran long. Any ideas? -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
am using mysql 3 so i can't do that subquery, how can we do that in mysql 3 ? On Mon, 7 Feb 2005 19:47:08 +, Simon Rees [EMAIL PROTECTED] wrote: On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
Zouari Fourat wrote: am using mysql 3 so i can't do that subquery, how can we do that in mysql 3 ? and neither can anyone else ;-)... rather a pain, but mysql.com have been kind enough to give a detailed explaination on how to rewrite subselect queries as join queries: If you put your brain in gear this page should tell what you need to know: http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html If you get stuck, you know where the list is :-) On Mon, 7 Feb 2005 19:47:08 +, Simon Rees [EMAIL PROTECTED] wrote: On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
:( nothing On Mon, 7 Feb 2005 14:10:10 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try INNER JOIN? On Mon, 7 Feb 2005 19:47:29 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN across db's in MySQL
i asked this same question once and somehwere in the archives is a post by John Holmes that shows an example of how mysql allows db joins. its from last month i believe http://marc.theaimsgroup.com/?l=php-db but in short, mysql does support this using . db.table.field hth Jeff Mike Tallroth [EMAIL PROTECTED]To: [EMAIL PROTECTED] exus.comcc: Subject: [PHP-DB] JOIN across db's in MySQL 09/18/2003 10:47 AM In the big scheme of things, I need several functions to be performed by a web interface / database system. In an attempt to maintain some sense of order, I'm hoping to divide common data into a seperate MySQL database away from the function specific data. For example, I need to do project allocation and vendor quality tracking. These functions are generally unrelated but call on similar data (people in the group). My plan was to create a MySQL database called admin which contained people, customers, locations, addresses, etc, and seperate databases for each of the other functions projectallocation, vendorquality, etc. The problem I'm seeing is that there is no clean way of creating queries across database boundaries. Does PHP support this and I'm not seeing it? Does anyone know of third party code that accomplishes this manually? Am I out in left field on my desire to setup the database structure this way? thanks, -- Mike Tallroth Engineering Supervisor Test DesignCenter Plexus Technology Group Neenah, WI mike.tallroth @plexus.com 920-751-5418 -- 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] JOIN across db's in MySQL
Thanks, I found the post from John Holmes and was able to reconstruct the query in mysql. But when I transferred that over to php and try to extract an associative array from each record, I found that only one column came out instead of two. By simply changing the query from: $query = select t1.name, t2.name from test1.table1 t1 join test2.table2 t2 on t1.id = t2.id; to: $query = select t1.name as first, t2.name as last from test1.table1 t1 join test2.table2 t2 on t1.id = t2.id; the associative array worked as desired. I suspect what was happening originally was both columns when extracted from the result and placed into the array were both stored under the index name. By changing them to have unique field names as first and as last allowed two different indexes. Perhaps there is a way of extracting data from the query result that will allow redundant names, but I don't know what that is. thanks for the help, Mike Jeffrey N Dyke [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] i asked this same question once and somehwere in the archives is a post by John Holmes that shows an example of how mysql allows db joins. its from last month i believe http://marc.theaimsgroup.com/?l=php-db but in short, mysql does support this using . db.table.field hth Jeff Mike Tallroth [EMAIL PROTECTED]To: [EMAIL PROTECTED] exus.comcc: Subject: [PHP-DB] JOIN across db's in MySQL 09/18/2003 10:47 AM In the big scheme of things, I need several functions to be performed by a web interface / database system. In an attempt to maintain some sense of order, I'm hoping to divide common data into a seperate MySQL database away from the function specific data. For example, I need to do project allocation and vendor quality tracking. These functions are generally unrelated but call on similar data (people in the group). My plan was to create a MySQL database called admin which contained people, customers, locations, addresses, etc, and seperate databases for each of the other functions projectallocation, vendorquality, etc. The problem I'm seeing is that there is no clean way of creating queries across database boundaries. Does PHP support this and I'm not seeing it? Does anyone know of third party code that accomplishes this manually? Am I out in left field on my desire to setup the database structure this way? thanks, -- Mike Tallroth Engineering Supervisor Test DesignCenter Plexus Technology Group Neenah, WI mike.tallroth @plexus.com 920-751-5418 -- 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] JOIN across db's in MySQL
You may be able to use mysql_fetch_array() and use numerical indexes to reference the redundant names, but isn't it easier and better to just assign aliases like you're doing? It makes for more readable code, that's for sure. -Micah On Thu September 18 2003 12:55 pm, Mike Tallroth wrote: Thanks, I found the post from John Holmes and was able to reconstruct the query in mysql. But when I transferred that over to php and try to extract an associative array from each record, I found that only one column came out instead of two. By simply changing the query from: $query = select t1.name, t2.name from test1.table1 t1 join test2.table2 t2 on t1.id = t2.id; to: $query = select t1.name as first, t2.name as last from test1.table1 t1 join test2.table2 t2 on t1.id = t2.id; the associative array worked as desired. I suspect what was happening originally was both columns when extracted from the result and placed into the array were both stored under the index name. By changing them to have unique field names as first and as last allowed two different indexes. Perhaps there is a way of extracting data from the query result that will allow redundant names, but I don't know what that is. thanks for the help, Mike Jeffrey N Dyke [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] i asked this same question once and somehwere in the archives is a post by John Holmes that shows an example of how mysql allows db joins. its from last month i believe http://marc.theaimsgroup.com/?l=php-db but in short, mysql does support this using . db.table.field hth Jeff Mike Tallroth [EMAIL PROTECTED]To: [EMAIL PROTECTED] exus.comcc: Subject: [PHP-DB] JOIN across db's in MySQL 09/18/2003 10:47 AM In the big scheme of things, I need several functions to be performed by a web interface / database system. In an attempt to maintain some sense of order, I'm hoping to divide common data into a seperate MySQL database away from the function specific data. For example, I need to do project allocation and vendor quality tracking. These functions are generally unrelated but call on similar data (people in the group). My plan was to create a MySQL database called admin which contained people, customers, locations, addresses, etc, and seperate databases for each of the other functions projectallocation, vendorquality, etc. The problem I'm seeing is that there is no clean way of creating queries across database boundaries. Does PHP support this and I'm not seeing it? Does anyone know of third party code that accomplishes this manually? Am I out in left field on my desire to setup the database structure this way? thanks, -- Mike Tallroth Engineering Supervisor Test DesignCenter Plexus Technology Group Neenah, WI mike.tallroth @plexus.com 920-751-5418 -- 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] join across databases - how to select databases?
to my knowledge this is not possible in MySQL. There are only joins at the table level. I've been curious about this in the past myself, so if i'm incorrect, i'm hoping to hear it via this post. hth jeff Moshe Weitzman [EMAIL PROTECTED]To: [EMAIL PROTECTED] ourmet.org cc: Subject: [PHP-DB] join across databases - how to select databases? 08/22/2003 11:16 AM I have looked all over the web but can't find an example *in php* for connecting to a mysql server, selecting database(s), and issueing a query which joins across databases. I already know the SQL required to achieve a multiple database query. My question is about how many calls to mysql_select_db() are required, does order matter, etc. -- 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] join across databases - how to select databases?
From: Moshe Weitzman [EMAIL PROTECTED] I have looked all over the web but can't find an example *in php* for connecting to a mysql server, selecting database(s), and issueing a query which joins across databases. I already know the SQL required to achieve a multiple database query. My question is about how many calls to mysql_select_db() are required, does order matter, etc. What have you tried? What does mysql_error() say after each attempt? I assume something like the following would work, regardless of whether/how you've called mysql_select_db() SELECT t1.column FROM database1.table1 t1 JOIN database2.table2 t2 ON t1.id = t2.id If you have something working from the MySQL command line, then it's going to work from mysql_query() with the same exact syntax. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] join across databases - how to select databases?
From: [EMAIL PROTECTED] to my knowledge this is not possible in MySQL. There are only joins at the table level. I've been curious about this in the past myself, so if i'm incorrect, i'm hoping to hear it via this post. You can stop hoping now. It is indeed possible. :) mysql select * from test1.table1; ++--+ | id | name | ++--+ | 1 | John | | 2 | Bill | | 3 | Mark | ++--+ 3 rows in set (0.00 sec) mysql select * from test2.table2; ++-+ | id | name| ++-+ | 1 | Holmes | | 2 | Foreman | | 3 | Smith | ++-+ 3 rows in set (0.00 sec) mysql select t1.name, t2.name from test1.table1 t1 join test2.table2 t2 on t1.id = t2.id; +--+-+ | name | name| +--+-+ | John | Holmes | | Bill | Foreman | | Mark | Smith | +--+-+ 3 rows in set (0.00 sec) ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] join across databases - how to select databases?
I assume something like the following would work, regardless of whether/how you've called mysql_select_db() SELECT t1.column FROM database1.table1 t1 JOIN database2.table2 t2 ON t1.id = t2.id If you have something working from the MySQL command line, then it's going to work from mysql_query() with the same exact syntax. You are indeed correct. I had a bug in my join syntax. How embarrassing. For the record, you don't even have to call mysql_select_db() at all. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] join query/relational database question
At 16:09 06/01/2003 06/01/2003, Doug Parker wrote: I have a relational database issue that I'll simplify for the sake of this post. [...] So it would be like this: id | company_id | status| company 1 46 Active Jones Inc. 2 54 InactiveBaker Inc. Using SQL the following SELECT should be the solution: SELECT projects.id, company_id, status, company FROM projects, clients WHERE projects.company_id = clients.id You can of course add any other filter in the WHERE clause using the usual AND OR NOT operators. Of course you have to embed this command in an appropriate PhP statement, e.g. using the odbc library, and having an open connection $conn to the DB: odbc_exec($conn, 'SELECT ... '); Hope this will be enough to solve your problem. Paolo Bonavoglia Cannaregio 3027/R 30121 V E N E Z I A Sito del Liceo Foscarinihttp://www.liceofoscarini.it/ Astronomia e Calendari http://digilander.iol.it/paolobona/astro/home.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] join query/relational database question
At 04:09 PM 1/6/03 -0500, Doug Parker wrote: I have a relational database issue that I'll simplify for the sake of this post. I have one table, called clients, that has an id and the company name. For example: id | company --- 46 Jones Inc. 54 Baker Inc. etc. I have another table called projects, that looks like this: id | company_id | status --- 1 46 Active 2 54 Inactive id | company_id | status| company 1 46 Active Jones Inc. 2 54 InactiveBaker Inc. SELECT id, company.company_id, status, company FROM projects LEFT JOIN Companies USING( company_id ) ORDER BY company Rick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN in embedded query
since you didnt list what type DB you are using, i assumed MYSQL. and MYSQL doesnt have sub SELECTs... :( http://www.mysql.com/doc/en/UPDATE.html the syntax lists ...WHERE where_condition... Michael Hendsbee wrote: UPDATE Emails SET Sent = 'T' WHERE ID = (SELECT Emails.ID FROM Emails RIGHT JOIN KeyWords ON Emails.ID = KeyWords.EmailsID) LIMIT 250 I am getting an error, and I am quite certain that it is my syntax -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN statement not producing expected output
Well, the first question is what's the expected output? If you're trying to get, say, title from table1 and rating from table2 and identify the books in table1 by the unique field id and in table2 by the field book_id, then this is what you should have to end up with: SELECT t1.title, t2.rating FROM table1 as t1, table2 as t2 where t1.id=t2.book_id; This is obviously a simple example, and depending on what your problem is, it may not help. But it's a good start for simple generic JOIN's. HTH Bogdan admin wrote: Okay, now this is my first try on fetching data from two tables, and frankly I'm starting to pull my hair out ;) All I want is to fetch some data from two seperate tables in my database. So far I have tried different approches, and below is what I think should work, but doesn't :( SELECT * FROM table1,table2 This I pasted into phpmyadmin, and it outputted two rows from table2 with ekstra columns at the tail of each row. These ekstra columns was just the same output from table2. Now I have searched the web, but have not found a simple solution for my problem. Any ideas? If you need more info, please let me know :) -Lasse -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN statement not producing expected output
If you're looking to do cross-table joins, I'd advise a good book/class on basic SQL or you're going to get in over your head very very quickly. SELECT * FROM table1,table2; This WILL run, but there's no where clause. Therefore it will return all of table1 matched against all of table 2. SELECT * FROM table1,table2 WHERE table1.col1 = table2.col2; will limit your result set to the match of the two tables (and is an implicit INNER JOIN call.) The SQL language is (mostly) database independant. Here's a link to the mySQL website which'll give some basic info on SELECTs. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SELECT 'Luck -Szii - Original Message - From: admin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 20, 2002 5:48 PM Subject: [PHP-DB] JOIN statement not producing expected output Okay, now this is my first try on fetching data from two tables, and frankly I'm starting to pull my hair out ;) All I want is to fetch some data from two seperate tables in my database. So far I have tried different approches, and below is what I think should work, but doesn't :( SELECT * FROM table1,table2 This I pasted into phpmyadmin, and it outputted two rows from table2 with ekstra columns at the tail of each row. These ekstra columns was just the same output from table2. Now I have searched the web, but have not found a simple solution for my problem. Any ideas? If you need more info, please let me know :) -Lasse -- 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] Join tables
Ok thanks for that info, but I guess I am ignorant to what MS Access is. Please enlighten me. Thanks Jennifer Downey Marius Ursache [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]; the best tool for building join queries is MS Access . make a db same as that you use in mysql and build the query with access. then copy/paste queries from there. to build queries in access is very simple . drag/drop and other visual ways. Jennifer Downey a écrit : Hi all, Ok first I have figured out my question on storing an image path. my question now is on joining data from two tables. wt_users has two fields (cols) called uid and pid, uid refers to the user pid refers to the users pet image_data has a field called id which refers to the image path id in image_data has the same value as id in wt_users 1 1 These two queries are what are needed to get the image to display: $pet = mysql_query(select id from wt_users where uid={$session[uid]}); $ret = mysql_query($pet); $query = select binary_data,filetype from image_data where id=$id; $result = @mysql_query($query); I'm using echo img src=\petdata.php?id= this should correspond with both id's \; to display the image how do I join the two tables so that the id's match and give the correct image? And again thanks in advance for your help Regards Jennifer Downey P.S. I have looked at the mysql manual and a few tutorials. None of them helped or worked. -- Marius Ursache (3563 || 3494) \|/ \|/ @'/ ,. \`@ /_| \__/ |_\ \__U_/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join tables
Microsoft Access: light-duty relational database with a world-class (in my opinion) GUI interface, but I don't think it's going to help you in this case. id in image_data has the same value as id in wt_users do you mean uid in wt_users? So, $id=session ID? I might set that ahead of time just to make it simpler. Therefore, you really only need one select statement. I'm no expert at this, and you may need to tweak this a little, but this should work : select tablename.fieldname, tablename.fieldname from wt_users, image_data where $id=wt_users.uid and $id=image_data.id substitute the table name and field names for tablename.fieldname, obviously. For example, wt_users.uid, image_data.id and so forth. That should get you on your way. aron Jennifer Downey [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]; Ok thanks for that info, but I guess I am ignorant to what MS Access is. Please enlighten me. Thanks Jennifer Downey Marius Ursache [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]; the best tool for building join queries is MS Access . make a db same as that you use in mysql and build the query with access. then copy/paste queries from there. to build queries in access is very simple . drag/drop and other visual ways. Jennifer Downey a écrit : Hi all, Ok first I have figured out my question on storing an image path. my question now is on joining data from two tables. wt_users has two fields (cols) called uid and pid, uid refers to the user pid refers to the users pet image_data has a field called id which refers to the image path id in image_data has the same value as id in wt_users 1 1 These two queries are what are needed to get the image to display: $pet = mysql_query(select id from wt_users where uid={$session[uid]}); $ret = mysql_query($pet); $query = select binary_data,filetype from image_data where id=$id; $result = @mysql_query($query); I'm using echo img src=\petdata.php?id= this should correspond with both id's \; to display the image how do I join the two tables so that the id's match and give the correct image? And again thanks in advance for your help Regards Jennifer Downey P.S. I have looked at the mysql manual and a few tutorials. None of them helped or worked. -- Marius Ursache (3563 || 3494) \|/ \|/ @'/ ,. \`@ /_| \__/ |_\ \__U_/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join
Thank you Beau, Appreciate that clarification. Jen Beau Lebens [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... what you are referring to actually has nothing to do with PHP Jen - that's why the manual wasn't much help :) JOIN is an SQL command, so check out the manual of your RDBMS (ie. http://www.mysql.com/doc/J/O/JOIN.html for MySQL) good luck :) Beau // -Original Message- // From: Jennifer Downey [mailto:[EMAIL PROTECTED]] // Sent: Thursday, 21 February 2002 10:47 AM // To: [EMAIL PROTECTED] // Subject: [PHP-DB] Join // // // Could someone point me in the right direction for joining // tables? I have // searched the php manual for it but only found info on joining arrays. // // If someone could point me to a code snip or a tutorial I would much // appreciate it. // // Thanks in advance // // Jen Downey // // // // -- // 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] Join
what you are referring to actually has nothing to do with PHP Jen - that's why the manual wasn't much help :) JOIN is an SQL command, so check out the manual of your RDBMS (ie. http://www.mysql.com/doc/J/O/JOIN.html for MySQL) good luck :) Beau // -Original Message- // From: Jennifer Downey [mailto:[EMAIL PROTECTED]] // Sent: Thursday, 21 February 2002 10:47 AM // To: [EMAIL PROTECTED] // Subject: [PHP-DB] Join // // // Could someone point me in the right direction for joining // tables? I have // searched the php manual for it but only found info on joining arrays. // // If someone could point me to a code snip or a tutorial I would much // appreciate it. // // Thanks in advance // // Jen Downey // // // // -- // 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] JOIN Very SLOW...
I would suggest changing COUNT(*) to COUNT(properties.PropertyID) Regards Jon -- Jon Farmer Systems Programmer, Entanet www.enta.net Tel 01952 428969 Mob 07763 620378 PGP Key available, send email with subject: Send PGP Key - Original Message - From: Martin E. Koss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 28, 2001 10:09 AM Subject: [PHP-DB] JOIN Very SLOW... The following does work but it extremely slow. $phselect = select COUNT(*) AS phTotal from properties LEFT JOIN propertydetails ON properties.PropertyID=propertydetails.PropertyID where ((propertydetails.Image1Desc='') AND (properties.PropertyType='$PropertyType')); $phresult = mysql_query ($phselect,$conID); while ($phrow = mysql_fetch_array($phresult)) { $phTotal = $phrow[phTotal]; PRINT td align=\right\$phTotal/td\n; } // end WHILE Is there an obvious problem here and can anyone help? Martin E. Koss M: 07946-706459 E: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- 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] JOIN Very SLOW...
You use LEFT JOIN so your query is optimized. Probably your tables are big, you don't have many MB of RAM. When you have big RAM, I think that tables reside in memory and join of 5 tables every 600,000 rows is done for 4-5s. May be you have to increase some of the buffers sizes of mysql. Regards, Andrey Hristov - Original Message - From: Martin E. Koss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 28, 2001 12:09 PM Subject: [PHP-DB] JOIN Very SLOW... The following does work but it extremely slow. $phselect = select COUNT(*) AS phTotal from properties LEFT JOIN propertydetails ON properties.PropertyID=propertydetails.PropertyID where ((propertydetails.Image1Desc='') AND (properties.PropertyType='$PropertyType')); $phresult = mysql_query ($phselect,$conID); while ($phrow = mysql_fetch_array($phresult)) { $phTotal = $phrow[phTotal]; PRINT td align=\right\$phTotal/td\n; } // end WHILE Is there an obvious problem here and can anyone help? Martin E. Koss M: 07946-706459 E: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- 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] JOIN - Not working.
Sorry, that was a bit hasty. I was just trying to get the method across quickly. SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails; would count the number of entries where Image1Desc!=default in PropertyDetails. This can be used in combination with a LEFT JOIN or a WHERE or a GROUP BY or anything else to perform the operation you're interested in, like: SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails WHERE 'fieldname'='condition'; or SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails WHERE 'fieldname'='condition'; or SELECT SUM(IF(PropertyDetails.Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from Properties LEFT JOIN PropertyDetails ON Properties.PropertyID=propertydetails.PropertyID WHERE Properties.PropertyType='$PropertyType'; Add a GROUP BY if you need one. Cheers, db Martin E. Koss wrote: Ehm...? You've lost me. Perhaps a real-world example may have explained it better. All the repeated 'whatever' kind of makes it a bit unexplained. Martin. -Original Message- From: Daniel Barton [mailto:[EMAIL PROTECTED]] Sent: 28 November 2001 4:14 PM To: Martin E. Koss Subject: Re: [PHP-DB] JOIN - Not working. Try: select sum(if(whatever=whatever,1,0)) as whatever from wherever where whatever=whatever; This is a pretty fast command for simple cross-tabs (outperforms left join?) and is easier to understand for a quick fix. You can also write a loop that creates a query with multiple rows like: select sum(if(whatever=whatever,1,0)) as whatever1 select sum(if(whatever=whatever,1,0)) as whatever2 select sum(if(whatever=whatever,1,0)) as whatever3 select sum(if(whatever=whatever,1,0)) as whatever4 from wherever where whatever=whatever group by whatever; yay! -db Martin E. Koss wrote: I have read the parts of the manual(s) but am just not grasping something... I have 2 tables, one holds the Property ID etc., and the other holds all the Property Details. They are connected by a field called 'PropertyID' which is a unique ID that appears in both records. I want to count the number of records in PropertyDetails that have an entry in a field called 'Image1Desc', but I need to do this based on a previously chosen field from Property called 'PropertyType'. However, the code below is simply not giving the results I need: $phselect = select COUNT(*) AS phTotal from properties LEFT JOIN propertydetails ON properties.PropertyID=propertydetails.PropertyID where ((propertydetails.Image1Desc='') AND (properties.PropertyType='$PropertyType')); $phresult = mysql_query ($phselect,$conID); while ($phrow = mysql_fetch_array($phresult)) { $phTotal = $phrow[phTotal]; PRINT td align=\right\$phresult/td\n; } // end WHILE Is there an obvious problem here and can anyone help? Martin E. Koss M: 07946-706459 E: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- -- Dan Barton Terrestrial Program Biologist Asst. Data Manager Point Reyes Bird Observatory http://www.prbo.org [EMAIL PROTECTED] [EMAIL PROTECTED] -- -- Dan Barton Terrestrial Program Biologist Asst. Data Manager Point Reyes Bird Observatory http://www.prbo.org [EMAIL PROTECTED] [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] JOIN operations
There isn't the field table_name.ID_city!!! because you didn't ask for it. You asked for: table_name.name table_city.ID table_city.city -Original Message- From: Carlo Loiudice [mailto:[EMAIL PROTECTED]] Sent: Friday, November 09, 2001 4:02 PM To: PHP DB Subject: [PHP-DB] JOIN operations Hi, I'm preforming a join between 2 Mysql tables in this way: table_name: ID,name,ID_city table_city: ID,city when I query this: SELECT table_name.name,table_city.ID,table_city.city FROM table_name,table_city WHERE table_name.ID_city=table_city.ID The result is a new table with this fields: result_table: name,city. There isn't the field table_name.ID_city!!! Here's the question: I need also this field ID_city because there's a function that builds a list box with all the cities, and search for a html hidden input named ID_city (like the table field) to eventually highlight that entry. can someone help me? is there a way to select also the ID_city field without break the join mechanism ? Carlo __ Abbonati a Yahoo! ADSL con Atlanet! Naviga su Internet ad alta velocità, e senza limiti di tempo! Per saperne di più vai alla pagina http://adsl.yahoo.it -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] JOIN operations
select n.name, c.city, c.ID from table_name n, table_city c where c.ID = n.ID_city No sweat. -Lorenzo -Original Message- From: Carlo Loiudice Sent: Fri 11/9/2001 5:01 PM To: PHP DB Cc: Subject: [PHP-DB] JOIN operations Hi, I'm preforming a join between 2 Mysql tables in this way: table_name: ID,name,ID_city table_city: ID,city when I query this: SELECT table_name.name,table_city.ID,table_city.city FROM table_name,table_city WHERE table_name.ID_city=table_city.ID The result is a new table with this fields: result_table: name,city. There isn't the field table_name.ID_city!!! Here's the question: I need also this field ID_city because there's a function that builds a list box with all the cities, and search for a html hidden input named ID_city (like the table field) to eventually highlight that entry. can someone help me? is there a way to select also the ID_city field without break the join mechanism ? Carlo __ Abbonati a Yahoo! ADSL con Atlanet! Naviga su Internet ad alta velocità, e senza limiti di tempo! Per saperne di più vai alla pagina http://adsl.yahoo.it -- 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] Join Sentences
No, I know how manage the functions I said for exemple : If I need insert or delete data in two tables, and one of them returns error, the data stay inconsistent. I know that languages like Oracle have sentences ROLLBACK, but I work in Mysql and I think this not works very well. A acuse of it, I ask me if PHP can control this for a group of sentences, in other situations, like add un global array element, or similar. If the function starts, modify this global variable, and after returns error, it's necessary rollback the actions made. You understand now ?? Thank you a lot. Gaby http://www.atrivia.com - Original Message - From: Jorge Santos [EMAIL PROTECTED] To: Gaby [EMAIL PROTECTED] Cc: PHP-DB [EMAIL PROTECTED] Sent: Friday, February 09, 2001 2:17 PM Subject: Re: [PHP-DB] Join Sentences Hi Gaby, Your best bet is to probably place the statements you want into a function. Then, when the function is called if any one statement fails you simply return from the function with an error code. Hope this answer helps you... -- (No Espanol mas Portugues) O melhor a fazer e para por as declaracoes numa funcao. Depois quando chamar a funcao, se alguma declaracao erra, regresse da funcao com um codico proprio. Espero que esta resposta lhe pode ajudar. Later, Jorge [EMAIL PROTECTED] - Original Message - From: Gaby [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 09, 2001 7:59 AM Subject: [PHP-DB] Join Sentences Hi, ( Excuse me for my english ) I wish execute in PHP some consecutives sentences , but if one of them returns un error, the program must'n execute anyone. I need one set of sentences that executes the code like one. I don't know if I explain ... Thanks a lot. --- Por si acaso alguien me entiende mejor en espaol : Lo que me gustaria es ejecutar un grupo de sentencias como si fueran solo una -- 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]