Page 1 of 2

[Solved] Update a column gives 1 no matter what you enter

Posted: Tue Mar 13, 2007 4:47 pm
by UrButtFullOfArr0ws
The essential part in my program is a table with 10 lines and 14 columns. I made a quick 13-line way (using FOR statement) to skip all of the "insert into database values(a1=1, b1=1,..., x1=1,a2=2,b2=2... etc etc... )" for my table, 140 entries :P.
That worked neatly enough so i used it in displaying the table also.
When it came to update the database entries through the same way it inserted data, it just seemed to edit the very first column of all rows that were being edited and entering 1 or 0 in that cell.
Explaining:
I entered 50 for first coulmn 50 for second column etc etc for 1st row.
I entered 60 for first column 60 for second column etc etc for 2nd row.
etc etc

The data was being inserted correctly.

I changed in the table first column with 40 second column with 40 etc. for 1st row
and so on...
and i pushed the update button.

In the database all the entries for first column , after update, came up as 1 or 0. The others changed values as normal (only after first update);

Any help or ideas before i start posting code?
I woulda posted code now but i have to change it so it'll fit in here and im too tired for that right now. Going to sleep.

Posted: Tue Mar 13, 2007 5:26 pm
by RobertGonzalez
No ideas until you post some code.

Posted: Wed Mar 14, 2007 1:51 pm
by UrButtFullOfArr0ws
Ok, here it goes :

Insert query:

Code: Select all

If ( $_POST[submit] == 1){
for($a = 0; $a <= 9; $a++){
$afm = "afm" .  $a;
$sql = "INSERT INTO database VALUES('" . $afm . "')";
$data = mysql_query($sql, $con);
}
}
Update query:

Code: Select all

If ( $_POST[submit] == 2){
for($a = 0; $a <= 9; $a++){
$afm = "afm" . $a;
$oldafm = "oldafm" . $a;
$sql = "update e9 set afm='" . mysql_real_escape_string($_POST[$afm]) . "' where afm='" . mysql_real_escape_string($_POST[$oldafm]) . "'";
$data = mysql_query($sql, $con);
}
}
Passing the old value for the update query, this one again IF submit >= 1

Code: Select all

If ($_POST[submit] >=1){
for($a = 0; $a <= 9; $a++){
echo "<input type='hidden' name='oldafm" . $a ."' value='" . $_POST[$afm] . "'>";
}
}
And lastly the table cell which belongs to the IF statement above:

Code: Select all

echo "<td align='center' width='188' style='border-style: solid; border-width: 1px' bgcolor='#339966'><input type='text' name='afm" . $a . "' value='" . $_POST[$afm] . "'></td>";
I've only entered the 'afm' column here but there are 13 more in absolutely same way.

So basically it works like this:
User goes to a blank table containing all 14 columns, enters some values, pushes 'Submit' (INSERT) button and the data is entered in the database.
It then prompts him again with the same table with all 10 rows but with the exact same data the user entered in previous page, the submit button now writes 'Submit Changes' and with the addition of a 'Data entered succesfully in our database.'
If a user changes any value (or doesnt even change it) and pushes 'Submit Changes' (UPDATE this time) it would go and update the database correctly for the other columns, but the afm column is changed into 0 or 1 (got no idea by what criteria it enteres 0 OR 1), but only for the entries he had previously inserted.
Example:
First page (INSERT page):
I enter 100 in the afm column ( this is afm1 ) and 200 in second column (dummycolumn1).
I enter 200 in the afm column (afm2) and 300 in the second column(dummycolumn2).
etc etc...
Data is entered...
Second page (UPDATE page):
I change 100 to 111 in the afm column ( this is afm1 ) and 200 to 222 in second column (dummycolumn1).
I change 200 to 222 in the afm column (afm2) and 300 to 333 in the second column(dummycolumn2).
etc etc...
dummycolumn1 is 222, dummycolumn2 is 333 but afm1 and afm2 are either 0 or 1 (not neccesarily the same).

Thx.

Posted: Wed Mar 14, 2007 2:02 pm
by RobertGonzalez
So this is where your problem is:

Code: Select all

