Save PHP array to MySQL?

Question 1

What is a good way to save an array of data to a single mysql field?

Also once I query for that array in the mysql table, what is a good way to get it back into array form?

Is serialize and unserialize the answer?

Question 2

There is no good way to store an array into a single field.

You need to examine your relational data and make the appropriate changes to your schema. See example below for a reference to this approach.

If you must save the array into a single field then the serialize() and unserialize() functions will do the trick. But you cannot perform queries on the actual content.

As an alternative to the serialization function there is also json_encode() and json_decode().

Consider the following array

$a = array(
    1 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
    2 => array(
        'a' => 1,
        'b' => 2,
        'c' => 3
    ),
);

To save it in the database you need to create a table like this

$c = mysql_connect($server, $username, $password);
mysql_select_db('test');
$r = mysql_query(
    'DROP TABLE IF EXISTS test');
$r = mysql_query(
    'CREATE TABLE test (
      id INTEGER UNSIGNED NOT NULL,
      a INTEGER UNSIGNED NOT NULL,
      b INTEGER UNSIGNED NOT NULL,
      c INTEGER UNSIGNED NOT NULL,
      PRIMARY KEY (id)
    )');

To work with the records you can perform queries such as these (and yes this is an example, beware!)

function getTest() {
    $ret = array();
    $c = connect();
    $query = 'SELECT * FROM test';
    $r = mysql_query($query,$c);
    while ($o = mysql_fetch_array($r,MYSQL_ASSOC)) {
        $ret[array_shift($o)] = $o;
    }
    mysql_close($c);
    return $ret;
}
function putTest($t) {
    $c = connect();
    foreach ($t as $k => $v) {
        $query = "INSERT INTO test (id,".
                implode(',',array_keys($v)).
                ") VALUES ($k,".
                implode(',',$v).
            ")";
        $r = mysql_query($query,$c);
    }
    mysql_close($c);
}

putTest($a);
$b = getTest();

The connect() function returns a mysql connection resource

function connect() {
    $c = mysql_connect($server, $username, $password);
    mysql_select_db('test');
    return $c;
}

Question 3

This might do the trick: (I'm assuming ID is unique, if not substitute something that is)

SELECT
  p.ID, guid, post_parent, post_title
FROM (
SELECT
  a.ID as ID,
  COUNT(*) as rank
FROM (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS a
JOIN (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS b ON b.ID <= a.ID AND b.post_parent = a.post_parent
GROUP BY a.ID
) AS r
JOIN $wpdb->posts p ON r.ID = p.ID AND r.rank <= 3
WHERE p.post_parent IN (
  SELECT object_id FROM $term_relationships
  WHERE term_taxonomy_id = $post_term)
GROUP BY p.ID
;

EDIT: Attempt to include category in rank so it'll actually work.

Specifying conditions twice is a bit ugly, but I didn't see an easy way around it.

Question 4

var div_contents = $("#id-aaa").html();
$.post('myscript.php', { contents: div_contents });

This sends the contents of the div to the PHP script myscript.php.

myscript.php will have code like this.

// Using MySQL lib here, as it's most widely recognised
// Replace with MySQLi functions/object if you prefer
$con = mysql_connect($host, $username, $password);
mysql_select_db($database, $con);

$div = mysql_real_escape_string($_POST['contents']); // Make sure to clean the
                                                     // data before putting SQL

$sql = "INSERT INTO divs (contents) VALUES ('{$div}')";
$query = mysql_query($sql, $con);
if($query) {
     // Success!
} else {
     // Failure :(
}

Question 5

First, you need to convert you input data to another format:

$cyldata = $_POST['cylinder']; //this is the post from top.

$num_elements = 0;

$sqlData = array();

while($num_elements < count($cyldata['serie'])){
    $sqlData[] = array(
        'serie'         => $cyldata['serie'][$num_elements],
        'type'          => $cyldata['type'][$num_elements],
        'admission'     => $cyldata['admission'][$num_elements],
        'seriesap'      => $cyldata['seriesap'][$num_elements],
        'invoice'       => $cyldata['invoice'][$num_elements], // you miss this field, aren't you?
        'created_at'    => CarbonCarbon::now(), // only if your table has this column
        'updated_at'    => CarbonCarbon::now(), // only if your table has this column
    );
    $num_elements++;
}

Second, use the Fluent query builder to do a batch insert:

DB::table('table_name')->insert($sqlData);

Note: the created_at and updated_at appear here if your table has these field. When working with Eloquent model, these field is updated automatically. However, we do not use Eloquent, so that we have to assign the value to these field manually.

Question 6

When you're gathering the file information you're overwriting $imagename on every loop so it will be assigned to the last one. Try attaching it to the $files variable (hopefully this doesn't mess with the upload class you're using).

foreach ($l as $i => $v)
{
    if (!array_key_exists($i, $files))
    $files[$i] = array();
    $files[$i][$k] = $v;
    $files[$i]['imagename'] = $_POST['keyword'][$i];
}

Then update your $sql string to reference that

$sql = 'INSERT INTO cover (id, img, keyword, category_name, cat_id) 
     VALUES ("", "'.$this_upload['image'].'", "'.$file['imagename'].'", 
         "'.$category_name.'", "'.$category.'")';

Save PHP array to MySQL?

Answers

php,mysql,arrays