PHP- SQL utility

hbk
Member • 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...

Replies

Howdy guest!

Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.

Replies

elric

Member • 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."\"" );

Are you sure? This action cannot be undone.
Cancel
hbk

Member • 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...

Are you sure? This action cannot be undone.
Cancel

elric

Member • 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:

<?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)
?>

Are you sure? This action cannot be undone.

Cancel

hbk

Member • Jun 18, 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>

Are you sure? This action cannot be undone.
Cancel

elric

Member • Jun 19, 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.

Are you sure? This action cannot be undone.

Cancel

hbk

Member • 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...

Are you sure? This action cannot be undone.
Cancel
elric

Member • 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
```
<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.
Are you sure? This action cannot be undone.
Cancel
th3 ied kid

Member • Jun 23, 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

Are you sure? This action cannot be undone.
Cancel
hbk

Member • Jun 25, 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...

Are you sure? This action cannot be undone.
Cancel