<?php
If ( $_POST[submit] == 2){
for($a = 0; $a <= 9; $a++){
$afm = "afm" . $a;
$oldafm = "oldafm" . $a;
$sql = "update e9 set afm='" . mysql_real_escape_string($_POST[$afm]) . "' where afm='" . mysql_real_escape_string($_POST[$oldafm]) . "'";
$data = mysql_query($sql, $con);
}
}
?>
Try this (it will not update anything, it will only show you what the database will see so you can troubleshoot):

Code: Select all

<?php
if (isset($_POST['submit']) && $_POST['submit'] == 2) {
  for ($a = 0; $a <= 9; $a++) {
    $afm = 'afm' . $a;
    $oldafm = 'oldafm' . $a;
    $sql = "update e9 set afm='" . mysql_real_escape_string($_POST[$afm]) . "' where afm='" . mysql_real_escape_string($_POST[$oldafm]) . "'";
    // Echo out your SQL query to see what the database is seeing
    echo '<p>For a at position ' . $a . ' we have afm set to ' . $afm . ' and oldfm set to ' . $oldfm . '. Now for the SQL:<br />' . $sql . '</p>';
    //$data = mysql_query($sql, $con);
  }
}
?>
I am going to guess that the update is not liking the POST vars that you are using. You may want to view source on the HTML that is sending the form and make sure the form fields are named the same as what is being pushed into the query.

Posted: Wed Mar 14, 2007 2:16 pm
by UrButtFullOfArr0ws
Heh, already did debugging before you said that ;)

Code: Select all

oldafm = 321
update e9 set afm='789' and surname='321' where afm='321' and surname='321'
^ There i left surname intact...

Code: Select all

oldafm = 123
update e9 set afm='654' and surname='123' where afm='123' and surname='321'
^ Here both are changed...

Code: Select all

oldafm = 654
update e9 set afm='123' and surname='654' where afm='123' and surname='321'
^ Here afm was intact...

The mysql SELECT query gives me:

Code: Select all

afm     | surname
 1        | 321
 1        | 123
 1        | 654
Duh :S :x

Posted: Wed Mar 14, 2007 3:49 pm
by RobertGonzalez
Those queries are not the same queries as in the script above, are they? They look different:

Code: Select all

update e9 set afm='" . mysql_real_escape_string($_POST[$afm]) . "' where afm='" . mysql_real_escape_string($_POST[$oldafm]) . "'

Code: Select all

update e9 set afm='789' and surname='321' where afm='321' and surname='321'
This last query's syntax is a bit off.

Posted: Wed Mar 14, 2007 4:26 pm
by UrButtFullOfArr0ws
Lol...
Yes they're different...
No they're not different from my side.
The debug shows also the "dummycolumn" i talked about in this case surname.
It's there to show that no matter what i change the afm still gets changed to 0 or 1.

Posted: Wed Mar 14, 2007 4:34 pm
by volka
UrButtFullOfArr0ws wrote:Lol...
Yes they're different...
No they're not different from my side.
:?:

Posted: Thu Mar 15, 2007 7:25 am
by UrButtFullOfArr0ws
volka wrote:
UrButtFullOfArr0ws wrote:Lol...
Yes they're different...
No they're not different from my side.
:?:
What i meant is that what i have is the same with what i wrote except that i ommited the code for the second column becouse there was no need to reapeat it... It's absolutely the same.
So those queries look different to you but they're not. :)
Everah wrote:This last query's syntax is a bit off.
Where am i wrong? :S

Posted: Thu Mar 15, 2007 8:28 am
by volka
please provide complete example code. Something we can copy&paste and test.

Posted: Thu Mar 15, 2007 9:19 am
by UrButtFullOfArr0ws
volka wrote:please provide complete example code. Something we can copy&paste and test.
:!: :!: :banghead: I'd say you're a bit lazy but you'll say that other ppl need help too so i guess you're right.

Code: Select all

