View Feed
group-icon
Databases
Discuss databases and writing database queries with fellow DB enthusiasts.
273 Members
Join this group to post and comment.
hbk
hbk • Jun 17, 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)

Roll Number:







processing the form(comparing with database)

$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 • Jun 17, 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 • Jun 17, 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 • Jun 18, 2008
ok i have added mysql_error() calls to try debuggin where the error is, try out the following code and see what happens:

hbk
hbk • Jun 19, 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:



Result
include "menu.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)
?>
elric
elric • Jun 19, 2008
ok the error means your query isnt proper.
try this:

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 • Jun 20, 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 • Jun 20, 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
Roll Number:

and let your php file be
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 • Jun 23, 2008
well try using (back tic)` 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 • Jun 26, 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:


Roll Number:








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 "\n";
echo "\n";
do {
printf("\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 "
NameRoll NumberSubject 1Subject 2Subject 3Subject 4Subject 5
%s%s%s%s%s%s%s
\n";
} else {
echo "Sorry, no records were found!";
}
?>



hope this helps anyone with a similar problem...

cheers... and thx again...

Share this content on your social channels -