Updating a mysql column with keys from another column from the same table

Question

maybe the question title is not very descriptive. That's because I really didn't know how to write it. But my problem here is that I have one table named pagemeta with three columns id , page_property and property_value and this is how it looks like:

-----------------------------------
id | page_property | property_value|
------------------------------------
1  | telephone     | 445854541     |
2  | email         | c@gmail.com   |
3  | icon          | img/1.jpg     |

So far so good. Now I have a html form which sends a post request to a php page. The $_POST array keys are named after the fields from the page_property column, like: telephone and email and icon. The $_POST array values are set from the input fields in the form. At the end I have a $_POST array with these values:

telephone=>8988989
email=>b@gmail.com
icon=> img/2.jpg

The problem begins here where I try to update the pagemeta table. I don't know how to write the UPDATE query where the fields in the property_value column are updated with their corresponding key from the $_POST array. It means that the fields in property_value should be updated related to the field values in the page_property column. Sorry for asking so unclear and complicated. I really didn't know how to explain. Any edit would be welcome and any answer would be a great help. Thanks I've search everywhere but didn't find any answer.

score 1 · Answer 1 · answered Apr 07 '16 at 13:52

1

With this database structure, you are going to need 3 queries to update 3 values which in my opinion is pretty bad...

UPDATE pagemeta SET property_value = $_POST['telephone'] WHERE page_property = 'telephone'
UPDATE pagemeta SET property_value = $_POST['email'] WHERE page_property = 'email'
UPDATE pagemeta SET property_value = $_POST['icon'] WHERE page_property = 'icon'

You might want to rethink your database structure, I'm not sure what you win with your structure instead of doing it normal and creating a column called telephone, another called email and a last one called icon

PD: Please use prepared statements

answered Apr 07 '16 at 13:52

Borjante

9,767
6
35
61

The idea of creating a column for each field is not what I want because this table is used for multiple pages and each page has it's own properties and property values. If I do it this way and change my table structure I have to create a table for each page. – DevMan Apr 07 '16 at 13:57
Then my answer will work, however you will end in a nightmare where you are going to need to check DB everytime just tto figure out what options may be applied to a specific page. I still think you could/should do it like I said, you just need to add a column called page_id to your table and then you could link the settings to a specific page. – Borjante Apr 07 '16 at 14:01

vogomatix · Answer 2 · 2016-04-07T14:59:30.880

Normally when you create the HTML Form, you add a hidden field containing the id of the row you're editing.

<form>
<input type="hidden" name="id" value="1"> <!-- id of record being edited -->
<input type="tel" name="telephone" value="8988989">
<input type="email" name="email" value="b@gmail.com">
<input type="text" name="icon" value="img/2.jpg">
</form>

Your $POST array would be:

id=>1
telephone=>8988989
email=>b@gmail.com
icon=> img/2.jpg

In the absence of an Id, you would assume that you wish to create a new record i.e INSERT another row into the table.

Your table layout would also change to one containing everything in one row.

If you get an id, your UPDATE would be:

UPDATE pagemeta SET
telephone=$_POST[telephone],
email=$_POST[email],
icon=$+POST[icon]
WHERE id=$_POST[id]

NB The above SQL is for illustrative purposes only. In reality you must validate your form input to avoid SQL injection attacks

score 0 · Answer 3 · answered Apr 07 '16 at 14:08

0

Ok. You are sitting inside your PHP IDE looking at some global variables and thinking 'How can I smash these into a database?'

Any decent IDE will already be warning you about using a Superglobal to retrieve data. What the IDE won't tell you is that doing what you are doing will get exploited if you ever publish it to the web.

It sounds like you are right at the start of your journey so to answer your question as you asked it:

$conn = new mysqli("servername", "username", "password", "dbname");
    foreach($_POST as $key => $value) {
        $sql = "UPDATE pagemeta SET property_value = $value WHERE page_property = $key"
        $conn->query($sql)
    }
$conn->close()

answered Apr 07 '16 at 14:08

BlueWater86

1,773
12
22

I know how to handle $_POST and I know how to avoid SQL injection and other types of exploits. I just wanted to know if there is a way to write a query to solve this problem. – DevMan Apr 07 '16 at 14:20
No dramas. If you are going to stick with that design for your database, you will be writing an update query for each of your individual property_values. – BlueWater86 Apr 07 '16 at 14:33
Discussion on your 'schemaless' design: http://stackoverflow.com/questions/11779252/entity-attribute-value-table-design – BlueWater86 Apr 07 '16 at 14:36
I certainly will not write a query for each value.And I certainly will not pass queries in a loop. I just wanted to make sure that if there's a way to do it with mysql queries or not. And you answered my question "on your own way". – DevMan Apr 07 '16 at 14:42

Updating a mysql column with keys from another column from the same table

3 Answers3