Re: [PHP-DB] sql statement - complex order by

2007-07-02 Thread Stut

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

2007-07-02 Thread tg-php
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

2007-07-02 Thread Bryan
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

2007-07-02 Thread Bryan
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

2005-01-07 Thread Jochem Maas
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

2005-01-07 Thread Bastien Koert
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

2005-01-07 Thread Jochem Maas
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

2005-01-07 Thread graeme
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

2005-01-06 Thread Jason Walker

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

2004-05-18 Thread Cal Evans
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

2004-05-18 Thread John W. Holmes
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

2002-03-19 Thread Steve Fry

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

2002-03-18 Thread Jason Wong

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

2002-03-18 Thread Gurhan Ozen

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

2002-03-18 Thread Frank Flynn

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?????

2001-02-28 Thread Wesley Choate

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]