Search MySQL after accepting data from text field

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
vipul73
Forum Commoner
Posts: 27
Joined: Mon May 12, 2003 5:32 am

Search MySQL after accepting data from text field

Post by vipul73 »

HI,

I'm doing a website for one of the college and they want to display there result on the website. The result should be displayed when a user searchs for a roll number.

I made a page result.php in which the user enters the roll number and searches

Code: Select all

<form id="form1" name="form1" method="post" action="result1.php">

<input name="roll" type="text" id="roll" />
<input name="Go" type="submit" id="Go" value="Go" />
<input type="reset" name="Reset" value="Reset" />
This leads to a page result1.php where the code is:

Code: Select all

$db = mysql_connect("localhost", "username", "password");
	mysql_select_db("database",$db);

$roll = $_POST['roll'];
	if (!empty($_GET['roll']) && is_numeric($_GET['roll'])) 
		{ 
		    $sql = "SELECT roll, student, father, category, theory, prac1, prac2, total, percent, divison FROM tablename WHERE roll=".$_GET['roll']; 
		    $result = mysql_query($sql, $db) or die(mysql_error().'<p>'.$sql.'</p>'); 
			if (mysql_num_rows($result) == 1)
			{
				while ($row = mysql_fetch_array($result))
				{
					$roll = $row['roll'];
					$student = $row['student'];
					$father = $row['father'];
					$category = $row['category'];
					$theory = $row['theory'];
					$prac1 = $row['prac1'];
					$prac2 = $row['prac2'];
					$total = $row['total'];
					$percent = $row['percent'];
					$divison = $row['divison'];
				}
			}
			else
				{
					echo 'Roll Number Not Found. Please check the roll number and try again';
				}
		}
		else {
		echo 'Roll Number Not Found. Please check the roll number and try again';
		}

<table width="50%" border="0" align="center" cellpadding="0" cellspacing="4">
          <tr>
            <td>Roll Number of Candidate </td>
            <td>:</td>
            <td><?php echo $roll ?></td>
          </tr>
          <tr>
            <td>Name of Candidate </td>
            <td>:</td>
            <td><?php echo $name ?></td>
          </tr>
          <tr>
            <td>Father's Name </td>
            <td>:</td>
            <td><?php echo $father ?></td>
          </tr>
          <tr>
            <td>Category</td>
            <td>&nbsp;</td>
            <td><?php echo $category ?></td>
          </tr>
          <tr>
            <td>Rank</td>
            <td>:</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td>Theory</td>
            <td>:</td>
            <td><?php echo $theory ?>/600</td>
          </tr>
          <tr>
            <td>Practical - I </td>
            <td>:</td>
            <td><?php echo $prac1 ?>/300</td>
          </tr>
          <tr>
            <td>Practical - II </td>
            <td>:</td>
            <td><?php echo $prac2 ?>/300</td>
          </tr>
          <tr>
            <td class="textbold">Total Marks</td>
            <td class="textbold">:</td>
            <td class="textbold"><?php echo $total ?>/1200</td>
          </tr>
          <tr>
            <td>Percentage</td>
            <td>:</td>
            <td><?php echo $percent ?></td>
          </tr>
          <tr>
            <td>Divison</td>
            <td>:</td>
            <td><?php echo $divison ?></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
        </table>
The table structure is:

Code: Select all

`roll` tinyint(4) NOT NULL default '0',
  `student` varchar(50) NOT NULL default '',
  `father` varchar(50) NOT NULL default '',
  `category` varchar(10) NOT NULL default '',
  `theory` varchar(5) NOT NULL default '',
  `prac1` varchar(5) NOT NULL default '',
  `prac2` varchar(5) NOT NULL default '',
  `total` varchar(5) NOT NULL default '',
  `percent` varchar(5) NOT NULL default '',
  `divison` char(2) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Whenever I am trying to search the database it is giving "'Roll Number Not Found. Please check the roll number and try again"

Please help with the code!!
User avatar
dhrosti
Forum Commoner
Posts: 90
Joined: Wed Jan 10, 2007 5:01 am
Location: Leeds, UK

Post by dhrosti »

Try putting your $_GET['roll'] in single quote. And don't put it straight into an SQL command...

Code: Select all

// The least you should do is escape characters...
$roll = mysql_real_escape_string($_GET['roll']);
$sql = "SELECT roll, student, father, category, theory, prac1, prac2, total, percent, divison FROM tablename WHERE roll='".$roll."'";
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Umm, your form's Method is POST. But in result1.php, after assigning the value passed in the POST array to the variable $roll, you are using a GET paramter in your subsequent statements. Since you haven't passed anything as a GET parameter, it is searching for a null value.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

dhrosti wrote:Try putting your $_GET['roll'] in single quote. And don't put it straight into an SQL command...

Code: Select all

// The least you should do is escape characters...
$roll = mysql_real_escape_string($_GET['roll']);
$sql = "SELECT roll, student, father, category, theory, prac1, prac2, total, percent, divison FROM tablename WHERE roll='".$roll."'";
/tangent

Why not

Code: Select all

$sql = "SELECT roll, student, father, category, theory, prac1, prac2, total, percent, divison FROM tablename WHERE roll='".mysql_real_escape_string($_GET['roll'])."'";
?
User avatar
dhrosti
Forum Commoner
Posts: 90
Joined: Wed Jan 10, 2007 5:01 am
Location: Leeds, UK

Post by dhrosti »

I have a phobia of functions within quotes. Your way is better though, i just like the code to look tidy.
Post Reply