PHP - Mysql query formuleren.

[rfrancocantero]

Hallo,

Mijn eerste post dus alvast mijn excuses als het misgaat.

Ik heb een Mysql database draaien met een PHP frontend. De tabel ziet er als volgt uit:

Logboek
-id
-Firmanaam
-Datetime
-Aantekening

hierin worden aantekeningen van het bedrijf bijgehouden. Zo kan een Firmanaam meerdere Aantekeningen hebben op verschillende Datetime(s).
In deze tabel staan NIET alle Firmanamen in.

We hebben ook nog een tabel KLANTEN:

KLANTEN:
-Firmanaam
-Adres

Logboek.Firmanaam is een foreig key van KLANTEN.Firmanaam

Nu wil ik in PHP een tabel hebben met unieke Firmanamen en daarbij het jongste contactmoment, gesorteerd op oudste contactmoment.
Firmanamen waar het veld Datetime leeg is, moeten ook worden meegenomen.

De bedoeling is uiteindelijk om met deze lijst een soort relatie call te gaan hanteren waarbij de oudste ( of nooit benaderde ) klanten eerst ( dus bovenaan ) worden benaderd.

Uiteraard heb ik zelf al het een en ander lopen rommelen maar ik kom er niet aan uit.

Stukje van mijn code:

include 'connector_mysql_start.php';
$result = mysqli_query($con,"SELECT DISTINCT Firmanaam, Datetime FROM Logboek order by Datetime ASC");

$row_cnt = mysqli_num_rows($result);
echo $row_cnt . " klanten gevonden.";

Bij voorbaat dank voor jullie hulp.

 

[kenikavanbis]

ik zie niet onmiddelijk de vraag maar dat wat ik zo ongeveer zie.

           if(!$this->existTable()){
                $sql = "CREATE TABLE IF NOT EXISTS `firmanamen`(
                    `id`    INT(20) NOT NULL AUTO_INCREMENT,
                    `name`  varchar(510) COLLATE ".$COLLATE." NOT NULL,
                    `imputDate` datetime NULL DEFAULT NULL,
                    `contactDate` datetime NULL DEFAULT NULL,
                    `stars`    INT(4) DEFAULT 0,
                    PRIMARY KEY (`id`)
                )ENGINE = InnoDB DEFAULT CHARSET=".$CHARSET." COLLATE=".$COLLATE." ;";
              //voer uit
           }

"SELECT `firmanamen`.*  FROM `firmanamen` order by `contactDate` ";//desc asc

 

[rfrancocantero]

Excuus, de vraag is;

Welke SQL statement moet ik volgen om een tabel in PHP te krijgen waarbij in links de firmanaam krijg en rechts het laatste contactmoment van die firma.

Dus ik kan firma XYZ gesproken hebben op 01-04-2015 en daarna nog een keer op 11-01-2016. In dit geval moet dus 1 x Firma XYZ komen te staan met als laatste contactmoment 11-01-2016.

Bij voorbaat dank,

 

[kenikavanbis]

$result = mysqli_query($con,"SELECT DISTINCT Firmanaam, Datetime FROM Logboek GROUP BY Firmanaam order by Datetime ASC");

kan ook als...(ENKEL TER INFO) ZIE ref:http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by
The example query produces unusable results as its not always the latest post that is returned.

SELECT wp_posts.* FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
ORDER BY wp_posts.post_date DESC

The current accepted answer is

SELECT
wp_posts.*
FROM wp_posts
WHERE
wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
HAVING wp_posts.post_date = MAX(wp_posts.post_date) <- ONLY THE LAST POST FOR EACH AUTHOR
ORDER BY wp_posts.post_date DESC

Unfortunately this answer is plain and simple wrong and in many cases produces less stable results than the orginal query.

 

[rfrancocantero]

Hoi,

Bedankt voor je reactie. Dat laatste stukje tekst wil ik me niet in mengen aangezien het niet 100% waterproof is ( staat er ).

