Re: [PHP-DB] sql statement - complex order by
Bryan wrote: SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... select * from productgroup where groupid = $productid order by (label = 'Cats') desc, title And I do hope you're properly validating and escaping $productid. -Stut -- http://stut.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
I think there's one small piece of data I left out. I'm working with php/mssql, no mysql. I'll move to mysql when I get everything else built. Mssql 2000 doesn't seem to like the = sign in the order by clause. It looks like both of you so far have come up with the same syntax though so it must work on mysql. ;-) Thanks guys... [EMAIL PROTECTED] wrote: Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
I think there's one small piece of data I left out. I'm working with php/mssql, not mysql. I'll move to mysql when I get everything else built. Mssql 2000 doesn't seem to like the = sign in the order by clause. It looks like both of you so far have come up with the same syntax though so it must work on mysql. ;-) Thanks guys... [EMAIL PROTECTED] wrote: Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement
PHPDiscuss - PHP Newsgroups and mailing lists wrote: Hello everybody, I'm building a small application and I have trouble passing a POST variable form one page to another inside the SQL statement. The query displayed below works great without the .$_POST['CompanyName']. $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY CompanyName ASC; you need to quote the string (company name) in the actual sql, compare the following 2 statements (lets assume companyname is 'IBM'): WRONG (this is what you are doing now): SELECT CompanyID, CompanyName,CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=IBM ORDER BY CompanyName ASC RIGHT: SELECT CompanyID, CompanyName,CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName='IBM' ORDER BY CompanyName ASC there may be times when the companyname contains a single quote - that will break your query unless you escape the single quote in the name before placing the string into the query string... mysql.com can tell you more. But it messes up if I include it because the first is considered as the end of the previous one and so on, so the code gets messed up. I'll really appreciate any/all help! Have you all an excellent year! Jorge -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement syntaxis
missing the singles quotes around the company name text element $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName='.$_POST['CompanyName'].' ORDER BY CompanyName ASC; bastien From: [EMAIL PROTECTED] (PHPDiscuss - PHP Newsgroups and mailing lists) To: php-db@lists.php.net Subject: [PHP-DB] SQL statement syntaxis Date: 6 Jan 2005 19:12:16 - Hello everybody, I'm building a small application and I have trouble passing a POST variable form one page to another inside the SQL statement. The query (displayed below) works great without the .$_POST['CompanyName']. $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY CompanyName ASC; But it messes up if I include it because the first is considered as the end of the previous one and so on. So the code gets messed up. Any help will be greatly appreciated! Have everybody a wonderful 2005! Jorge -- 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] SQL statement
Jason, can you please turn off the return receipts on emails you send to the list. it's bloody annoying to have 'The Sender wishes to be notified' popup messages everytime I read one of your emails (and, alas, I don't have the skill to hack the return receipt crap right out of Tbird). BTW your not the only one that has it turned on - so this goes to the rest of you as well :-) cheers! Jason Walker wrote: First off - $_POST['CompanyName'] is valid, right? ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement
Hi, To stop the return receipt dialog appearing in Tbird... Tools... Options... Advanced... Return Receipt and select Never send a return receipt. Or you can choose some of the other selections. graeme Jochem Maas wrote: Jason, can you please turn off the return receipts on emails you send to the list. it's bloody annoying to have 'The Sender wishes to be notified' popup messages everytime I read one of your emails (and, alas, I don't have the skill to hack the return receipt crap right out of Tbird). BTW your not the only one that has it turned on - so this goes to the rest of you as well :-) cheers! Jason Walker wrote: First off - $_POST['CompanyName'] is valid, right? ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement
First off - $_POST['CompanyName'] is valid, right? Can you do something like this?: if (isset($_POST['CompanyName'])){ $sqlCompanyName = $_POST['CompanyName']; } else { return them back to the form, or something? } $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName='$sqlCompanyName' ORDER BY CompanyName ASC; Also, what datatype is CompanyName? If it is varchar - or really anything else - I have had better look single quote encapsulation on the VALUE portion of the query (company.CompanyName='VALUE' vs. company.CompanyName=VALUE) Not knowing the datatypes may make this an irrelevant point though. -Original Message- From: PHPDiscuss - PHP Newsgroups and mailing lists [mailto:[EMAIL PROTECTED] Sent: Thursday, January 06, 2005 12:09 PM To: php-db@lists.php.net Subject: [PHP-DB] SQL statement Hello everybody, I'm building a small application and I have trouble passing a POST variable form one page to another inside the SQL statement. The query displayed below works great without the .$_POST['CompanyName']. $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY CompanyName ASC; But it messes up if I include it because the first is considered as the end of the previous one and so on, so the code gets messed up. I'll really appreciate any/all help! Have you all an excellent year! Jorge -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Statement
Jimi, PHP does not return an error because it knows nothing about valid sql. It's just knows if it's a valid PHP statement. (which it is because you've got the 's in the right place and a ; at the end. ) :) It's up to MySQL to return an error. As to your statement. 1: It's easier and valid in PHP to write it: $sqlwrk = SELECT pk_phone_reports, SUM(calls) AS total_calls, date, calls , fk_ph_num FROM phone_reports WHERE (pk_phone_number = {fk_phone}) AND (date BETWEEN '{$my_startdate}' AND '$my_enddate') GROUP BY pk_phone_reports, fk_ph_num, date, calls; In reformatting the string I found a couple of PHP things that are probably what's tripping you up. PHP used the . as a string concatonator. You were missing several of them. Notice that I remove them all. Because we enclosed the entire statement in a sing pair, we can use {$variable} for substitution. Makes life a lot easier when building sql statements. Also, I removed all the back-tiks. Not because they were wrong but they annoy my and in 99% of the cases are not necessary. Oh and welcome to PHP/MySQL. I hope you'll find the language easy and the people friendly. Finally, a could of things that will make life easier for you if you are going to be doing much database work in PHP/MySQL. http://php.weblogs.com/ This is the best database abstraction layer I've found. Even if you only use MySQL, it's worth the investment in time to learn it. (mainly for the debug feature.) www.sqlyog.com Best FE for MySQL on Windows I've ever found. It's $49.00 (I think...I forget) but it's worth it. It's got it's bugs but overall it's a killer tool. http://www.fabforce.net/dbdesigner4/ A killer, open source tool for designing databases. If you are used to the commercial tools costing $4k+ then you'll feel right at home with this. (It's my understanding that MySQL has purchased this project, but I may have my story wrong.) Finally, you've already found the greatest tool for debugging MySQL/php, the lists. See ya round. =C= : : Cal Evans : Evans Internet Construction Company : 615-360-3385 : http://www.eicc.com : Building web sites that build your business : Thompson, Jimi wrote: For some background, I've been tasked with building a marketing tracking application by the PHB's who think that being able to write SQL means you can code. I know how to get data into a database and I can do thing with it once it's in there, but this is one of my first attempts at extracting anything remotely end-user-ish. Since the only server I can get is an old cobalt RAQ 2, the only database I can run is MySQL. Im not terribly familiar with MySQL (spent more time working with commercial databases) and Im a complete newbie at PHP, so please dont flame me yet Im not even sure what information Ill need to provide you so here goes: Platform Red Hat 9.0 Linux on a BogoMIPS CPU PHP Version - 4.3.3 Apache Version - 1.3.28 MySQL Version - 4.0.14 Heres my SQL statement which works fine from a DBA perspective (meaning that I can execute it from the command line against the database and obtain the desired results), but Im obviously missing something in the syntax in converting this to an acceptable PHP SQL statement. I know that I can connect to the database and can extract other records, but I keep getting unable to parse error message and dont know enough to know which thing Im doing is wrong. SELECT phone_reports.pk_phone_reports, SUM(phone_reports.calls) AS total_calls, phone_reports.fk_ph_num, phone_reports.`date`, phone_reports.calls FROM phone_reports WHERE (phone_reports.fk_ph_num = 1) AND (phone_reports.`date` BETWEEN '2004/05/17' AND '2004/07/05') GROUP BY phone_reports.pk_phone_reports, phone_reports.fk_ph_num, phone_reports.`date`, phone_reports.calls Heres the PHP SQL statement built from the SQL statement above ?php if ($fk_phone != NULL) { $sqlwrk = SELECT `pk_phone_reports`, `date`, `calls` , `fk_ph_num` FROM `phone_reports`; $sqlwrk .= WHERE `pk_phone_number` = . $fk_phone; $rswrk = mysql_query($sqlwrk); if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) { echo $rowwrk[number]; } @mysql_free_result($rswrk); } ? This seems to work ok, but doesnt return any results (which I expected) but it does parse! So then I try do this ?php if ($fk_phone != NULL) { $sqlwrk = SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`, `date`, `calls` , `fk_ph_num` FROM `phone_reports`; $sqlwrk .= WHERE `pk_phone_number` = . $fk_phone; $rswrk = mysql_query($sqlwrk); if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) { echo $rowwrk[number]; } @mysql_free_result($rswrk); } ? Note that this shouldnt
Re: [PHP-DB] SQL Statement
Thompson, Jimi wrote: So then I try do this ?php if ($fk_phone != NULL) { $sqlwrk = SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`, `date`, `calls` , `fk_ph_num` FROM `phone_reports`; $sqlwrk .= WHERE `pk_phone_number` = . $fk_phone; $rswrk = mysql_query($sqlwrk); if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) { echo $rowwrk[number]; } @mysql_free_result($rswrk); } ? Note that this shouldnt work since it isnt a valid SQL statement. I'm not sure why PHP doesn't return some kind of an error message. PHP does return an error message, you're just not displaying it. $rswrk = mysql_query($sqlwrk) or die(mysql_error()); $sqlwrk .= WHERE (`pk_phone_number` = . $fk_phone) AND (`date` BETWEEN '$my_startdate' AND '$my_enddate'); Which brings me to my lovely parse error Parse error: You're not concatinating your string correctly. $sqlwrk .= WHERE (`pk_phone_number` = . $fk_phone . ) AND (`date` BETWEEN ' . $my_startdate . ' AND ' . $my_enddate . '); or $sqlwrk .= WHERE (`pk_phone_number` = $fk_phone) AND (`date` BETWEEN '$my_startdate' AND '$my_enddate'); -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement - PHP/mySQL - brain fart
Gurhan: snip I was wondering if you'd wanna use temporary tables to accomplish it.. Actually, another person on this list came up with the following: SELECT s1.site_id FROM site_category AS s1 JOIN site_category AS s2 WHERE s1.category_id=10 AND s2.category_id=12 AND s1.site_id=s2.site_id It worked like a charm! Thanks to all who replied! Steve You may wanna do: CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE category_id=$category_id_1; ok this will give us the records that matches $category_id_1 in category_id field put them into a temporary table called tmp1 Then do: CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE category_id=$category_id_2; and this will give us the records matching $category_id_2 in category_id field and put them into a temporary table called tmp2.. Now you have 2 temporary tables, tmp1 and tmp2 as shown below: tmp1: +--+--+--+ | sci | si | ci | +--+--+--+ |1 |2 | 10 | |4 |4 | 10 | +--+--+--+ tmp2: +--+--+--+ | sci | si | ci | +--+--+--+ |3 |4 | 12 | |5 |5 | 12 | +--+--+--+ Now you can use use join syntax to find the si value thats common to the both tables... select * from tmp1, tmp2 where tmp1.si=tmp2.si; Does this work for you?? Gurhan -Original Message- From: Summit [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 9:22 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] TIA, Summit There is no such thing as a stupid person - there are only those who choose not to learn! Summit - [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- -- Peak to Peak Trail and Wilderness Links Steve Fry - LinkKeeper - [EMAIL PROTECTED] http://www.peaktopeak.net -- --
Re: [PHP-DB] SQL statement - PHP/mySQL - brain fart
On Tuesday 19 March 2002 10:22, Summit wrote: For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] SELECT sc.* FROM site_category sc WHERE sc.category_id = $category_id_1 AND sc.category_id = $category_id_2 Or am I missing something? -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Let's just be friends and make no special effort to ever see each other again. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement - PHP/mySQL - brain fart
ok it is 1:37 am here and i have been up since 6:00 am (the day before) and have to be awake ths mornign again at 6:00 am so if I make any mistakes trying to answer your question, please take it easy:) I was wondering if you'd wanna use temporary tables to accomplish it.. You may wanna do: CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE category_id=$category_id_1; ok this will give us the records that matches $category_id_1 in category_id field put them into a temporary table called tmp1 Then do: CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE category_id=$category_id_2; and this will give us the records matching $category_id_2 in category_id field and put them into a temporary table called tmp2.. Now you have 2 temporary tables, tmp1 and tmp2 as shown below: tmp1: +--+--+--+ | sci | si | ci | +--+--+--+ |1 |2 | 10 | |4 |4 | 10 | +--+--+--+ tmp2: +--+--+--+ | sci | si | ci | +--+--+--+ |3 |4 | 12 | |5 |5 | 12 | +--+--+--+ Now you can use use join syntax to find the si value thats common to the both tables... select * from tmp1, tmp2 where tmp1.si=tmp2.si; Does this work for you?? Gurhan -Original Message- From: Summit [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 9:22 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] TIA, Summit There is no such thing as a stupid person - there are only those who choose not to learn! Summit - [EMAIL PROTECTED] -- 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] SQL statement - PHP/mySQL - brain fart
Well it's sorta' simple once you get your mind around this - you need two tables but you've only got one. And there's no OR, you need both to be true for a site_id ... The query would be easy if you actually had two tables so we'll search off of the same table twice giving it different names each time (sc1 and sc2). SELECT sc1.* FROMsite_category sc1, site_category sc2 WHERE sc1.category_id = $category_id_1 AND sc2.category_id = $category_id_2 AND sc1.site_id = sc2.site_id DISTINCT and such are not necessary unless it's possible you'll have duplicate rows (and you'd mind getting them back multiple times). I know this will work in all the big ones (Oracle, MS SQL, IBM) but I'm less sure about MySQL as I don't use it all that much. This is standard SQL. Good Luck, Frank On 3/18/02 10:53 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From: Summit [EMAIL PROTECTED] Date: Mon, 18 Mar 2002 19:22:22 -0700 To: [EMAIL PROTECTED] Subject: SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL STATEMENT?????
alter table remove column_name Or something along those lines. Hope that helps a little bit. Wesley Choate Daymark Group Programmer / Web Developer Phone: (501)-968-4038 X2204 Email: [EMAIL PROTECTED] Web: www.daymarkgroup.com -Original Message- From: Scott Fletcher [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, February 28, 2001 7:50 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL STATEMENT? Hi! I am looking for a SQL Statement that would allow me to remove a column from the table in the database. Just give me what you know and I'll check out the SQL Reference I have for the database. Don't worry about what type of database do I have, that's what hte SQL Reference is for. (It came with this database software). Thanks, Scott -- 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]