 [help] Student DB Script In PHP & MySQL |
|
|
|
|
  |
 Jul 20 2006, 08:30 PM
Post
#1
|
|
|
Binary Geek  Group: Members Posts: 444 Joined: 4-November 05 From: The Digital Arena Member No.: 9,440  |
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 ..
QUOTE 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 : CODE CREATE TABLE `marks` ( `id` INT( 10 ) NOT NULL , `name` VARCHAR( 30 ) NOT NULL , `marks` FLOAT( 5 ) NOT NULL ) ENGINE = MYISAM COMMENT = 'Student Marks'; 4) CODE ALTER TABLE `marks` ADD PRIMARY KEY ( `id` ) 5) CODE ALTER TABLE `marks` CHANGE `name` `name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL 6) CODE INSERT INTO marks VALUES ( 2006001, 'John', 20 );# Affected rows: 1 INSERT INTO marks VALUES ( 2006002, 'Gavin', 40 );# Affected rows: 1 INSERT INTO marks VALUES ( 2006003, 'Ter', 32 );# Affected rows: 1 INSERT INTO marks VALUES ( 2006004, 'Mac', 46 );# Affected rows: 1 7) The result looks like this :  8) I made 2 PHP scripts .. 1 to input ID and show Id on the next page .. here is the code for index.php CODE <?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 CODE <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 : CODE [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. |
 |
 
|
|
Jul 20 2006, 08:46 PM
Post
#2
|
|
|
PsYcheDeLiC dR3aMeR  Group: Admin Posts: 2,242 Joined: 29-January 05 From: Nakorn Chaisri, Thailand Member No.: 2,411 myCENTs:84.36 |
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: SQL 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: SQL INSERT INTO marks ( id, name, marks ) VALUES ( 2006001, 'John', 20 ), ( 2006002, 'Gavin', 40 ), ( 2006003, 'Ter', 32 ), ( 2006004, 'Mac', 46 );
|
|
|
|
|
Jul 20 2006, 08:54 PM
Post
#3
|
|
|
Binary Geek Group: Members Posts: 444 Joined: 4-November 05 From: The Digital Arena Member No.: 9,440 |
Sorry bro .. i was posting while u posted .. newayz .. thankx for letting me know about the 1 step bulk queries
 .. 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 ? Regards Dhanesh. This post has been edited by dhanesh: Jul 20 2006, 08:56 PM |
|
|
|
|
Jul 20 2006, 09:09 PM
Post
#4
|
|
|
PsYcheDeLiC dR3aMeR Group: Admin Posts: 2,242 Joined: 29-January 05 From: Nakorn Chaisri, Thailand Member No.: 2,411 myCENTs:84.36 |
As for your MySQL Queries.. try the following:
CODE $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. |
|
|
|
|
Jul 20 2006, 09:46 PM
Post
#5
|
|
|
Binary Geek Group: Members Posts: 444 Joined: 4-November 05 From: The Digital Arena Member No.: 9,440 |
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 CODE <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  Regards Dhanesh. |
|
|
|
|
Jul 21 2006, 12:04 PM
Post
#6
|
|
|
Cosmic Overlord Group: Members Posts: 560 Joined: 26-November 05 From: Denver, Colorado, US Member No.: 9,811 myCENTs:38.41 |
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:
QUOTE(Vyoma) To get a tabular format put the code something like this. Instead of CODE while ($row = mysql_fetch_assoc($result)) { echo $row['id']; echo $row['name']; echo $row['marks']; } put the following code: 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'? |
|
|
|
|
Jul 21 2006, 01:36 PM
Post
#7
|
|
|
Binary Geek Group: Members Posts: 444 Joined: 4-November 05 From: The Digital Arena Member No.: 9,440 |
QUOTE 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 .. 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. This post has been edited by dhanesh: Jul 21 2006, 01:37 PM |
|
|
|
|
Jul 21 2006, 01:41 PM
Post
#8
|
|
|
Cosmic Overlord Group: Members Posts: 560 Joined: 26-November 05 From: Denver, Colorado, US Member No.: 9,811 myCENTs:38.41 |
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: CODE $result = mysql_query('SELECT * FROM `marks`'); you would need to give a CODE $query = "SELECT * FROM 'marks' WHERE id = ".$id; $result = mysql_query($query); And that is it. |
|
|
|
|
Jul 21 2006, 07:33 PM
Post
#9
|
|
|
Binary Geek Group: Members Posts: 444 Joined: 4-November 05 From: The Digital Arena Member No.: 9,440 |
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
..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 CODE <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 CODE <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. |
|
|
|
|
Jul 25 2006, 05:50 PM
Post
#10
|
|
|
Guilty Until Proven Innocent Group: Members Posts: 372 Joined: 13-April 05 Member No.: 3,937 |
QUOTE(Vyoma @ Jul 21 2006, 09:41 PM)  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 CODE $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 CODE $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. |
|
|
|
|
Similar Topics
|