[RESOLVED] php array to mysql and back

nogeekyet

I have a PHP field which is an array. I need to write this field as part of an "update" to a table. What do I need to do to write it to the MySql db table and get it back into php later? Any ideas? Currently, when I check the db with phpMyAdmin it only says "Array" where I expect to find my data.

fnp

You would use the 'update' instead of 'select' parameter.

Your query should look something like this:

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE some_column=some_value

This will update the table.

I would also recommend checking the array before trying to update the database, just to make sure the array is coming across correctly. You could just print some of the variables to see that they are what they should be.

nogeekyet

Thanks for your reply. Perhaps I should have been a bit more specific. This is what the php array looks like (the variables date, time and result have been imploded using an "*", thus creating their own array):
$Log
($entry1 (try1-date, try1-time, try1-result,try2-date, try2-time, try2-result,try3-date, try3-time, try3-result, ...etc. etc.)
$entry2 (try1-date, try1-time, try1-result,try2-date, try2-time, try2-result,try3-date, try3-time, try3-result, ...etc. etc.)
$entry3....4.......5....etc. etc. etc.))

Now, when I UPDATE column-x = '$Log' I can only see the word "Array" in the column on the db.

laserlight

One way is to serialise the array and store it in a single field in the database. However, a more generally correct method is to store the elements of the array into the database in a manner that makes the most sense. The details of this depends on what exactly you are trying to do, e.g., what is this data that you are working with?

traq

use serialize() (or my personal preference, json_encode()). These functions convert your array into a string, which is something that mysql can save in a normal TEXT field.

$myarray = array('key'=>'value','otherkey'=>'othervalue','etc'=>'etcetc');

$id = 0; // some value that identifies proper record to update

$encoded = json_encode($myarray);
$escaped = mysql_real_escape_string($encoded);
mysql_query("UPDATE `tablename` SET `array`='$escaped' WHERE `id`='$id'");

$result = mysql_fetch_assoc(mysql_query("SELECT `array` FROM `tablename` WHERE `id`='$id' LIMIT 1"));
$decoded = json_decode($result['array'],TRUE); // now holds your original $myarray
// true forces the result to be an associative array
// (it would come back as an object otherwise)

Weedpacket

raq wrote:
which is something that mysql can save in a normal TEXT field.

Unfortunately, that's about all it can do (without a load of regex searching).

I agree with laserlight that the data would be better saved in the database as usable data, rather than as an opaque string (which would mean, for example, not imploding stuff together with an '*').

nogeekyet

