Request cycle from the database

The question was asked: 49 years 9 months ago   views: 3

Hello there, just two tables post and comments, I need to pull the post and all the comments on the idof the post: post

| id | title | text|

and comments

|id_post | author | comment_text |

Rustic method of course works,

$a = mysql_query('SELECT * FROM post WEHER id='.$_GET["id"]."); while ($b = mysql_fetch_array($a)) { $c= mysql_fetch_array(mysql_query("select * from comments where id_post = '$b['id']'")); }

but I want no cycles tried this but not working:

SELECT t1.title t1.text t2.comment_text FROM post t1, comments t2 Post JOIN ON t2.id_post = WHERE = $_GET['id']
Asked: 01-01-1970 в 03:00:00

Answers   3


I am sure that the id field is unique throughout the table post. Therefore, knowing id, it is possible to select comments.

select author,comment_text from comments where id_post=$id;


Answered: 14-01-2013 в 11:09:39
In this case, only the first comment is displayed and the rest like them as much? - 14-01-2013 в 11:17:26
$sql = " SELECT t1.title t1.text t2.comment_text FROM post t1 LEFT JOIN comments t2 ON t2.id_post = WHERE = ".intval($_GET['id']).";";

By the way, in the table Comments I would unique ID (comment) added. For example, so it was convenient to remove)

Answered: 14-01-2013 в 11:25:36
I like your method, we have to attach like that:) right now.. - 14-01-2013 в 11:39:55
Hmm... also, only the first comment is displayed - 14-01-2013 в 11:45:59
Bad. In conclusion the text of the post will be repeated as many times as him comments, and I think the field type is TEXT and size may be anything. - 14-01-2013 в 13:05:02
@bemulima. 1. Then the question you have the same field of the comment post_id is Id of post to which a comment? What do you have in the comments table there are entries that have the same post_id. 2. The result of this query you have processed a loop of the form: while ($row = @mysql_fetch_array($req)) { if ($lastPost != $row['id']) { Output post } $lastPost != $row['id'] comments } don't forget to add - 14-01-2013 в 16:15:00
@KaZaца - well - cookie))) ...Sorry. I couldn't resist. In this issue salt for me, not to show how from the point of view of optimization, but as a General cost joins you. Yes. Specifically, in this case better to make two queries - the posts separately, the comments separately. But in General, and very often there are problems when too much data is minimal and the gain in performance (by reducing the number of queries with a lot to one) is substantial. - 14-01-2013 в 16:20:36

Loop is needed here, but your not there. You get only 1 comment from all

$c=array(); $a=mysql_query("select * from comments where id_post='$_GET[id]'"); while($b=mysql_fetch_assoc($a)) $c[]=$b;
Answered: 14-01-2013 в 11:27:08
I think here you can get around without cycles - 14-01-2013 в 11:40:21
You are right, I just have not written it so the text was less than the amount - 14-01-2013 в 11:47:03
just judging by the title mysql_fetch_array() sometimes people think that this function returns all rows of result is not true ( in addition has long been time to move on to mysqli - 14-01-2013 в 12:01:13