If ( $_POST[submit] == 1){ 
for($a = 0; $a <= 9; $a++){ 
$afm = "afm" .  $a; 
$surname = "surname" .$a;
$sql = "INSERT INTO database VALUES('" . mysql_real_escape_string($_POST[$afm]) . "', '" . mysql_real_escape_string($_POST[$surname])  . "')"; 
$data = mysql_query($sql, $con); 
} 
} 
If ( $_POST[submit] == 2){ 
for($a = 0; $a <= 9; $a++){ 
$afm = "afm" . $a; 
$surname = "surname" . $a;
$oldafm = "oldafm" . $a; 
$oldsurname = "oldsurname" . $a
$sql = "update e9 set afm='" . mysql_real_escape_string($_POST[$afm]) . "' surname='" . mysql_real_escape_string($_POST[$surname]) . "' where afm='" . mysql_real_escape_string($_POST[$oldafm]) . "' and surname='" . mysql_real_escape_string($_POST[$oldsurname]) . "')"; 
$data = mysql_query($sql, $con); 
} 
} 
If ($_POST[submit] == Null){     //Not sure about it...this is just for your sample code.... i pass submit = 0 from previous form. (don't care much about security atm)
for($a = 0; $a <= 9; $a++){ 
echo "<table><form method='POST' action='yourfile.php'><td align='center' width='188' style='border-style: solid; border-width: 1px'><input type='text' name='afm" . $a . "'></td>";
echo "<td align='center' width='188' style='border-style: solid; border-width: 1px'><input type='text' name='surname" . $a . "'>";
echo "<input type='hidden' name='submit' value='1'><input type='submit' value='Insert'></form>";
}
}
If ($_POST[submit] >=1){ 
for($a = 0; $a <= 9; $a++){ 
echo "<table><form method='POST' action='yourfile.php'><tr><td align='center' width='188' style='border-style: solid; border-width: 1px'><input type='text' name='afm" . $a . "' value='" . $_POST[$afm] . "'></td>";
echo "<td align='center' width='188' style='border-style: solid; border-width: 1px'><input type='text' name='surname" . $a . "' value='" . $_POST[$surname] . "'></td>";
echo "<input type='hidden' name='oldafm" . $a ."' value='" . $_POST[$afm] . "'><input type='hidden' name='oldsurname" . $a ."' value='" . $_POST[$surname] . "'><input type='hidden' name='submit' value='2'></tr>";
echo "<input type='submit' value='Update'></form>";
} 
}
That should be it... it might have a mistake or two but i didn't copy-paste all of it. You add the beginning and the e.o.f. code.
La-Z-Boy :D :wink:

Posted: Thu Mar 15, 2007 10:54 am
by volka
It's not laziness, I just don't trust your small code snippets and the arbitrary output. And I knew this would be code-hide-and-seek from the beginning. Nothing personal, just experience. And as usual in such case the "hidden" code is erroneous and unstructured. My guess is that is why people are so protective and secretive when it comes to posting code. And as usual there is no quick answer like "remove the second xyz".
Let's start with the form code. String literals in php can span more than one row. Let's use this to bring a little more structure to the html code.

Code: Select all

if ($_POST['submit']) {
	for($a = 0; $a <= 9; $a++) {
		echo '<table>
				<form method="POST" action="yourfile.php">
					<!-- missing tr element -->
					<td align="center" width="188" style="border-style: solid; border-width: 1px">
						<input type="text" name="afm' , $a , '" />
					</td>
					<td align="center" width="188" style="border-style: solid; border-width: 1px">
						<input type="text" name="surname', $a, '" />
						<input type="hidden" name="submit" value="1" />
						<input type="submit" value="Insert" />
					<!-- missing /td, missing <tr -->
				</form>
			<!-- missing /table -->
		';
	}
}
please correct the errors and indent the second form code accordingly.

p.s.:
UrButtFullOfArr0ws wrote:it might have a mistake or two but i didn't copy-paste all of it.
Of course you didn't.

Posted: Thu Mar 15, 2007 11:12 am
by UrButtFullOfArr0ws
volka wrote:It's not laziness, I just don't trust your small code snippets and the arbitrary output. And I knew this would be code-hide-and-seek from the beginning. Nothing personal, just experience. And as usual in such case the "hidden" code is erroneous and unstructured. My guess is that is why people are so protective and secretive when it comes to posting code. And as usual there is no quick answer like "remove the second xyz".
Let's start with the form code. String literals in php can span more than one row. Let's use this to bring a little more structure to the html code.

