PHP MySQL: Inneer Join

[Blackbenji]

Hallo,

ich würde gerne folgendes Problem lösen:

Ich möchte auf meiner Seite alle Kategorien anzeigen, dahinter in Klammern wie oft diese Kategorie benutzt wird.

category_name

id | categorie_name
1 | test1
2 | test2
3 | test3

category_node

category_id | content_id
1 | 5
1 | 7
3 | 8
2 | 1

content

id | headline | state
5 | test a | 1
7 | test b | 0
8 | test c | 1

mein problem ist nun, dass aus der tabelle content der wert state relevant ist.
state 0 bzw 1 sagt, ob der eintrag versteckt ist, oder angezeigt wird.

Versucht habe ich es damit:

$result = $database->query("
        SELECT * FROM ".GLOBAL_CATEGORY_NAME_DB
);
if (!mysql_num_rows($result) == 0) {
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $category = $database->query("
        SELECT
            category.*,
            content.*
        FROM
            ".GLOBAL_CATEGORY_NODE_DB." category,
            ".GLOBAL_CONTENT_DB." content
        WHERE
            category.category_id = '".$row['id']."'
        AND
            content.id = category.content_id
        AND
            content.state = '1'
    ");
        $countcategory = mysql_num_rows($category);
        echo $countcategory;
    }
}

Bekomme hier aber leider nur den Wert 0 raus.

Jemand vielleicht einen Denkanstoß für mich?

 

[Daaron]

Aus der Kalten würd ich sagen:

