hbk
hbk
Branch Unspecified
17 Jun 2008

PHP- SQL utility

hi guys...

i'm developing a site 4 my college.... theres a link onthe site to checkur results...

there's this backend datbase(MySQL), the front end being HTML+PHP+CSS....

the database table(called result) contains columns (Roll NUmber, subject 1, subj2 etc)...

i ask the user to enter his roll number via a HTML form(POST method).... and on the target page of the form, i retreive the roll number he had entered....

i then compare the eneterd value with entries in the 'Roll NUmber' column of the databsae table.... and when a match is found, i print the entire record for that roll number...

plain and simple..

but i'm facing a problem...

when i try to retrieve the record (of the enetered roll number) using mysql_fetch_array($xyz), i get an error...

here's the source code...

the user form (collecting the roll number)
<form action="results2.php" method="post">
Roll Number: <input type="text" name="Roll Number" />
<br><br>
<input type="submit" value="Submit"/>
</form>


processing the form(comparing with database)

<?php
$rno = $_POST["Roll Number"];
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database", $con);
$data = mysql_query("SELECT * FROM result WHERE 'Roll Number' = $rno ");
while(mysql_fetch_row($data))
{
echo "HI";
}
mysql_close($con)
?>




please help me figure out the error.... i'll be really grateful...
elric

elric

Branch Unspecified
17 Jun 2008
i think there is an error in your query string
try something like
$data = mysql_query("SELECT * FROM result WHERE 'Roll Number' = \"".$rno."\"" );
hbk

hbk

Branch Unspecified
17 Jun 2008
thanks for the reply, but even this doesnt seem to work..... it gives the same output(blank screen--> no error message though) as with my original bit of code...
elric

elric

Branch Unspecified
18 Jun 2008
ok i have added mysql_error() calls to try debuggin where the error is, try out the following code and see what happens:
<?php 
$rno = $_POST["Roll Number"]; 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
   die('Could not connect: ' . mysql_error());
  }
mysql_select_db("database", $con) or die (mysql_error());;
$data = mysql_query("SELECT * FROM result WHERE 'Roll Number' = $rno ") or die (mysql_error());
$row=mysql_fetch_row($data) or die (mysql_error());
while($row)
  {
    echo "HI";
$row=mysql_fetch_row($data) or die (mysql_error());
  }
mysql_close($con)
?>
hbk

hbk

Branch Unspecified
19 Jun 2008
Thanks for the reply.. though even this doesnt work....

it gives the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

now what does that mean??

the entire source code is as follows:

<html>
<head>
<title>Result</title>
<?php
include "menu.php";
?>
</head>
<?php
$rno = $_POST["Roll Number"];
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("database", $con);
$data = mysql_query("SELECT * FROM result WHERE 'Roll Number' = $rno ") or die (mysql_error());
$row=mysql_fetch_row($data) or die (mysql_error());
while($row)
{
echo "HI";
$row=mysql_fetch_row($data) or die (mysql_error());
}
mysql_close($con)
?>
</html>
elric

elric

Branch Unspecified
19 Jun 2008
ok the error means your query isnt proper.
try this:
<?php 
$rno = $_POST["Roll Number"]; 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
   die('Could not connect: ' . mysql_error());
  }
mysql_select_db("database", $con) or die (mysql_error());
$query="SELECT * FROM result WHERE \'Roll Number\' = $rno ";
echo $query;
$data = mysql_query($query) or die (mysql_error());
$row=mysql_fetch_row($data) or die (mysql_error());
while($row)
  {
    echo "HI";
$row=mysql_fetch_row($data) or die (mysql_error());
  }
mysql_close($con)
?>
now the query that php is sending mysql will also be printed, now try to execute this query from the mysql shell and see what happens.
I guess that not using the escape slash for 'Roll Number' has made the query malformed. Anyways check the new code.
hbk

hbk

Branch Unspecified
20 Jun 2008
thanks for the suggestion elric.... i tried what u said...

but that gives the following error message---

SELECT * FROM result WHERE \'Roll Number\' = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'Roll Number\' =' at line 1


please help...
elric

elric

Branch Unspecified
20 Jun 2008
ok, the escape slashes werent needed for 'Roll Number'
next your query is
SELECT * FROM result WHERE 'Roll Number' =
so that means your $rno is blanck, that is $_POST["Roll Number"] is empty.
Iam quite uncomfortable using a space in the name for the text box name, so try this in your html form
<form action="results2.php" method="post">
Roll Number: <input type="text" name="Rollno" />
<br><br>
<input type="submit" value="Submit"/>
</form>
and let your php file be
<?php 
$rno = $_POST["Rollno"]; 
$con = mysql_connect("localhost","root","");
if (!$con)
  {
   die('Could not connect: ' . mysql_error());
  }
mysql_select_db("database", $con) or die (mysql_error());
$query="SELECT * FROM result WHERE 'Roll Number' = ".$rno ";
echo $query;
$data = mysql_query($query) or die (mysql_error());
$row=mysql_fetch_row($data) or die (mysql_error());
while($row)
  {
    echo "HI";
$row=mysql_fetch_row($data) or die (mysql_error());
  }
mysql_close($con)
Also which version of php are you using? if you are using php5 then shouldn't be a problem but as far as i know in php4 (not sure), you cant embed a variable within a string and expect the string to have the contents of the string, ie.
$rno = "foo";
$query = "select * from $rno";
now $query wont be a string "select * from foo"
to obtain which you will need to use the dot operator as
$query = "select * from ".$rno
above is just an example. So to support backward compatibility dont embed variables within strings, if your script has to run on a php4 running server it would be a problem.
th3 ied kid

th3 ied kid

Branch Unspecified
23 Jun 2008
well try using (back tic)` <the one over tilde > instead of(small quote) ' over the field names coz i found the default myphp admin softwtre doing xactly that.
Secondly use the less power full quote to cloak the more power full one i.e. use less of " and more ' whenever possible.try this:
$query ='SELECT * FROM result WHERE `Roll Number` ='. $rno;
coz something like this worked in php5-mysql combi engine in my case.
Well take a look at this working piece of line:

$query='UPDATE chat SET Data ='."$val".'WHERE Pass ='.$Too;
Please note that i have 2 kinds of quotes over variables and i didn't use back tic but i tested otherwise too and it seems to work with me
hbk

hbk

Branch Unspecified
26 Jun 2008
hi guys...

thank u all for ur time n effort...

i finally figured out the solution (though it happened by chance)

here is the FORM page:

<form action="rs2.php" method="post">
Roll Number: <input type="text" name="Roll" />
<br><br>
<input type="submit" value="Submit"/>
</form>
</html>


and here is the other page

$xyz=$_POST["Roll"];
$res = mysql_query("SELECT * FROM result where ID = '$xyz' ",$db);
if ($myrow = mysql_fetch_array($res)) {
echo "<table border=1>\n";
echo "<tr><td>Name</td><td>Roll Number</td><td>Subject 1</td><td>Subject 2</td><td>Subject 3</td><td>Subject 4</td><td>Subject 5</td></tr>\n";
do {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow["Name"], $myrow["ID"], $myrow["Subject 1"], $myrow["Subject 2"], $myrow["Subject 3"], $myrow["Subject 4"], $myrow["Subject 5"]);
} while ($myrow = mysql_fetch_array($res));
echo "</table>\n";
} else {
echo "Sorry, no records were found!";
}
?>
</body>
</html>

hope this helps anyone with a similar problem...

cheers... and thx again...

Share this content on your social channels -

Only logged in users can reply.