from multi dimensional array to MySQL

Question

I have a multidimensional array called $data that is basically data extracted from a table into the array.

This is how I get my array using JS_extractor:

set_include_path(get_include_path() . PATH_SEPARATOR . './library/');
require_once 'JS/Extractor.php';
$extractor = new JS_Extractor(file_get_contents('temp.html'));

$body = $extractor->query("body")->item(0);

$table = $body->query("//table[@class='rstatisztika_tabla']")->item(0);

$data = array();
foreach ($table->query('tr') as $i => $tr) {
    if ($i == 0) {
        continue;
    }
    $a = $tr->query('.//a');
    $a = $a->item($a->length - 1);
    $url = $a->getAttribute('href');
    $parsed = parse_url($url);
    parse_str($parsed['query'], $query);
    $data[] = array(
        $a->textContent,
        $url,
        $query['user'],
    );
}
//var_dump($data);

when I actually do

var_dump($data);

I get this:

array(3)
{
    [0]=> array(3)
    {
        [0]=> string(4) "Thad"
        [1]=> string(7) "http://localhost/index.php?m=karakterlap&user=91"
        [2]=>  string(2) "91"
    }
    [1]=> array(3)
    {
        [0]=> string(4) "Bill"
        [1]=> string(8) "http://localhost/index.php?m=karakterlap&user=110"
        [2]=> string(3) "110"
    }
    [2]=> array(3)
    {
        [0]=> string(7) "Thadson"
        [1]=> string(7) "http://localhost/index.php?m=karakterlap&user=147"
        [2]=> string(3) "147"
    }
}

I also have a Mysql database table called warlord

CREATE TABLE IF NOT EXISTS `warlord` (
  `id` int(5) NOT NULL default '0',
  `name` varchar(35) character set utf8 NOT NULL default '',
  `active` tinyint(1) NOT NULL default '1',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `warlord` (`id`, `name`, `active`) VALUES
(2, 'Admin', 0), (100, 'Thadson', 1), (147, 'John', 1);

From the array, I want to add the new users (Thad & Bill) to the warlord table and set them active (1)
I want the user (Thadson), who is also in the array, to stay active (1)
However I want the user (John) who is not in the array, set to inactive (0)
and leave admin who is also not in the array (and is already inactive) inactive (0)

I know this is a very beginners question, but how do I do this?

Thanks

Answer 1

You might want to use array_map function to set a callback function for the multi-array and process it accordingly:

array_map

Answer 2

I saw this and I have tried it:

$data = array();
for($i=0; $i<count($data_array); $i++){
  $data_items[$i] = explode(',', $data_array[$i]);   // create a multi-dimensional array
  $data[] = '( ' .$data_items[$i][2]. ', '.$data_items[$i][0]. ', '. 1 .')';
}

$sql = "INSERT INTO warlord(id, name, active) VALUES ('".$data_items[$i][2]."','".$data_items[$i][0]."','1') ";

...to get the data into my table

I ignore the 2nd elements from the array (The ones that look like this: [1]=> string(7) "user=91") and I try to insert elements [2] and [0] into the table and make the new users active with placing 1 into the active field.

This obviously doesn't do all I need (actually it doesn't do any of it) and I have no idea what I'm doing. This is why I'm asking for help.

Answer 3

If I was to approach this question I would firstly try to organise my arrays (the $data array and also the array from the database) into a similar format (possibly using the id as the array key) so that I could use array_intersect to work out the people in both $data and $database (ie. people who should set active) and array_diff to work out the people in $data and not in $database (ie. the people who need to be added to the database) and then array_diff again (with $database first this time) to work out the people in $database and not in $data (ie. the people who should be set inactive).

Hope this helps put you on the write track. Sorry but I don't have the time to actually write the code for you.