Psycho Posted August 11, 2012 Share Posted August 11, 2012 I been trying to do JOINS / UNION and im still unsuccessful. Well, what you have is a complete mess. I tried making sense of it and just gave up. Why don't you give some details about your table structures and what you are trying to achieve. Quote Link to comment Share on other sites More sharing options...
id Posted August 11, 2012 Author Share Posted August 11, 2012 Ok... So the application that i am making is a forum board. To achieve this, i have 2 tables in my DB : forum_question and forum_answer. forum_question structure is: id (id of the forum_question) , username (the user's username) , topic (title) , details (the thread) , date_time (the time on which the thread was posted) , view (amount of views) , replies (amount of replies). forum_answer : question_id (the forum_question id ) , a_id (the response id) , a_username (the user's who responded username) , a_datetime (the time that the reply was posted on). AND i want the user's post_count to increase per post. So the user's DB table that im using is only the post_count. I added some comments. <?php session_start(); /* * TOO MANY QUERIES IN THIS SCRIPTS, HOWEVER WHEN I TRY TO USE JOINS AND UNIONS THE CODE DO NOT WORK * EVERYTHING WORKS BESIDES THE DATE IN THE FORUM_ANSWER PART, HOWEVER LIKE I SAID BEFORE THIS CODE IS VERY SLOPPY * WHICH MAKES SENSE OF WHY IM NOT RECEIVING THE RESULTS THAT I WANT. I BELIEVE THAT I HAVE TO RE-CODE THIS PROGRAM * IN OOP, BUT I NEED A PLACE TO KEEP MY LOGS AND THIS IS ALL I HAVE SO FAR. SO THANKS FOR HELPING */ //GET THE FILE THAT CONNECT TO THE SERVER AND SET THE DEFAULT TIME ZONE FOR THE DATE include("../includes/config.php"); ?> <html> <head> <link href="../css/main.css" type="text/css" rel="stylesheet" /> </head> <div id='header'> <h1>Home Page</h1> </div> <div id='primary_nav'> <ul> <li><a href='../index.php'>Home Page</a></li> <li><a href='index.php'>Forums</a></li> <li><a href='../index.php?app=members'>Member's Page</a></li> <li><a href='../index.php?app=members&module=account'>Account</a></li> <li><a href='../index.php?app=pages&module=extra'>Extras</a></li> </ul> </div> <div id='content'> <div id='forum_container'> <?php //GET THE VARIABLE FROM THE URL TO DISPLAY THE CORRECT THREAD $id = isset($_GET['id']) ? $_GET['id']: ''; //QUERY THE DATA THE IS IN THE FORUM_QUESTION WHERE ID = $ID IN ORDER TO GET THE TOPIC THROUGH REPLY CORRECTLY. $result = mysql_query("SELECT * FROM forum_question WHERE id='$id'"); while($row = mysql_fetch_array($result)) { //VARIABLE FOR THE USER THAT MADE THE THREAD $p_username = isset($row['username']) ? $row['username'] : ''; //GET THE USER'S POST_COUNT $post_count = mysql_query("SELECT * FROM members WHERE username='$p_username'"); //GET THE CORRECT FORMAT FOR DATE (WORKS FINE) HOWEVER, I DO NOT THINK THAT I SHOULD BE RUNNING SO MANY WHILE LOOPS WITH QUERIES. $get_date = mysql_query("SELECT DATE_FORMAT(date_time,'%a %b %d, %Y %l:%i %p') AS date_time FROM forum_question WHERE id='$id' "); while($row_1 = mysql_fetch_array($post_count)) { while($row_3 = mysql_fetch_array($get_date)) { //USERS POST_COUNT $post_count_1 = $row_1['post_count']; //VARIABLES FROM THE TABLE (FORUM_QUESTION) $topic = isset($row['topic']) ? $row['topic'] : ''; $detail = isset($row['detail']) ? $row['detail'] : ''; $view = isset($row['view']) ? $row['view'] : ''; $reply = isset($row['replies']) ? $row['replies'] : ''; //DATE IN THE CORRECT FORMAT, HOWEVER I FEEL LIKE I DO NOT NEED A SEPERATE QUERY TO ACHIEVE THIS, BUT IT WORKS. $date = isset($row_3['date_time']) ? $row_3['date_time'] : ''; //THE NEXT SECTION ON CODE, BASICALLY DISPLAY'S ALL THE VARIABLES FROM ABOVE IN A 'THREAD' FORMAT, ALL WHICH WORKS. ?> <div class='view_post'> <div class='view_post_title'> <p><?php echo $topic?></p> </div> <div class='view_post_date'> <p>Published: <?php echo $date;?></p> </div> <div class='view_post_user'> <p> Username: <?php echo $p_username;?> <br /> Post Count: <?php echo $post_count_1;?> <br /> </p> </div> <div class='view_post_content'> <p><?php echo $detail; ?></p> </div> </div> <?php }}} //END ALL THE WHILES LOOPS. //GET THE DATE FROM FORUM_ANSWER (REPLIES) $result1 = mysql_query("SELECT * FROM forum_answer WHERE question_id = '$id'"); /*CHANGE THE FORMAT OF THE DATE. MAIN ISSUE (WORKS FOR ONLY THE FIRST REPLY THEN DISPLAY THAT TIME FOR THE REST OF THE REPLIES) * HOWEVER, WHEN I JUST USE THE DATE FROM $ROW THEN IT WORKS FINE, IT JUST DO NOT DISPLAY THE FORMAT CORRECTLY */ $u_datetime = mysql_query("SELECT DATE_FORMAT(a_datetime,'%a %b %d, %Y %l:%i %p') AS a_datetime FROM forum_answer WHERE question_id='$id'"); while($row_5 = mysql_fetch_array($u_datetime)) { while($row = mysql_fetch_array($result1)) { //VARIABLES FROM FORUM_ANSWER $a_id = isset($row['a_id']) ? $row['a_id'] : ''; $a_username = isset($row['a_username']) ? $row['a_username'] : ''; $a_answer = isset($row['a_answer']) ? $row['a_answer'] : ''; $a_datetime = isset($row_5['a_datetime']) ? $row_5['a_datetime'] : ''; //GET THE USER WHO REPLIED POST_COUNT $a_post_count = mysql_query("SELECT * FROM members WHERE username='$a_username'"); while($row_2 = mysql_fetch_array($a_post_count)) { $a_post_count_1 = $row_2['post_count']; ?> <div class='view_post'> <div class='view_post_date'> <p>Published: <?php echo $a_datetime;?></p> </div> <div class='view_post_user'> <p> Username: <?php echo $a_username;?> <br /> Post Count: <?php echo $a_post_count_1; ?> <br /> </p> </div> <div class='view_post_content'> <p><?php echo $a_answer;?></p> </div> </div> <?php }}} //END ALL THE WHILES LOOPS //UPDATE THE VIEWS, GO UP PER ONE. (WORKS) $result2 = mysql_query("SELECT * FROM forum_question WHERE id='$id'"); while($row = mysql_fetch_array($result2)) { $view = isset($row['view']) ? $row['view'] : ''; //If the views is empty, give it a number value! (1) if(empty($view)) { $view = 1; $result3 = mysql_query("UPDATE forum_question SET view='$view' WHERE id='$id'"); } else{ //Count more values! $add_view = $view + 1; $result4 = mysql_query("UPDATE forum_question SET view='$add_view' WHERE id='$id'");} } ?> <br /> <div class='view_post'> <div class='view_post_title'> <p>Post Reply...</p> </div> <div class='view_post_reply'> <p> <form action='add_reply.php' method='post'> <center> <textarea name='a_answer' id='a_answer' rows='3' cols='45' style='min-height: 200px; width: 90%;'></textarea> <input name='id' type='hidden' value='<?php echo $id;?>' /><br /> <input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"> </center> </form> </p> </div> </div> </div> <!-- END CONTAINER TAG!--> <div id='footer'> <p>Copyright Empora Tech 2012</p> </div> </div><!-- END THE CONTENT TAG! --> </body> </html> Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 11, 2012 Share Posted August 11, 2012 Ok... So the application that i am making is a forum board. To achieve this, i have 2 tables in my DB : forum_question and forum_answer. forum_question structure is: id (id of the forum_question) , username (the user's username) , topic (title) , details (the thread) , date_time (the time on which the thread was posted) , view (amount of views) , replies (amount of replies). forum_answer : question_id (the forum_question id ) , a_id (the response id) , a_username (the user's who responded username) , a_datetime (the time that the reply was posted on). AND i want the user's post_count to increase per post. So the user's DB table that im using is only the post_count. I added some comments. Does the members table have an id field? If so, you should be using that instead of the username. And what is "forum_replies" - is that the number of "forum_answer"'s associated with the forum post? And, I'm not understandin this . . . i want the user's post_count to increase per post. Are you trying to update a value in the user's table when they make a post? If so, you are doing this completely wrong. You should really go back and read some tutorials on databases. You can very easily get a users post count by JOINing the posts table on the user table and having the database do the calculation for you. Here is an example of a query to return each user along with their associated post count from a properly structured DB. The table/field names are not specific to your structure. SELECT users.name, COUNT(posts.user_id) AS post_count FROM users LEFT JOIN posts ON posts.user_id = users.id GROUP BY users.id Quote Link to comment Share on other sites More sharing options...
id Posted August 11, 2012 Author Share Posted August 11, 2012 Ok... So the application that i am making is a forum board. To achieve this, i have 2 tables in my DB : forum_question and forum_answer. forum_question structure is: id (id of the forum_question) , username (the user's username) , topic (title) , details (the thread) , date_time (the time on which the thread was posted) , view (amount of views) , replies (amount of replies). forum_answer : question_id (the forum_question id ) , a_id (the response id) , a_username (the user's who responded username) , a_datetime (the time that the reply was posted on). AND i want the user's post_count to increase per post. So the user's DB table that im using is only the post_count. I added some comments. Does the members table have an id field? If so, you should be using that instead of the username. And what is "forum_replies" - is that the number of "forum_answer"'s associated with the forum post? And, I'm not understandin this . . . i want the user's post_count to increase per post. Are you trying to update a value in the user's table when they make a post? If so, you are doing this completely wrong. You should really go back and read some tutorials on databases. You can very easily get a users post count by JOINing the posts table on the user table and having the database do the calculation for you. Here is an example of a query to return each user along with their associated post count from a properly structured DB. The table/field names are not specific to your structure. SELECT users.name, COUNT(posts.user_id) AS post_count FROM users LEFT JOIN posts ON posts.user_id = users.id GROUP BY users.id 1. Yes the member's table have an id field. And where do you see forum_replies? 2. Yes, basically when an user posts, i want their post_count to increase. I knew their was a simpler way in achieving this, i just did not focus too much on sql, but i know that i have too in order to properly code programs. 3. But can you pin point the issue going around the date? Or is my queries messing everything up? Quote Link to comment Share on other sites More sharing options...
Psycho Posted August 12, 2012 Share Posted August 12, 2012 1. Yes the member's table have an id field. And where do you see forum_replies? Then you should be using the User ID in the posts table to associate the post back to the user - not the username. As for your question, I mean "forum_replies". Your names are really obtuse in my opinion. 2. Yes, basically when an user posts, i want their post_count to increase. I knew their was a simpler way in achieving this, i just did not focus too much on sql, but i know that i have too in order to properly code programs. Well, what you are trying to accomplish is hard because you are not doing it correctly. 3. But can you pin point the issue going around the date? Or is my queries messing everything up? Your queries and structure are very disorganized. I see a lot of problems. I was going to try and give you some tips, but to be honest I would just rewrite that from scratch. Quote Link to comment Share on other sites More sharing options...
id Posted August 12, 2012 Author Share Posted August 12, 2012 Ok, thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted August 12, 2012 Share Posted August 12, 2012 3. But can you pin point the issue going around the date? Can you post the output from this please <?php $result = mysql_query("SELECT a_datetime, question_id, a_id FROM forum_answer WHERE DATE(a_datetime) = '2012-08-11' ORDER BY a_datetime"); echo '<pre>'; while($row = mysql_fetch_assoc($result)) { vprintf('%-30s%10d%10d<br />', $row); } echo '</pre>'; ?> Quote Link to comment Share on other sites More sharing options...
id Posted August 12, 2012 Author Share Posted August 12, 2012 2012-08-11 00:32:42 18 1 2012-08-11 00:35:25 18 2 2012-08-11 00:35:44 18 3 2012-08-11 00:37:01 18 4 2012-08-11 00:38:34 14 5 2012-08-11 00:39:45 14 6 2012-08-11 00:45:28 6 7 2012-08-11 01:07:29 20 8 2012-08-11 10:34:21 18 9 2012-08-11 10:35:47 13 10 2012-08-11 10:36:08 19 11 2012-08-11 10:36:49 18 12 2012-08-11 10:37:12 6 13 2012-08-11 10:57:27 20 14 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.