Главная » Языки программирования

Php update нескольких полей

Содержание
  1. Как одним запросом обновить множество строк?
  2. 3 ответа 3
  3. How to update multiple columns in mysql using php
  4. 5 Answers 5
  5. Updating multiple MySQL table columns using arrays with PDO
  6. How to update two MySQL tables with PHP?
  7. 8 Answers 8

Как одним запросом обновить множество строк?

Вопрос:
$array_update — массив записей для обновления БД.
Как написать аналогичный запрос на UPDATE нескольких записей по выборочным id ? Учитывая, что записей может обновляться порядка 10-ти штук за 1 раз.
Как должны выглядеть значения массива $array_update ? Обновление Мне необходимо сделать апдейт следующим образом: у меня есть список уникальных id и, соответствующие каждому id , строки для апдейта (данные в строках могут повторяться). Необходимо одним запросом сделать апдейт всех строк, где поле id соответствует текущему id из списка. Почитал про INSERT . ON DUPLICATE KEY UPDATE. пока не понимаю, как применить к моей ситуации.

@lommusic, какое-то странное обновление, где нужно десяток записей изменять. Ощущение, что структура БД не совсем правильно составлена. Данные на обновление для всех записей одинаковы?

Запрос, как обычно, но условие или через OR, или IN: UPDATE table_name SET field = ‘new_value’ WHERE id IN (1,2,50, 100, N); Если вы говорили о каких-то массивах, то в данном случае — это может быть массивом id-шников: $query = «UPDATE table_name SET field = ‘new_value’ WHERE id IN («.implode(‘,’, $array_id).»)»; Кроме того, есть конструкция INSERT . ON DUPLICATE KEY UPDATE, которую так же можно использовать для обновлений, при условии наличия уникальных/первичных ключей. Но в вашем случае, как я понял, этот способ не подойдет.

@Deonis мне необходимо сделать апдейт следующим образом: у меня есть список уникальных id и, соответствующие каждому id, строки для апдейта (данные в строках могут повторяться). Необходимо одним запросом сделать апдейт всех строк, где поле id соответствует текущему id из списка. Почитал про INSERT . ON DUPLICATE KEY UPDATE. пока не понимаю, как применить к моей ситуации.

Читайте также:  Java jdk last version

3 ответа 3

Если хотите использовать ON DUPLICATE KEY UPDATE , то вот пример, как это сделать. Тестировал на tutorialspoint.com:

MariaDB [(none)]> create database test_1; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use test_1 Database changed MariaDB [test_1]> create table t1 (id INT NOT NULL AUTO_INCREMENT, name varchar(100), email varchar(100), PRIMARY KEY (id) ); Query OK, 0 rows affected (0.03 sec) MariaDB [test_1]> insert into t1 (name, email) values ('user1', 'user1@gmail.com'), ('user2', 'user2@gmail.com'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test_1]> select * from t1; +----+-------+-----------------+ | id | name | email | +----+-------+-----------------+ | 1 | user1 | user1@gmail.com | | 2 | user2 | user2@gmail.com | +----+-------+-----------------+ 2 rows in set (0.01 sec) MariaDB [test_1]> INSERT INTO t1 (id, name, email) VALUES (1, 'user1', 'new_email_user1@gmail.com'), (2, 'new_name', 'user2@gmail.com') ON DUPLICATE KEY UPDATE name = VALUES(`name`), email = VALUES(`email`); Query OK, 4 rows affected (0.01 sec) Records: 2 Duplicates: 2 Warnings: 0 MariaDB [test_1]> select * from t1; +----+----------+---------------------------+ | id | name | email | +----+----------+---------------------------+ | 1 | user1 | new_email_user1@gmail.com | | 2 | new_name | user2@gmail.com | +----+----------+---------------------------+ 2 rows in set (0.00 sec)

Источник

How to update multiple columns in mysql using php

i have 5 column values to be updated in table, i am using variable to save data and using that variable want to update the values in table how can i do that?

@Mithun Please check your query. I think, you are confused with INSERT and UPDATE query. Refer the UPDATE query syntax here

5 Answers 5

 $sql = "UPDATE `product_list` SET `product_name` = '$product_name', `product_category` = '$product_category', `product_price` = '$product_price', `product_description` = '$product_description', `product_size_category` = '$size_category' where clause. (if required) "; 
$sql = "UPDATE product_list SET product_name='".$product_name."',product_category='".$product_category."',product_price='".$product_price."',product_description='".$product_description."',size_category='".$size_category."' WHERE product_id https://dev.mysql.com/doc/refman/5.0/en/update.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.0/en/update.html
)" data-controller="se-share-sheet" data-se-share-sheet-title="Share a link to this answer" data-se-share-sheet-subtitle="" data-se-share-sheet-post-type="answer" data-se-share-sheet-social="facebook twitter devto" data-se-share-sheet-location="2" data-se-share-sheet-license-url="https%3a%2f%2fcreativecommons.org%2flicenses%2fby-sa%2f3.0%2f" data-se-share-sheet-license-name="CC BY-SA 3.0" data-s-popover-placement="bottom-start">Share
)" title="">Improve this answer
)">edited Sep 7, 2015 at 12:13
answered Sep 7, 2015 at 12:06
Add a comment |
4

