Updating MySQL table with php issue

Question

I'm sending some data through to a mysql server via android in an attempt to update some details. Currently the php side looks something like this:

for($i=0; $i<(10); $i++){
for($k=0; $k<(10); $k++){

mysql_query("UPDATE sometable SET data_".$i."_".$k." = '10'
WHERE id = '".$_REQUEST['id']."'");
}
}

I have to use a loop becuase I'll be building up lots of generic types of data with the style "data_x". Unfortunately, this layout doesn't seem to update any fields in the database.

Does this method create some type of space, or just simply disrupt a complete variable when read in a statement?

Thanks

Apart from using severely outdated functions, how do you know that variable $i is even set? Have you checked whether you're getting any sort of errors from MySQL seeing you construct field names dynamically for querying? Where's your WHERE clause? Why don't you post entire code? It's not cool to guess around what might be wrong by seeing half the puzzle. — Michael J.V.
– Michael J.V., Commented Jun 3, 2011 at 13:19
You're not checking for MySQL errors with mysql_error(), you're not cleaning $_REQUEST['id'] and worst of all you're issuing 100 queries to update a single record.. that kind of isn't how it's supposed to be done (plus a table with 100 columns smells like something bad happened to db schema). So, you can do the mentioned - check for errors with mysql_error(), ensure you have the fields in the table and clean your $_REQUEST['id'] - then we can take it from there if it still doesn't work. — Michael J.V.
– Michael J.V., Commented Jun 3, 2011 at 13:41

cwallenpoole · Accepted Answer · 2011-06-03 13:50:05Z

1

Ok, couple of things about current iteration.

Log/output your errors!
`$res = mysql_query( $query ); if( !$res ) log( myslq_error() );/* or die or whatever */`
Do one update, not 100.

$query = "UPDATE sometable SET";
for($i=0; $i<(10); $i++){
    for($k=0; $k<(10); $k++){
        $query .= ' data_'.$i.'_'.$k.' = \'10\''
        if( !( $k == $i && $i == 10 ) ) $query .= ',';
    }
}
//see side note below
$query .= 'WHERE id = ' . mysql_real_escape_string( $_REQUEST['id'] ); 
$res = mysql_query( $query );
if( !$res ) do_something_with_error( mysql_error() );

100 updates can make your database/PHP angry and the last thing you want is an angry database. Further, this only does one array lookup in REQUEST, whereas the code above does 100. (O(1) * 100 is still 100).

As a side note: just because something is supposed to be sent from Android, that is no reason to expect that it does not need to be properly escaped. Remember the lessons of Bobby Tables!

I also cannot suggest strongly enough that you reconsider your schema. That may seem to be the easiest way to handle things right now, but later developers (including yourself) will wonder what the heck was supposed to be stored there. I've worked on projects like that and they were not fun. (On the other hand, I don't know your specifics, so I could be completely wrong).

This was addressing an initial copy paste error:

At a bare minimum, PHP can't parse this line:

for(ik=0; $i<(10); $i++)

Rewrite it with the dollar sign:

for($i=0; $i<(10); $i++)

edited Jun 3, 2011 at 13:50

answered Jun 3, 2011 at 13:33

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user723789 Over a year ago

Cheers for seeing this, unfortunately I think just I hit wrong keys when copying and pasting

user723789 Over a year ago

My Man! Thanks very much, I was beginning to think of compiling some type of script Android side, but that'll probably be pointless if I did this instead. Yeah its a good call on the data set, but it should be sufficient for now if I don't think about it too often lol. Cheers again.

Mullins · Accepted Answer · 2011-06-03 13:32:45Z

0

Did you debug the code at all?

Is the query actually valid and does the generated field 'data_X_X' definitely exist in the table 'sometable'?

My guess is that the generated field name does not exist in your table.

answered Jun 3, 2011 at 13:32

Mullins

2,3641 gold badge19 silver badges19 bronze badges

Collectives™ on Stack Overflow

Updating MySQL table with php issue

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related