Working with the information_schema

[blackbox]

Hi all,

I have a requirement to run a SELECT query which sums a column. The issue I have is the column is going to by selected by the user (wildcard).

Is there a way to run such a query and sum one column without knowing the column name prior to execution.

I was looking at using the information_schema or am I looking at this incorrectly.

This is what I have: Q1A is the column I need to SUM.

SELECT SUM(Q1A) FROM information_schema.columns  WHERE table_name = 'Answers' AND column_name LIKE '".$QuestionRef."'

When I run the query it reports that "Q1A" is a unknown column.

Any help with this would be great. Many thanks in advance.

[NogDog]

Not sure why you need the information schema in here at all? If the data you want to sum is in the Answers table, then you will have to select from answers, not from the information schema. 😕

[Weedpacket]

blackbox wrote:

Is there a way to run such a query and sum one column without knowing the column name prior to execution.

How is the query supposed to know which column to sum? Telepathy?

(And the error message is of course because the Information Schema's "columns" table doesn't have a column named "Q1A".)

[blackbox]

Hi Guy's,

Many thaks for your replies. Looking at using the Information Schema's "columns" was following incorrect advice. That aside I have found out that I can use a variable as a column name which I now have working.

The issue I now have is this; if I run the following query:

mysql_select_db($database_navex, $navex);
$query_Questions = sprintf("SELECT AirlineName, ".$Question." FROM CrewFlightFeedBackAnswers ORDER BY AirlineName");
$Questions = mysql_query($query_Questions, $navex) or die(mysql_error());

The result is a list of the "AirlineName" and the content of the variable colmun "$Question".

The I need to SUM the content of al the results from "$Question" for each "AirlineName". Something like:

while($row_Questions = mysql_fetch_assoc($Questions)) { 

mysql_select_db($database_navex, $navex);
$query_Answers = sprintf("SELECT  AirlineName,  SUM(".$Question.") AS A FROM CrewFlightFeedBackAnswers");
$Answers = mysql_query($query_Answers, $navex) or die(mysql_error());
$row_Answers = mysql_fetch_assoc($Answers);
$totalRows_Answers = mysql_num_rows($Answers);

print $row_Answers['AirlineName']. " " . $row_Answers['A'];
}

Or am I again looking at this completely wrong.

I then need to SUM the content of $Question for each record and produce a list of AirlineNames with the resulting SUM.

[blackbox]

Hi again,

Before I waist your time I thin I may have solved the issue:

I have:

mysql_select_db($database_navex, $navex);
$query_Questions = sprintf("SELECT AirlineName, ".$Question." FROM CrewFlightFeedBackAnswers GROUP BY AirlineName ASC ORDER BY AirlineName");
$Questions = mysql_query($query_Questions, $navex) or die(mysql_error());
$totalRows_Questions = mysql_num_rows($Questions);

$Count = $totalRows_Questions ;

while($row_Questions = mysql_fetch_assoc($Questions)) { 

mysql_select_db($database_navex, $navex);
$query_Answers = sprintf("SELECT  ".$Question." AirlineName,  SUM(".$Question.") AS A FROM CrewFlightFeedBackAnswers  WHERE AirlineName = '".$row_Questions['AirlineName']."' GROUP BY AirlineName ASC");
$Answers = mysql_query($query_Answers, $navex) or die(mysql_error());
$row_Answers = mysql_fetch_assoc($Answers);
$totalRows_Answers = mysql_num_rows($Answers);

$A1 = $row_Answers['A'];
$Final_Percent1 = ($A1/$MaxPossible) * 100;
$Over_percentage = ($Final_Percent1) / 1;
print $row_Questions['AirlineName'] . " Score: " . $A1  . "  Percentage: " . $Final_Percent1  ."<br>";
}

The resulkt looks right, would you like to comment?

[dalecosp]

blackbox wrote:

Before I waist your time

If your issue is resolved, please use the "Thread Tools" dropdown to "Mark Thread Resolved." Thanks! 🙂

[Bonesnap]

blackbox;11047623 wrote:

waist

blackbox;11047623 wrote:

thin

What are you implying? :glare: