Storying Array in Database

[onlyican]

Hey all

I want to store an array in a MySQL Database
But I dont know how

I tried having the MySQL Field as TEXT
but it just stored ARRAY in the field

I know I can make it a string
then make it an array on export from MySQL

But CAN i store an Array in MySQL?

[genericnumber1]

erm.... no

let me be more constructive....

how about you try combining the values and seperating them with some delimiter into a string... you can then store that string in the database and parse it back into an array later?

[kenrbnsn]

You have to serialize the array before storing it in the database and also pass it through mysql_real_escape_string().

[code]<?php
$ary = array('1','two','three','4');
$q = "insert into test_table set `text_field` = '" . mysql_real_escape_string(serialize($ary)) . "'";
?>[/code]

When you retrieve it, you will need to unserialize it before you can use it.

Ken

[onlyican]

I know I can do that, that is what I said I will do

Just thought if there was a way of storing an Array Data into a MySQL Table, it would be easier

I will do something like
$link_string = "";
foreach($links as $link){
$link_string.= $link." |";
}

Then on the reverse

$links = explode(" |", $link_string);

Then I am back in an array


RE: parsing mysql_real_escpae_string

I have my own function that EVERYTHING is run through before looking at a Query string

[kenrbnsn]

If you insist on doing it this way:
[code]<?php
$link_string = "";
foreach($links as $link){
$link_string.= $link." |";
}?>[/code]

instead of using the serialize/unserialize method, use the implode() function instead:
[code]<?php
$link_string = implode('| ',$links);
?>[/code]

since it won't leave the dangling '| ' at the end of the string.

Ken

[onlyican]

Good point Kenrbnsn

The only reason I want them in an array is because they are links
so with them being in an array I can just loop through them using foreach
and make them a <a href='' link

[genericnumber1]

hate to complicate things... but if you wanted to save the index as well as the value you could do something like...

:P I'm the king of complicating things! of course.. I use my array indexes way too much so it would explain it I suppose :X

[code=php:0]
<?php
function database_array_to_string($array) {
$num_loops = 0;
foreach($array as $index => $value) {
++$num_loops;
$new_string .= $index . '=>' . $value;
if($num_loops != count($array)) {
$new_string .= '\n';
}
}
return $new_string;
}

function database_string_to_array($string) {
$array = explode('\n', $string);
foreach ($array as $array_chunk) {
list($index, $value) = explode('=>', $array_chunk, 2);
$new_array[$index] = $value;
}
return $new_array;
}

$test_array = array(
'test' => 'array',
'foo' => 'bar'
);

$test_string = database_array_to_string($test_array);
echo $test_string; // test=>array\nfoo=>bar

$test_array = database_string_to_array($test_string);
print_r($test_array); // Array([test] => array [foo] => bar)
?>
[/code]

[hitman6003]

why would you reinvent the wheel using that function, genericnumber1, when the serialize function does exactly that, and more...it saves any php object to a string that is suitable for storing in a database or text file...include array indexes.  Nevermind multidimensional arrays..........

http://www.php.net/serialize
http://www.php.net/unserialize

[genericnumber1]

because I tend to overlook the simple solutions of course :\ you win this one

in my defense, my "serialized" is a little more readable by a person though..

[kenrbnsn]

Instead of using your functions, you can use the [url=http://www.php.net/var_export]var_export()[/url] function to create a readable string which can be "eval"ed to recreate the original array:

[code]<?php
$ary = array('one'=>array('two'=>2,'three'=>3),'zero'=>array('x'=>100,'y'=>200));
$str = var_export($ary,true);
echo '<pre>' . $str . '</pre>';
eval("\$ary1 =" . $str . ';');
echo '<pre>' . print_r($ary1,true) . '</pre>';
?>[/code]

Ken

[genericnumber1]

oh cmon, now yall are just kicking me while I'm down :D

[onlyican]

Cheers

The reason I want arrays is beause
I have a textbox
Which I make everything in there a full link (change www.site.com to http://www.site.com
by parsing them in arrays

Then
On Export from the database I want to make them an array, so I can loop through them and change
for example
[code]
<?php
foreach($links as $link){
echo "<a href='".$link."' title='".$link."'>".$link."</a>\n";
}

SO I dont need anything special or the key of the array

?>
[/code]