Code: Select all

if ($_POST['submit']) {
	for($a = 0; $a <= 9; $a++) {
		echo '<table>
				<form method="POST" action="yourfile.php">
					<!-- missing tr element -->
					<td align="center" width="188" style="border-style: solid; border-width: 1px">
						<input type="text" name="afm' , $a , '" />
					</td>
					<td align="center" width="188" style="border-style: solid; border-width: 1px">
						<input type="text" name="surname', $a, '" />
						<input type="hidden" name="submit" value="1" />
						<input type="submit" value="Insert" />
					<!-- missing /td, missing <tr -->
				</form>
			<!-- missing /table -->
		';
	}
}
please correct the errors and indent the second form code accordingly.

p.s.:
UrButtFullOfArr0ws wrote:it might have a mistake or two but i didn't copy-paste all of it.
Of course you didn't.
First off, the "all of it" was refering to the code for just those 2 columns.... i don't see the need to show you anymore columns becouse that's pointless and would slow down anyone who is trying to work this out.
Second, why should i give my whole code away open to the public when tons of users visit this site daily and one of them might see my site and he "might" decide to check out my security gaps.... I said "might" as that's a not a really high probability but not willing to take chances at all. "Weak security" but not "No security".
Thirdly, the problem couldn't possibly be in the rest of the code... Let's say it was my "Name" column wrong (just making a wild guess):
1) it couldn't affect the very first column
2) even if it affected it, it wouldn't turn it to 0 or 1 (still no idea why 0 or 1)
3) All of the other columns wouldn't work at all
and
4) I'd get a mysql error if the mysql syntax was wrong
Of course you didn't.
Why are you in this mood??

Anyway... If that php code was my code you were refering to, then i'd have to tell you that i manually rewrote it like that out of boredom... that wasn't entirely copy-paste i just read the code from my mind (checking at times with the actual code) and i wrote it, becouse it was faster then copy-pasting and then adjusting.
And what do you mean with "missing tr" or "missing </table>"... those aren't missing in my actual code. Duh..
:x :x :roll: :roll:

P.S.:
UrButtFullOfArr0ws wrote:i don't see the need to show you anymore columns becouse that's pointless and would slow down anyone who is trying to work this out.
Pointless as in ALL of the other columns are written in the same way.
P.S.S: I've double-checked and triple-checked and quadruple-checked for syntax errors and i've found none. And btw you asked for a code to copy-paste to a file and run it... not for my very own code. I just want to think that the error(s) is(are) in the logic and not in the actual code.
volka wrote:Ok, pointless. Good Luck.
Ehm... ya... whatever...
I don't wanna turn this into a flame war but, wth is your problem???
Would adding TONS OF USELESS code help you??... i've checked for syntax errors 1000 times before i even posted here. Geeze. Tyvm btw. :!: :!: :!: :!:
volka wrote:It's not laziness, I just don't trust your small code snippets and the arbitrary output. And I knew this would be code-hide-and-seek from the beginning. Nothing personal, just experience. And as usual in such case the "hidden" code is erroneous and unstructured. My guess is that is why people are so protective and secretive when it comes to posting code. And as usual there is no quick answer like "remove the second xyz".
*cough*all knower my butt*cough* why do you have to act like one?... Excuse me when i say, you've got knowledge about PHP and MySQL (at least that's what the posts show) in my eyes but not about people.
I know you guys over here are doing (as far as i know) voluntary work but that doesn't mean you can really just throw us to the garbage if you don't like what i'm saying...I hope im not talking about everyone that helps ppl out when i say "you" but that's my impression...
Anyway i'm getting off-topic, so is any1 willing to help? :?

Posted: Thu Mar 15, 2007 11:17 am
by volka
Ok, pointless. Good Luck.

Posted: Thu Mar 15, 2007 11:40 am
by RobertGonzalez
@UrButtFullOfArr0ws: Volka is trying to help you. If you don't like his help or don't want his help, then don't take it. I think he is frustrated because he wants to help you and is asking questions of you that would lead him to be able to help you, but you are not answering his questions with the answers that will help him help you. Again, he is trying to help you. If you don't want his help, don't take the help, but you don't need to respond to him, or any other poster, in that way.