HOME BLOG PORTFOLIO PHOTO CONTACT Admin Login
Query for select first second third largest

php Mysql Query for select FIRST SECOND THIRD FOURTH …. largest value from a table with 2 best methods.
½. Now Below code Save As index.php


<?php
$link=mysql_connect(‘localhost’,'root’,”) or die(mysql_error());
mysql_select_db(‘marks’);

//qry for select top first marks
$qry1 = “SELECT * FROM examfinal ORDER BY marks DESC LIMIT 1″;
$result1 = mysql_query($qry1) or die(“Error1″);
$row1 = mysql_fetch_array($result1);
echo “<br>top first marks-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;

//qry for select top second marks methoad JAY1
$qry1 = “SELECT * FROM examfinal ORDER BY marks DESC LIMIT 2″;
$result1 = mysql_query($qry1) or die(“<br>qry Error2″);
$i=1;
while($row1 = mysql_fetch_array($result1))
{
if($i == 2)
{
echo “<br>top second marks methoad JAY1-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;
}
$i++;
}

//qry for select top third marks methoad JAY1
$qry1 = “SELECT * FROM examfinal ORDER BY marks DESC LIMIT 3″;
$result1 = mysql_query($qry1) or die(“<br>qry Error3″);
$i=1;
while($row1 = mysql_fetch_array($result1))
{
if($i == 3)
{
echo “<br>top third marks methoad JAY1-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;
}
$i++;
}

//qry for select top fourth marks methoad JAY1
echo “<br>See here this query method has no any bug in the table the same marks is reapiting for two students then this query sure correctly produce perfect output”;
$qry1 = “SELECT * FROM examfinal ORDER BY marks DESC LIMIT 4″;
$result1 = mysql_query($qry1) or die(“<br>qry Error4″);
$i=1;
while($row1 = mysql_fetch_array($result1))
{
if($i == 4)
{
echo “<br>top fourth marks methoad JAY1-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;
}
$i++;
}

echo “<br><br>”;
//qry for select top first marks methoad JAY2
$qry1= “SELECT * FROM examfinal C1 WHERE 0 = (SELECT count(*) FROM examfinal C2 WHERE C1.marks < C2.marks)”;
$result1 = mysql_query($qry1) or die(“<br>qry Error5″);
$row1 = mysql_fetch_array($result1);
echo “<br>top first marks methoad JAY2-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;

//qry for select top second marks methoad JAY2
$qry1= “SELECT * FROM examfinal C1 WHERE 1 = (SELECT count(*) FROM examfinal C2 WHERE C1.marks < C2.marks)”;
$result1 = mysql_query($qry1) or die(“<br>qry Error6″);
$row1 = mysql_fetch_array($result1);
echo “<br>top second marks methoad JAY2-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;

//qry for select top third marks methoad JAY2
$qry1= “SELECT * FROM examfinal C1 WHERE 2 = (SELECT count(*) FROM examfinal C2 WHERE C1.marks < C2.marks)”;
$result1 = mysql_query($qry1) or die(“<br>qry Error7″);
$row1 = mysql_fetch_array($result1);
echo “<br>top third marks methoad JAY2-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;

//qry for select top fourth marks methoad JAY2
echo “<br>See here this query method has bug in the table the same marks is reapiting for two students then this query not produce any output”;
$qry1= “SELECT * FROM examfinal C1 WHERE 3 = (SELECT count(*) FROM examfinal C2 WHERE C1.marks < C2.marks) “;
$result1 = mysql_query($qry1) or die(“<br>qry Error8″);
$row1 = mysql_fetch_array($result1);
echo “<br>top fourth marks methoad JAY2-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;

//qry for select top fifth marks methoad JAY2
$qry1= “SELECT * FROM examfinal C1 WHERE 4 = (SELECT count(*) FROM examfinal C2 WHERE C1.marks < C2.marks) “;
$result1 = mysql_query($qry1) or die(“<br>qry Error8″);
$row1 = mysql_fetch_array($result1);
echo “<br>top fifth marks methoad JAY2-> id= $row1[id] name= $row1[name] marks= $row1[marks] “;
?>


2/2. Now Below is mysql database/table.

 

– phpMyAdmin SQL Dump
– version 2.10.1
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Mar 14, 2009 at 02:27 PM
– Server version: 5.0.45
– PHP Version: 5.2.5

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;


– Database: `marks`

– ——————————————————–


– Table structure for table `examfinal`

CREATE TABLE `examfinal` (
`id` int(2) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`marks` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;


– Dumping data for table `examfinal`

INSERT INTO `examfinal` (`id`, `name`, `marks`) VALUES
(1, ‘rajesh’, 50),
(2, ‘mohan’, 65),
(3, ‘shyam’, 85),
(4, ‘naresh’, 95),
(5, ‘azim md’, 45),
(6, ‘kunal singh’, 70),
(7, ‘jay’, 80),
(8, ‘bharat’, 88),
(9, ‘renuka’, 88),
(10, ‘moushami’, 99),
(11, ‘lund’, 10),
(12, ‘rajiv’, 66),
(13, ‘raju’, 55),
(14, ‘mona’, 66),
(15, ‘ram’, 77),
(16, ‘jendu’, 66);


Now you are ready for run it index.php

   Share on Facebook

Page views:752