Page 1 of 1

Search MySQL after accepting data from text field

Posted: Wed May 09, 2007 2:52 am
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!!

Posted: Wed May 09, 2007 3:07 am
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."'";

Posted: Thu May 10, 2007 10:41 pm
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.

Posted: Fri May 11, 2007 6:17 am
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'])."'";
?

Posted: Fri May 11, 2007 6:19 am
by dhrosti
I have a phobia of functions within quotes. Your way is better though, i just like the code to look tidy.