How do I update a MySQL table in PHP from a submitted form?

Check out this article on PHPBuilder: http://www.phpbuilder.com/columns/laflamme20001016.php3

I'll add more links as I find them to this post.

Thanks, Abby. I know what I want to do, lol, just not how to tell anyone! I'll happily accept any and all pointers, hints and general advice! That article looks like an excellent starting place, thank you!

Okay, I've read through it (a few times) and I think I need remedial help.

A little more searching yielded only more pages that link to the article above...

It may be easier to explain this if you could say exactly what form inputs you want to use, what you want updated, and how (what values you are working with), and the rest of the details.

Are you having trouble with accessing selected values/setting up the form? Or the update query?

The update query.

It's really very simple, because while I tend to make things more complex than I need (in general when building an application), in this case, I just want to change a checkbox value from "0" to "1" when I submit the form.

There are a pile of checkboxes, each attached to one other field (the item), and I want to check the boxes, submit the form and then rebuild the form with the items already checked.

Think -- todo list, but you can undo an item that's already been done.

Eventually it will be more complex, but right now, I just need to figure out how to make the change and for some reason I'm totally stymied. I'm sure once I get it it will be so overwhelmingly clear that I will be blushing for weeks, but right now, it's just not coming through to me.

checkboxes eh?

'
Implode the checkboxes array that is sent to PHP, separating with whatever isn't in the values for the checkboxes, and then add it to what was already in there, by selecting what was in the DB and adding it together... IE

That's not the most proficient way to do it, but it'll work...

Another way you could do it, is to take the already checked boxes and put them in checked boxes on the form... that way, you don't have to select then add on the same handler, you'd take the data from the SELECT statement on the form, and UPDATE on the handler...
Is this what you are looking for?

Number One:
Can I see your DB layout? How is the stuff stored where your pulling it from to print the form.

Number Two:
For now, I'll do a mock example....

'This isn't tested... I'm just trying to give a general idea. This script also assumed register_globals is on.

I'm fighting with how to best do it. The db that is.

At the moment I have two ideas -- one is that everything is in one big table -- category, item, checked, id. The other is that there are separate tables for everything, so a table of categories, a table of items for each category (with the checked field).

I'm sorry I'm so disorganized, I'm not sure how to best do this. At the moment, the data is all in one table.

I think that having it in separate tables would be better?

I have to take my step-son to the orthodontist right now, but I'll make a .phps file and the database structure when I return. I really appreciate any help that will get this through my thick head.

I like to keep everything as normalized as I can get it. Meaning that I put everything in different tables, and link them together with foreign keys.

'

Still not tested... but I re-designed the DB layout.

Mark, I'm returning to try this again. I've modeled the db based on your suggestions, and thankfully the break I took allowed the concept to sink in. Thanks for the clear explanation on how to set up the database.

I'm going to try the code. I changed my field names to match the ones in your example to make it work better, but I'm not sure how it connects to the db in the first place. Can you (or someone) help me fill in this missing leap?

I have the details (host, username, password, database name, table names) but I'm not sure how it talks to the script. Little words, please!

Suzanne

