[SOLVED] Updating MySQL Row

[9three]

I have 5 fields in total that store information into my database. Here is one of them:

 

if(!empty($_POST['title']))
{
$title = mysql_real_escape_string(ucfirst($_POST['title']));
$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='WebsiteTitle'");	
}

 

As you can tell, when the field in my html is filled it is updated in my database.

 

The problem, if I use:

"UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='WebsiteTitle'"

 

My information is not updated. But, if I use:

"UPDATE mainsettings SET WebsiteTitle='$title'"

 

It works perfectly. The problem is that it updates all columns, although I only have one table and row to worry about in this part of my script so it doesn't really matter. I'm just trying to figure it out for future purposes.

[gevans]

	

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='WebsiteTitle'");

 

Is the field WebsiteTitle called WebsiteTitle??

 

Can you show your database structure, and content?

[9three]

	

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='WebsiteTitle'");

 

Is the field WebsiteTitle called WebsiteTitle??

 

Can you show your database structure, and content?

 

Basically it goes like this:

 

DB_Name

  ->MainSettings

            Fields:

          ->WebsiteTitle, var char 255, Primary key, not null

          ->WebsiteSlogan, var char 255, not null

          ->etc, var char 255, not null

          ->etc, var char 255, not null

          ->etc, var char 255, not null

[gevans]

Ok, so let me explain the query you have so far

 

   

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='WebsiteTitle'");

 

UPDATE the table 'mainsettings'. SET the field 'WebsiteTitle' to equal var $title WHERE the field WebsiteTitle is equal to 'WebsiteTitle'.

 

That last WebsiteTitle needs to be a variable!! otherwise it will always look for the string WebsiteTitle which I imagine does not exist, you may as well be running this query;

 

   

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='blablarandomstuff'");

 

[9three]

thanks for the quick replies. I tried setting it like so:

 

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE WebsiteTitle='$title'");

 

but it didn't change anything nor any errors were thrown it. I'm not sure what suppose to go there really :/

[gevans]

Well, if you're setting WebsiteTitle = $title there won't already be a WebsiteTitle that = $title

 

the WHERE WebsiteTitle=$oldtitle"  //i just called that old title is used to search for the row to update in the database, so you have to know the current WebsiteTitle before updating it!

[9three]

Well, if you're setting WebsiteTitle = $title there won't already be a WebsiteTitle that = $title

 

the WHERE WebsiteTitle=$oldtitle"  //i just called that old title is used to search for the row to update in the database, so you have to know the current WebsiteTitle before updating it!

 

Unfortunately, for me I'm no longer home, I have evening classes now so I can't test it.

 

I had tried to leave the (WHERE WebsiteTitle=") blank so that it didn't matter what the old one was earlier in the day today and it didnt work either. If I *have* to search for the old title first, what would be a good method to do that?

[gevans]

Well, the only option is to find what row you want to update and then update it, or update every row

 

What is the page before this one?

[9three]

Well, the only option is to find what row you want to update and then update it, or update every row

 

What is the page before this one?

 

It's only one row there is no increment. lol

[gevans]

True, but if there was more you'd need to do this.... that was your initial question right?

[9three]

Yea that was my initial question. So how do I find out what is suppose to go between the quotes? This is really confusing lol

[gevans]

Ok, what's on the page before this, is it all getting sent from a form?

[9three]

Yea its all one form there is no previous page. I also added an extra field: ID - that is the new primary key. I attached a pic of my table from my phpmyadmin side. I don't know if it will help. I put random text just to have a filler for it.

 

[attachment deleted by admin]

[kev wood]

are you just adding a new field to the mysql table or are you updating an existing field which is already in the table.  if you are just inserting a new field then you should use

 

$db_title = mysql_query("INSERT INTO mainsettings (WebsiteTitle) VALUE ('$title')"); //check the brackets are correct untested

 

if you are updating the table now that you have an id that is the auto increment you can use this to update the website title that you want.  but you would have to either know what the row id number was that you wanted to update with out checking or have all the rows displayed on a page before or on the same page so that the correct id number can be viewed.

 

the code for this would then look like

 

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE id='$id'");

 

this would then update the table mainsettings find the column named WebsiteTitle with the specified id and update the information accordingly.

 

 

 

 

[9three]

are you just adding a new field to the mysql table or are you updating an existing field which is already in the table.  if you are just inserting a new field then you should use

 

$db_title = mysql_query("INSERT INTO mainsettings (WebsiteTitle) VALUE ('$title')"); //check the brackets are correct untested

 

if you are updating the table now that you have an id that is the auto increment you can use this to update the website title that you want.  but you would have to either know what the row id number was that you wanted to update with out checking or have all the rows displayed on a page before or on the same page so that the correct id number can be viewed.

 

the code for this would then look like

 

$db_title = mysql_query("UPDATE mainsettings SET WebsiteTitle='$title' WHERE id='$id'");

 

this would then update the table mainsettings find the column named WebsiteTitle with the specified id and update the information accordingly.

 

I'm only updating the table. Thanks for clearing that up for me. Here is something strange though.... If I use this code

"UPDATE mainsettings SET WebsiteTitle='$title' WHERE id='0'"

It updates the section I want, which in this case is my web site title. But when I use the same format in my 2nd field:

"UPDATE mainsettings SET WebsiteSlogan='$slogan' WHERE id='0'"

It won't update anything for me. How weird is that? I used id='0' because its the first row

 

EDIT:

 

That's weird, I just gave it a 2nd try and they all work fine but the last field

elseif(!empty($_POST['copyright']))
{
$copyright = mysql_real_escape_string(ucfirst($_POST['copyright']));
$db_copyright = mysql_query("UPDATE mainsettings SET WHERE Copyright='$copyright' id='0'");
}

 

Its the same exact code from the top except the name changes.... It's the only one that doesn't work. Now this confuses me.

 

I think what's happening here is that my php code is bad. I noticing that I can only update one field at a time for it to go through properly. I'm currently using a bunch of If and elseifs to check if any of the fields contain a string. If they do it gets updated in the table. Is there any other way to approach this method?

 

[attachment deleted by admin]

[revraz]

Learn troubleshooting techniques.

 

Echo your Querys to see if your variables are set.

 

Use mysql_error() after your queries to see if errors are returned.

 

Doing both of these will point you to your problem.

[kev wood]

the id is set to auto increment it will start on 1 and not zero.  why this is i dont know because everything else to do with computers and counting always starts from 0.  try it with a 1 in stead of the 0 and see what the results are then.

[9three]

Actually zero works fine, I just noticed I had a typo...oops... hehe

 

I actually fixed an error I had. I was using elseif statements when I should've been using IFs statements to always check for a string in a field.

 

Thanks everyone for all the help.   

[crtreedude]

Learn troubleshooting techniques.

 

Echo your Querys to see if your variables are set.

 

Use mysql_error() after your queries to see if errors are returned.

 

Doing both of these will point you to your problem.

 

Just a comment - this is the answer. Not to the error, but why you had the problem. Beginning programmers actually think computers do what we tell them, old geezers know better. I have 30 or so years experience writing code and I can tell you it ain't done till you check all the lines. Learn to use debuggers and traces - or pull your hair out.

 

And learn to do unit testing.

 

Okay, ole geezer lecture over, for now. 

[kev wood]

it k we all need help sometimes.  revraz made a good point about the error checking tho you should always check for errors when entering data into the db's it can save alot of time and effort.

[9three]

yep thanks for the advice