1.) nicht mehrere FROM, sondern nimm das JOIN ... ON ...
SELECT ... FROM category_name JOIN category_node ON category_name.id = category_node.category_id...
2.) mir fehlt da n count() und n GROUP BY
Du willst ja sicherlich wissen, wie viele content_id in einer category_id stecken: klarer Fall für n GROUP BY category_id (und n count(content_id)

 

[Blackbenji]

habe es jetzt mal so versucht, aber da bekomme ich immer NULL zurück

$result = $database->query("
    SELECT
      *
    FROM
        ".GLOBAL_CATEGORY_NAME_DB." category_name
    LEFT JOIN
        ".GLOBAL_CATEGORY_NODE_DB." category_node ON category_node.category_id = category_name.id
    LEFT JOIN
        ".GLOBAL_CONTENT_DB." content ON content.id = category_node.content_id
    WHERE
        content.state = '1'
    GROUP By
        category_node.category_id
");

 

[ice-breaker]

SELECT category_name.id, category_name.categorie_name, COUNT(*) AS cnt
FROM category_name
JOIN category_node ON(category_name.id = category_node.category_id)
LEFT JOIN content ON(category_node.content_id = content.id AND content.state = 1)
GROUP BY BY category_name.id, category_name.categorie_name

So?

Die Spalten sind aber sehr komisch benannt, vor allem category_name.categorie_name ist schon irgendwie ein Indiz für eine miserable Benennung. Tipp: Wenn du Spalten hast, die zum joinen sind und in anderen Tabellen auch vorkommen, dann versuch doch diese gleich zu bennenen. Dann sieht man sofort, dass die Tabellen anhand dieser Kriterien verknüpft werden können und man kann in vielen Fällen ein JOIN ... USING(...) nutzen.

 

[Blackbenji]

@icebreaker: danke!

ja das mit der benamung sehe ich auch gerade als problem. sehr verwirrend wenn man mit joins arbeitet :-(

so funktioniert es nun!

$result = $database->query("
    SELECT category_name.id, category_name.category_name, COUNT(*) AS cnt
    FROM ".GLOBAL_CATEGORY_NAME_DB." category_name
    JOIN ".GLOBAL_CATEGORY_NODE_DB." category_node ON(category_name.id = category_node.category_id)
    LEFT JOIN ".GLOBAL_CONTENT_DB." content ON(category_node.content_id = content.id AND content.state = 1)
    GROUP BY category_name.id, category_name.category_name
");
if (!mysql_num_rows($result) == 0) {
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "<pre>";
        var_dump($row);
        echo "</pre>";
    }
}

aber: count liefert hier leider einen eintrag, obwohl state = 0 in der datenbank ist.

damit hat mir das ganze bislang nichts gebracht

 

[ice-breaker]

Dann solltest du vllt. beschreiben wie dieser State mit der Zählung zusammenhängt.

 

[Blackbenji]

state hat den wert 0 oder 1
0 = hidden
1 = visible

es geht darum das wenn ich einen content entry auf 0 (hidden) setze soll er nicht in den categorys auftauchen.

 

[ice-breaker]

Das erklärt imho weder warum falsch gezählt wird (Was ist falsch?) noch welchen Einfluss state auf die Zählung haben soll. Denn ausgeschlossen werden aktuell Datensätze mit state=0 - was die Anforderung zu sein scheint?

Edit: vllt weiß ich ja doch, was du meinst. Füge mal ein "HAVING COUNT(*) > 0" am Ende des Querys ein.

 

[Blackbenji]

In meiner Datenbank gibt es aktuell einen Datensatz innerhalb von "content"
Dieser hat den "state" = 0.
Innerhalb von category_node besteht aber die Verknüpfung von 2 Categories.
Wenn ich nun den Query abschicke sollte ein "0" als Ergebnis kommen, weil der Content ja nicht sichtbar ist.
Leider ist es, stand der dinge oben, immernoch so, dass ich "1" pro category zurück bekomme.

Auch "HAVING COUNT(*) > 0" ändert daran leider nichts.

 

[ice-breaker]

Ganz ehrlich? Du beschreibst schon wieder nur was du haben willst, aber nicht was wo und warum nicht stimmt. An den Biepsieldaten die du oben gepostet hast, ist imho der Query richtig. Nur für Kategorie 2 kann ich das nicht absolut sagen, da du den Content-Datensatz 1 nicht dargestellt hast.

Kategorie 1 hat die Content-Datensätze 5 und 7, 5 ist sichtbar, 7 ist nicht sichtbar --> Anzahl 1
Kategorie 2 hat den Content-Datensatz 1. Ob 1 (nicht) sichtbar ist, weiß man nicht --> Anzahl ?
Kategorie 3 hat den Content-Datensatz 8, 8 ist sichtbar. --> Anzahl 1

Ist es nur ein Zufall deiner Demo-Daten oder besteht wirklich eine 1:1-Verknüpfung von einer Kategorie zu einem Content-Datensatz? Weil dann ist die Tabelle category_node absolut sinnlos.

 

[Blackbenji]

gut machen wir es anders. anbei code und sql zum importieren:

-- phpMyAdmin SQL Dump
-- version 3.5.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 15. Dez 2012 um 18:59
-- Server Version: 5.5.28
-- PHP-Version: 5.4.7

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `test2`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `category_name`
--

CREATE TABLE IF NOT EXISTS `category_name` (
  `id` int(20) NOT NULL,
  `category_name` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `category_name`
--

INSERT INTO `category_name` (`id`, `category_name`) VALUES
(1, 'Test1'),
(2, 'Test2'),
(3, 'Test3');

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `category_node`
--

CREATE TABLE IF NOT EXISTS `category_node` (
  `category_id` int(20) NOT NULL,
  `content_id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `category_node`
--

INSERT INTO `category_node` (`category_id`, `content_id`) VALUES
(1, 5),
(1, 7),
(3, 8),
(2, 1);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `content`
--

CREATE TABLE IF NOT EXISTS `content` (
  `id` int(20) NOT NULL,
  `headline` text NOT NULL,
  `state` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `content`
--

INSERT INTO `content` (`id`, `headline`, `state`) VALUES
(5, 'test a', 1),
(7, 'test b', 0),
(8, 'test c', 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

code:

$result = $database->query("
    SELECT category_name.id, category_name.category_name, COUNT(*) AS cnt
    FROM category_name
    JOIN category_node ON(category_name.id = category_node.category_id)
    LEFT JOIN content ON(category_node.content_id = content.id AND content.state = 1)
    GROUP BY category_name.id, category_name.category_name
");
if (!mysql_num_rows($result) == 0) {
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "<pre>";
        var_dump($row);
        echo "</pre>";
    }
} else {
    echo "keine daten";
}

als var_dump kommt raus:

array(3) {
  ["id"]=>
  string(1) "1"
  ["category_name"]=>
  string(5) "Test1"
  ["cnt"]=>
  string(1) "2"
}

array(3) {
  ["id"]=>
  string(1) "2"
  ["category_name"]=>
  string(5) "Test2"
  ["cnt"]=>
  string(1) "1"
}

array(3) {
  ["id"]=>
  string(1) "3"
  ["category_name"]=>
  string(5) "Test3"
  ["cnt"]=>
  string(1) "1"
}

wie du selber schon sagtest müsste "Test1" (Kategorie 1) Wert 1 anzeigen, zeigt aber 2 an. Es wird also der State = 1 bei COUNT nicht berücksichtigt

 

[ice-breaker]

SELECT category_name.id, category_name.category_name, COUNT(content.id)
FROM category_name
JOIN category_node ON (category_name.id = category_node.category_id)
LEFT JOIN content ON (category_node.content_id = content.id
AND content.state =1)
GROUP BY category_name.id, category_name.category_name

büdde

Problem war, dass natürlich auch deine Verknüpfungstabelle im Result vorkam und mitgezählt wurde, das hatte ich nicht bedacht. Generell ist es mit so einem SQL-Dump leichter, hat so keine 60s gedauert.

 

[Blackbenji]

perfekt!

danke für die hilfe und die geduld! werde beim nächsten mal gleich einen passenden dump liefern!