I try to make notifications on several tables, the script itself basically works, gives out the data that is needed, but with one flaw.

The output comes from the friends and repost table, if both tables have the necessary record, then everything is fine, but if we say friends have it and the repost does not, then the friends record is displayed and with it the whole repost table and so with each id found.

Here is the request and output in the loop:

 <?php $query = "SELECT `friends`.id AS friends_id, `repost`.id AS repost_id FROM `friends`,`repost` WHERE (`friends`.id_2 = '$_SESSION[id]' AND `friends`.id_1 <> '$_SESSION[id]' AND `friends`.obrabotan = '1') OR (`repost`.users = '$_SESSION[id]' AND `repost`.users2 <> '$_SESSION[id]' AND `repost`.status = '1')"; $sql_yvedom = mysql_query($query); while($row = mysql_fetch_array($sql_yvedom)){ echo $row["friends_id"].$row["repost_id"]; }

I would be very grateful for the help, I do not know how to build a condition, so that if there is no repost , it would not output the entire table.

2 answers 2

You have a Cartesian product of the tables ( friends, repost ), so if the restricting condition does not work, then each record of one table is assigned each record of another table. It is necessary to pick up the ON-condition for linking records from two tables.

 <?php $query = "SELECT `friends`.id AS friends_id, `repost`.id AS repost_id FROM `friends`,`repost` ON friends`.id_2 = repost`.users AND friends`.id_2 = $_SESSION[id] WHERE (`friends`.id_1 <> '$_SESSION[id]' AND `friends`.obrabotan = '1') OR (`repost`.users2 <> '$_SESSION[id]' AND `repost`.status = '1')"; $sql_yvedom = mysql_query($query); while($row = mysql_fetch_array($sql_yvedom)){ echo $row["friends_id"].$row["repost_id"]; }
  • In theory, the condition is correct, but something does not work. I changed the condition in ON in different ways, but there is no output. - Albert Ushakov
  • For a good dump of your database is needed (albeit trimmed), otherwise it is difficult to reproduce the situation. - cheops
  • The friends table (id, id_1 is the subscribing user, id_2 to whom they subscribe, date, obrabotan - the subscription status). Table repost (id, users - to whom the post was sent, users2 - who is the sender, status - the status of the send). - Albert Ushakov
  • The question is relevant, you can not figure out and build the correct algorithm for the output? Or you will have to make a multi-request conclusion (and yes, it would be a pleasant surprise for the output to take place according to the date given both tables, I understand that this is a cycle, but if it’s not difficult for you to show how it is done correctly?) - Albert Ushakov
  • I almost figured out and found the output through UNION, but I don’t understand how to add ORDER BY date DESC. The output framework is: SELECT T.cnt FROM (SELECT id_1 AS cnt FROM friends WHERE id_2 = '$ _SESSION [id]' AND id_1 <> '$ _SESSION [id]' AND obrabotan = '1' UNION SELECT users2 AS cnt FROM repost WHERE users = '$ _SESSION [id]' AND users2 <> '$ _SESSION [id]' AND status = '1' UNION SELECT my_id AS cnt FROM comment WHERE my_id <> '$ _SESSION [id]' AND you_id = '$ _SESSION [id] 'AND status =' 1 ') AS T - Albert Ushakov

Found another way out. UNION.

Here is the source code for the request:

 SELECT T.cnt FROM ( SELECT id_1 AS cnt, date FROM friends WHERE id_2 = '$_SESSION[id]' AND id_1 <> '$_SESSION[id]' AND obrabotan = '1' UNION SELECT users2 AS cnt, date FROM repost WHERE users = '$_SESSION[id]' AND users2 <> '$_SESSION[id]' AND status = '1' UNION SELECT my_id AS cnt, date FROM comment WHERE my_id <> '$_SESSION[id]' AND you_id = '$_SESSION[id]' AND status = '1' ) AS T ORDER BY date DESC

I do not know how this option on the speed of work, and in general there is a difference in the load. But I think that if the request is one and not 3, then mysql is less loaded. I hope I was not mistaken in the code, if there are any comments, I would be happy to hear them in the comments.