requeteComplexe.txt

/**
	
Requete pour a la fois creer l'objet post et le commentaire en relation

**/

INSERT INTO 'objectposts' (ID, ID_User, Date_Post, Url_Media, Description) 
VALUES (NULL, '1', '2025-04-04 00:00:00', NULL, 'salut');

INSERT INTO 'comments' (ID_Object, ID_Post) 
VALUES(LAST_INSERT_ID(), 8);

/**

Requete pour a la fois creer l'objet post et l'event en relation

**/

Insertion
$req = mysqli_prepare($db, "INSERT INTO objectposts (ID_User, Url_Media, Description) VALUES (?, ?, ?)");

mysqli_stmt_bind_param($req, "iss", $id_User, $Url_Media,$description);

mysqli_stmt_execute($req);

$lastid = mysqli_insert_id($db);


$req = mysqli_prepare($db, "INSERT INTO events (ID_Object, Date, Location, Status) VALUES(?, ?, ?, ?)");

mysqli_stmt_bind_param($req, "isss", $lastid, $Date, $Location, $Status);
mysqli_stmt_execute($req);


mysqli_stmt_close($req);

INSERT INTO 'objectposts' (ID, ID_User, Date_Post, Url_Media, Description) 

VALUES (NULL, '1', '2025-04-04 00:00:00', NULL, 'salut');


INSERT INTO 'events' (ID_Object, Date, Location, Status) 

VALUES
(LAST_INSERT_ID(), '2025-04-04 00:00:00', 'Paris, 'Private');


/**
	
Requete pour recuperer tous les posts chronologiquement auquel l'utilisateur X a accès => ID_User2

**/

SELECT DISTINCT o.ID, o.ID_User, us.Firstname, us.Lastname, o.Description, o.Date_Post, e.Date, e.Location, e.Status 
FROM events e, objectposts o, users us 
WHERE o.ID = e.ID_Object AND(
    EXISTS(SELECT * FROM friendships f WHERE f.ID_User1 = o.ID_User 
AND f.ID_User2 = '6' AND f.Status = 'Accepted' 
 AND ((f.Relationship = 'Friend' 
AND e.Status IN ('Public','Friends Only','Network')) 
OR (f.Relationship = 'Pro' 
AND e.Status IN ('Network','Public')))) 
OR (o.ID_User = '6') ) AND us.ID = o.ID_User  ORDER BY o.Date_Post DESC LIMIT 25


/**
requete pour savoir si une discussion est engagée entre 2 individus
**/

select a.ID 
  from chatgroups a
  join chatgroups b on b.ID = a.ID and a.ID_User != b.ID_User
where a.ID_User = '6'
  or a.ID_User = '2'
  GROUP BY a.ID
  HAVING COUNT(*)= 2

/**
requete qui recupere les discussions les plus récentes
**/

SELECT g.ID, g.Name, g.Notif FROM chatgroups g 
JOIN chatmessages m on m.ID_Conv = g.ID
JOIN objectposts o on o.ID = m.ID_Post
WHERE g.ID_User = '6'
GROUP BY g.ID
ORDER by MAX(o.Date_Post) DESC