alternative sql query in column

Questionsalternative sql query in column
Harry asked 9 years ago

I would like to have the result of column be the count from a sql query rather than a field from the table.

For example,

$col = array();
$col["title"] = "refs";
$col["name"] = "SELECT count(*) FROM studies INNER JOIN staging on studies.rs_number = staging.rs WHERE rs_number = '$rs' ";
$col["editable"] = false;

$cols[] = $col;

The above example is clearly not possible but how can it be done?

Thank you

5 Answers
Abu Ghufran answered 9 years ago

You can have count query in select_command and have aliased column mapped to grid column.

$grid->select_command = "select staging.*, (SELECT count(*) FROM studies WHERE studies.rs_number = staging.rs) as c from staging";

$col = array();
$col["title"] = "Count";
$col["name"] = "c";
$col["width"] = "45";
$col["sortable"] = true;
$col["search"] = true;
$col["export"] = true;
$cols[] = $col;

Harry answered 9 years ago

My workaround was to build the count into the sql statement that generated the table, e.g.

$grid->select_command = "SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs GROUP BY staging.idstaging";

And I used 'refcount' as an aliased column.

This satisfies the problem I was having but doesn't answer my question. Is it possible to have an alternative sql query mapped to an aliased column? That would be very useful. If not its fine, as the workaround from mysql should accomodate most requirements.

Thank you.

Abu Ghufran answered 9 years ago

Pasting from docs …

For extended conditional data, you can also have callback function, that will allow you to display based on row data. For e.g.

$col["on_data_display"] = array("display_keyword","");

function display_keyword($data)
{
$kw = $data["keyword_name"];
$numKeywords = count(explode("n",$pass));
if ($numKeywords > 3)
return $numKeywords." Keywords";
else
{
$pass = str_replace("+"," ",$pass);
return $pass;
}
}

Based on demo code above, you can have myssql_query called inside callback function and return result value.
This will be less optimized way as query will get executed each time it renders columns data.

Harry answered 9 years ago

Could you please show how the sql query,

SELECT staging.*, COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs GROUP BY staging.idstaging

would be implemented using the callback function method you've described

Thank you

Abu Ghufran answered 9 years ago

I've not tested it on your code but it would be like:

function display_count($data)
{
// $data contains all columns names

$rs = $data["rs"];
$r = myysql_fetch_assoc(mysql_query("SELECT COUNT(studies.PMID) AS refcount FROM staging LEFT JOIN studies ON studies.rs_number = staging.rs WHERE staging.rs = '".$rs."' GROUP BY staging.idstaging"));
return $r["refcount"];
}

Your Answer

15 + 14 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?