[RESOLVED] How to Store Array Data into MySQL Database using serialize

[Majeste]

Dear friends,

I'm trying to get our notes in a Facebook page and store in in our database so that can have the source of these notes online. Also, we want to use it to construct our web page.

You know with Graph API we can get the notes with title, content, published date etc. But since Graph API is not working fast and I cannot do it with FQL (Facebook Query Language) so, I though I needed to store the information coming from Graph API in an array into a database.

I did lots of search on Google, I found best way to do it first serialize, store and then show it unserializing. I'm only able to serilize the array now. When it comes to inserting it into database, it fails.

In the below code, I get the last 25 notes with Graph API (not shown) in $notes array, then I take some of the elements and put them into $needed_fields array. And the query comes after I serialize it.

I think the problem is in the $query. Because I got an error: Column count doesn't match value count at row 1

for($i=0;$i<25;$i++)
	{
		$each_note = $notes['data'][$i];
		$needed_fields = array (
						'subject' => $each_note['subject'],
						'message' => $each_note['message'],
						'icon' => $each_note['icon'],
						'created_time' => $each_note['created_time'],
						'updated_time' => $each_note['updated_time'],
		);
		foreach($needed_fields as $k => $v) {
  				  $needed_fields[$k] = mysql_real_escape_string($v);
									}
		$serialized_notes = serialize($needed_fields);
		$query = "INSERT INTO notes
			(subject, message, icon, created_time, updated_time) VALUES
			('$serialized_notes')";

             mysql_query($query) or die(mysql_error()); 

}

Best wishes,
Gungor

[Bonesnap]

First, don't store serialized data in a database; the database doesn't understand what each "part" is and you won't be able to query it for results.

Second, a serialized array is a string, AKA a single value. You're getting your error because your query is expecting to input data for five columns (five values) but you're only giving it one value (the serialized array, which is a string).

Third, why bother serializing the data when it's clear you already have it separately?

[Majeste]

Hello Bonesnap,

Thank you for that information, I didn't know. Now, I understand why it could not be inserted into. But I really need to do this, because it is Facebook Graph API works slowly if you have large amount of data. And we have lots of notes with long text. I don't want people to wait that long.

So I want to insert the data into a MySQL database to make it quick. But I couldn't.

Do you know any way to do such thing? I have an array with some elements and I want to insert them to the columns I created in a table.

Actually FQL is faster but there is no such option like getting notes of a page, as far as I know.

I retrive the data with the code below. Maybe I don't retrive all of them at once, but I don't know how to do it, either.

$notes = $facebook->api('/treeofevolution/notes');

I also found implode function can be a solution? What do you think? Is it again like the serialize case?

Other than this, I have no other option.

Thank you ver much.

[NogDog]

(untested)

for($i=0;$i<25;$i++) {
	$each_note = $notes['data'][$i];
	$needed_fields = array (
		$each_note['subject'],
		$each_note['message'],
		$each_note['icon'],
		$each_note['created_time'],
		$each_note['updated_time'],
	);
	foreach($needed_fields as &$v) {
		$v = mysql_real_escape_string($v);
	}
	$values = "'".implode("','", $needed_fields)."'";
	$query = "
		INSERT INTO notes
		(subject, message, icon, created_time, updated_time) VALUES
		($values)
	";
	mysql_query($query) or die(mysql_error());
}

[Majeste]

Hi NogDog,

I was testing your codes but before I tried not to implode or serialize something and surprisingly it worked. I mean what I did actually to read the array and send it to the database with a simple query. The code is just like below.

Though I read I have to do something other than directly doing it. Do you think this has any problem? For me it works great. I do the real_escape thing, too.

for($i=0;$i<25;$i++)
	{
		$each_note = $notes['data'][$i];
		$needed_fields = array (
						'subject' => $each_note['subject'],
						'message' => $each_note['message'],
						'icon' => $each_note['icon'],
						'created_time' => $each_note['created_time'],
						'updated_time' => $each_note['updated_time'],
		);
		foreach($needed_fields as $k => $v) {
  				  $needed_fields[$k] = mysql_real_escape_string($v);
				}
		$subject = $needed_fields['subject'];
		$message = $needed_fields['message'];
		$icon = $needed_fields['icon'];
		$created_time = $needed_fields['created_time'];
		$updated_time = $needed_fields['updated_time'];

	$query = "INSERT INTO notes
		(id, subject, message, icon, created_time, updated_time) VALUES
		('','$subject','$message','$icon','$created_time','$updated_time')";

	mysql_query($query) or die(mysql_error());

	if(mysql_query($query)) {echo "Yaz&#305;lar veri taban&#305;na gönderildi.";} else {echo "Bir hata olu&#351;tu.";}

}

Thank you for helping, I hope this thread will help many others,
Gungor