Update specific field in MySQL

[bettina702]

Hi,

I want to update a specific field in my database. My database has only one row with one field in it. How do I overwrite that field? This is what I have at this point, but its not working:

mysql_query(UPDATE Table SET column = "$item" WHERE column = "1")
or die('I cannot insert into the Database because: ' . mysql_error());

I have column=1 because I wanted to select the first field in the column.

Thanks in advance for your help!!

[Fest3er]

Hi,

I want to update a specific field in my database. My database has only one row with one field in it. How do I overwrite that field? This is what I have at this point, but its not working:

mysql_query(UPDATE Table SET column = "$item" WHERE column = "1")
or die('I cannot insert into the Database because: ' . mysql_error());

I have column=1 because I wanted to select the first field in the column.

Thanks in advance for your help!!

If your table has exactly one row in it and it has exactly one field, you can simply

$query_string = "UPDATE my_table SET my_column='$item'";
mysql_query($query_string) or die ...

This will update my_column in all rows (there's only one) of the table. You don't need the WHERE clause.

You seem to be a mite bewlidered as to how a DB works. But that's OK; I think we all were at first. [Why, I remember a time when a bunch of us were sent off to 8 days of Informix training to learn every aspect of the Informix product; that was rather intense, even though the instructor couldn't teach as fast as we wanted to learn.] If I may, I'll expound a little; perhaps I can thin that fog a little. This won't be pedantically perfect, but should convey the concept(s).

• A database is a collection of information tables.
• A table is most often a collection of rows (records) of information that share a common structure as defined by the table's schema.
• A schema is the 'formal' definition of a table's structure, specified in terms of columns (fields).

That said, you stated that your database (perhaps you mean 'table'?) is defined to contain one column (field) of data, that you previously inserted exactly one row of information into the table, and that you now want to update that one item of information (or datum).

Without a limiting clause, mysql is happy to update every row. So, in your case, updating the table without a WHERE clause will update your single datum. But using a full-blown RDBMS to store a single datum is, perhaps, overkill; however, I's'll assume you are starting small and working your way up to a more complex data store.

So let's say you've now added ten more rows; your table now contains 11 rows of one datum each, and you've ensured that each datum is unique. (I'll let you guess what'd happen if the data are not all unique.) To change the value of any one of those data, you first put the value you want to change into a variable, add a WHERE clause to your query, then execute the query:

$current_value = "thisValue";
$query_string = "UPDATE my_table SET my_column='$item' WHERE my_column='$current_value'";
mysql_query($query_string) or die ...

This will update every row in the table that matches the WHERE clause; it will match only one if you've ensured that the data are unique.

As you add columns to your table and make some of them unique indices, it becomes easier to select exactly the row you want to affect, and the UNIQUE property of a column/index lets mysql ensure that there are no duplicates in that column.

Thus concludes the first lesson of RDBMS-101.

[bettina702]

Thanks for the info. I actually figure out another way to do it, which works great.