CODEWITHSUNDEEP
phpmysqlsql-update
Jsmith
Jsmith

Reputation: 73

Updated Individual Rows in PHP/MYSQL

I am retriving data from a SQL DB which I am stroring in a form. I have a update page however, I dont know how I can update table according to what the user wishes to change and hence update the database.

This is what I mean. Say after running the query I have the output :

Product  | Descption  | Quantity   | Price   | StartDate  | End Date
chcolates   xyz           2.0         4.99     2013-03-11    2013-03-20      
sweets      yum!          1.0         1.99     2013-03-15    2013-03-27

so this is being outputted as a table in php by queuing the db.(i.e. this seller is selling 2 products mentioned).

Now say the user wanted to update the first row and change the quantity for example. Then how can I do this? I know I can use the SQL QUERY UPDATE TABLE sellers WHERE ??

Should I output a table or a form? The user is able to update any row they wish and any field also. (i.e. can update the product they wish to sell, descp, quantity etc.)

How can I go about doing something like this? Any ideas please?

====EDIT====== Thanks for everyones suggestions. I have a formatting error. I am inside my php tags and after running the query as such:

while($rows = mysql_fetch_assoc($querySellers)){
echo 'Product:'.'<br />'.'<input type="text" value = $rows[\'ProductName\']>';

This does not seem to be printing the VALUE of the query from the DB and instead prints 

$rows['ProductName']

inside the text box this is a formatting error right? What can I do to solve this? And also initially the productName is a drop down combo box... Is there a way I can do it so that when I run the query the output is shown as the SELECTED combo box option?

Thanks once again.

Upvotes: 1

Views: 532

Answers (2)

UnholyRanger
UnholyRanger

Reputation: 1971

The best thing you should do is implement an ID column (auto increment maybe?) or some unique identifier (Primary Key) to the items in the table. This way, you can easily edit one item by doing

UPDATE table SET value = 'value' WHERE id = $id

In your case, you do not have any unique identifier so you would have to build a large WHERE statement to include everything ie.

WHERE Product = 'chocolates' AND Description = 'xyz' AND .. AND..

and this may not be entirely unique.

EDIT: (in reference to edit) The problem is how PHP handles single quotes vs. double quotes. PHP will NOT evaluate variables in single quotes but will in double quotes.

$myvar = "value";
echo '$myvar'; // output: $myvar
echo "$myvar"; // output: value

So you need to change your construction

echo "Product: <br /> <input type=\"text\" value = \"{$rows['ProductName']}\">";

Notice the change of single quotes to double and the escaping of the double quotes. As for your question on the selected option. You need a way to determine which is selected and add an IF statement with the code to add Selected attribute to the option.

Upvotes: 2

Anele
Anele

Reputation: 162

You need to have an id with primary key that auto increaments, not null. THen

UPDATE `your_db_table`.`column_table` SET `Product` 
= 'Sweet Stuff' WHERE 'Product`.`prod_id` = 1 LIMIT 1;

Then the rest of the stuff is is clearly explained by @UnholyRanger

Upvotes: 0

Related Questions