[help] Student DB Script In PHP & MySQL

dhanesh1405241511 · July 20, 2006

Since my earlier try with using access went wild .. i decided to choose MySQL as my DB and PHP as the code .. so just changing a few things in my earlier post .. for the new people ..

ok .. i have this little problem i am going through ... Leme explain this project of mine first .

I have a MySQL DB of student fields like ID no, Name, Marks .. etc .. This one MySQL DB would be updated by just adding more rows and would be done on the server.

Now .. I have a HTML page that shows this :

I would enter the ID number of a student (no authentication .. anyone can access anybodys marks), and when i hit submit .. I would be taken to a page that gets the values from the ID specified and shows it in the next page. The page design would be the same .. just the values comming in will depend on the ID number inputed in the previous screen.

The result should be something like this :

Is it possible to make a system like this ? To test this .. i will be using my own asta account .. so i guess if it works here .. then this system can be accessed from anywhere. I'd preffer HTML .. but since HTML doesnt allow dynamic updating .. i am fine with PHP. But it would be nice if someone helped me out with this.

I think ..

Page1 : Enter ID ---> HTML

Page2 : Show results ---> PHP

Hope i didnt confuse, if so please let me know .. i'll try to explain in a better way if i can.

Now .. i did a little self learning experiment with MySQL as suggested by vyoma ..

1) Created a DB in MySQL: dhanesh_studentdb

2) Created a user with pass: password / password

3) Went to the SQL Query and typed in this :

CREATE TABLE `marks` (`id` INT( 10 ) NOT NULL ,`name` VARCHAR( 30 ) NOT NULL ,`marks` FLOAT( 5 ) NOT NULL) ENGINE = MYISAM COMMENT = 'Student Marks';

4)

ALTER TABLE `marks` ADD PRIMARY KEY ( `id` )

5)

ALTER TABLE `marks` CHANGE `name` `name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL

6)

INSERT INTO marksVALUES ( 2006001, 'John', 20 );# Affected rows: 1INSERT INTO marksVALUES ( 2006002, 'Gavin', 40 );# Affected rows: 1INSERT INTO marksVALUES ( 2006003, 'Ter', 32 );# Affected rows: 1INSERT INTO marksVALUES ( 2006004, 'Mac', 46 );# Affected rows: 1

7) The result looks like this : Posted Image

8) I made 2 PHP scripts .. 1 to input ID and show Id on the next page .. here is the code for index.php

<?php$id = $_POST["id"];if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form} else {   if(empty($id)) {		echo "You did not fill in all the 				fields, try again<p>"; 		   }				 else {					 echo "Thank you, $id";  				 }		} 		?><html><head><title>Marks Example</head><body><form method="post" action="ID Number:<input type="text" size="12" maxlength="12" name="id"><input type="submit" value="submit" name="submit">

9) Then i made a page to retrive values from the DB i created and show in the SQL page .. no ID input .. nothing .. just to show the values .. This is the code for index.php3

<html><body><?php$db = mysql_connect("localhost", "root");mysql_select_db("marks",$db);$result = mysql_query("SELECT * FROM marks",$db);printf("ID: %s<br>\n", mysql_result($result,0,"id"));printf("Name: %s<br>\n", mysql_result($result,0,"name"));printf("Marks: %s<br>\n", mysql_result($result,0,"marks"));?></body></html>

With this code i get the following error :

[b]Warning[/b]:  mysql_connect() [[color="#3333ff"]function.mysql-connect[/color]]: Access denied for user 'admin'@'localhost' (using password: YES) in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]13[/b]  [b]Warning[/b]:  mysql_select_db(): supplied argument is not a valid MySQL-Link resource in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]17[/b]  [b]Warning[/b]:  mysql_query(): supplied argument is not a valid MySQL-Link resource in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]21[/b]  [b]Warning[/b]:  mysql_result(): supplied argument is not a valid MySQL result resource in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]25[/b] ID:   [b]Warning[/b]:  mysql_result(): supplied argument is not a valid MySQL result resource in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]29[/b] Name:   [b]Warning[/b]:  mysql_result(): supplied argument is not a valid MySQL result resource in [b]/home/dhanesh/public_html/testfinal.php3[/b] on line [b]33[/b] Marks:

What am i doing wrong .. could someone make me a file so i could use it as reference and continue building up ..

Thankx ..

Regards

Dhanesh.

miCRoSCoPiC^eaRthLinG · July 20, 2006

Ok.. step by step.Here's a couple of things to optimize your queries. First why are you breaking up the CREATE TABLE statement into so many steps ? It can be done in one sweep like this:

CREATE TABLE `marks` (`id` INT( 10 ) NOT NULL ,`name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL ,`marks` FLOAT( 5 ) NOT NULL,PRIMARY KEY ( `id` )) ENGINE = MYISAM COMMENT = 'Student Marks';