Deze query: SELECT DISTINCT Firmanaam, Datetime FROM Logboek GROUP BY Firmanaam order by Datetime ASC
Geeft niet het gewenste resultaat:
1. Firma XYZ heeft in de tabel 5 entries, ik krijg de indruk dat nu het oudste record getoond wordt in plaats van de nieuwste / jongste. Wellicht heb ik het verkeerd uitgelegd.
2. Ik denk dat we moeten gaan werken met een LEFT join maar hoe precies weet ik niet. De Firmanamen waar namelijk (nog) geen Datetime record gevuld is moeten ook getoond worden en dat gebeurd nu niet.

Voorbeeld:

Firma A - Leeg <--- Nooit benaderd
Firma B - 04-01-2016 <--- Het laatst benaderd op 04-01-2016

 

[PatrickStel]

Als ik je vraag zo lees denk ik dat je eerst het beste een paar extra velden kunt maken in je tabel.

Logboek
-id
-Firmanaam
-Datetime
-Aantekening

Kan je beter aanpassen naar iets als dit

Logboek
-ID
-Firmanaam
-PostTime (Tijd dat je ticket aangemaakt is door jezelf of collega´s)
-Aantekening
-Finished (True/False)
-ReplyTime (Tijd dat je hebt gereageerd op het ticket)