After a lot of soul searching and wasted time, I decided on a totally different approach. I am now using the "KISS" method (in case you don't know, kiss stands for "keep it simple, stupid,") OK, I am the stupid one here but at least it's working. Thanks for all your help.

h4x0rmx

Just for future reference:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::😛11_QUESTION_ID:6692296628899

traq

traq;10982096 wrote:
These functions convert your array into a string, which is something that mysql can save in a normal TEXT field.

Weedpacket;10982126 wrote:
Unfortunately, that's about all it can do (without a load of regex searching).

I agree with laserlight that the data would be better saved in the database as usable data, rather than as an opaque string (which would mean, for example, not imploding stuff together with an '*').

no, it's not well-suited to situations where you need to search the data. I use this in situations where I can store and retrieve the block of data, "blindly," as it were.

Weedpacket

traq;10982175 wrote:
no, it's not well-suited to situations where you need to search the data. I use this in situations where I can store and retrieve the block of data, "blindly," as it were.

I find the problem arising when you've stored something as an opaque lump, and then the need arises to search it. I prefer not to throw semantic content away unless I need to (because, e.g., it just makes searching/updating way too slow or contingent - note that in this particular situation, throwing away structure would contribute to such problems).

nogeekyet

OK, I think I have to defend myself a little bit. I am NOT throwing away an "opaque lump" of data. The data has to be stored because of some government regulations. So then, here is the situation once more: I have about a dozen courses. Each course is divided into between 10 and 16 lessons. Each lesson is divided into between 3 and 11 sections. For each lesson, there is 1 quiz at the end. However, the student can take the test as many times as he needs to until he passes. Now then, for each time the student takes a quiz, whether he passed it or not, I need to capture the date and time he finished and the quiz result. To make matters worse, each time the student finishes a section, a lesson and a course, I need to capture the date and time the student finished a section, a lesson and a course. I also need to capture the date and time, including the length it took to finish a section, lesson and course when the student repeated any part because he failed the quiz. And YES, I will have to retrieve part or all of this data based on some inspectors wishes which will change as the weather. So, now what? I had this all worked out in my head and in COBOL and IMS (does anybody even know what this is?) I would have this problem licked in no time. But, I am using php and MySQL.

traq

I think Weedpacket's comments are directed more at me than you, nogeekyet. and he's right; storing a bunch of data in a serialized or json format defeats most purposes of storing it in a database (as opposed to a flat file).

But there are some situations where it works just fine. I think your situation would be a good candidate: if I understand correctly, you need a way to keep a "snapshot" of your student's progress. If you never need to search the data (e.g., you don't need to find all students that got question #5 wrong), and you just need a way to retrieve the data "just as it was," then it might be perfectly acceptable to impose this limitation. "Snapshots" is what serialize() is intended for. json was intended for transferring data between languages, but it can be used in place of serialization, and I (personally) find it much more convenient to use.

Other info, such as names, which lessons are completed, times, retries, etc., would be better stored in their own fields in the database. And, as I mentioned above, if you need to be able to search the results independently, then you might need to use another method.

In any case, using serialize or json_encode would address your original question. Neither method would "throw away" structure (assuming your structure was reflected in how you construct the array; using key=>value pairs, etc.. -Weedpacket, if you were referring to something else, please let me know), but it would make it less immediately accessible outside of the specific process you develop for this.

nogeekyet

I have created a table specifically for storing the data. Each time I need to collect/store the information I insert a row. The row has an ID (auto increment) the student ID, course id, an identifier consisting of Lesson or Test, Lesson nbr. and Section nbr. Basically, it is now a flat file which also allows the student to have unlimited retries and allows him to fail the individual test as many times as he wishes.

Thanks for all your help.

Weedpacket

traq wrote:
I think Weedpacket's comments are directed more at me than you, nogeekyet. and he's right; storing a bunch of data in a serialized or json format defeats most purposes of storing it in a database (as opposed to a flat file).

You're right; hence the quote to make explicit what I was referring to.

You also say

If you never need to search the data (e.g., you don't need to find all students that got question #5 wrong), and you just need a way to retrieve the data "just as it was," then it might be perfectly acceptable to impose this limitation.

I agree with this also. Unfortunately, as nogeekyet mentions:

nogeekyet wrote:
And YES, I will have to retrieve part or all of this data based on some inspectors wishes which will change as the weather.

Which is exactly the situation I was worried about. Storing the data as a string without any of the structure that an RDBMS would need to efficiently search for it would ruin the point of using one.

What I and laserlight wanted to see was more detail about what items were being stored and how they related to each other; from that we could have worked towards a suitable table structure (there would definitely be more than one table involved); describing relational database design is much easier when concrete and relevant examples are available, and the current application would have provided those).

traq wrote:
-Weedpacket, if you were referring to something else, please let me know)

I was speaking of the meaning of the data being stored from the perspective of the database. There are these attempts, results and time stamps being stored, but all the database knows about is this serialised string, which as far as it is concerned could contain anything (the database can't see the data encoded within the string, hence opaque). What has been lost is any meaning to the data, which is what the database's design would have captured. Deserialisation can restore it, but that comes too late to be of any help to the database.

traq

Weedpacket;10982297 wrote:
I was speaking of the meaning of the data being stored from the perspective of the database. There are these attempts, results and time stamps being stored, but all the database knows about is this serialised string, which as far as it is concerned could contain anything (the database can't see the data encoded within the string, hence opaque). What has been lost is any meaning to the data, which is what the database's design would have captured. Deserialisation can restore it, but that comes too late to be of any help to the database.

I agree. This is exactly what I was alluding to when I mentioned that

traq wrote:
it would make it less immediately accessible outside of the specific process you develop
[...]
if you need to be able to search the results independently, then you might need to use another method.

I wasn't being dismissive of your suggestions, and I hope no one interpreted it that way. I wanted to address the original question in a way that also considered limitations of the method.

I stand by my position that there are situations where this is acceptable; where all data that needs to be accessible to the database is, and data that is only needed in "a chunk" (once it has already been determined -e.g., via searching related data in other columns- which "chunk" is needed) is stored in a format that is quickly retrieved and prepared for processing.

Yes, it is nothing more than a flat file saved in a database field. All things equal, it would make more sense to save it as a file (I never meant to imply that this "chunk" would be the entire DB record; and I assumed that this was not the case with the OP's situation). In certain applications, it can save time and make for a cleaner organizational structure if the serialized string is stored in the database alongside relevant, related information.

nogeekyet wrote:
And YES, I will have to retrieve part or all of this data based on some inspectors wishes which will change as the weather.

It is certainly not possible to anticipate every possible change; therefore, they can't all be designed for. But I do agree that, this being the case, the model should be as flexible as possible; and that serialization is not the way to accomplish that.