Re: [PHP-DB] UPDATE lot of registers

[Chris]

Emiliano Boragina wrote:

Hello everyone…

 


I dont know how to update a lot of registers on one table.

 


I have two tables: Students and Signatures

 


STUDENTS has got: id / name / signature

SIGNATURES has got: id / name / classroom

 


I want to use words and not numbers to write the signature on the STUDENTS
table.

I’ve got a long list with signatures, and I must to change one signature...
How do I do update SIGNATURE AND STUDENTS tables o sam time if 100 students
has got the same signature and must update automatically?


The better question would be why do you need to update two tables with 
the same information?



Updating two tables at the same time will require using a transaction. 
If you're using mysql, this means the table has to be an 'innodb' table. 
The default 'myisam' doesn't support transactions.


If you don't use a transaction, you'll potentially lose your changes.

update signatures set name='New name here' where id in 
(1,2,3,4,5,6,7,8,9,10);


server dies (power loss, hard drive crash, someone reboots it) after it 
updates record 5 but before it updates record 6.


You now have half the table updated.

Even worse would be it updates the first table but not the second - now 
your data is inconsistent.



You can update multiple records at once quite easily.

Assuming 'id' is a unique (or primary) key:

Begin;

update signatures set name='New name here' where id in 
(1,2,3,4,5,6,7,8,9,10);

update students set name='New name here' where id in (1,2,3,4,5,6,7,8,9,10);

commit;

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] UPDATE lot of registers

[Chris]

[ please always send to the mailing list ]

Emiliano Boragina wrote:

Hi Chris, thanks for your ask...
This doubt was beacause I have two tables... one with categories (category
id and category name), and the other with products (product id, product
name, category id which the prod belong). The client, sometimes, change the
category name. To there no problem using the in the in the products table.


I'm still confused.

If you're changing the category name, you only need to change the 
categories table.


update categories set name='new name here' where category_id='X';


But, I must do a finder to search and list the products under category name
which it belongs, it name and other items... So the finder is not so easy to
do...


To understand this, it'd be easier in two steps to start off with:

1) Get the category_id:
select category_id from categories where category_name='XX';

2) Then get the products:
select product_id, product_name from products where category_id='X';


That can be combined in to one query:

select product_id, product_name from products p inner join categories c 
on (p.category_id=c.category_id) where c.category_name='XX';


Or am I still missing a piece of the puzzle?

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php