Sub query results withing a query that display at the bottom

Question

Apologies for the title, I am wanting to run a query like so:

$query = mysql_query("
  select * from taskdetail
  where userid = '" . $rec['id'] . "'
    and comp_status = '0'
    and duedate > '" . date("Y-m-d") . "'
  order by duedate,importantlevel ASC
  LIMIT " . $getnextrecord . " , $limit
");

and then run one like so

$query_noduedate = mysql_query("
  select * from taskdetail
  where userid = '" . $rec['id'] . "'
    and comp_status = '0'
    and duedate = '0000-00-00'
") or die(mysql_error());

I would like the results of the second query (tasks with no duedate) to appear below the first queries results, (almost as if it is the results of the first query).

The reason being is I have lots of extra functionailty that alters the number of tasks you see, so eg if the user had selects to see one result and click next it currently goes through each task, I simpy want to kind of append the second queries results at the end of the first instead of having two sets of results.

I could combine the queries together eg:

$query = mysql_query("
  select * from taskdetail
  where userid='" . $rec['id'] . "'
    and comp_status='0'
    and ( duedate > '" . date("Y-m-d") . "' or duedate = '0000-00-00' )
  order by duedate,importantlevel ASC
  LIMIT " . $getnextrecord . " , $limit");

However, the tasks with the duedate of 0000-00-00 will appear at the top.

Not sure of the best solution

Cheers

Please don't do that http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — dynamic, Feb 19 '12 at 17:59

Maciej Goszczycki · Answer 1 · 2015-05-18T00:56:36.443

There that's what I come up with:

$idnum = 0; //New count
$tasklist = array(); //New task array
while($task_with_due = mysql_fetch_array($query)){ //Add each task with due date to array
    $tasklist[$idnum] = $task_with_due;
    $idnum++;
}
while($task_with_nodue = mysql_fetch_array($query_noduedate)){ //Add each task with no due date to array
    $tasklist[$idnum] = $task_with_nodue;
    $idnum++;
}

foreach($task as $tasklist){ //Echo each task field 
    echo $task["name_of_the_field_in_db"];
}

I had a lot of problems with something similar not that long ago so I know the pain but it should work...

Cameron Chapman · Answer 2 · 2012-02-19T18:27:41.940

You could try a while loop to handle rows of data.

 //Your first query
    $query = mysql_query("select * from taskdetail where userid='" . $rec['id'] . "' and comp_status='0'  and  duedate > '" . date("Y-m-d") . "' order by duedate,importantlevel ASC LIMIT  " . $getnextrecord . " , $limit");

    //Now perform what you want with each row of results
    //Cycle through each row of results
    while($row = mysql_fetch_array($query)){
        //assign the result sest to the $row array
        extract($row);

        //Here would go your code to display what you want from the first results which are now stored in the $row array with keys starting at 0

        //Your sub query
        $query = mysql_query("select * from taskdetail where userid='" . $rec['id'] . "' and comp_status='0'  and  ( duedate > '" . date("Y-m-d") . "' or duedate = '0000-00-00' ) order by duedate,importantlevel ASC LIMIT  " . $getnextrecord . " , $limit");

        //Here is where you would display what you want from the second query. If you have multiple results you could duplicate they while loop from above.

$i++;

    }

If you only want two specific rows instead of displaying the reuslts inside of a loop which could display several rows you could replace the sections that are marked out to display results with sections of code that assign results with specific values to an array or string variable and then display them outside of the while loop.

Hope that gives you a decent direction to start.

score 0 · Answer 3 · answered Feb 19 '12 at 17:56

0

ORDER BY duedate = '0000-00-00' -- 0 if false, 1 if true
         duedate,
         importantlevel ASC

answered Feb 19 '12 at 17:56

biziclop

14,466
3
49
65

score 0 · Answer 4 · answered Feb 19 '12 at 18:35

0

Use union for merge data from 2 queries

select a,b,c,d from table1 order by a
union
select a,b,c,d from table2 order by a;

Or

Use

ifnull(columnThatCanBeNull, "value that will replace nulls")

statement to manage null fields.

answered Feb 19 '12 at 18:35

Kamil

13,363
24
88
183

score -1 · Accepted Answer · answered Feb 19 '12 at 17:55

There could be 2 ways:

Query 1 - by modifying the ORDER BY

$query = mysql_query("select *
    from taskdetail
    where userid='" . $rec['id'] . "'
    and comp_status='0'
    and  ( duedate > '" . date("Y-m-d") . "' or duedate = '0000-00-00' )
    order by (CASE
        WHEN duedate > '0000-00-00' THEN duedate
        ELSE '9999-99-99'
        END), importantlevel ASC
    LIMIT  " . $getnextrecord . " , $limit");

Query 2 - adding another column 'custom_date' to the SELECT and using it for ORDER BY

$query = mysql_query("select *, (CASE
        WHEN duedate > '0000-00-00' THEN duedate
        ELSE '9999-99-99'
        END) AS `custom_date`
    from taskdetail
    where userid='" . $rec['id'] . "'
    and comp_status='0'
    and  ( duedate > '" . date("Y-m-d") . "' or duedate = '0000-00-00' )
    order by `custom_date`, importantlevel ASC
    LIMIT  " . $getnextrecord . " , $limit");

Hope it helps!

Thank you so much, Query 1 was easiest for me to implement, works a treat, thank you — AJFMEDIA, Feb 19 '12 at 18:12
inefficient? Well it worked fine for the poster. Why do you think so @Kamil? — Abhay, Feb 19 '12 at 18:54

Sub query results withing a query that display at the bottom

5 Answers5