Test if MySQL row exists inside of query

Question

I am trying to create an SQL query that compares two databases with each other, however also will not result in a query failure if one of the database rows has not been created elsewhere yet.

Here's what I've gotten so far (a bit of pseudo code)

$Query="SELECT * FROM campaigns,statistics WHERE statistics.dataDate = '$todaysDate' OR statistics.dataDate DOES NOT EXIST AND where campaigns.campaignName = 'example'";

The "DOES NOT EXIST" part is where I need the help, how can I create a query that allows the query to continue with more checks if the row has not been created yet?

Thanks very much for any suggestions! I've taken a look around and can't find a solution that checks if the row doesn't exist inside of the query (will be needed for when the query is built out a bit further)

Thanks again,

Ash

edit:

To clear up any confusion here's a second example:

`$Query="SELECT * FROM campaigns,statistics WHERE campaigns.campaignName = '$randomVar' AND (campaigns.budget > statistics.budget OR statistics.budget DOES NOT EXIST )";`

I have created this example showing that if the budget does not exist (we would treat it as 0) and still return the results because the budget field hasn't been created yet elsewhere in the platform. If I run the query without this extra "OR" it will look something like this:

    `$Query="SELECT * FROM campaigns,statistics WHERE campaigns.campaignName = '$randomVar' AND (campaigns.budget > statistics.budget)";`

The problem with this is that in my platform so far the statistics.budget is not always created before this query is ran, therefor if it doesn't have a row I'm happy for the query to treat it as 0

Thanks!

Ash

Answer 1

What I understand from what you were asking is that you want to check if the Query return a results or not. If that's what you were asking this is the way to check if it is empty or if there is results were returned from the Query.

To check if the Query returns rows you can use Empty() function.

     $Query="SELECT * FROM campaigns,statistics WHERE statistics.dataDate = '$todaysDate' OR statistics.dataDate DOES NOT EXIST AND where campaigns.campaignName = 'example'";

 if(empty($Query))
    {
      // You can create any Query here , so when the $Query is empty and no data were returned Anything here is going to execute , you can create fields or anything you want . 
    }

  else 
    {
       // This is the will execute when there is returned data in the $Query , Its optional this .
   }

Answer 2

First your OR and AND code is muddled. You should definitely put some () to make it clean. Also you don't have a connection between your two databases so I made one up. Try This

$Query="SELECT * FROM campaigns LEFT OUTER JOIN statistics WHERE
(statistics.dataDate = '$todaysDate' OR statistics.dataDate IS NULL ) AND 
campaigns.campaignName = 'example' AND 
campaigns.campaignName=statistics.campaignName";

Answer 3

Based on this example you provide:

SELECT *
FROM campaigns,statistics
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget DOES NOT EXIST)

First, explicitly define your JOIN:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget DOES NOT EXIST)

(Or whatever relational join makes sense for these tables.) Explicit joins are going to take a lot of the guesswork out of how your data is related.

If you want records where statistics.budget is NULL then you would do something like this:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget IS NULL)

Or if you want to further narrow it to only those records where campaigns.budget has a value at all, something like this:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR (statistics.budget IS NULL
             AND campaigns.budget IS NOT NULL))

When a value in a record "does not exist" that means the value is NULL. So you can use IS NULL and IS NOT NULL to check for the presence/absence of that case.

---

we would treat it as 0

That would take place in the SELECT clause. First, explicitly define the columns you want:

SELECT
    campaigns.aColumn,
    campaigns.anotherColumn,
    statistics.someOtherColumn

... and so on. Once you have that, you can define a clause to return a given column as a given value if it meets a certain condition. For example, you can return statistics.budget as-is if it isn't NULL, or 0 if it is. Something like this:

SELECT
    campaigns.aColumn,
    campaigns.anotherColumn,
    statistics.someOtherColumn,
    IFNULL(statistics.budget, 0) AS budget

---

Note: This is a SQL injection problem waiting to happen:

WHERE campaigns.campaignName = '$randomVar'

Use query parameters to treat values as values instead of inserting strings directly into the query as executable code.