Php, MySQL JOIN auf mehere Einträge

[d0ne]

Hallo,

ich habe mal wieder ein Problem mit PHP, MySQL & Co!

Kurze beschreibung, ich habe eine Tabelle, in welcher eine bestimtme Usergruppe gespeichert ist,
sagen wir "AutoHändler", in dieser wird die Marke gespeichert,
auf die sich der entsprechende AutoHändler spezialisiert hat, z.B. BMW, Audio etc.
In ein weiteren Tabelle stehen Käufer, diese haben ihre "Lieblinsmarke".
In einer dritten Tabelle werden alle AutoHändler gespeichert, die im Moment "verfügbar" sind, also einen Auftrag suchen.
Wenn jetzt ein Käufer einen Auftrag ("suche Auto") aufgiebt, soll in dieser Tabelle ein verfügbarer Händler gesucht werden.
Und natürlich soll ein Händler, der sich auf die Lieblingsmakre spezialisiert hat bevorzugt werden.

Und jetzt kommen die Probleme!

1. Wie sortiere ich die dritte Tabelle, wenn ich sie abfrage!?
Ich möchte das alle verfügbareren AuroHändler abgefragt werden, aber das die mit der Lieblingsmarke "oben stehen" - wie schaffe ich das!?
(sortieren geht nicht, gruppieren auch nicht ... oder!?)

2. Ein Händler soll sich auf mehere Marken spezialisieren können.
Da ich von wegen Volltextsuche etc. eh nicht die Marke direkt in der AutoHändler - Spalte speichere,
sondern in einer zweiten/dritten Tabelle erst einmal kein Problem.
(In einer zweiten Tabelle werden den Marken nummern zugewiesen, BMW = 1, Audi = 2, etc.
In einer dritten Tabelle werden die ID von AutoHändler und die Nummern aus der zweiten Tabelle verknüpft -
also kein Problem, hier mehere Verknüpfungen für einen AutoHändler zu speichern ...).
Aber wie mache ich dass dann bei der SQL - Abfrage, mit einem JOIN,
kann nicht von der Tabelle AutoHändler über die "Verknüpfungstabelle" ja nur zu einem Eintrag in der "BWM = 1 " - Tabelle, oder!?

3. Wenn 2. lösbar ist ( -.- ) könnte man vll. noch einbauen, dass ein Käufer mehere Lieblinsmarken hat - aber das später!

Ich hoffe ich habe jetzt nichts (wichtiges) vergessen und hier versteht jemand diese wirrwarr!

mfg
d0ne

 

[mauralix]

Bist du von gebrauchtwagen.at?

 

[d0ne]

Nein, bin ich nicht.

Und so wie oben Beschrieben existiert auch (bei mir) kein System, soll heißen, ich habe nichts mit Auto (ver)käufern zu tun!
Ich wollte nur nicht beschreiben, was ich eigentlich mache ...

vll. kann trotzdem jemand helfen !?

mfg
d0ne

 

[she3p]

1 http://dev.mysql.com/doc/refman/5.1/de/union.html Union-Select

SELECT h.Marke h.Name 
FROM Händler h Aufträge a Käufer k
WHERE k.Lieblingsmarke = h.Marke
AND a.id = h.id

UNION SELECT h2.Marke h2.Name
FROM Händler h2 Aufträge a2 Käufer k2
WHERE k2.Lieblingsmarke <> h2.Marke
AND a2.id = h2.id

[I](untested)[/I]

Kanns nicht testen, darum folgendes: Ich bin mir nicht ganz sicher ob ich die Tabellen beim zweiten Select wirklich noch einmal abfragen muss, oder ob ich die Aliasen des ersten Selects weiterverwenden kann. Einfach kurz testen.

Zweite Möglichkeit: Einfach zwei Querys ausführen.

2 Vergleichbares Prinzip

Das Problem ist die Form, wie du den Output gerne hättest; Folgendes Konstrukt

SELECT h.Name m.Name
FROM Händler h Marken m MapHändlerMarken map
WHERE h.id = map.h_id
AND m.id = map.m_id

[I](untested)[/I]

ergäbe Tupel der Form

Händler | Marke
---------------
Eins    | Foocar
Eins    | Barcar
Eins    | Meepcar
Zwei    | Foocar

