[MYSQL] Query aanpassen
Momenteel ben ik bezig met het aanpassen van een standaard gedownload script, nu heb ik een ander datamodel dan de standaard versie en moet ik hier en daar dus wat aanpassen. Alleen kom ik er niet uit om onderstaande query aan te passen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php
case "list":
$id = (int)str_replace("node_","",$_REQUEST["id"]);
$db->query("SELECT s.id, ( SELECT COUNT(*) FROM structure WHERE parent_id = s.id ) AS children, c.language, c.name FROM structure s LEFT JOIN content c ON c.id = s.id WHERE s.parent_id = ".$id." ORDER BY position");
$data = array();
$children = array();
while($db->nextr()) {
$children[$db->f("id")] = (int)$db->f("children");
$data[$db->f("id")][$db->f("language")] = $db->f("name");
}
echo "[\n";
$i = 0;
foreach($data as $k => $v) {
echo "{\n";
echo "\tattributes: {\n";
echo "\t\tid : 'node_".$k."'\n";
echo "\t},\n";
if($children[$k]) echo "\tstate: 'closed', \n";
echo "\tdata: {\n";
$kf = 0;
foreach($v as $lang => $name) {
if($kf > 0) echo ",\n";
else echo "\n";
$kf ++;
echo "\t\t'".$languages[$lang][0]."' : { title : '".$name."' }";
}
echo "\n";
echo "\t}\n";
echo "}";
if(++$i < count($data)) echo ",";
echo "\n";
}
echo "\n]";
?>
case "list":
$id = (int)str_replace("node_","",$_REQUEST["id"]);
$db->query("SELECT s.id, ( SELECT COUNT(*) FROM structure WHERE parent_id = s.id ) AS children, c.language, c.name FROM structure s LEFT JOIN content c ON c.id = s.id WHERE s.parent_id = ".$id." ORDER BY position");
$data = array();
$children = array();
while($db->nextr()) {
$children[$db->f("id")] = (int)$db->f("children");
$data[$db->f("id")][$db->f("language")] = $db->f("name");
}
echo "[\n";
$i = 0;
foreach($data as $k => $v) {
echo "{\n";
echo "\tattributes: {\n";
echo "\t\tid : 'node_".$k."'\n";
echo "\t},\n";
if($children[$k]) echo "\tstate: 'closed', \n";
echo "\tdata: {\n";
$kf = 0;
foreach($v as $lang => $name) {
if($kf > 0) echo ",\n";
else echo "\n";
$kf ++;
echo "\t\t'".$languages[$lang][0]."' : { title : '".$name."' }";
}
echo "\n";
echo "\t}\n";
echo "}";
if(++$i < count($data)) echo ",";
echo "\n";
}
echo "\n]";
?>
Nu is dit de nieuwe structure tabel:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
CREATE TABLE `menu` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`pid` bigint(20) unsigned NOT NULL,
`pos` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`pid`,`pos`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
`id` bigint(20) unsigned NOT NULL auto_increment,
`pid` bigint(20) unsigned NOT NULL,
`pos` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`pid`,`pos`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
En dit de nieuwe content tabel:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
CREATE TABLE `content` (
`id` int(11) unsigned NOT NULL auto_increment,
`langid` int(11) unsigned NOT NULL,
`name` text NOT NULL,
`data` text NOT NULL,
en nog wat velden...
PRIMARY KEY (`id`,`langid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
`id` int(11) unsigned NOT NULL auto_increment,
`langid` int(11) unsigned NOT NULL,
`name` text NOT NULL,
`data` text NOT NULL,
en nog wat velden...
PRIMARY KEY (`id`,`langid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Hierbij vraag ik jullie hulp om bovenstaande query aan te passen aan mijn datamodel.
Alvast bedankt!
Gesponsorde koppelingen:
