0

I have a table in mySQL whose structure is as given below with a single row of data

info     R1    R2    R3      R4      R5         ( Columns)
------------------------------------------   
Percent  80    90    70      85      69         (Data)

My app is to take attedance in a lecture which stores the attendance data and the overall percentage in the above format.

I need to retrieve the column name if the percent is below 75% ? How do I do this using a Php script? Which mySQL query should I use?

Improve this question
6
  • 3
    Your data structure is against all rules Commented Apr 10, 2013 at 10:23
  • Why is it against the rules? Only the first row will contain the percentage data. The second row will have the date and hour in the first column and the rest of the columns will contain either an 'A' or a 'P' depending on whether the student is absent or present. Commented Apr 10, 2013 at 10:27
  • storing percentage that can be easily calculated is called denormalization. And all these A to P things have to be stored in rows, not columns. Commented Apr 10, 2013 at 10:32
  • Each day when we make a new entry of attendance I would prefer adding a new row. If I store the Roll numbers (R1,R2....) as rows then I would have to alter the Table structure everytime to add a column for that specific date. Commented Apr 10, 2013 at 10:49
  • The problem is that the data you store in R1, R2, etc is the same kind of thing. You should use one field to store the attendance, not 5. You have designed your database table to look like a form that is filled in, but that is rarely beneficial. Look at the answers below and notice all the hoops that had to be jumped through to generate the simplest of reports. That will only get worse, as more reports are required. Commented Apr 10, 2013 at 11:06

4 Answers 4

Reset to default
3

You can check this SQL FIDDLE if you want to go with easy PHP scripting.

The query is like:

SELECT r1,'R1' FROM info WHERE R1<75
UNION ALL 
SELECT r2,'R2' FROM info WHERE R2<75
UNION ALL 
SELECT r3,'R3' FROM info WHERE R3<75
UNION ALL 
SELECT r4,'R4' FROM info WHERE R4<75
UNION ALL 
SELECT r5,'R5' FROM info WHERE R5<75
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I can't see why this has been down voted, it answers the question perferctly "retrieve the column name if the percent is below 75%".
2

If I understand what you need try this:

SELECT info,
       if(R1 > 75, 'R1','-') AS 'R1>75',
       if(R2 > 75, 'R2','-') AS 'R2>75',
       if(R3 > 75, 'R3','-') AS 'R3>75',
       if(R4 > 75, 'R4','-') AS 'R4>75',
       if(R5 > 75, 'R5','-') AS 'R5>75'
FROM Table1
WHERE R1 > 75
  OR R2 > 75
  OR R3 > 75
  OR R4 > 75
  OR R5 > 75;

Here is the link to the sqlfiddle.

Improve this answer

6 Comments

Why do we need the WHERE clause?
at least one of the field in the row must have percent more than 75. Otherwise it will take all the rows.
added single quotes in if condition around field name so that it will take the row name instead row value.
Ok. I will try this out and see if it works. EDIT : The SQL Fiddle showed me the all the column names and only the columns with percent greater than 75 had percentage value, others had Null. Is it possible to get only column names>
@Suresh I intentionally used no single quote to have values, but I don't now the requirements of user2157501
|
0

You can select all the field first and then display only those data which has value less than 75. One way of doing this is:

$sql = "SELECT * FROM table_name";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
if($row[R1]<75) 
    echo "R1: " . $row[R1] . "<br />";
if($row[R2]<75) 
    echo "R2: " . $row[R2] . "<br />";
if($row[R3]<75) 
    echo "R3: " . $row[R3] . "<br />";
if($row[R4]<75) 
    echo "R4: " . $row[R4] . "<br />";
if($row[R5]<75) 
    echo "R5: " . $row[R5] . "<br />";
Improve this answer

Comments

-1

Pass the query to retrieve the data from database with percent below 75%. So it will retrieve only those columns which has value less than 75.

Update

select
  case
     when R1<75 then R1
     end
     as 'R1',
  case
     when R2<75 then R2
     end
     as 'R2',
  case
     when R3<75 then R3
     end
     as 'R3',
  case
     when R4<75 then R4
     end
     as 'R4',
  case
     when R5<75 then R5
     end
     as 'R5'
from Table;

It will get all columns (except info) with column names but the values of columns will be null if percent is above or equals to 75. From the result, based on the condition, you can get the column name Then using ResultSetMetaData you can get the column names.

Improve this answer

4 Comments

What query will that be? Select query won't work as it will return a complete row.
I think you have got the select query from @Alepac
Have you got the answer buddy?
Not the column names only but with Alepac's code i am getting NULL values in the columns where the condition is false. So I can probably go through the entire row and check for NULL values and determine the column names.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.