Mutley Posted August 21, 2006 Share Posted August 21, 2006 I know how to add data to a table in a database using PHP and also how to delete it.However, I'm uncertain when it comes to editing. Could anyone give me a run through of how I can get information in a database to be loaded in the form fields, which I can then edit live and submit to edit the data?Thank-you. Quote Link to comment Share on other sites More sharing options...
theblain Posted August 21, 2006 Share Posted August 21, 2006 <?if ($submit){<$sql = update foo where bar = $bar>;}<select * from foo>?><input type=text name=bar value=<?echo $bar?>><input type=submit> Quote Link to comment Share on other sites More sharing options...
SharkBait Posted August 21, 2006 Share Posted August 21, 2006 So in non-pseudo code:[code]<?phpif (isset($_POST['submit'])) { $strqry = "UPDATE foo SET field ='{$_POST['value']}' WHERE bar = 'woo'"; $query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error()); $num = mysql_affected_rows($query); if($num > 0) { // Database was updated } else { // database was not updated }}// Display Form$strqry = "SELECT * FROM foo WHERE bar = 1";$query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());$item = mysql_fetch_array($query, MYSQL_ASSOC);?><!-- Do your form -->Enter Value <input type="text" name="bar" value="<?php echo $item['bar'];?>" /><input type="submit" value="submit" name="submit /><!-- End Form -->[/code]Of course you'll need data validation etc but thats the jist of updating a field in SQL using an HTML form. Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 23, 2006 Author Share Posted August 23, 2006 Still can't get the jist of it.For example, my database is "scores" and I want to show all the entries in a row called "home" each entry would have a field showing the current sql/data which you can edit then resubmit.Thanks for your time so far, it is seeming alot more complicated than I origionaly thought! Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 27, 2006 Author Share Posted August 27, 2006 Heres what I tried to do:[code]<table cellspacing="0" class="scores"><tr> <td class="greenfill">Date:</td> <td class="greenfill">Home Team:</td><td class="greenfill">Away Team:</td><td class="greenfill">Result:</td><td class="greenfill" colspan="2">Score <i></i>:</td><td class="greenfill">Description</td></tr><?phprequire_once("connection.php");if (isset($_POST['submit'])) { $strqry = "UPDATE scores SET field ='{$_POST['value']}'"; $query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error()); $num = mysql_affected_rows($query); if($num > 0) { // Database was updated } else { // database was not updated }}// Display Form$strqry = "SELECT * FROM scores";$query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />". mysql_error());$item = mysql_fetch_array($query, MYSQL_ASSOC); while(list($date, $home, $away, $scorehome, $scoreaway) = mysql_fetch_row($result)) { ?> <tr> <td class="vs" colspan="8"><?=$home?> vs <?=$away?></td> </tr> <tr> <td>05-09-2006</td> <td><input type="text" name="scoreaway" value="<?php echo $item['home'];?>" /></td> <td><input type="text" name="scoreaway" value="<?php echo $item['away'];?>" /></td> <td> <? if($home == York) { if($scorehome > $scoreaway) { $home?> <p class="win">Win</p> <? } else { $away?> <p class="loss">Loss</p> <? } } else { if($away == York) { if($scorehome < $scoreaway) { $home?> <p class="win">Win</p> <? } else { $away?> <p class="loss">Loss</p> <? } } } ?> </td> <td><input type="text" name="scorehome" value="<?php echo $item['scorehome'];?>" /></td> <td><input type="text" name="scoreaway" value="<?php echo $item['scoreaway'];?>" /></td> <td>N/A</td> </tr> } }<input type="submit" value="submit" name="submit /></table>[/code]Make it list all the entries in the database table "scores" so they are all editable fields in a PHP form. Unfortunatly, as you can see, lots of errors which I'm sure to you are obvious, if anyone could quickly debug it for me please? :) Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 28, 2006 Author Share Posted August 28, 2006 I'm suprised how quickly my topic went about 5 pages back.Could anyone help me please? :( Quote Link to comment Share on other sites More sharing options...
Iceman512 Posted August 28, 2006 Share Posted August 28, 2006 Hi Mutley,I've just submitted this in another post, but it should work fine for your needs as well, just needs some modification to work for your situation.[code]<?php$con = mysql_connect("dbhost","dbuser","dbpass");if (!$con) { die('Could not connect to the database: ' . mysql_error()); }if(isset($_POST['id'])){$id = $_POST['id'];mysql_select_db("dbname", $con);$result = mysql_query("SELECT * FROM dbname WHERE id='$id';");while($row = mysql_fetch_array( $result )) {{?><form action="Update/Form/Page" method="POST"><input type="hidden" name="id" value="<?php echo $row['id']; ?>">Author:<input type="text" name="author" value="<?php echo $row['author']; ?>"><br />Date: <input type="text" name="date" value="<?php echo $row['date']; ?>">Body: </font><br /><textarea name="body"><?php echo $row['body']; ?></textarea><br /><br /><table align="center" width="800"><tr><td align="center"><input type="submit" value="Update Form"></td></tr></table></form></td></tr></table><?php } }}?>[/code]Note that it gets everything according to the unique id of each row in the db.Post again if you need help making modifications to this.Regards,Iceman Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 28, 2006 Author Share Posted August 28, 2006 [code]<?phprequire_once("connection.php");if(isset($_POST['id'])){$id = $_POST['id'];mysql_select_db("scores");$result = mysql_query("SELECT * FROM scores WHERE team_id='$id';");while($row = mysql_fetch_array( $result )) {{?><form action="Update/Form/Page" method="POST"><input type="hidden" name="id" value="<?php echo $row['team_id']; ?>">Author:<input type="text" name="author" value="<?php echo $row['home']; ?>"><br /><input type="text" name="author" value="<?php echo $row['away']; ?>"><br /><input type="text" name="author" value="<?php echo $row['homescore']; ?>"><br /><input type="text" name="author" value="<?php echo $row['awayscore']; ?>"><br /><input type="text" name="date" value="<?php echo $row['date']; ?>"></font><br /><table align="center" width="800"><tr><td align="center"><input type="submit" value="Update Form"></td></tr></table></form></td></tr></table><?php } }}?>[/code]That's what I'm trying. My "ID" row is called "team_id". It just brings up a blank page. Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 28, 2006 Author Share Posted August 28, 2006 Done this now, changed my "team_id" to "id" and some other small fixes:[code]<?phprequire_once("connection.php");if(isset($_POST['id'])){$id = $_POST['id'];mysql_select_db("scores");$result = mysql_query("SELECT * FROM scores WHERE id='$id';");while($row = mysql_fetch_array( $result )) {{?><form action="update" method="POST"><input type="hidden" name="id" value="<?php echo $row['id']; ?>">Author:<input type="text" name="home" value="<?php echo $row['home']; ?>"><br /><input type="text" name="away" value="<?php echo $row['away']; ?>"><br /><input type="text" name="homescore" value="<?php echo $row['homescore']; ?>"><br /><input type="text" name="awayscore" value="<?php echo $row['awayscore']; ?>"><br /><input type="text" name="date" value="<?php echo $row['date']; ?>"></font><br /><table align="center" width="800"><tr><td align="center"><input type="submit" value="Update Form"></td></tr></table></form></td></tr></table><?php } }}?>[/code]Still a blank white page. Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 28, 2006 Share Posted August 28, 2006 You have one too many opening curly braces ( { ) after the while statement.[code]<?phprequire_once("connection.php");if(isset($_POST['id'])){ $id = $_POST['id']; mysql_select_db("scores"); $result = mysql_query("SELECT * FROM scores WHERE id='$id';"); while($row = mysql_fetch_array($result)) { echo ' <form action="update" method="POST"> <input type="hidden" name="id" value="' . $row['id'] . '"> Author: <input type="text" name="home" value="' . $row['home'] . '"> <br /> <input type="text" name="away" value="' . $row['away'] . '"> <br /> <input type="text" name="homescore" value="' . $row['homescore'] . '"> <br /> <input type="text" name="awayscore" value="' . $row['awayscore'] . '"> <br /> <input type="text" name="date" value="' . $row['date'] . '"> </font><br /> <table align="center" width="800"> <tr> <td align="center"><input type="submit" value="Update Form"></td> </tr> </table> </form>'; } }}?>[/code] Quote Link to comment Share on other sites More sharing options...
spfoonnewb Posted August 29, 2006 Share Posted August 29, 2006 If that didnt fix it I noticed your not telling the script to connect;[code]mysql_select_db("scores");[/code]To[code]mysql_select_db("scores, $connect");[/code] Quote Link to comment Share on other sites More sharing options...
.josh Posted August 29, 2006 Share Posted August 29, 2006 1st thing i see, or don't see, rather, is any kind of query to actually update the database. Sharkbait gave an example waaay up there ^^^^^^^ but then in all your code since then..where did it go? Did you seperate that part into a target processing script that the form will send the posted variables to? This leads me to 2nd thing I see: i look at your form action, and all you have there is action="update". perhaps you meant update.php ? or is the script that you have shown, update.php? In other words, are you wanting to do all of this in one script, or are you making your form on this script here, and the part that processes the info will be in some other script? Let's assume that you were meaning for it to all be on on script. It seems to me that you want a form that auto-fills in the blanks, and you can change the values, click submit, and it updates the information in the database. is this correct? Here is a sample piece of code i threw together. it doesn't really do a whole lot. It's all on one script. There are 2 forms. One where you can enter in the id to show information on. If you enter in an existing id, it will show the information currently in the db associated with the id, in the 2nd form. you can then modify it, click on update, and it will update the db. i have 2 fields that can be altered, as an example. expand the update query and the form to suit your needs. Also, you need to change the db connection info to your own, as well as the table and column names. The ones used below are what i used to test the script. [code]<?php // connect to the database $link = mysql_connect("localhost", "username", "password") or die(mysql_error()); //select the database $db_selected = mysql_select_db("databasename", $link) or die(mysql_error()); // make some vars out of the post array for easier coding if ($_POST) { extract($_POST); } // if user clicked the submit update button if ($update) { // query string to update the information. $sql = "update test set name = '$name', type = '$type' where serialnum = '$serialnum'"; // run the query. $result = mysql_query($sql) or die(mysql_error()); } // end if user clicked submit // check to see if number was entered, and is numeric. if // either is false, set default number to zero. this is // mostly for security. $findnum = (is_numeric($_POST['findnum'])) ? $_POST['findnum'] : 0; // select information from db. if the user entered // in a serialnum then it will return a result $sql = "select * from test where serialnum = '$findnum'"; $result = mysql_query($sql) or die(mysql_error()); // if there is a result... $rows = mysql_num_rows($result); if ($rows > 0) { // get the information from the result source $info = mysql_fetch_assoc($result); // extract the data into easy to use vars extract($info); } // end if results were found?><-- plain old generic table with a border to seperate the two forms --><table border = '1'> <tr> <td> <-- form to enter in the number to search for --> <form action = "<?php echo $_SERVER['PHP_SELF']; ?>" method = "post"> id <input type = 'text' name = 'findnum'> <input type = 'submit' name = 'getinfo' value = 'getinfo'> </form> </td> </tr> <tr> <td align = 'right'> <-- form that shows the info if found, edit it and update it in db --> <form action = "<?PHP echo $_SERVER['PHP_SELF']; ?>" method = "post"> id : <?php echo $serialnum; ?><br> name <input type = 'text' name = 'name' value = '<?php echo $name; ?>'><br> type <input type = 'text' name = 'type' value = '<?php echo $type; ?>'><br> <input type = 'submit' name = 'update' value = 'update'> </form> </td> </tr></table>[/code]please understand that the only reason why i did this "out of the box" thing for you is because you and everybody else is spending a lot of time trying to debug some code that will not do what you are trying to do -- update the database -- even if you work out the syntax errors. It is not the normal function of this forum for people to write your code for you. I was just really bored, and to be honest, felt it was a lot easier to do this rather than try to get you back on track from where you're at. also note that i am not some l337 scripter so this is by no means the most elegant way of doing it. i'm sure plenty of people will offer their 2 cents on how to make it better. Quote Link to comment Share on other sites More sharing options...
Iceman512 Posted August 29, 2006 Share Posted August 29, 2006 Hi all,Crayon Violent is absoloutely right. Man! I feel like an idiot!If the snippet above doesn't work, try this:[code]<?php$con = mysql_connect("dbhost","dbuser","dbpass");if (!$con) { die('Could not connect to the database: ' . mysql_error()); }if(isset($_POST['team_id'])){$team_id = $_POST['team_id'];mysql_select_db("scores", $con);$result = mysql_query("SELECT * FROM scores WHERE team_id='$team_id';");while($row = mysql_fetch_array( $result )) {{?><form action="update_scores.php" method="POST"><input type="hidden" name="team_id" value="<?php echo $row['team_id']; ?>">Home:<input type="text" name="home" value="<?php echo $row['home']; ?>"><br />Away:<input type="text" name="away" value="<?php echo $row['away']; ?>"><br />Home Score:<input type="text" name="homescore" value="<?php echo $row['homescore']; ?>"><br />Away Score:<input type="text" name="awayscore" value="<?php echo $row['awayscore']; ?>"><br />Date:<input type="text" name="date" value="<?php echo $row['date']; ?>"></font><br /><table align="center" width="800"><tr><td align="center"><input type="submit" value="Update Details"></td></tr></table></form></td></tr></table><?php } }}?>[/code]Notice that the form action is set to '[color=red]update_scores.php[/color]' Here is the code for that file:[code]<?php$con = mysql_connect("dbhost","dbuser","dbpass");if (!$con) { die('Could not connect to the database: ' . mysql_error()); }if(isset($_POST['team_id'])){$team_id = $_POST['team_id'];$home = $_POST['home'];$away = $_POST['away'];$homescore = $_POST['homescore'];$awayscore = $_POST['awayscore'];$date = $_POST['date'];mysql_select_db("scores", $con);mysql_query("UPDATE scores SET home='$home', away='$away', homescore='$homescore', awayscore='$awayscore', date='$date' WHERE team_id = '$team_id'");mysql_close($con); echo "The selected information was updated!"; echo "<a href='somewhere.php'>Click here</a> to continue";?><!-- Some HTML Here If You Like --><?php }?>[/code]Regards,Iceman Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 29, 2006 Author Share Posted August 29, 2006 I got Crayon Violent to work but I want it to repeat like Icemans is supposed to do (but it just brings up a blank page still, if I use Icemans).I think it's a problem in the update_scores.php?Thanks so much for the help/work guys, it really means alot! Quote Link to comment Share on other sites More sharing options...
Iceman512 Posted August 29, 2006 Share Posted August 29, 2006 Hi Mutley,I think you have a problem in your connection file ('[color=red]connection.php[/color]').I have modified my previous post above. Notice that I removed the following line :[code]<?phprequire_once("connection.php");?>[/code]Just insert your information for the db fields ('[color=red]dbhost, dbuser, dbpass[/color]') and give it another try.Regards,Iceman Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 29, 2006 Author Share Posted August 29, 2006 Doesn't work still. I tried your way but I can assure you my connection file is fine. Quote Link to comment Share on other sites More sharing options...
Mutley Posted August 29, 2006 Author Share Posted August 29, 2006 I fixed it partly, changed POST to GET in the id lines. So it calls it from the URL ?team_id=2 etc.However I get this error:Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 13Which is:[code]while($row = mysql_fetch_array( $result ))[/code]There is rows in the table, can't see where this error is coming from. Quote Link to comment Share on other sites More sharing options...
SharkBait Posted August 29, 2006 Share Posted August 29, 2006 What is $result set to?Should be something similar to [code]<?php$result = mysql_query($Whatevermyqueryis) or die("MySQL Error: <br />{$Whatevermyqueryis}<br />". mysql_error());?>[/code]Not sure if your current code has it but make sure there one Curly Brace { after your while() Quote Link to comment Share on other sites More sharing options...
Iceman512 Posted August 29, 2006 Share Posted August 29, 2006 Hi Mutley,With the code I supplied earlier, I assumed you were using a form to get the data. If you want to use a link such as: [color=blue]http://www.your-site.com/the_page.php?team_id=32[/color], then just change the [color=blue][b]MySQL[/b][/color] query on the first page to look like this:[code]<?php$result = mysql_query("SELECT * FROM scores WHERE team_id='".$team_id."'");?>[/code]Let's see if that works,Iceman Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.