Update SQL query, see following method:

Update database_tablename SET column_name1 = column_value1 , column_name2 = column_value2

$sql = "UPDATE product_list SET product_name='".$product_name."',product_category='".$product_category."',product_price='".$product_price."',product_description='".$product_description."',size_category='".$size_category."'";

Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually won't help the OP to understand their problem.

Источник

Updating multiple MySQL table columns using arrays with PDO

I'm trying to switch all my MySQL connections from the old mysql_query to PDOs. I'm trying to update multiple rows and columns of a MySQL table using different arrays and I'm receiving the following error: [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(accnt, car, radio, misc) values ('admin', '300.00', '400.00', '10.00') WHERE ID' at line 1 From the following code:

$account = $_POST['account']; $car_lease = $_POST['car_lease']; $radio_lease = $_POST['radio_lease']; $misc_lease = $_POST['misc_lease']; $lease_ID = $_POST['lease_ID']; //$data = array_map(null,$account,$car_lease,$radio_lease,$misc_lease); $A = count($lease_ID); try < $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $STH = $DBH->prepare('UPDATE lease (accnt, car, radio, misc) values (:account, :car_lease, :radio_lease, :misc_lease) WHERE $i = 0; while($i < $A) < $STH->bindParam(':account', $account[$i]); $STH->bindParam(':car_lease', $car_lease[$i]); $STH->bindParam(':radio_lease', $radio_lease[$i]); $STH->bindParam(':misc_lease', $misc_lease[$i]); $STH->bindParam(':lease_ID', $lease_ID[$i]); $STH->execute(); $i++; > > catch(PDOException $e) < echo "I'm sorry, but there was an error updating the database."; file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND); >

I believe this problem is arising from the way I'm calling the statement handle, but I'm not sure what part of my syntax is incorrect. Also, is this the best way of handling such situations? Or is there a better method to update multiple rows in a table?

Источник

How to update two MySQL tables with PHP?

When i update the *category_name* in the categories table i would like to change it in the Products table as well, but i am not sure on how to do this. I couldnt find and answer because i dont know how to phrase it correctly. Any help would be appreciated. What my update.php looks like now:

 db_update("UPDATE category SET `category_name` = '".$_POST['category_name']."' WHERE category_id = '".$id."'");

what is should look like. Note: "FIXED - I forgot to send my old value to the save.php file so the $id never knew what to update".

Are the category names supposed to be the same in both tables? If not, can you explain a bit more what needs to happen on an update? Maybe give an example?

I edited it a bit to look better! I have an admin area where i can edit both tables but separately. The products admin area pulls a categories selection from the categories table so if i add more categories to the categories table they will be available under products. But i need to be able to change a categories as well at a later stage and it needs to then update it in the products table as well. Hope i explained in a way you understand.

8 Answers 8

You could just run two queries changing both names:

If you want to do it in one string you can do this with multiple queries, a joined query or a stored procedure.

Multiple queries in one string:

If none of the above work I suggest changing your tables so there isn't a matching name but a matching ID.

Categories Table - id | category_name 1 : basic 2 : midlevel 3 : highlevel 4 : profesional Product Table - id | category_ID | product 1 : 1 : adsl 1G 2 : 2 : adls 2G 3 : 3 : adsl 3G 4 : 3 : adsl 4G

Updating them with 2 different sets wont help me i have tried this, i need one string to update both tables. Essentially if i update Categories Table it needs to update Products Table with the new formatted category.

I am using the "Joined query:" and made some small changes to my database. I am testing everything now. Will add an update as soon as it is running smoothly.

UPDATE Categories t1 JOIN Product t2 ON (t1.category_name = t2.category_name ) SET t1.category_name = ? t1.category_name = ? WHERE t1.id = ?

But you are feeling yourself that your data model is ugly. It might be better to de-normalize.

Have the product table refer to the category table:

Product Table - id | product | category_ID Categories Table - id | category_name

I suggest that you link your products table to your categories table by use of the category table's 'id' field. This way you will be linked to the same category and can change the category name without fear of breaking anything else, as you will still be linking to the same category id.

Categories Table - id | category_name 1 : basic 2 : midlevel 3 : highlevel 4 : profesional Product Table - id | product | category_id l : adsl 1G : basic 2 : adls 2G : midlevel 3 : adsl 3G : highlevel 4 : adsl 4G : profesional

The way you are currently doing it will seemingly be very hard to maintain, as you may need to add another column in future that uses a category in some way, and having to keep changing the 'category_name' column for all of those tables will probably end up very messy. By linking to the id, you can make any change you like in the category table without worrying that you will lose the link to any other tables(which is what will happen with the current tables).

Edit: You can get any information about categories by use of joins such as INNER JOINS. This will be a better approach, I think. Also, consider adding a relationship between the 'category_id' column in the products table with the 'id' column in the category table. This will ensure that a category will not just get deleted while there are still products linked to it.

Источник

Оцените статью
© 2024 Программирование