Dan kan je aan de hand van je True/False field een selectie gaan maken als je tickets nog open of gesloten zijn.
In het geval van openstaande tickets kan je dan een selectie gaan maken met PostTime (deze kan je door PHP mee laten sturen naar je database, zodat je nooit geen lege entry hebt.
Als je dan wilt zien welke ticket open staat en niet op gereageerd is en niet een reply heeft gekregen dan zal je iets in deze strekking krijgen.

SELECT * FROM Logboek WHERE Finished = ´false´ AND ReplyTime IS NULL OR ´´ ORDER BY PostTime ASC

Zoiets zal je kunnen proberen.

Gr,
Patrick

 

[rfrancocantero]

Hoi Patrick,

Bedankt voor het meedenken. Dit is echter niet de oplossing die ik wens. Het gaat mij puur om de lijst met alle Firmanamen en dan van iedere firmanaam het bijbehorende jongste contactmoment van die betreffende firma.

Toch bedankt!

 

[PatrickStel]

Oke geen probleem

SELECT DISTINCT Firmanaam, DateTime AS DT1, DateTime AS DT2 FROM Logboek ORDER BY [DT1] IS NULL ASC, [DT2] ASC

Je kan dan dit proberen, haal het zelfde veld twee keer uit je table maar je geeft ze een andere naam zodat je bij ORDER BY kan weergeven op als er geen DateTime [DT1] is en daarna DateTime [DT2] op de datum die erin staat.

Of gewoon simpel

ORDER BY [DateTime] IS NULL ASC, [DateTime] ASC

Gr,
Patrick

 

[rfrancocantero]

Hoi Patrick,

Super bedankt, hij geeft echter niet de blanco firmanamen.
Dus hij toont niet de firmanamen waarbij de tabel Datetime leeg is.

 

[PatrickStel]

Kan je misschien vertellen welke type je Datetime veld is?

En een print out van je table zal ook wel handig zijn, dan kan ik je beter helpen.

Gebruik je MySql?

 

[rfrancocantero]

Maar zeker,

zie hier:

CREATE TABLE `Logboek` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Firmanaam` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `Onderwerp` varchar(255) NOT NULL,
  `Datetime` datetime NOT NULL,
  `Aantekening` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Firmanaam` (`Firmanaam`),
  CONSTRAINT `FK_Logboek_KLANTEN` FOREIGN KEY (`Firmanaam`) REFERENCES `KLANTEN` (`Firmanaam`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=latin1

en van KLANTEN

CREATE TABLE `KLANTEN` (
  `Firmanaam` varchar(255) NOT NULL,
  `Straatnaam` text,
  `Postcode` text,
  `Woonplaats` text,
  `AM` varchar(255) DEFAULT 'Onbekend',
  PRIMARY KEY (`Firmanaam`),
  KEY `AM` (`AM`),
  CONSTRAINT `FK_KLANTEN_Accountmanager` FOREIGN KEY (`AM`) REFERENCES `Accountmanager` (`Accountmanager`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

[PatrickStel]

Hoe schrijft je php pagina de entry weg in je database? Want aangezien je in de table hebt staan not null, zal datetime eigenlijk geen null waarde mogen accepteren, probeer de insert query eens weer te geven met print_r, zodat je de daadwerkelijke query ziet die naar de database gaat, daar zie je dan ook datetime als je niks ingevuld hebt welke waarde die mee krijgt

 

[rfrancocantero]

De NULL waarde is de standaard waarde. Er is dus ooit een klant aangemaakt maar waar nog nooit contact mee is geweest. Dan is het veld 'Datetime' leeg / NULL.

Deze klanten moeten dus als eerst benaderd worden. Vervolgens wordt er een aantekening gemaakt en komt die datum in het veld Datetime te staan.

In een later stadia sorteren we de lijst op UNIEKE klant en dan pakken we daarvan het MEEST recente contactmoment. Dat sorteren we vervolgens van OUD(ste) naar NIEUW(ste) en die klant gaan we dan weer opnieuw contacteren. Dat zou dan in theorie de klant zijn waarmee we het langst geen contact meer mee hebben gehad. Hopelijk begrijp je wat ik probeer te verkrijgen. Sorry als het onduidelijk is.

 

[PatrickStel]

Ik snap dat je klanten aanmaakt, maar dit doe je in de tabel ´KLANTEN´ lijkt mij? Op dat moment doe je dus nog niks met de tabel ´Logboek´.

Dus dan kan je ook nog geen sortering maken op klanten die geen Datetime hebben omdat ze nog niet in je ´Logboek´staan.

Of heb je misschien een INSERT query die de klantnaam in zowel ´KLANTEN´ als ´Logboek´ wegschrijft?

Dat is mij niet helemaal duidelijk.

 

[rfrancocantero]

Exact. Dat zou je dus kunnen oplossen met een join van KLANTEN.Firmanaam óf in het logboek iedere klant een default waarde van bijvoorbeeld 01-01-2015 geven in Datetime.

Toch?

 

[PatrickStel]

  $Default_Datetimte = "01-01-2015"; //Standaard waarde meegeven voor DateTime

  $query = "INSERT INTO KLANTEN(Firmanaam, Straatnaam, Postcode, Woonplaats, AM) VALUES ('$Firmanaam', '$Straatnaam', '$Postcode', '$Woonplaats', '$AM')"; //SQL INSERT voor je eerste tabel
  $query2 = "INSERT INTO Logboek (Firmanaam, Email, Onderwerp, Datetime, Aantekening) VALUES ('$Firmanaam', '$Email', '$Onderwerp', '$Default_Datetime', '$Aantekening')";// SQL INSERT voor je tweede tabel

  $result = mysql_query($query) && mysql_query($query2))
  if ($result) { // If it Ran OK.

Een LEFT JOIN kan je eigenlijk alleen maar een SELECT op doen, dus dan zal je elke keer als je de Query laat lopen een INSERT INTO naar je Logboek tabel doen en dan een SELECT in je klanten tabel naar welke klant je op dat moment wilt kopiëren naar je Logboek tabel.

INSERT INTO Logboek(Firmanaam)
SELECT Firmanaam FROM KLANTEN
WHERE Firmanaam='$Firmanaam';

Zoiets dus.

 

[rfrancocantero]

Yes daar kan ik wat mee! Dank je wel voor je hulp!

 

[PatrickStel]

Geen probleem man, als je probleem opgelost is laat dan je post even op beantwoord zetten.

 

[rfrancocantero]

Bij deze gedaan!