Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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!
0
Cree
Asked:
Cree
  • 4
  • 2
2 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now