PHP MYSQL write an associative array(record) to mysql

Question

If I have mysql table foo with fields a, b, c, d with multiple records and a being the primary key. Record 1 has the following data: a = 1, b = true, c = val1a, d = val2a. If I want to access the data using php, I do:

$result = mysql_query("SELECT * FROM foo WHERE a = '1'");
$rec = mysql_fetch_array($result);

To change b and c, I would do:

mysql_query("INSERT INTO foo (b,c) VALUES (false,'newval') WHERE a = '1'");

Is there a way to write the whole record without listing all the fields (a,b,c,d)?

What I would like to do is write the whole $rec back to table foo:

$rec['b'] = false;
$rec['c'] = 'newval';
mysql_query("INSERT INTO foo $rec");

I know I could do a foreach and build the whole insert, but is there already a php function that does what I am tryng to accomplish?

Nikoloff · Accepted Answer · 2012-01-06 15:47:09Z

3

Simple answer - no. There are a lot of things that should be considered when inserting the values, what their type is, have they been escaped or not, do they need quotes around them or not, etc. So generally all the values cannot be treated the same and just added to a string. It's better to use an extension like MySQLi, not build the queries by concatenating stings. A simple function can do what you want with a loop as you've guessed, but I wouldn't recommend that.

Also - you've mistaken about something:

mysql_query("INSERT INTO foo (b,c) VALUES (false,'newval') WHERE a = '1'");

is invalid syntax. You need to use UPDATE to change b and c. You also don't need the quotes around the id, i.e. WHERE a = '1' should be WHERE a = 1, it's an integer value.

answered Jan 6, 2012 at 15:47

Nikoloff

4,1602 gold badges19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user999684 Over a year ago

Yes, typo, I meant UPDATE. I have been doing mysql for a while, but come from a read record, change record, write record methodology and thought that being mysql_fetch_array read in a record as an associative array if the select is for all fields, there may be a php function write to put the associative array back out there. I have looked at the mysqli stuff and am using some of it.

Nikoloff Over a year ago

Reading is very different than writing - when reading all the data is returned as strings, exactly as stored in the DB. Thus it doesn't need all the type validations, escaping, etc. that the write does. OTOH, writing without taking the proper actions for different fields and data, you can very easily end up with errors, incorrect data and/or security issues.

user999684 Over a year ago

Thanks for the info. Is there a good article that explains this stuff? So if I read an int type field for a record, it returns it as a string so that if I write it back, it will mess up the database or is this not what you are referring to? Are you saying that if I have an int field called intfld with the value 123, read that field using the method above and assign it to a variable like $var1 = $rec['intfld'] and then do mysql_query("UPDATE foo SET intfld="'.$var1.'" WHERE a = '1'"); it will cause database corruption? I appreciate your help on this.

Nikoloff Over a year ago

In the case that you are describing - no, there wouldn't be a problem unless there's another manipulation of this variable. However, there will be a problem if intfld was say a varchar column, it would've needed quotes around it's value. If it is an int, but it's value is 'abc' then that will result in an error. This are simple examples, but in real code it could get messy. The bigger issues occur when there's user input involved. The character limit for comments is too small and this topic has been discussed many times before, so just google for "SQL injection" and read on.

devNoise · Accepted Answer · 2012-01-06 15:47:26Z

0

My MySQL is a bit rusty, but I'm not sure you can do an insert like that. You would normally create an update statement for the new values and that would generally require the foreach loop you're trying to avoid.

Maybe you want to use something like doctrine, http://www.doctrine-project.org/, to abstract the row into an object that would handle the fetching and updating.

answered Jan 6, 2012 at 15:47

devNoise

3743 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL write an associative array(record) to mysql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related