Nun nur noch erstes auf zweites Prinzip anwenden und 2 ist gelöst.

3 Ich versuchs mal

SELECT h.Name m.Name
FROM Händler h Marken m MapHändlerMarken mapHM Käufer k mapMarkenKäufer mapMK
WHERE h.id = mapHM.h_id
AND m.id = mapHM.m_id
AND m.id = mapKM.m_id
AND k.id = mapMK.k_id
AND a.h_id = h.id

[I](untested)[/I]

Händler - Händlertabelle
Marken - Markentabelle
MapHändlerMarken - Tabelle mit Händler & MarkenIDs
MapKäuferMarken - Tabelle mit Käufer & MarkenIDs
​

Gerade bei der letzten Query wäre es sehr gut möglich, dass ich irgendetwas vergessen habe. Folglich; bitte nicht darauf festnageln.

 

[she3p]

So. Da ich gerade in einer Wartephase stecke, hier mal einige Spielereien.

Query1
Holt sich alle Tupel (Händlername, Markenname) welche von mindestens einem Händler angeboten werden, und welche von einem spezifischen Kunden als Lieblingsmarke eingetragen sind.

  SELECT    r.name AS rname,
            b.name AS bname
    
    
    /* GET TABLES */
    
    FROM    retailers r,
            brands b,
            customers c,
            offers o,
            map_brands_retailers mbr,
            map_brands_customers mbc
    
            
    /* MAP BRAND TO RETAILER */
    
    WHERE    r.id = mbr.id_retailer
    AND        b.id = mbr.id_brand
    
    
    /* JOIN OFFERS FROM RETAILER */
    
    AND        r.id = o.id_retailer
    AND        b.id = o.id_brand
    
    
    /* JOIN CUSTOMERS NEED */
    
    AND        c.id = mbc.id_customer
    AND        b.id = mbc.id_brand
    
    
    /* SELECT JUST MY NEEDS */
    
    AND        c.name = 'put your name here'

Query2 Macht das genau Gegenteil. Es holt sich alle Tupel (Händlername, Markenname) welche zwar von einem Händler im Angebot stehen, aber von jenem spezifischen Kunden nicht gewünscht werden.

  SELECT    r.name AS rname,
            b.name AS bname
    
    
    /* GET TABLES */
    
    FROM    retailers r,
            brands b,
            offers o,
            map_brands_retailers mbr
            
            
    /* MAP BRAND TO RETAILER */

    WHERE    r.id = mbr.id_retailer
    AND        b.id = mbr.id_brand


    /* JOIN OFFERS FROM RETAILER */

    AND        r.id = o.id_retailer
    AND        b.id = o.id_brand
    
    
    /* EXLCUDE CUSTOMERS NEED */
    
    AND NOT EXISTS(
    
        SELECT 1
        
        FROM     customers c,
                map_brands_customers mbc

        WHERE    c.id = mbc.id_customer
        AND        b.id = mbc.id_brand
        
        AND        c.name = 'put your name here'
        )

Habs auch gleich noch getestet. Hier mal die Scripts zum selber ausprobieren.

Datenbankdump

Spoiler: install

-- phpMyAdmin SQL Dump
-- version 3.2.2.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 09, 2009 at 01:32 PM
-- Server version: 5.1.37
-- PHP Version: 5.2.10-2ubuntu6.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `habo`
--

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

--
-- Table structure for table `brands`
--

DROP TABLE IF EXISTS `brands`;
CREATE TABLE IF NOT EXISTS `brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `brands`
--

INSERT INTO `brands` (`id`, `name`) VALUES
(1, 'bmw'),
(2, 'opel'),
(3, 'ferarri');

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

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`) VALUES
(1, 'me');

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

--
-- Table structure for table `map_brands_customers`
--

DROP TABLE IF EXISTS `map_brands_customers`;
CREATE TABLE IF NOT EXISTS `map_brands_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_brand` int(11) NOT NULL,
  `id_customer` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `map_brands_customers`
--

INSERT INTO `map_brands_customers` (`id`, `id_brand`, `id_customer`) VALUES
(1, 1, 1);

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

--
-- Table structure for table `map_brands_retailers`
--

DROP TABLE IF EXISTS `map_brands_retailers`;
CREATE TABLE IF NOT EXISTS `map_brands_retailers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_brand` int(11) NOT NULL,
  `id_retailer` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `map_brands_retailers`