Next comes your INSERT query. Instead of inserting one by one, you should use a BULK INSERT statement.. which is like:

INSERT INTO marks ( id, name, marks ) VALUES ( 2006001, 'John', 20 ), ( 2006002, 'Gavin', 40 ), ( 2006003, 'Ter', 32 ), ( 2006004, 'Mac', 46 );

dhanesh1405241511 · July 20, 2006

Sorry bro .. i was posting while u posted .. newayz .. thankx for letting me know about the 1 step bulk queries :unsure: .. my first step to enlightnment .. hehe .. Now since thats done .. i gues the sql part is over .. now the only thing left is the PHP part rite ? RegardsDhanesh.

Edited July 20, 2006 by dhanesh (see edit history)

miCRoSCoPiC^eaRthLinG · July 20, 2006

As for your MySQL Queries.. try the following:

$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');if (!$conn) {   die('Not connected : ' . mysql_error());}$db = mysql_select_db('foo', $conn);if (!$db) {   die ('Can\'t use foo : ' . mysql_error());}$result = mysql_query('SELECT * ...');if (!$result) {   die('Invalid query: ' . mysql_error());}while ($row = mysql_fetch_assoc($result)) {   echo $row['id'];   echo $row['name'];   echo $row['marks'];}

Just alter wherever needed, but this should give you a basic outline of how to go about it. Instead of trying to parse $result directly, you should break it up into an array using a function like mysql_fetch_assoc and then use the fieldnames as array element pointers to get each individual data.

dhanesh1405241511 · July 20, 2006

With the help of m^e .. who did 100% of the code lol .. this is how i got to SHOW values from a MySQL DB to a PHP file.

index.php

<html><body><?php$conn = mysql_connect('localhost', 'mysql_user', 'mysqlpass');if (!$conn) {   die('Not connected : ' . mysql_error());}$db = mysql_select_db('mysql_dbname', $conn);if (!$db) {   die ('Can\'t use foo : ' . mysql_error());}$result = mysql_query('SELECT * FROM `marks`');if (!$result) {   die('Invalid query: ' . mysql_error());}while ($row = mysql_fetch_assoc($result)) {   echo $row['id'];   echo $row['name'];   echo $row['marks'];}?></body></html>

Result : 2006001John202006002Gavin402006003Ter322006004Mac46

Now i just need to beautify the results to show properly. Will post up when i m done with that too.

If you have problems making a MySQL DB .. please refer to m^e's first post in this thread.

If anyone could pass on some ideas on how to put these values in a table format, it would be helpful ..

Thankx m^e :unsure:

Regards

Dhanesh.

Vyoma · July 21, 2006

Well dhanesh, I have replied for you at the other forum where you asked for help. For sake of record, I am quoting it here again:

To get a tabular format put the code something like this.
Instead of
while ($row = mysql_fetch_assoc($result)) {   echo $row['id'];   echo $row['name'];   echo $row['marks'];}
put the following code:
echo "<TABLE><TR><TD>ID</TD><TD>Name</TD><TD>Marks</TD></TR>";while ($row = mysql_fetch_assoc($result)) {   echo "<TR>";   echo "<TD>".$row['id']."</TD>";   echo "<TD>".$row['name']."</TD>";   echo "<TD>".$row['marks']."</TD>";   echo "</TR>";}echo "</TABLE>";
Well, that should do it.

