Help writing SQL statement in PHP script

This might be in the wrong group, but...

Here is an example of my data:

entry_id cat_id
1 20
2 25
3 30
4 25
5 35
6 25
2 30
2 35
3 35

As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35

I have captured the cat_id's 30 and 35 with my script, so I need all
entry_id's that belong to BOTH cat_id 30 and 35.

I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
'35' but obviously that is incorrect.

Can someone help? Thanks...

Chuck Cheeze wrote:
> This might be in the wrong group, but...
>
> Here is an example of my data:
>
> entry_id cat_id
> 1 20
> 2 25
> 3 30
> 4 25
> 5 35
> 6 25
> 2 30
> 2 35
> 3 35
>
> As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35
>
> I have captured the cat_id's 30 and 35 with my script, so I need all
> entry_id's that belong to BOTH cat_id 30 and 35.
>
> I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
> '35' but obviously that is incorrect.
>
> Can someone help? Thanks...

There's probably a nicer way but you can do something like this to get
through:

$q = "SELECT `entry_id`, `cat_id` FROM `myTable` WHERE `cat_id` = '30'
OR `cat_id` = > '35';
$res = $mysql->getRes();
while( $row = $res->fetch() ) {
if(30 == $row['cat_id']) $myBase[$row['entry_id']] &= 0x01;
elseif(35 == $row['cat_id']) $myBase[$row['entry_id']] &= 0x02;
}

foreach( $myBase as $key => $flags ) if( 0x03 == $flags ) $myEntries[] =
$key;


Cheers,
CJW

CJ Willcock wrote:
> Chuck Cheeze wrote:
>> This might be in the wrong group, but...
>>
>> Here is an example of my data:
>>
>> entry_id cat_id
>> 1 20
>> 2 25
>> 3 30
>> 4 25
>> 5 35
>> 6 25
>> 2 30
>> 2 35
>> 3 35
>>
>> As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35
>>
>> I have captured the cat_id's 30 and 35 with my script, so I need all
>> entry_id's that belong to BOTH cat_id 30 and 35.
>>
>> I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
>> '35' but obviously that is incorrect.
>>
>> Can someone help? Thanks...
>
> There's probably a nicer way but you can do something like this to get
> through:
>
> $q = "SELECT `entry_id`, `cat_id` FROM `myTable` WHERE `cat_id` = '30'
> OR `cat_id` = > '35';
> $res = $mysql->getRes();
> while( $row = $res->fetch() ) {
> if(30 == $row['cat_id']) $myBase[$row['entry_id']] &= 0x01;
> elseif(35 == $row['cat_id']) $myBase[$row['entry_id']] &= 0x02;
> }
>
> foreach( $myBase as $key => $flags ) if( 0x03 == $flags ) $myEntries[] =
> $key;
>
>
> Cheers,
> CJW

Whoops!

Read SQL statement:


$q = "SELECT `entry_id`, `cat_id` FROM `myTable` WHERE `cat_id` = '30'
OR `cat_id` = '35';

Hi,

You could also do:

select entry_id from tbl where cat_id
in (30, 35) group by entry_id having
count(entry_id) = 2;

Assuming the table is duplicate-free:

create unique index ent_cat on tbl
(entry_id, cat_id)

Regards,

John Peters

On May 11, 2:06 am, Chuck Cheeze <chadcrow...@gmail.com> wrote:
> This might be in the wrong group, but...
>
> Here is an example of my data:
>
> entry_id cat_id
> 1 20
> 2 25
> 3 30
> 4 25
> 5 35
> 6 25
> 2 30
> 2 35
> 3 35
>
> As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35
>
> I have captured the cat_id's 30 and 35 with my script, so I need all
> entry_id's that belong to BOTH cat_id 30 and 35.
>
> I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
> '35' but obviously that is incorrect.
>
> Can someone help? Thanks...

Chuck Cheeze wrote:
> This might be in the wrong group, but...
>
> Here is an example of my data:
>
> entry_id cat_id
> 1 20
> 2 25
> 3 30
> 4 25
> 5 35
> 6 25
> 2 30
> 2 35
> 3 35
>
> As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35
>
> I have captured the cat_id's 30 and 35 with my script, so I need all
> entry_id's that belong to BOTH cat_id 30 and 35.
>
> I tried "Select entry_id from myTable where cat_id = '30' and cat_id =
> '35' but obviously that is incorrect.
>
> Can someone help? Thanks...
>

This isn't a PHP question - it's a database question. You need a group
for your database (i.e. if it's MySQL, comp.databases.mysql).

You'll get good answers there because that's where the SQL experts hang
out. The answers posted here so far are incorrect.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> You'll get good answers there because that's where the SQL experts hang
> out. The answers posted here so far are incorrect.

Pay attention to the posts. Peter's solution will work.

You can learn about grouping queries at this tutorial page:
http://www.w3schools.com/sql/sql_groupby.asp

On May 10, 11:06 pm, Chuck Cheeze <chadcrow...@gmail.com> wrote:
>
> Here is an example of my data:
>
> entry_id cat_id
> 1 20
> 2 25
> 3 30
> 4 25
> 5 35
> 6 25
> 2 30
> 2 35
> 3 35
>
> As you can see, entry_id's 2 and 3 both belong to cat_id 30 and 35
>
> I have captured the cat_id's 30 and 35 with my script, so I need all
> entry_id's that belong to BOTH cat_id 30 and 35.

SELECT t1.entry_id
FROM theTable AS t1 LEFT JOIN theTable AS t2
ON t1.entry_id = t2.entry_id
WHERE t1.cat_id = 30 AND t2.cat_id = 35

Cheers,
NC

On 12 May, 02:36, Mike Lahey <mikey6...@yahoo.com> wrote:
> Jerry Stuckle wrote:
> > You'll get good answers there because that's where the SQL experts hang
> > out. The answers posted here so far are incorrect.
>
> Pay attention to the posts. Peter's solution will work.

It may work, but that does not make it the "correct" way to do it.

On May 12, 9:20 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 12 May, 02:36, Mike Lahey <mikey6...@yahoo.com> wrote:
>
> > Pay attention to the posts. Peter's solution will work.
>
> It may work, but that does not make it the "correct" way to do it.

Oh no, the answer is correct, but there are alternatives. What you
must to realize is if the OP decides to match not 2, but 30 or 500
category IDs, you must use group by. This is more universal solution.

--
"Man who lives in glass house should change clothes in basement."

Mike Lahey wrote:
> Jerry Stuckle wrote:
>> You'll get good answers there because that's where the SQL experts
>> hang out. The answers posted here so far are incorrect.
>
> Pay attention to the posts. Peter's solution will work.
>
> You can learn about grouping queries at this tutorial page:
> http://www.w3schools.com/sql/sql_groupby.asp
>

Not necessarily. What happens if he has two entries with (2,30)? It
will fail.

Nothing in the description of the problem prohibits such an occurrence.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================