[SOLVED] Undefined Index problem with SELECT *, (SELECT COUNT(*) ...) FROM ... syntax

[baphomet]

Dear All,

 

I have the following problem to which the solution must be fairly easy but I can't get my head round it at the moment. Perhaps someone could point me to the starting point? Would be fantastic!

 

I have two tables: depend, table

 

table:

a_idnamedesc

aaaaaA A

bbbbbB B

cccccC C

 

depend:

a_d_id

num

a21

a32

b43

c54

c65

c76

 

My query:

SELECT *, (SELECT COUNT(*) AS numcount FROM depend WHERE (a_d_id = a_id))
FROM table
WHERE a_id = 'c';

 

Theoretically (at least in my mind), this would have the following table as result as there are 3 lines in depend where a_d_id = a_id (i.e. c = c):

a_id

name

desc

numcount

cccccC C3

 

Now I tried to present the results via PHP as follows:

 

echo "a_id: ";
print $result['a_id'];
echo "<br>a_name: ";
print $result['name'];
echo "<br>a_desc: ";
print $result['desc'];
echo "<br>count: ";
print $result['numcount'];

 

Expecting a result like this:

 

a_id: c
a_name: cccc
a_desc: C C
count: 3

 

But what I got was:

 

a_id: c
a_name: cccc
a_desc: C C
count: Undefined index: numcount in file.php on line XXX

 

Can anybody please stoop my nose into my thinking error?

 

A million thanks in advance!

Steve

[baphomet]

p.s.:

# Server version: 5.0.27-community-log

# Protocol version: 10

 

Sorry, forgot it the first time round... 

[baphomet]

Solved!

 

The alias should be for the whole expression in brackets as this is the new col.

 

SELECT *, (SELECT COUNT(*) FROM depend WHERE (a_d_id = a_id)) AS numcount