happypete Posted March 23, 2011 Share Posted March 23, 2011 I can get the info from the database fine with this: $sql2 = "SELECT 1, 2, 3, 4, 5, 6 FROM ratestable ORDER BY id ASC"; $stmt2 = $db->prepare($sql2); $stmt2->execute(); $e2 = $stmt->fetch(); and display it with this: <?php while($e2 = $stmt2->fetch()) { ?> <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" /> <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" /> <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" /> <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" /> <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" /> <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" /> <?php } ?> How do I update it to the database? $sql = "UPDATE rates SET title1=?, title2=?, title3=?, title4=?, title5=?, title6=? THE CODE HERE IS WHERE I AM STUCK.... and that's as far as I can get, need to use an array or foreach??????? Quote Link to comment Share on other sites More sharing options...
will35010 Posted March 23, 2011 Share Posted March 23, 2011 http://w3schools.com/sql/sql_update.asp Quote Link to comment Share on other sites More sharing options...
happypete Posted March 23, 2011 Author Share Posted March 23, 2011 http://w3schools.com/sql/sql_update.asp Thanks, but that link didn't help at all. I can update an entry in a database if it was a single entry it would be something like this: $sql = "UPDATE ratestable SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? WHERE id=1"; $stmt = $db->prepare($sql); foreach($stmt as $key) { $stmt->execute(array( $_POST['1'], $_POST['2'], $_POST['3'], $_POST['4'], $_POST['5'], $_POST['6'], ) ); } $stmt->closeCursor(); but what I can't do is update multiple entries of the same table at the same time... This code below prints out the data for EACH ID in the 'ratestable' and makes it editable, so I need to be able to update multiple rows at a time, that is what I'm having the trouble with... so I'm thinking it's going to have a loop, foreach or array or something.....?????? <?php while($e2 = $stmt2->fetch()) { ?> <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" /> <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" /> <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" /> <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" /> <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" /> <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" /> <?php } ?> Quote Link to comment Share on other sites More sharing options...
will35010 Posted March 23, 2011 Share Posted March 23, 2011 You would have to put the results in a form and submit any changes to update. Here is a decent tutorial on that http://www.freewebmasterhelp.com/tutorials/phpmysql/7. Example: Step 1: Retrieve Data into form for user to edit with value attribute set with initial value Step 2: Submit form with any changes Step 3: Write code to update those changes in the db I hope this helps! Quote Link to comment Share on other sites More sharing options...
happypete Posted March 23, 2011 Author Share Posted March 23, 2011 You would have to put the results in a form and submit any changes to update. Here is a decent tutorial on that http://www.freewebmasterhelp.com/tutorials/phpmysql/7. Example: Step 1: Retrieve Data into form for user to edit with value attribute set with initial value Step 2: Submit form with any changes Step 3: Write code to update those changes in the db I hope this helps! 1: So yes my results are in a form as per the code above.... 2: Yes sent it to this page: rates-editupdate.php ( I know I got this bit right because I have simple update code written on my other pages of the site) 3: 'Write code to update those changes' This is where I need help.. I don't know how to write the code to update the database for each for the ID's.. If it was easy to find I would have found it by now as I have been searching the web since yesterday, hence the fact I'm asking for help on this forum... The link sent me to a simple 'update a database' and a loop, so I look at the loop and cant figure how to write the code based on my example: so somewhere in the code below I will need a loop and a count???? $sql = "UPDATE ratestable SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? WHERE id=1"; $stmt = $db->prepare($sql); foreach($stmt as $key) { $stmt->execute(array( $_POST['1'], $_POST['2'], $_POST['3'], $_POST['4'], $_POST['5'], $_POST['6'], ) ); } $stmt->closeCursor(); Quote Link to comment Share on other sites More sharing options...
happypete Posted March 24, 2011 Author Share Posted March 24, 2011 updated the code to this: $sql2 = "SELECT id, 1, 2, 3, 4, 5, 6 FROM ratestable ORDER BY id ASC"; $stmt2 = $db->prepare($sql2); $stmt2->execute(); $e2 = $stmt->fetch(); <?php while($e2 = $stmt2->fetch()) { ?> <input type="hidden" name="id" value="<?php echo $e2['id']; ?>" /> <input type="text" name="1" maxlength="10" value="<?php echo $e2['1'] ?>" class="rates" /> <input type="text" name="2" maxlength="10" value="<?php echo $e2['2'] ?>" class="rates" /> <input type="text" name="3" maxlength="10" value="<?php echo $e2['3'] ?>" class="rates" /> <input type="text" name="4" maxlength="10" value="<?php echo $e2['4'] ?>" class="rates" /> <input type="text" name="5" maxlength="10" value="<?php echo $e2['5'] ?>" class="rates" /> <input type="text" name="6" maxlength="10" value="<?php echo $e2['6'] ?>" class="rates" /> <?php } ?> Can get it to update the last last ID but not all rows: $sql = "UPDATE ratestable SET 1=?, 2=?, 3=?, 4=?, 5=?, 6=? WHERE id=?"; $stmt = $db->prepare($sql); $stmt->execute(array( $_POST['1'], $_POST['2'], $_POST['3'], $_POST['4'], $_POST['5'], $_POST['6'], $_POST['id'], )); $stmt->closeCursor(); Quote Link to comment Share on other sites More sharing options...
happypete Posted March 24, 2011 Author Share Posted March 24, 2011 sorted it Here is the working code: <input type="text" name="dates[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['dates']; ?>" class="rates" /> <input type="text" name="night[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['night']; ?>" class="rates" /> <input type="text" name="week[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['week']; ?>" class="rates" /> <input type="text" name="month[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['month']; ?>" class="rates" /> <input type="text" name="min[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['min']; ?>" class="rates" /> <input type="text" name="rank[<?php echo $e2['id']; ?>]" maxlength="20" value="<?php echo $e2['rank']; ?>" class="rates" /> $sql3 = "UPDATE ratestable SET dates=?, night=?, week=?, month=?, min=?, rank=? WHERE id=?"; $stmt3 = $db->prepare($sql3); if(count($_POST['rank']) > 0) { foreach($_POST['rank'] AS $key => $val) { $stmt3->execute(array( $_POST['dates'][$key], $_POST['night'][$key], $_POST['week'][$key], $_POST['month'][$key], $_POST['min'][$key], $val, $key )); }} $stmt3->closeCursor(); 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.