But I thought you wanted to get the details of only one student, as requested from 'ID'?

dhanesh1405241511 · July 21, 2006

But I thought you wanted to get the details of only one student, as requested from 'ID'?

First off thankx .. and i will right away give this code a try .. well what u say is tru i NEED to give an ID input and get the values from the DB for that specific ID ... but since i dont know anything regarding the PHP and SQL relation .. i thought of learning it while i make the script :unsure:

.. Tho you guys are doing more than half the work here lol .. but it really is helping me out.

Once i get this script to show the values in a table .. THEN, i will be modifying .. to give values and get results ..

Regards
Dhanesh.
Edited July 21, 2006 by dhanesh (see edit history)

Vyoma · July 21, 2006

Tell us how it comes up.

As for the modifications, I do not think there would be many changes.

All you have to do is have a form in one HTML page, and pass the 'id' to the next .php file throught the FORM POST action. Once you have captured it into a $id variable, you just need to change the query to MySQL. Instead of:

$result = mysql_query('SELECT * FROM `marks`');

you would need to give a

$query = "SELECT * FROM 'marks' WHERE id = ".$id;$result = mysql_query($query);

And that is it.

dhanesh1405241511 · July 21, 2006

Its done ! .. finally .. well atleast the coding part .. so here is the source for the 2 files .. all you have to do is the design part .. and guess most of us can manage that :unsure: ..

I would suggest the mods to rename this post as: Using PHP to connect to a MySQL DB and show Values.

2 Files : index.php & studmark.php

index.php

<html><head><title>Enter ID</head><body><form id="getinfo" name="gi" method="post" action="studmark.php"> Please Enter Your ID Number:	<input type="text" name="id">	<input type="submit" name="submit" value="Submit"></form></body></html>

studmark.php

<html><head><title>Student Mark</head><body>  <?php$conn = mysql_connect('localhost', 'dhanesh_admin', 'admin');if (!$conn) {   die('Not connected : ' . mysql_error());}$db = mysql_select_db('dhanesh_studentdb', $conn);if (!$db) {   die ('Can\'t use foo : ' . mysql_error());}$id = $_POST["id"];$query = "SELECT * FROM `marks` WHERE id = ".$id;$result = mysql_query($query);if (!$result) {   die('Invalid query: ' . mysql_error());}if (!isset($_POST['submit'])) {} else {if(empty($id)) {echo "You did not fill in all the fields, try again<p>"; ($_POST['submit']);}else {echo "Here are Your Results, $id";}} echo "<TABLE>IDNameMarks";while ($row = mysql_fetch_assoc($result)) {   echo "<TR>";   echo "<TD>".$row['id']."";   echo "<TD>".$row['name']."";   echo "<TD>".$row['marks']."";   echo "</TR>";}echo "</TABLE>";?></body></html>

Last but not the least, I would like to thank miCRoSCoPiC^eaRthLinG & Vyoma for their help in making MOST of this script.

Regards

Dhanesh.

vhortex · July 25, 2006

Tell us how it comes up.
As for the modifications, I do not think there would be many changes.

you would need to give a
$query = "SELECT * FROM 'marks' WHERE id = ".$id;$result = mysql_query($query);
And that is it.

i am sorry but i guess your code is not the most accurate way to do it..

this one is much way better

$query = "SELECT * FROM 'marks' WHERE id = " . $_POST['id'];$result = mysql_query($query);

in the above code.. you order the php parser to load the data from the servers post channel. this is safer since there will be no variable conflicts inside your script and the post data variables.

Plus when in safe mode, the server will only accept submited data via $_GET[] and $_POST[]. When register globals is also off, the code above will still work unlike the previous version of the query.

Sign In

[help] Student DB Script In PHP & MySQL

Recommended Posts

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Important Information