Im new to PHP and need help editing data on mysql

[albumfreaks]

Hey guys im new to PHP and i want to learn it!

so far ill explain what ive done and whats not working, i hope someone can help me.

 

m trying to make a form to edit tables on my mysql database, i lack alot of php experience and have just started to play with it for the first time ever.

 

so far ive made it show it shows the tabels in the database and ive made a form to submit to the database and they work fine

 

i just dont know how to make another form to edit things i have already submitted.

This is for a revirew site and im trying to make something so the staff can update status's on reviews from say.... in progess to ... finished

or something

 

....here is the php my form uses to write stuff to the database.

 

<?php

//caputure data

$Artist= $_POST['Artist'];
$Album = $_POST['Album'];
$Status = $_POST['Status'];

// Connect to the database

mysql_connect ("localhost", "test", "") or die ('I Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("test");

//query

$query="INSERT INTO TestTable (Artist, Album, Status)VALUES ('".$Artist."','".$Album."','".$Status."')";

mysql_query($query) or die ('error in the database');
echo "database updated with: " .$Artist. " ".$Album." ".$Status ;

?>

 

ike i said that work fine and it will add new artists to the database

 

but can anyone help me on going about updating/editing them once they are submitted.

[.josh]

The first thing you want to do is query your database for the current information.  What your query string will look like depends on what you're wanting to edit.  If your table is for instance account information for people, each row should have some kind of uniquely identifying data, like a user id.  So you will want to select the row from the table by that user id (or whatever).  A basic query for this might look like:

 

select * from table where userid = '$userid'

 

Now you will get $userid from some kind of initial input from the user.  Something as simple as a form to get an id will work.  A common example of this is with login scripts, or account editing scripts.  Someone enters in a user name and password and you would select data from the table based on those, or after they are logged in, you'd retrieve their account information based on their userid that you would have retrieved from the login script.

 

After you have selected the data you wish to edit, you will want to make a form with fields that match the data.  Now, in each form field you can add the value = "..." attribute.  You will put the retrieved data inside those quotes, and the information will display in your fields as default values in your form.

 

From there, you will be able to edit the data at run time in the form just like normal, and as far as adding the altered data back into the table, you will use an update query (instead of an insert query), based on that same user id.  A basic update query will look something like this:

 

update tablename set name = '$name', email = '$email' where userid = '$userid'

 

the $name and $email will be the posted variables from the form, just like how you did with the insert query, and the $userid will be the user's id that you can have carried over from a hidden form field or a session variable or whatever.

[albumfreaks]

well its artists that im using but they dont have ID's they just have names

So ill give them ID's and try again, i think i get what ur saying like i said im new so i will probs have more questions.

Will i have to use the update query in my code and duplicate it for every artist, so that each one can be edited also will this mean if someone needs to edit the artist they will need the artist ID?

 

thanks for the reply

[.josh]

well...you could do a blanket select * from table and make a form display for every single artist, but that's pretty inefficient.  I mean, 1 or 2 artists is one thing, but if you plan on having hundreds of rows...

 

Anyways, you're going to need some kind of unique identifier for each of your rows in your database. That's the way databases work.  If you have 10 books on a shelf and you ask someone to get you "a book," that doesn't really tell them which book you want.  Even if you narrow it down and say give me a book by "some author," well, you could have 10 books on that shelf by that author.  That's the trick to databases.  Think of it as a giant bookshelf with lots of rows and even lots of shelves.  Think old-school like how they group books together by subject/genre, then by title and/or author, and finally by serial numbers (that whole dewey decimal system thing).

 

So the efficient thing to do is have a unique identifier for every single row.  Other users and even you do not need to necessarily know the id number.  I mean, it really depends on what kind of data you are holding in your database and how you have it setup.  Let's for example say that you have artists in your table and each row has to do with their songs and there's a different row for each song.  You could make a form to do a search for the artist.  Let's say you put "Janet Jackson" into the form it could return all rows that have to do with Janet Jackson.    You could then make it to where you click on the one you want, and it will show the form with the editable information.  But again, it really depends on what data you have and your intentions of your site, as to how you're gonna do this.

 

But the uniquely identifying id for each row is going to be a necessity regardless; that's for your code to figure out which row to update.  If there's only going to be one row per artist and each artist's name is unique, then for all intents and purposes, that could be the unique id per row. 

 

It sounds to me like the first thing you need to do is sit down and make a flowchart of your website.  Make some diagrams on paper showing what exactly it is you're wanting to be able to display and edit.  From there, you will be able to decide how to design your database structure, and from there, you can design how to get your information from the database to the webpage and back again. 

 

 

[albumfreaks]

thats a great way of putting it! thanks for the reply

well i have a website this is just add on basically the structer of what i want is

 

Artist: blah blah

Album: cheese hits

Status : review in progress

 

Artist: blah blah
Album: cheese hits
Status : review in progress

is what i have it doing at the moment, when i add artists

the only thing i really need to edit is the status

so people can change it from E.G. review in progress, to review complete

when they are done, but yeah ill get onto the ID's to help this process.

 

 

[.josh]

yeah so you're basically going to want to have a table that looks like this (with example rows):

 

idartistalbumstatus

1Artist1somealbumsomestatus

2Artist2somealbumsomestatus

3Artist3somealbumsomestatus

4Artist1somealbumsomestatus

 

Now notice that there's 2 rows in there with the same artists, because as you know, artists can have more than one album.  So you can't do a query to select rows to edit based on artist name, because you will get more than one result back.  But you CAN make a query to do that and then display them and then users can pick which one to edit. Your query would include getting the id for that row.  They click on the album (or whatever) and since your code knows the id, you do a query to get that specific information.  Or rather, you would already have queried and gotten that information, so all you have to do is take that same information and put it in your value = "" attributes in your editing form (no need to query the db twice for the same info).  Then your script would UPDATE the row based on that id, so it knows which one to update. 

[albumfreaks]

ahhh sweet thanks for the help i will give it a go later and see what happens its 7am and ive been up all night so not tryin it now

 

thanks for all the help