--

INSERT INTO `map_brands_retailers` (`id`, `id_brand`, `id_retailer`) VALUES
(1, 2, 1),
(2, 1, 2),
(3, 3, 1);

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

--
-- Table structure for table `offers`
--

DROP TABLE IF EXISTS `offers`;
CREATE TABLE IF NOT EXISTS `offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_retailer` int(11) NOT NULL,
  `id_brand` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `offers`
--

INSERT INTO `offers` (`id`, `id_retailer`, `id_brand`) VALUES
(1, 1, 2),
(2, 2, 1),
(3, 1, 3);

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

--
-- Table structure for table `retailers`
--

DROP TABLE IF EXISTS `retailers`;
CREATE TABLE IF NOT EXISTS `retailers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `retailers`
--

INSERT INTO `retailers` (`id`, `name`) VALUES
(1, 'ret one'),
(2, 'ret two');

Kleine Datenbankklasse (nicht weiter zu beachten)

Spoiler: sqlclass

<?php

class Dbc
{
    private $server;
    private $user;
    private $pass;
    private $db;
    private $returntype;
    
    public $data;
    public $count;
    public $query;
    
    public function __construct($server = null, $user = null, $pass = null, $db = null)
    {    
        $this->server    =    (is_null($server)) ? 'localhost' : $server;
        $this->user        =    (is_null($user)) ? 'root' : $user;
        $this->pass        =    (is_null($pass)) ? 'pass' : $pass;
        $this->db        =    (is_null($db)) ? 'habo' : $db;
        
        $handler = $this->connect($this->server, $this->user, $this->pass, $this->db);
        
        if(!$handler)
        {
            die('Connection to database refused.');
        }
    }
    
    private function connect($server, $user, $pass, $db)
    {
        $connection = mysql_connect($server, $user, $pass);
        $use = mysql_query("USE $db;");
        
        return $use;
    }
    
    public function query($query, $autoexec = false)
    {
        if(trim($query))
        {
            $this->query = trim($query);
        
            $this->returntype = 'bool';
            if(substr_count(strtolower($this->query), 'select', 0, 8) > 0) $this->returntype = 'data';
            if(substr_count(strtolower($this->query), 'delete', 0, 8) > 0) $this->returntype = 'bool';
            if(substr_count(strtolower($this->query), 'insert', 0, 8) > 0) $this->returntype = 'bool';
        
            if($autoexec)
            {
                return $this->exec();
            }
            else
            {
                return true;
            }
        }
        else
        {
            return false;
        }
    }
    
    public function exec()
    {
        if(!empty($this->query))
        {
            $sqlget = $this->query;
            $sqlgot = mysql_query($sqlget);
            
            if(strlen( mysql_error() ) > 0)
            {
                echo '[' . mysql_errno() . '] ' . mysql_error() . '<br />';
            }
            
            if($this->returntype == 'data')
            {
                $this->data = array();
                $this->count = 0;
                
                if($sqlgot)
                {
                    while($sqlobj = mysql_fetch_object($sqlgot))
                    {
                        if($sqlobj instanceof stdClass)
                        {
                            $this->data[] = $sqlobj;
                            $this->count ++;
                        }
                    }
                }
                
                return $this->data;
            }
            else
            {
                return $sqlgot;
            }
        }
    }
}

?>

Das index-Script, mit alles Querys und Outputanweisungen.

Spoiler: script

<?php

//core
require_once('class.dbc.php');

function out($data)
{
    if($data)
    {
        echo '<table border="1">';
        echo '<tr><th>retailer</th><th>brand</th></tr>';
        
        foreach($data as $row)
        {
            echo '<tr>';
                
                echo '<td>' . $row->rname . '</td>';
                echo '<td>' . $row->bname . '</td>';        
                
            echo '</tr>';
        }
        
        echo '</table>';
    }
}

//config
$customer = 'me';

//habo::my offers
$myDbc = new Dbc();
$myDbc->query("
    SELECT    r.name AS rname,
            b.name AS bname
    
    
    /* GET TABLES */
    
    FROM    retailers r,
            brands b,
            customers c,
            offers o,
            map_brands_retailers mbr,
            map_brands_customers mbc
    
            
    /* MAP BRAND TO RETAILER */
    
    WHERE    r.id = mbr.id_retailer
    AND        b.id = mbr.id_brand
    
    
    /* JOIN OFFERS FROM RETAILER */
    
    AND        r.id = o.id_retailer
    AND        b.id = o.id_brand
    
    
    /* JOIN CUSTOMERS NEED */
    
    AND        c.id = mbc.id_customer
    AND        b.id = mbc.id_brand
    
    
    /* SELECT JUST MY NEEDS */
    
    AND        c.name = '$customer'
");
$myDbc->exec();

echo '<h1>my requested offers</h1>';
out($myDbc->data);

unset($myDbc);

//habo::not my offers
$myDbc = new Dbc();
$myDbc->query("
    SELECT    r.name AS rname,
            b.name AS bname
    
    
    /* GET TABLES */
    
    FROM    retailers r,
            brands b,
            offers o,
            map_brands_retailers mbr
            
            
    /* MAP BRAND TO RETAILER */

    WHERE    r.id = mbr.id_retailer
    AND        b.id = mbr.id_brand


    /* JOIN OFFERS FROM RETAILER */

    AND        r.id = o.id_retailer
    AND        b.id = o.id_brand
    
    
    /* EXLCUDE CUSTOMERS NEED */
    
    AND NOT EXISTS(
    
        SELECT 1
        
        FROM     customers c,
                map_brands_customers mbc

        WHERE    c.id = mbc.id_customer
        AND        b.id = mbc.id_brand
        
        AND        c.name = '$customer'
        )
");

$myDbc->exec();

echo '<h1>other offers</h1>';
out($myDbc->data);

unset($myDbc);


?>

Ich hoffe du kannst damit was anfangen.
LG. Schäfchen

 

[d0ne]

Hallo,

leider komme ich erst jetzt dazu an diesem Problem weiter zu arbeiten ...

@she3p, du musst ja echt langeweile haben ;P
Ich habe dein "System" mal ausprobiert und es funktioniert, leider verstehe ich nicht ganz, warum! -.-

Wenn ich ein Select (mit einem Where x = y) auf eine Tabelle mache wird doch jede Spalte durchlaufen und geguckt, ob dor x = y ist. Aber was passiert wenn ich mehrere Select habe, und mehrere Where !?
Irgendwie blicke ich da nciht ganz durch ...

Wie sieht dass denn von der Performa aus, mit sovielen Select und Where (And) - irgendwie wirkt das sehr unperformand...

ich glaube ich habe noch mehr Frage dazu, aber erst 'mal diese

mfg
d0ne

 

[she3p]

Zum Thema Performanz:

<?php

require_once('class.dbc.php');

$time = microtime(true);
for($i = 0; $i < 100000; $i++)
{
    $myDbc = new Dbc();
    $myDbc->query('
        SELECT *
        FROM brands b, customers c, map_brands_customers mbc
        WHERE b.id = mbc.id_brand
        AND c.id = mbc.id_customer
    ');
}
echo 'direct: ' . (microtime(true) - $time) . '<br />';

$time = microtime(true);
for($i = 0; $i < 100000; $i++)
{

    $myDbc = new Dbc();
    $myDbc->query('
        SELECT *
        FROM brands b
        JOIN map_brands_customers mbc ON ( b.id = mbc.id_brand )
        JOIN customers c ON ( c.id = mbc.id_customer )
    ');
}
echo 'joins: ' . (microtime(true) - $time);

?>

Gemäss dieser Quelle sei es die beste Möglichkeit die Performanz einer Query zu testen. Falls irgendjemand eine bessere Variante kennt, bitte berichtigen.

Anyway. Der Output des Scripts wird in etwa so aussehen

direct: 4.73232603073
joins: 4.89208507538

Meiner Meinung nach ist dieser Unterschied irrelevant. Keine Ahnung, ob sich die zeitliche Differenz erhöht, wenn mehr Daten vorhanden sind, aber soweit ich mich an meine Datenbankeinführungsvorlesungen zu Beginn meines Studiums zurück erinnere, ist es der SQL-Parsing-Engine egal, in welcher Form der Query-Input erfolgt, schlussendlich wird er in der selben Weise abgearbeitet.

 

[d0ne]

Hallo she3p,

auch meiner Meinung nach ist der Unterschied irrelevant,
auf jeden Fall bei meiner "Größenortnung" ...

Dann werde ich mal versuchen deine Scripte so umzubauen, dass sie mit meinem eigentlichen System funktionieren. Trotzdem möchte ich noch verstehen, wie das jetzt genau funktioniert! ^^

Ich habe eine Tabelle

Tabelle1
id | SpalteA | SpalteB
1 | Wert1 | Wert2
2 | Wert4 | Wert5
3 | Wert1 | Wert3


jetzt mache ich ein "SELECT * FROM Tabelle1 WHERE SpalteA = Wert4" auf diese Tabelle (wie würde man das richtig ausdrpcken!? ).

Als ergebnis bekomme ich die zweite Spalte ...(also 2 | Wert4 | Wert5 )

Wenn ich mit JOIN arbeteite, werden ja "temponär" aus meheren Tabelle eine, (bzw. eine neue, die die per JOIN verbundenen Tabellen beinhaltet).

ich habe zwei Tabellen:

Tabelle1
id | SpalteA | SpalteB
1 | Wert1 | Wert2
2 | Wert4 | Wert5
3 | Wert1 | Wert3

Tabelle2
id | SpalteX | SpalteY
1 | Zahl1 | Zahl2
2 | Zahl4 | Zahl5
3 | Zahl1 | Zahl3

Jetzt mache ich ein "SELECT * FROM Tabelle1 LEFT JOIN Tabelle2 ON Tabelle1.id = Tabelle2.id WHERE SpalteA = Wert4".

Dann wird eine temponäre Tabelle erstellt die so aussieht:

id | SpalteA | SpalteB | SpalteX | SpalteY
1 | Wert1 | Wert2 | Zahl1 | Zahl2
2 | Wert4 | Wert5 | Zahl4 | Zahl5
3 | Wert1 | Wert3 | Zahl1 | Zahl3

Mit dem Befehl erhalte ich also "2 | Wert4 | Wert5 | Zahl4 | Zahl5".

So weit glaube ich es auch zu verstehen. -.-

Aber wie läuft das "SELCECT" - Verfahren, wenn ich mehere Tabellen habe, wie wird da ausgesucht. Weil bei einer Tabelle (und JOIN macht ja aus vielen Tabellen eine) wird doch Spalte für Spalte durchsucht, bis das "WHERE" erfüllt ist - oder!?

es wäre nett, wenn sich jemand die Mühe macht, meine Denkfehler zu finden und mir zu erklären!

mfg
d0ne

 

[beavisbee]

Diese Query

<?php
    $myDbc->query('
        SELECT *
        FROM brands b, customers c, map_brands_customers mbc
        WHERE b.id = mbc.id_brand
        AND c.id = mbc.id_customer
    ');
?>

arbeitet genauso wie diese:

<?php
    $myDbc->query('
        SELECT *
        FROM brands b
        JOIN map_brands_customers mbc ON ( b.id = mbc.id_brand )
        JOIN customers c ON ( c.id = mbc.id_customer )
    ');
}
?>

was in der zweiten Query über "ON (<Verbindung>)" realisiert wird, passiert in der ersten Query einfach im "WHERE <Verbindung>"...

An sich, rein mathematisch, stellt

        FROM brands b, customers c, map_brands_customers mbc

ein Kreuzprodukt aus den Zeilen von brands und den Zeilen von customers und anschließend ein Kreuzprodukt aus diesem Ergebnis und den Zeilen der Tabelle map_brands_customers dar.

Über die WHERE-Klausel werden dann die sinnvollen Zeilen herausgefiltert (quasi die JOIN-Verbindung)

 

[d0ne]

Ups ...

ich bin gerade durch zufall noch mal hier auf das Thema gestoßen und habe gemerkt, dass ich mcih noch gar nicht bedankt habe! -.-

Sry, das will ich jetzt nachholen: Danke!

Im Endeffekt habe ich ich zwar so viel an dem Projekt (welches ich erst übernommen habe) geändert dass ich ganz andere Probleme habe/hatte aber dennoch waren hier einige neue Dinge dabei, die mir garantiert mal weiter helfen werden!

deshalb noch mal danke, Frohe Weihnachten, einen guten Rutsch und mfg
d0ne