MYSQL PHP Query subquery issue

I'm trying to add a subquery to a query that if  A.owner_id = 0 then all numbers but if A.owner_id > 0 then just use the actual A.owner_id.  I'm writing this in PHP so using quotes are confusing at best.. especially since I'm using a concat in the query.

this is what I have now.

 $query =  'SELECT concat(A.firstName, " ", A.lastName) as Name, A.emailAddress, B.co_name from administrators A join owners B on A.owner_id = B.id where A.owner_id (select case where A.owner_id = 0 then > 0 else = $ownerid) ';

Open in new window


without the where portion, the following works fine.

 $query =  'SELECT concat(A.firstName, " ", A.lastName) as Name, A.emailAddress, B.co_name from administrators A join owners B on A.owner_id = B.id';

Open in new window


but i really need the  Where portion.  

any help? Thanks!
CreeAsked:
Who is Participating?
 
GaryCommented:
Would it not be easier to just build the case in php and append the sql

$mycase= ($ownerid =0 ?  ">0"  :  "=" . $ownerid);

$query =  'SELECT concat(A.firstName, " ", A.lastName) as Name, A.emailAddress, B.co_name from administrators A join owners B on A.owner_id = B.id where A.owner_id " . $mycase

Didn't see your comment Julian
0
 
Julian HansenCommented:
that if  A.owner_id = 0 then all numbers
Not sure what you mean by all numbers?

Can you give some sample results with both cases - need to visualise this?
0
 
CreeAuthor Commented:
$ownerid can be any number.

What I want to do in my where clause is not only determine a number but an operator as well.

So if $ownerid is = 0 then I want the rest of the where statement to be "< 0"  cause I want the where clause to be where A.owner_id > 0

however, it A.owner_id is anything other that 0, i want the where clause to be
where A.owner_id = $ownerid

you just opened my eye to a logic issue.. i meant the code to be

$query =  'SELECT concat(A.firstName, " ", A.lastName) as Name, A.emailAddress, B.co_name from administrators A join owners B on A.owner_id = B.id where A.owner_id (select case where $ownerid = 0 then > 0 else = $ownerid) ';

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Julian HansenCommented:
Ok so why not have a condition in your PHP script that based on owner id you construct a different query?

Struggling to see why this needs to be a subquery solution?
0
 
CreeAuthor Commented:
You are both right.. I should just do an if statement on my php .  Thanks..
0
 
CreeAuthor Commented:
I've been programming all day and wasn't thinking of just having php do it for me.  I should take breaks more often..<br /><br />Thank you again.
0
 
CreeAuthor Commented:
This is what I came up with:
if ($_SESSION['ownerid'] == 0){ $operator = '>=';}else {$operator = '=';};
               
                 $query =  "SELECT concat(A.firstName, ' ', A.lastName) as Name, A.emailAddress, B.co_name from administrators A join owners B on A.owner_id = B.id where A.owner_id $operator $ownerid";

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.