I don't have too much time right now. I'll try to get back to it later. (If I don't send me a friendly reminder. )

But basically put this near the top of your PHP file, before any mysql_ functions:

You don't have to use the define() statements. I just like to put these in an included file so their global (constants are not restricted to scope).

Okay, thanks Mark.

Please don't worry about coming back -- if you have the time I would certainly appreciate it, but I'll suss it out eventually on my own if you don't. I really do appreciate all the time you've taken thus far.

S

Quote: Originally posted by Suzanne

$update = "Update $tableName set $FIELD='$fieldvalue' WHERE $KEYFIELD=$keyfield" or something like that?

i'll just point out that building a query like that can be a huge security risk if that data is coming from the browser. you *could* potentially be giving visitors free reign of your database.

imagine if someone invoked your script with this query string:
?tableName=mysql.user&KEYFIELD=User&keyfield=root&FIELD=Host&fieldvalue=myserver.com
?tableName=mysql.user&KEYFIELD=User&keyfield=root&FIELD=Password&fieldvalue=mynewpassword

I'm sorry, ROB, I have no idea what that query string is, though it appears to be changing the password?

This script is currently password protected, so they'd have to get access to that password first, but it won't be password protected forever, so I'll keep your warning first and foremost in my mind. I assume that Mark's code, while making less intuitive sense to my newbie brain, is more secure?

Also, if this matters, the variables like $tableName are there for illustration purposes -- I would use the actual table name and field name.

Quote: Originally posted by Suzanne

Also, if this matters, the variables like $tableName are there for illustration purposes -- I would use the actual table name and field name.

yeah, that's the issue. as long as you did that it'd be fine, otherwise you'd essentially be letting visitors build any update query they wanted to.

gotcha, thanks for clarifying!

So much to learn, good thing I'm doing this on my own time!

I don't mind spending time answering your questions. I was just in a hurry last time I posted. Things have been falling apart at school, and I've been running around to get my classes re-organized. Things have finally come together, and this should be my last semester -- provided no more classes are canceled on me.

Did I answer your question about connecting to the DB? Is that what you were looking for?

My code has hard coded table names, so it's more secure.

The script does assume register_globals is on, so some may consider that a flaw. Newer versions of PHP (>4.2.x) have register_globals default to off, but you can change this. I also don't like using the new global vars like $_POST yet, as older versions of PHP don't support them and not all hosts have updated to 4.2.x versions yet (like me, I'm still on 4.0.6).

Quote: Originally posted by Mark Hensler
I also don't like using the new global vars like $_POST yet, as older versions of PHP don't support them and not all hosts have updated to 4.2.x versions yet (like me, I'm still on 4.0.6).

a bit off the topic of this thread, but i've been adding the following bit to the beginning of scripts i plan to distribute:

granted it's not perfect, theyre not superglobals like the real ones, so you gotta remember to declare them global in functions, but at least you don't have to resort to using deprecated features.

this has also inspired me to write a PHP4 version library, which will define php 4.2 functions that are not defined in the running 4.x version. I'll post it here when i'm done.

Good idea... I could use a pre-made lib, but I'm too lazy to make one right now.

I'm still not there, but it's only because I keep having to work and pack and then start all over again forgetting what I learned. Yes, the database connection information got me connected and now I have no $result, so I'm working on debugging that.

I've set aside my own code for comparison if I can get yours working. I'm sure I have just done something daft, lol, so give me a couple of days and I'll be able to report success or failure for certain.

The good news is my brain no longer hurts when trying to build a database sensibly. I've built a few more and planned a couple, to see if I got all the pieces connected properly. Displaying them may be a little more of a challenge, but at least I have all the information mapped out.

S

P.S. Edit: yes, something daft, must have the same table and field names, lol... working on it now.

okay, I'm stuck.

I have my original version working as far as displaying the categories and headings (so far so good), but I'm stuck on this part of yours, Mark:

Why? I don't know what it means, or how it works, so I can't edit it (if needed?) to fit the column/table names...

Any ideas?

Suzanne

I'm sure Mark will make it clear next time he's here but in the mean time I'll have a crack.

$query = what your going to get the database to do
SELECT = what your going to do, could be SELECT, UPDATE ..
t.task_id, t.name as task_name, ... = are the database columns or tabled sections
FROM = will point to the database table
task t = table name
LEFT JOIN = not 100% sure on this one, think its add to or collect from second db table
category c = could be the second db table
ON t.category_id=c.category_id" = not sure about this either, think its where t.cat..id is equal to c.cat..id from second table
ORDER BY = which order the results will be
cat_name = catagory name
ASC = ascending
task_name = catagory name
ASC = ascending

in my terms
select rows of content from the database named task_t, match it with second db and match id numbers and display results in ascending order.

say you have id number, name and phone number in one table
1 - allan - 12345678
2 - bob - 98765423
3 - carl - 5246382
and second table with stuff brought, id number, product and serial num
1 - book - 112233
2 - pen - 223344
3 - paper - 887753

if bob brought a pen it would match these and diplay his details next to product info, if everyon brought a book, would display them all by matching the different tabled id numbers.
Hope this has helped and not made it worse. I'm sure Mark can explain it better

Okay, I actually learned something last night reading my MySQL book (incredible!) about joins and finally understood what was happening in that code. Your explanation, Busy, makes it very clear, thank you.

I got the opinion of another php programmer I know about using implode. I've been having problems making it work and she suggested that having implode in the sql update command wasn't working because it was reading it as a string since it's a php function.

SO... to use it first, then run the query.

However, she also came up with an alternate option, which is because all the checkboxes have distinct names, to use those names to update the tables.

I think I'm explaining that right. Each checkbox is named "ch".$details["task_id"] -- so ch1, ch2, ch3, chx. Then using a while loop, if this checkbox is selected (value=1), then update this row.

Lets see what I can clear up.
(That means get ready for a long read.)

~=~=~=~=~=~=~=~=~=~=~

Quote: The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see section 6.4.1.1 JOIN Syntax. For each table specified, you may optionally specify an alias.

'
I just told it to retrieve information from the `task` table and the `category` table, and I gave those tables the aliases `t` and `c` (respectively). This allows me to use `t` and `c` to reference these tables rather than typing out the whole table name (yes, I can be that lazy).

~=~=~=~=~=~=~=~=~=~=~

'
I specified that this query would be retrieving records from both the `task` and `category` tables.

The ON join_condition speficies which fields a match is to be made.

JOIN queries take records from all specified tables and create a new record set containing fields from all specified tables. Being a LEFT JOIN, all records in the left table will be returned, with or without a matching record in the right table. However, records in the right table must have a match in the left table to be returned. So all fields from the left table will contain data, while fields from the right table may contain data or be NULL (no match in right table).

~=~=~=~=~=~=~=~=~=~=~

Quote: You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See section 6.1.2 Database, Table, Index, Column, and Alias Names, for examples of ambiguity that require the more explicit column reference forms.

Quote: A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses.

'
So, I told it to retrieve the `name` field from the `task` (`t`) table and gave it the alias `task_name`. I gave it an alias because the `category` (`c`) table also has a `name` field, which I gave the alias `cat_name`. Now, in the returning result, these two colums would be labeled `task_name` and `cat_name`. If your going to use mysql_fetch_array(), this is important to know.

~=~=~=~=~=~=~=~=~=~=~

'
Fairly simple, order the results by `cat_name` ascending first, then `task_name` ascending.

~=~=~=~=~=~=~=~=~=~=~

mySQL Docs:
6.4.1 SELECT Syntax
6.4.1.1 JOIN Syntax

Ah ha! Okay, now I know how to edit it, lol. I have to fix the table names. Thank you!

In case I sound incredibly bizarre, I am in fact writing this two ways, one using your code, Mark, and one using my own (oh-so-special) logic, lol.

What I'm wondering is if your code is more efficient than mine. I.e. what the benefits of using join are v.v. what I'm doing. What I suspect is that your code is better for scalability.

But I'm going to get them both working, and then report back (eventually) with my conclusions and the code for both.

Opinions on the best method are encouraged and welcomed!

S

Normalizing a database makes it more scalable, but costs more time per query (because of the extra work done to join tables).

I like to normalize as much as possible, but my projects are usually more complex than a todo list.

Looking forward to hear of your progress. Good luck!

As I suspected, then. I'm still having a hard time figuring things out, but I will persevere.

What I'm currently having trouble with is this not working and I can't see why...

Doesn't return me anything I can use.

And here is the page: http://www.synapticimpulse.com/dana/checklist.php and if you try to use the form, it goes to the part that is supposed to update the table.

I'm sure it's something incredibly simple.

Edit to add: okay, I figured out that the increment script needed to be elsewhere, so it would test all the checkboxes. But I still can't get it to work. Ideas?

Edited again: I updated the code. I have the same problem I had when I started this thread -- I cannot figure out how to get the name of the checkbox and the value into the script properly. I can get it to set ALL the rows to checked, or ALL the rows to not-checked, but not a SPECIFIC row to checked or not-checked.

Please, someone, smarten me up!

you have the page http://www.synapticimpulse.com/dana/checklist.php password protected so i can't see what youre doing with the checkboxes.

one thing that sticks out, is this
if ($x == "yes")'
should be something like
if($checkbox[$x] == 'yes')'

not sure if this is exactly what you're trying to do, but it may give you some hints:

'

oh, sorry! I forgot about the password protection:

username: moving
password: ackackack

Sorry, it was late and my eyes were crossing. Maybe I do need to use implode after all.

ok what you were doing makes more sense now. one thing i'd definately change:

'

what this will do is cause your script to receive an array of checked id's rather than recieving seperate variables.

you can test this with something like:

so now that you know all the id's which are checked, you know everything else is unchecked. unfortunately, anything that is not checked is undefined and you only know it's not defined because of it's lack of existence, which means you need to be aware of it's existence beforehand. i really hate this because it makes it difficult to deal with large arrays of dynamic checkbox items, but there are workarounds.

since we know what is checked, i'd mark everything unchecked(in the database) and then check the ones we know are checked like so:

hope that helps

would it be possible to only set the value to 1 since the checklist is printed in order and the task_id is the checklist name? In order to validate, the checklist has to have a unique name...

?

ah, in that case you can do

'

then, you would get you're array of checked keys like:

Am I right in hearing that you're saying this is a horribly awkward way to do it? (And thank you, I think it's beginning to dawn on me how this works).

So, in order to get the array as needed, what would the code look like? This is what I have:

Would I do this?

Now that I'm this far into it, I can see how ordering by task id would be a problem if you, say, wanted to put items in and take items out.

Perhaps I need to entirely rethink how to approach this. I'm still having trouble with Mark's script, mostly because I have to learn how it works in order to edit it to fit, and I'm trying to do this and pack and sell everything we're not moving, lol.

I'm thinking when I get to Ontario I'm going to sign up for a course at UW and see how if that helps...

in answer to your question "would i do this?"...

as far as your concern about ordering by id, i'm not sure i understand. if you mean, by deleting records and effectively removing an element from the id order, that's not a concern. 1,6,3,9 will sort the same as 1,2,3,6,7,8,4,5,9.

That should fix your previous question.

Rob, I think the ordering concern is when you add a new record and want to stick it between two other entries.

If this is the case, you simply add another field (lets say "`sort` int(11) unsigned NULL default '0'"). Then sort on the `sort` field. This, of course, adds a layer of complexity as you'll need to provide a method of updating the `sort` number for existing records.

With the latest changes Rob made, your script should start breathing soon.

You know what I don't see on that list?
"Send Mark china to pack"

That must be why it hasn't arrived yet.

*rofl* oh dear, I knew I was forgetting something!

Here's the plan:

1. Figure out how to make this work (before I move, hopefully, I'll be working on it again tomorrow).
2. Once it's working, figure out how to add and delete whole items (not just checkboxes).
3. Something happens here.
4. I become a master programmer and rule the world...

in the meantime, thank you both for your example code, your time, and your patience with me figuring out how this works. I'm glad I had it half right on the counting, and I understand the list concept for getting the # of items (the MySQL book and site were misleading, darn their souls!).

Thanks for the very thorough explanation of the arrays, ROB. I'm familiar with multi-dimensional arrays (and referencing them in PHP thanks to Mark) but my foray in MySQL means I don't have the cognitive infrastructure to understand how this is all actually put together in the language (PHP? MySQL? Which one is doing what has been tripping me up).

You guys, quite simply, rock.

Okay, boys and girls, don't pass out!

I didn't get the good code working, but I have three versions, lol. The one that was closest to my original thought is the one that's fully working. Now I have to go back and understand what ROB is doing (so close, I am, so close) and then, what Mark is doing. This is so good, to have different ways of doing the same thing to see how they work.

Anyway!

LOOK!

synapticimpulse.com/forsale.php

Heh. Not the same page, an entirely different page, with the checkboxes and submit buttons hidden and the update script password protected, but the main list totally displayed.

WHEE!