Greysoul Posted January 19, 2011 Share Posted January 19, 2011 So i'm trying to update multiple rows of data at once..and for some reason i don't think i'm getting the "WHERE" right in my update query. if i hit update, of course nothing happens. i really hate looking like a retard but i don't have much choice as i'm stuck i was trying to make it simple by just updating one column at a time but i'm even failing at that. the checkbox is for deleting...that function works fine and i did not post that delete query. i was however, trying to use it to get the ID..so it would know WHERE to update each row. help :)The form: <form name='william' method='post' action=''><?php$bills="SELECT * FROM bills WHERE Owner = 'Both' OR Owner = 'William' ORDER BY Owner,DueDate";$result=mysql_query($bills);$count=mysql_num_rows($result);$x=0;while($row = mysql_fetch_array($result)) {$x++;echo "<tr><th class='centerbox'>" . "<input name='checkbox[]' type='checkbox' id='checkbox[]' value='" . $row['ID'] . "'>" . "</th><th>" . "<input type='text' size='10' name='wname[$x]' class='form' value='" . $row['Name'] . "'>" . "</th><td>" . "<input type='text' size='10' name='wpa[$x]' class='form' value='$" . $row['ProjAmnt'] . "'>" . "</td><td>" . "<input type='text' size='10' name='waa[$x]' class='form' value='$" . $row['ActAmnt'] . "'>" . "</td><td>" . "<input type='text' size='10' name='wdd[$x]' class='form' value='" . $row['DueDate'] . "'>" . "</td><td>" . "<input type='text' size='10' name='wc[$x]' class='form' value='" . $row['Company'] . "'>" . "</td>";if($row['PayMethod'] != check)echo "<td>" . '<a href="' . $row['PayMethod'] . '" target=_blank>' . 'Website </a>' . "</td></tr>";elseecho "<td>" . "Check" . "</td></tr>";++$x; }$billtotals= mysql_query("SELECT SUM(ProjAmnt) AS projected, SUM(ActAmnt) AS actual FROM bills WHERE Owner = 'Both' OR Owner = 'William'");while($row = mysql_fetch_array($billtotals)) {$wbp=$row['projected'];$wba=$row['actual'];echo "<tr><th>" . "<input name='delete' class='button' type='submit' id='delete' value='DEL'>" . "</th><th>" . "Totals" . "</th><td>" . "$" . number_format($row['projected'],2) . "</td><td>" . "$" . $row['actual'] . "</td><th colspan='3' class='buttonbox'>" . "<input name='update' class='button' type='submit' id='update' value='Update'>" . "</th></tr></form>"; } This is the query: <?php$updatewilliam=$_POST['update'];$checkbox = $_POST['checkbox'];if($updatewilliam){$size = count($_POST['wname']);$x = 0;while ($x < $size) {$wname = $_POST['wname'][$x];$wpa = $_POST['wpa'][$x];$waa = $_POST['waa'][$x];$wdd = $_POST['wdd'][$x];$wc = $_POST['wc'][$x];$wid = $checkbox[$x];$query = "UPDATE bills SET Name = '$wname' WHERE ID = '$wid'";$result = mysql_query($query) or die ("Error in query: $query");++$x;if($result){echo "<meta http-equiv=\"refresh\" content=\"0;URL=bills.php\">";}}}?> Link to comment Share on other sites More sharing options...
thescientist Posted January 19, 2011 Share Posted January 19, 2011 you should echo $wname and $wid in that while statement to verify what values are going to be used in the query. Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 it echo's the name, but for only for the field that already has something in it. doesn't show the ID..so i suppose thats my problem..not sure how to correct it however..or if i even need the ID as my where clause. I guess i'd rather it, incase i have two fields with the same name. Link to comment Share on other sites More sharing options...
ShadowMage Posted January 19, 2011 Share Posted January 19, 2011 it echo's the name, but for only for the field that already has something in it. doesn't show the ID..so i suppose thats my problem..not sure how to correct it however..or if i even need the ID as my where clause. I guess i'd rather it, incase i have two fields with the same name.The checkbox needs to be checked in order for its value to be submitted.You can add mysql_error to your die() statement to see if there was an error running the query:$result = mysql_query($query) or die ("Error in query: $query<br />".mysql_error());And, yes, you do need the WHERE clause otherwise every single record will have the Name set to the same value.Also, you might want to consider using mysql_real_escape_string on your variables before using them in a query to protect yourself form SQL injection. Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 yes i know i need it, i just don't seem to be figuring out where to pull it from up top in the form ugh. i'm fairly good at missing the obvious. i didn't think about the checkbox needing to be checked, that makes sense..hm..well, is there a way to pull like an invisible ID there so i don't have to show it in my tables visibly lol Link to comment Share on other sites More sharing options...
ShadowMage Posted January 19, 2011 Share Posted January 19, 2011 well, is there a way to pull like an invisible ID there so i don't have to show it in my tables visibly lolcould you rephrase that? I'm not sure I understand. You have to have some way to tell PHP which rows you want it to update. There are a couple of ways to do it and having a checkbox with an id as a value is probably the simplest. Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 well i don't want the checkbox to have to be checked in order to update it. i wanted that to just be for deleting. is there a way i can get the ID from the form without actually displaying the ID on the page. Link to comment Share on other sites More sharing options...
ShadowMage Posted January 19, 2011 Share Posted January 19, 2011 well i don't want the checkbox to have to be checked in order to update it. i wanted that to just be for deleting. is there a way i can get the ID from the form without actually displaying the ID on the page.You could maybe use javascript to update a hidden input whenever the user makes changes to a field, but that could end up being a lot of work. You'd have to have some sort of system to identify which id each of your inputs corresponds to.By displaying on the page, do you mean in the rendered view of the page (what the average user sees) or the source code? A checkbox's value will only appear in the source code. I'm still going to advocate the usage of the checkbox. There has to be something sending the id's of the records that were updated back to PHP. That's all there is to it. You can't just submit a form with a bunch of inputs and expect PHP to know which records those inputs belong to. Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 again, i know there has to be an ID associated with the records, thats what i want. i don't mind if its in the source, i just don't want it displayed in my table. there's no reason for the user to see the ID's for the bill name..he just has no use for it and its taking up spacei just referred to myself in third person Link to comment Share on other sites More sharing options...
ShadowMage Posted January 19, 2011 Share Posted January 19, 2011 again, i know there has to be an ID associated with the records, thats what i want. i don't mind if its in the source, i just don't want it displayed in my table.Ok. Then your options are:Use the checkboxorUse something more complicated, like the JavaScript solution I mentioned in my previous post.At least that's all I can think of. Perhaps others have better ideas. Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 <input type='hidden' size='10' name='id' class='form' value='" . $row['ID'] . "'>hows that? seems to work pretty good, dunno if its "correct" lol. its printing the ID's in my echo ... names appear to be printing in the echo as well, just not taking to the DB Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 its only echo'n the first 7 ID's in my information, and it is letting me update them and change them how i want. why only 7 and not the whole table? lol Link to comment Share on other sites More sharing options...
Greysoul Posted January 19, 2011 Author Share Posted January 19, 2011 fixed, thanks for the help Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.