CODEWITHSUNDEEP
phpmysql
Hunnenkoenig
Hunnenkoenig

Reputation: 199

how to run a query as long as another day with specific data found

Maybe it sounds crazy, but I couldn't come up with a better title.

I click a button and I send a date (Y-m-d) to a query, which looks up if data found and if data is found, it gives me the data back, if no data found, then it returns null.

Now I would like to do it so, that if no data available for the date I sent, the next date will be returned where data actually exists.

So practically I send a search for 30 August and if no data available for 30 August, the query should search for the next day in THE PAST for available data, so if it finds data on the 2nd August, then the data of 2nd August should be returned.

I tried with "date <= actual date" and "if result == 0" etc. but I am just tapping in the dark. The return is always a bunch of data, not just a single row.

My code looks like this now:

$category = $_GET['category'];
$query = mysqli_query($bd, "SELECT MAX(datum) as max_datum, MIN(datum) as min_datum FROM macapps WHERE free = 1 AND category = '".$category."' ") or die(mysqli_error());
$date = mysqli_fetch_assoc($query);

$day = isset($_GET['date']) ? $_GET['date'] : 0;
$date = date('Y-m-d', strtotime($date['max_datum'] . ' ' . $day . ' day'));

$result = mysqli_query($bd, "SELECT appID, title, icon, category, datum FROM macapps WHERE datum =  '".$date."' AND free = 1 AND category = '".$category."' ORDER BY title") or die(mysqli_error());
$rows = array();
while($count = mysqli_fetch_assoc($result)) {
    $rows[] = $count;   
}

echo json_encode($rows);

Upvotes: 1

Views: 31

Answers (1)

jonju
jonju

Reputation: 2736

Try this query

Select 
      appID, title, icon, category, datum 
From 
      macapps 
Where datum = (Select 
                     m.datum 
               From 
                     macapps m 
               WHERE
                     m.datum <= '.$date.'
               Order By m.datum Desc
               LIMIT 1
             )
And
    Free=1
And
    category = '.$category.'
Order By title;

Another Query

Select 
      appID, title, icon, category, datum 
From 
      macapps 
Where datum = (Select 
                     Max(m.datum)
               From 
                     macapps m 
               Where
                     m.datum <= '.$date.'                   
             )
And
    Free=1
And
    category = '.$category.'
Order By title

Upvotes: 2

Related Questions