CODEWITHSUNDEEP
phpmysqlswitch-statement
Montiyago
Montiyago

Reputation: 647

How to fetch the values from MySQL with switch case in PHP

I'm working with PHP and MySQL on a student registration project.

There is a table named programme from which I have to find the value of total no. of registration in every campus.

There are 4 campuses (Delhi, Noida, Jaipur and Mumbai) and 27 courses with 2 date of exam cycle (12-04-2014 and 07-06-2014).

I have to display total no. of registration in every campus for every course in every campus. for e.g. Delhi campus I made this query for first date:

$sql="select * from programme where campus1='delhi' && course1='Fashion Design (FD)' && examdate='12-04-2014'";
    $result=mysql_query($sql);
    $delhi=mysql_num_rows($result);

and it does echoe the result into the td for the first cycle.

But now I have to make a single query for 27 courses.

I've thought to do this via switch cases, but am unable to do it. How should I proceed?

Here is the Table format which I have to fill from database; it's for one course and similarly there are 26 other courses:

<table class="table table-bordered">
  <tr>
    <th>&nbsp;</th>
    <th colspan="10">Course Wise Registration Report</th>
    <th>&nbsp;</th>
  </tr>
  <tr>
    <th>PROGRAMME</th>
    <th colspan="2">Delhi</th>
    <th colspan="2">Noida</th>
    <th colspan="2">Jaipur</th>
    <th colspan="2">Mumbai</th>
    <th colspan="2">Cycle</th>
    <th>TOTAL</th>    
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>Cycle-1</td>
    <td>Cycle-2</td>
    <td>Cycle-1</td>
    <td>Cycle-2</td>
    <td>Cycle-1</td>
    <td>Cycle-2</td>
    <td>Cycle-1</td>
    <td>Cycle-2</td>
    <td>Cycle-1</td>
    <td>Cycle-2</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>UG-FD</td>
    <td><?php echo $delhi; ?></td>
    <td></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
   <tr>
    <td>UG-CD</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>UG-TD</td>
    ------------
  </tr>

UG-JD ------------ 

    <th>Total</th>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>

Here is DB table structure:

examdate campus1 course1 campus2 course2

Here is the format which i want : enter image description here

Here is the screenshot of DB table programme: enter image description here

Upvotes: 0

Views: 292

Answers (3)

Anand Somasekhar
Anand Somasekhar

Reputation: 596

here different solutions for ur problem

  1. in the main page use combo and selecting campus, list details of that campus below that

combo using a ajax page

combo posting page you just receive the value of that combo means compus name and add to
ur sql directly.

If we use switch we want to enter a input to switch. depending on this input we can

change the output in each case.

Upvotes: -1

Jakub Kania
Jakub Kania

Reputation: 16487

The query you want is:

SELECT campus1
       ,course1
       ,examdate
       ,count(*) AS count
FROM programme
WHERE examdate IN ('12-04-2014','07-06-2014')
GROUP BY campus1,course1,examdate

You may want to expand the WHERE clause if you don't want to include all campuses and courses.

Upvotes: 1

Muneer
Muneer

Reputation: 7564

You need to create a query something below.

SELECT COUNT(*) FROM programme 
WHERE campus1 = 'delhi' AND examdate = '12-04-2014'
GROUP BY course1

Listen! this is not the final solution. Your tables structures seems not well organized. It should go through several normalization process. Until I have your complete set of table structure I can not give you a final answer.

For now.. just copy and run the above query in your PhpMyAdmin and see what it produce, then you will get an idea.

NOTE : I think it is not possible for you to explain well enough, if so, put some screenshots of your all tables grabbed form PhpMyAdmin and screenshot of your final result how it should looks like.

Upvotes: 1

Related Questions