PHP MYSQL site function help!

Hi there,

So I am rebuilding an existing site with a basic PHP MYSQL system to enable non experienced site owners to be able to edit the text themselves.

I have the system working beautifully HOWEVER I need the .php to delete or overwrite the existing text before updating.

I am using the following to place the information in a MYSQL table but can't get it to overwrite the existing information:

$sql="INSERT INTO frontpage_whatson (regular_services_text)

Open in new window

Would love some help on this one... its starting to drive me nuts!
Thanks in advance, Luke
Who is Participating?
right, so you don't have an ID column, which i think you should have!

insert a new field at the beginning of the table. name it "id", set it to "int" and set "auto_increment" to be on. this means that each new piece of data added will have it's own id number - the first row of data will have "1", the next row of data will have "2" etc, and this will happen automatically when you INSERT new data, the id field will update automatically.

then when you load the data to display it on the screen (i don't have your code that does this), as well as displaying the text that you're already displaying, you'll be able to store this "id" in a variable somewhere

i'm going to assume your code for displaying the data looks something like this :
$sql = "SELECT * FROM frontpage_prayer";
$result = mysql_query($sql);
$data = mysql_fetch_array($result);

so you now have $data[id] as a unique identifier that refers to this data. no matter what else happens, we will always know that this will always refer to this data and not anything else.

so now you can do this (i'll make up variables for the new data that we're changing)

$post_id = $data[id];

$sql = "UPDATE frontpage_prayer SET title_blue='$new_title', meditate='$new_meditate' WHERE id='$post_id'";
$result = mysql_query($sql);

the key is here "WHERE id='$post_id'", it means it will update the data where the "id" field matches the $post_id variable we retrieved earlier.
INSERT INTO does exactly what it says, it inserts new data into the table. to update existing data use the UPDATE function instead.

UPDATE table_name SET data1='somedata', data2='moredata' WHERE comparison='true'
so for example, if a user was changing their email address for an account profile, you might use something like this :

$sql="UPDATE users SET email='$email' WHERE username='$user'";
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

getinkedAuthor Commented:
Will that work in this example though:

Existing text on site: (1)
So I am rebuilding an existing site with a basic PHP MYSQL system to enable non experienced site owners to be able to edit the text themselves.

New information from maintenance form: (2)
I have the system working beautifully HOWEVER I need the .php to delete or overwrite the existing text before updating.

Will 1 replace 2 with your code?
can you please be more clear in the question : when you say you "need the php to delete or overwrite the existing text", where is this existing text? on the screen? in a form? in the database?

the MySQL function UPDATE is to change (update) existing data in the database. so if the text that you want to delete or overwrite is in the database, this is how to do it.
i'm going to assume that the text is in the database. so when someone changes the existing text, you need a way to identify it.

what's the format of your table? what columns are there?
getinkedAuthor Commented:
okay... different parts of the site have their own section in the database.
The database will have the first lot of information loaded in then when the site owner wants to update the "whats on this week" section of the site he will use a web form that sends test to the database and is then echo'ed to the site where needed.

In this case  the form has two parts:
Heading & Text, These are stored in
DB_frontpage_whatson > heading
DB_frontpage_whatson > text

I have already got "Nothing is on this week" in the database for both Heading and Text, so when the owner goes to the maintenance form he will fill in the fields as required.

I want what ever he types into  either field to take place of its predecessor.

I hope this makes it a little clearer...
If you want or need screen shots or something let me know.

i could still do with knowing what columns your mysql table has. presumably it at least has two text columns for "heading" and "text". is there a unique ID column as well?

when you use the UPDATE function, you need a way of telling mysql which piece of data needs updating. normally we would have a column called "id" or something similar, which is simply a number which identifies each piece of data. so the first piece of data has id="1" the next is id="2" etc. in your case i guess we only have the one piece of data, so "id" would =1 here.

then we would tell mysql to UPDATE my_table SET heading='$new_heading', txt='$new_text' WHERE id='1'

the reason we use the id to identify the data, and not any other column, is because the data in other columns can and will change, but the unique id will always stay the same. yes you could choose to identify the data with something else, like this :

UPDATE my_table SET heading='$new_heading', txt='$new_text' WHERE heading='$old_heading'

but this requires you to already know what the old heading was, and also you have to be certain that there isn't another piece of data somewhere that shares the same old heading. for example, if we had 100 users on a system, so we have 100 pieces of data that say username, password, email, etc, and some guy chooses to change his password :

UPDATE users SET password='$new_password' WHERE password='$old_password'

this would cause ALL users who have the old password to have their passwords changed. so if 3 people have the same password, they will all change. this is not what we want! far better to use

UPDATE users SET password='$new_password' WHERE id='$user_id'

where $user_id refers to the unique id of the user we are updating. then we are certain we are only changing exactly the data we want to change.

it's clear your new to the MySQL thing - the more information you can give me, the clearer the examples i can give you to help you with. it would really help if you could tell me what columns your table has.

Aaron TomoskySD-WAN SimplifiedCommented:
There are many ways to handle update vs insert. Here is one where you define a unique index an if it already exists it will update else it inserts
There is also insert ignore, or you can just do a select and if it exists than update else insert.
getinkedAuthor Commented:
ok... heres a side question....
What about if I only want one to print at a time.... ie;

Heading One
Main Story blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah
 blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah
 blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah

Next Story >>

Is that goinmg to be hard? It may be a more suitable solution... rather than getting rid of the information...

And yes... im new at this... LOL this will be my first PHP MYSQL site :D
to only print one row, you only retrieve one row of data.
if you don't need to keep the data, it makes sense to replace it!

you still haven't told me the layout of your table yet
i suggest you work through a tutorial to familiarize yourself with some of the basic concepts

this one is good
getinkedAuthor Commented:
Okay, Sorry for the delay!
Thanks you for the link Statick it look to be a great resource! - However after a little browse through it didn't seem to answer the question for me (did teach me some tricks though!)

As for the "Tables" question i do apologise i though I had covered it... here is a screen of my phpAdmin hope this has the information your looking for!

I have had a chat to client and they like the Idea of having additional/old content avail. as per my example in "1 07:33 PM, ID: 35464203"

Look forward to your thoughts...

getinkedAuthor Commented:
This looks great thanks Statick,
I am off to the office for a few hours so will give it a run tonight.
Thanks again!
getinkedAuthor Commented:
Sorry for the delay guys... Life has been hectic!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.