1

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);
Hunnenkoenig
  • 193
  • 1
  • 2
  • 14

1 Answers1

2

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
jonju
  • 2,711
  • 1
  • 13
  • 19
  • Thanks for the reply! It takes extremely long and the first item it found was from July 2 although there are items on 12. and 7. July too. – Hunnenkoenig Aug 03 '16 at 14:46
  • have you tried the updated. I put `DESC` later. And by **Extremely long**. what do you mean. I'm kind of lost with that – jonju Aug 03 '16 at 14:51
  • I'm trying right now. Are you sure, that I need the ";" after "Free=1;"? – Hunnenkoenig Aug 03 '16 at 14:52
  • Extremely long means ways over 1 minute. – Hunnenkoenig Aug 03 '16 at 14:54
  • About 21 thousand in the table for testing. Over 2 million in the other one. – Hunnenkoenig Aug 03 '16 at 14:55
  • 1
    If the second query does not solve your problem. then try creating an `INDEX` on column `datum` – jonju Aug 03 '16 at 14:59
  • how about using `FORCE INDEX`? And I believe you not selecting `*` but with the corresponding column name – jonju Aug 03 '16 at 15:11
  • If I use "Select * FROM..." then nothing changes. It works just like my actual code. I don't know what FORCE INDEX is, I have to look it up. – Hunnenkoenig Aug 03 '16 at 15:20
  • avoid `select *` until and unless necessary [reason](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful). FORCE INDEX is to tell query optimizer to pay attention on specific INDEX name – jonju Aug 03 '16 at 15:28