php mysql billed tabel join

SELECT e_id, `date`, head, descr, img_filename FROM cms_events
INNER JOIN cms_images
ON cms_events.e_id = cms_images.img_arrid

soduno (3) skrev:

Hvad går den sidste WHERE i din sætning?

soduno (3) skrev:

Har leget lidt med nogen lunde samme sætning. Dog er det eneste problem at den ikke henter alle tilhørende billeder ud til hvert arrangement, kun 1.

SELECT e_id, `date`, head, descr, img_filename
FROM cms_events
INNER JOIN cms_images ON cms_events.e_id = cms_images.img_arrid
WHERE `date` >='". $timenow ."' GROUP BY head LIMIT 0,9

SELECT evnts.e_id, evnts.date, evnts.head, evnts.descr, img.img_filename
FROM cms_events evnts
JOIN cms_images img
ON evnts.e_id = img.img_arrid

tazimn (6) skrev:

Altså uden INNER JOIN, men bare JOIN, hvilket i MSSQLs tilfælde betyder en FULL OUTER JOIN.

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

SELECT e_id, `date`, head, descr, img_filename FROM cms_events
RIGHT JOIN cms_images
ON cms_events.e_id = cms_images.img_arrid

arne_v (12) skrev:

Men prøv at uddyb hvad "uden held" dækker over.

arne_v (15) skrev:

#13
Det er kun hvis man vil vise billeder uden event. Det lyder ikke plausibelt.

PoulErik (19) skrev:

SELECT e_id, `date`, head, descr, GROUP_CONCAT(img_filename) FROM cms_events
LEFT JOIN cms_images
ON cms_events.e_id = cms_images.img_arrid GROUP BY e_id

arne_v (20) skrev:

#18

Det har jo ikke noget med SQL at gøre.

En SELECT returnerer rektangulære data (N kolonner x M rækker).

En anden visning skal laves i applikationen !

arne_v (23) skrev:

#22
Et test på om et felt har samme værdi som forrige række.

CREATE TABLE main (
id INTEGER NOT NULL,
txt VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE subs (
id INTEGER NOT NULL,
nam VARCHAR(50) NOT NULL,
main_id INTEGER NOT NULL,
PRIMARY KEY(id)
);

INSERT INTO main VALUES(1, 'All A');
INSERT INTO main VALUES(2, 'All B');
INSERT INTO main VALUES(3, 'All C');
INSERT INTO subs VALUES(1, 'A1.jpg', 1);
INSERT INTO subs VALUES(2, 'B1.jpg', 2);
INSERT INTO subs VALUES(3, 'B2.jpg', 2);
INSERT INTO subs VALUES(4, 'B3.jpg', 2);

SELECT main.txt,subs.nam
FROM main JOIN subs ON main.id=subs.main_id
ORDER BY main.txt;
SELECT main.txt,subs.nam
FROM main LEFT JOIN subs ON main.id=subs.main_id
ORDER BY main.txt;

<?php
class Main {
public $txt;
public $subs;
public function __construct($txt) {
$this->txt = $txt;
$this->subs = array();
}
};

class Sub {
public $nam;
public function __construct($nam) {
$this->nam = $nam;
}
};

function load() {
$data = array();
$con = new mysqli('localhost', 'root', '', 'Test');
$stmt = $con->prepare('SELECT main.txt,subs.nam FROM main LEFT JOIN subs ON main.id=subs.main_id ORDER BY main.txt;');
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($maintxt, $subnam);
$ix = 0;
$lastmaintxt = '>>>>BOF<<<<';
while($stmt->fetch()) {
if($maintxt != $lastmaintxt) {
$ix++;
$data[$ix] = new Main($maintxt);
$lastmaintxt = $maintxt;
}
if($subnam != null) $data[$ix]->subs[] = new Sub($subnam);
}
$stmt->close();
$con->close();
return $data;
};

function display_as_lists($data) {
echo "<ul>\r\n";
foreach($data as $main) {
echo "<li>$main->txt</li>\r\n";
echo "<ul>\r\n";
foreach($main->subs as $sub) {
echo "<li>$sub->nam</li>\r\n";
}
echo "</ul>\r\n";
}
echo "</ul>\r\n";
};

function display_as_table($data) {
echo "<table border='1'>\r\n";
echo "<tr><th>Text</th><th>Name</th></tr>\r\n";
foreach($data as $main) {
echo "<tr>\r\n";
$n = count($main->subs);
echo "<td rowspan='$n'>$main->txt</td>\r\n";
for($ix = 0; $ix < $n; $ix++) {
if($ix > 0) echo "</tr>\r\n<tr>\r\n";
$sub = $main->subs[$ix];
echo "<td>$sub->nam</td>\r\n";
}
echo "</tr>\r\n";
}
echo "</table>\r\n";
}

$data = load();
display_as_lists($data);
display_as_table($data);

?>

mysql> SELECT main.txt,subs.nam
-> FROM main JOIN subs ON main.id=subs.main_id
-> ORDER BY main.txt;
+-------+--------+
| txt   | nam    |
+-------+--------+
| All A | A1.jpg |
| All B | B2.jpg |
| All B | B3.jpg |
| All B | B1.jpg |
+-------+--------+
4 rows in set (0.00 sec)

mysql> SELECT main.txt,subs.nam
-> FROM main LEFT JOIN subs ON main.id=subs.main_id
-> ORDER BY main.txt;
+-------+--------+
| txt   | nam    |
+-------+--------+
| All A | A1.jpg |
| All B | B2.jpg |
| All B | B3.jpg |
| All B | B1.jpg |
| All C | NULL   |
+-------+--------+
5 rows in set (0.00 sec)

mysql> SELECT main.txt,GROUP_CONCAT(subs.nam)
-> FROM main LEFT JOIN subs ON main.id=subs.main_id
-> GROUP BY main.txt
-> ORDER BY main.txt;
+-------+------------------------+
| txt   | GROUP_CONCAT(subs.nam) |
+-------+------------------------+
| All A | A1.jpg                 |
| All B | B2.jpg,B3.jpg,B1.jpg   |
| All C | NULL                   |
+-------+------------------------+
3 rows in set (0.00 sec)

soduno (33) skrev:

uden oop

arne_v (35) skrev:

uden classes

arne_v (8) skrev:

tazimn (6) skrev:

Altså uden INNER JOIN, men bare JOIN, hvilket i MSSQLs tilfælde betyder en FULL OUTER JOIN.

????

http://msdn.microsoft.com/en-us/library/ms177634%2...

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

tazimn (6) skrev:

Hvis det ikke virker, så skyder jeg skylden på at jeg er stiv..