RestlessThoughts

From my understanding the mysql database should read NULL (without quotes) or '' as equal to NULL or nothing just fine, the same as it handles not putting in a value for the ID just fine.

Well they're logging in, meaning you have a users table? If so, I'd suggest add a new field to that table labeled Tracker or whatever. Depending on how the form for entry is set up (unless they're going through phpMyadmin?) change it so that every time they submit the form, another query is preformed that bumps up the Tracker number by one. Then when they click logout, add a new snippet of code that emails you their tracker number and resets it back to zero. I don't have a code onhand to give you to modify, but if you have problems making the script edits just post again.

Sorry! Yes of course. //Update Class name. $size = count($_POST['class']); // start a loop in order to update each record. $i = 0; while ($i < $size) { // define each variable. $class= $_POST['class'][$i]; $classID = $_POST['classID'][$i]; $results = $_POST['results']; $query = "UPDATE `database`.`table` SET `Class`='$class' WHERE `ID` = '$classID' LIMIT 1"; mysql_query($query) or die ("Error in query: $query"); foreach ($results as $line) { $insertLine = explode("\n", $line); for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i])))); $query = "UPDATE `database`.`sametableasbefore` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `ID` = '{$classID}'"; mysql_query($query) or die ("Error in query: $query"); } $i++;}

This is a repost from this topic. Thanks to Mchl I've weeded out what I believe to be the problem, and figured it'd be best to post a new topic so others wouldn't have to go through the whole thread. I am using a modified code to draw out individual values from a textarea using foreach and explode, that I got from here. This works fine, except it updates every row with the same data. The problem is it's not reading the ID of the rows. I have a seperate ID for each box in a hidden field in the form. Printing out the query shows me it's getting only the one ID number for the WHERE clause. It's not looping through the ID numbers properly and I'm not sure how to make it. If I stick another foreach loop in there, it goes through the IDs fine, but ends up going through all the IDs for each row and hence updating every row with every result, ending with every row having the last result. How annoying. This is the relating form code. $stuff = mysql_query("SELECT * FROM `database`.`table` WHERE ShowID=$info") or die(mysql_error()); while ($in = mysql_fetch_assoc($stuff)) { echo "<TR><TD><input type=\"hidden\" name=\"classID[]\" value=\"".$in['ID']."\"><input type=\"text\" size=\"60\" name=\"class[]\" value=\"".$in['Class']."\"></td></tr><TR><TD>"; echo "<textarea input type=\"text\" name=\"results[]\" rows=10 cols=60>"; }echo "</textarea></td></tr><TR><TD>"; And this is the code that I can't get to update properly. foreach ($results as $line) { $insertLine = explode("\n", $line); for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i])))); $query = "UPDATE `database`.`table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `ID` = '{$classID}'"; mysql_query($query) or die ("Error in query: $query"); } The class updates fine with the id using a while loop, but I can't get a while loop to work correctly with the foreach loop (causes it to repeat a lot). Help, please?

Hm... Maybe stick it in a div layer and absolute position it?

I saw your site! It looked quite fun! And I don't even like truth or dare haha. Anyway when I had a similar problem, I created two tables. In this case, one for the game information, one for the players. There's probably better ways to do it, but I did it like so: $s = mysql_query("SELECT GameID FROM `GameInfoTable`") or die(mysql_error()); while ($fetch = mysql_fetch_assoc($s)) { $count = mysql_num_rows($s); $sID = 1; $sID = $count+$sID; } $GameID = $sID; Then insert into the players table using the $GameID. Every new game will have a unique ID then and if you'd like you can more easily store other information about specific games that are the same for every player (such as the Final Score or date the game was played).

I don't know if there is a specific way to do that (would be useful if there was) but for your problem have you considered using ID numbers, and having a table with usernames to ID numbers, that way they keep the ID number for life and only one table needs updated everytime they change their username? It would mean selecting the table with names to ID everytime you wanted to print their name but it'd be easier to change it then.

Sorry I didn't notice the table info wasn't easy to read. ----------------------------------------------------------------------------------------------------------- |ShowID | ID (which is auto-increment) | Class | Champion | Reserve Champion | 1st | ..all the way to 10th | ----------------------------------------------------------------------------------------------------------- Anyway this is a screenshot of how the form looks. This can go on for a two hundred text boxes. The array prints out like this: [ 0 ] => Name Name Name [1] => Name Name Name [2] => Name Name Name And it's exploded using the perviously posted code. This works fine. Right now it updates the last box only. I have a seperate ID for each box in a hidden field in the form. Printing out the query shows me it's apparently getting only the one ID number for the WHERE clause. It's not looping through the ID numbers properly and I'm not sure how to make it. If I stick a foreach loop in there, it goes through the IDs fine, but ends up going through all the IDs for each field and hence updating every field with the last result. How annoying. :-\

Teehee, right-o Thanks for wanting to help some more. Class table I'm trying to insert the data into: ShowIDID (which is auto-increment)ClassChampionReserve Champion1stall the way to 10th It inserts fine into the columns that I want it to, just only one row is updated and usually the wrong one haha. Right now it's set up so the Class is updated before Champion-10th, and the Class updates correctly using the ID and the while loop from the link above. Can't get the rest to work, though it's updating from a textarea with an explode... maybe that's why. I think if I put everything into it's own input text box instead of all together into a textarea it'd probably work, but wouldn't it be as easy to update then.

Oh so you want to populate the table based on a specific date or agent. Okay then, you're on the right track. First create your drop down lists. Then use a IF and ELSEIF statements to search the table for specific information. The query will look something like: $stuff = mysql_query("SELECT * FROM `zevasce` WHERE salesagentname=$salesagentname") or die(mysql_error()); while ($in = mysql_fetch_assoc($stuff)) { TABLE HTML } And it should throw out all the information (should be no need for the $i statements). Do you need help building the dropdown lists or the IF, ELSEIF statements?

Teehee, yeah long nights staring at code, who needs real sleep after that especially when there's more code waiting! In other news...I still haven't figured out how to fix my own bloody script.

I'm not totally sure what you mean by filtering the data by date or name. What are you filtering out of the results? Do you mean search by date or name? Could you possibly give an example of what you're looking for? I.E. do you want users to be able to select a date, then things that happened on that date are shown?

Well if you try it like this: $query="insert into Class_Cl (Cl_Act,Cl_PK,Cl_S_Name,Cl_F_Name,Cl_Notes,Cl_R1,Cl_R2,Cl_R3,Cl_R4,Cl_R5,Cl_R6,Cl_R7,Cl_R8,Cl_R9,Cl_R10,CL_Unknown) values ('".$ClAct."','""','".$ClSName."','".$ClFName."','".$ClNotes."','".$ClR1."','".$ClR2."','".$ClR3."','".$ClR4."','".$ClR5."','".$ClR6."','".$ClR7."','".$ClR8."','".$ClR9."','".$ClR10."','".$CLUnknown."')"; mysql_query($query); $s = mysql_query("SELECT Cl_PK FROM `Class_Cl`") or die(mysql_error()); while ($fetch = mysql_fetch_assoc($s)) { $count = mysql_num_rows($s); $sID = 1; $sID = $count+$sID; } $ClPK = $sID; $query="insert into Class_Alias_Cla (Cla_Cl_PK,Cla_F_Name) values ('".$ClPK."','".$ClFName."')"; mysql_query($query); It should insert the record into Class_Cl then take $ClPK from it before sticking the information into Class_Alias_Cla, making both records have the same number in the tables. And you don't need to auto_increment Class_Alias_Cla since the records are to always match up, though you can make it the primary key if you're afriad of an accidental overwrite. If that doesn't work for you let me know.

The code looks like it's all there though, just spread out over the pages. This is basically how I do links for same page changes: <tr><TD><a href = {$_SERVER['PHP_SELF']}?mode="add">Add</a></td></tr> <TR><TD><?php echo $id['name'] ?></td> <TD><a href = {$_SERVER['PHP_SELF']}?mode="edit">Edit</a></td> <TD><a href = {$_SERVER['PHP_SELF']}?mode="delete">Remove</a></td></tr> You can also direct to another page through <a href="otherpage.php"> for adding/editing/deleting if you'd like. And you might even be able to do the link just as you posted, with index.php?mode=whatever, but if you check out the last page of the tutorial you linked from you'll see about.com suggests using php_self (that way you don't have to update links if you change the webpage name). Hope that helps.

Hm, so you mean $ClPK is generated when a record is inserted into table Class_Cl? Then you shouldn't have $ClPK in your first query, just " " is fine and the database should generate the number. Then you have to get the number back out of table Class_Cl before inserting data into Class_Alias_Cla. There's several ways to do this I'm sure. Google 'last ID after insert' for some ideas. When I had to do something like this, I ganked the last inserted ID from one table like so: $s = mysql_query("SELECT Cl_PK FROM `Class_Cl`") or die(mysql_error()); while ($fetch = mysql_fetch_assoc($s)) { $count = mysql_num_rows($s); $sID = 1; $sID = $count+$sID; } $ClPK = $sID; Which isn't very elegant and doesn't work well if you're inserting more than one row at a time. Tis a start for you though.

I've never seen ' id="add" ' in a form before. Try changing the 'name' of the submit button to 'add', or the 'if(isset)' to 'submit'. Maybe that'll help.

"%22" means there's a space in the URL. I'm not totally sure I understand your problem, but here's my guess at a solution. Before you insert the links into the database, strip the slashes (ie: stripslashes($link). You don't need the slashes in a MySql database, it seems to handle quote marks just fine, only when using php (and you can add the slashes back with addslashes($link) if need be). So yes, keep taking out the slashes from the database and the links will hopefully work correctly.

You're welcome, glad it helped. Anyway. If $ClPK is auto incrementing, where is $ClPK coming from? Is it a number being pulled from a form, or another table in the database? I get numbers from one table to the next by selecting the info from the table first and just inserting it into the next, but if you already have $ClPK it should be inserting into both tables.

Try putting "mysql_query($query);" after the first query as well as after the second. Right now you're replacing the first query with the second.

Thanks for your help though, got me on the right track with most of the script Hope you have/had a good night!

Thanks anyway, but that does something weird. It puts the last box in the first place and doesn't change any of the other results. It may be doing something else, I'm not totally sure. Seems to be defying my logic. I'm about at my wit's end with this script. I did leave out the first bit of code by accident. This is the full part of what's not working but should (and does when it's just an insert). $results = $_POST['results']; foreach ($results as $line) { $insertLine = explode("\n", $line); for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i])))); $query = "UPDATE `table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = '{$classID}'"; } mysql_query($query) or die ("Error in query: $query"); Darn it, I got everything else working but this tiny code makes everything come crashing down, deleting info willy nilly!

Oh nope, only got part of it working. :-X The results part is defeating me. Still replacing every results box with the last entered, even with a counter and while loop. $insertLine = explode("\n", $line); for ($i = 0; $i < 14; $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i])))); $query = "UPDATE `database.table` SET `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = '{$classID}'"; } mysql_query($query) or die ("Error in query: $query"); If anyone has any suggestions for making several results textareas update into a database, spreading out among columns as above and for mulitple rows, I'd love to hear it! This code works for inserting mulitple rows, so why not for updating ??? lol I'm stumped.

Thanks! That pointed me toward the right terms to search for and this site gave me my answer: http://www.theblog.ca/update-multiple-rows-mysql My script works now Thank you again!

Hello, I guess can't quite understand how to properly use arrays. I have a form that generates a different number of fields depending on what's being updated. Specifically here, show results.There's a class/title box then a textarea of winning names, which are split on the line break to be inserted into the database. My problem is every title and textarea is transformed into the very last one, rather than each title and box keeping their own info. This is part of the form that relates to the code: $stuff = mysql_query("SELECT * FROM `database`.`table` WHERE ShowID=$info") or die(mysql_error()); while ($in = mysql_fetch_assoc($stuff)) { echo "<TR><TD><input type=\"hidden\" name=\"classID[]\" value=\"".$in['ID']."\"><input type=\"text\" size=\"60\" name=\"class[]\" value=\"".$in['Class']."\"></td></tr><TR><TD>"; echo "<textarea input type=\"text\" name=\"results[]\" rows=10 cols=60>"; }echo "</textarea></td></tr><TR><TD>"; And this is the code that's causing me headaches: foreach($_POST['class'] as $k => $v) { // update name in the table $sql = "UPDATE `database`.`table` SET `Class`='$v' "; } // end foreach foreach($_POST['classID'] as $ka => $va) { $sql .= "WHERE `ID`='$va' LIMIT 1"; $resultc = mysql_query($sql); if (!$resultc) { echo "<p>Query: $sql</p>"; echo "<p>MySQL Error: " . mysql_error() . "</p>"; } } //As an aside, this doesn't seem to work either, but formating the data is of less concern to me than actually being able to update it! $vowels = array("RCH:", "CH:", "RCH ", "CH ", "Ch ", "Rch ", "Top Ten", "Champion ", "Res Champion", "Ch:", "Rch:", ":", ".", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"); $results = str_replace($vowels, NULL, $results); //I partly modified this code from a post on this message board, it works great when inserting. //But my use here must be flawed as the last enty ends up replacing everything else. foreach ($results as $line) { $insertLine = explode("\n", $line); for ($i = 0; $i < count($insertLine); $i++) $insertLine[$i] = mysql_real_escape_string(trim(htmlentities(stripslashes($insertLine[$i])))); $queryc = "UPDATE `database`.`table` SET `ShowID`='$ShowID', `Champion`='{$insertLine[0]}', `Reserve Champion`='{$insertLine[1]}', `1st`='{$insertLine[2]}', `2nd`='{$insertLine[3]}', `3rd`='{$insertLine[4]}', `4th`='{$insertLine[5]}', `5th`='{$insertLine[6]}', `6th`='{$insertLine[7]}', `7th`='{$insertLine[8]}', `8th`='{$insertLine[9]}', `9th`='{$insertLine[10]}', `10th`='{$insertLine[11]}' WHERE `RealResults`.`ID` = {$classID[$b]}"; $queryc = substr($queryc, 0, -2); $resultc = mysql_query($queryc); if (!$resultc) { echo "<p>Query: $queryc</p>"; echo "<p>MySQL Error: " . mysql_error() . "</p>"; } echo "Show classes updated."; I get an error currently because the foreach on the Class part causes the 'WHERE...' to tack on twice instead of once. Otherwise the code goes through error free but as I said replaces everything with the last class name & results box instead of keeping stuff seperate. Any help in getting these arrays to properly update would be fabulous!

Ah, Envexlabs will probably tell you this. Either change your form method to GET (which will change the address bar) or where it says $_GET in your string change it to $_POST. Should work then.