Group statement

I am trying to query a UNION statement, but am having issues finishing.  I'd like to hav the latest information be displayed from both tables in order of their date.  Any help is greatly appreciated.
$grabContribs = "SELECT answers.user, answers.answer, answers.qID, answers.date, user_question.question, user_question.id, user_question.make, user_question.model
                        	FROM answers
                         	LEFT JOIN user_question ON answers.qID = user_question.id
                         	WHERE answers.user = '" . $_SESSION['username'] . "'
                         	GROUP BY date
                         	UNION
                         	SELECT how_to.user, how_to.howto, how_to.id, how_to.NULL, how_to.date, how_to.question, how_to.viewable, how_to.make, how_to.model
                         	WHERE how_to.user = '" . $_SESSION['username'] . "'
                         	GROUP BY date";
                        $contribsGrabbed = mysql_query($grabContribs) or die("Grab did not work because: " . mysql_error());
                        	while($c = mysql_fetch_array($contribsGrabbed)) {

Open in new window

LVL 1
pingeyegAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Aren't you missing a FROM in the second SELECT? And how_to.NULL will not work, it is just NULL.

SELECT answers.user, answers.answer, answers.qID, user_question.question,
       answers.date, user_question.id, user_question.make, user_question.model
  FROM answers LEFT JOIN user_question ON answers.qID = user_question.id
 WHERE answers.user = '" . $_SESSION['username'] . "'
UNION
SELECT how_to.user, how_to.howto, how_to.id, NULL,
       how_to.date, how_to.question, how_to.viewable, how_to.make, how_to.model
  FROM how_to LEFT JOIN user_question ON how_to.ID = user_question.id
 WHERE how_to.user = '" . $_SESSION['username'] . "'
ORDER BY 5

Open in new window

0
 
pingeyegAuthor Commented:
By the way, the friendly error message I'm getting is:

Grab did not work because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE how_to.user = 'pingeyeg' GROUP BY date' at line 8
0
 
pmessanaCommented:
You are missing the "ORDER BY" statement.  Group simply groups results it does not order them.  You will want to use ORDER BY date DESC or ORDER BY date ASC.

You will only need this at the end of the entire statement and not where you have the GROUP BY statement.  Also, typically in a UNION statement you have the same in each statement, since your's are different you will need to position the data in the same location and then reference the location.

ORDER BY 2 (if it is in position 2).

Here is more information: http://www.techonthenet.com/sql/union.php

Peter
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
pingeyegAuthor Commented:
So technically I don't even need the GROUP BY statement.

When you say same location, are you meaning Alias them?
0
 
pmessanaCommented:
Correct, GROUP BY is to group data together, typically when doing a SUM or a COUNT in the select.  You don't appear to be doing any sort of function that requires grouping.  I have edited it below, see it that works for you.
SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id, user_question.make, user_question.model
                                FROM answers
                                LEFT JOIN user_question ON answers.qID = user_question.id
                                WHERE answers.user = '" . $_SESSION['username'] . "'
                                UNION
                                SELECT how_to.user, how_to.howto, how_to.id, how_to.NULL, how_to.date, how_to.question, how_to.viewable, how_to.make, how_to.model
                                WHERE how_to.user = '" . $_SESSION['username'] . "'
                                OPRDER BY 5

Open in new window

0
 
pingeyegAuthor Commented:
Unfortunately, that didn't make any difference.
0
 
pmessanaCommented:
What is the error you are receiving?
0
 
pingeyegAuthor Commented:
Grab did not work because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE how_to.user = 'pingeyeg' OPRDER BY 5' at line 7
0
 
pmessanaConnect With a Mentor Commented:
Olemo has two valid things I didn't notice, the last issue besides these two is that you have uneven column counts, the queries must be the same number of columns.  Just remove that NULL out completely so you have 8 columns in each.  They must match the same number and the date must be in the same position.
SELECT answers.user, answers.answer, answers.qID, answers.date,
       user_question.question, user_question.id, user_question.make, user_question.model
  FROM answers LEFT JOIN user_question ON answers.qID = user_question.id
 WHERE answers.user = '" . $_SESSION['username'] . "'
UNION
SELECT how_to.user, how_to.howto, how_to.id,  how_to.date, how_to.question, 
       how_to.viewable, how_to.make, how_to.model
  FROM how_to LEFT JOIN user_question ON how_to.ID = user_question.id
 WHERE how_to.user = '" . $_SESSION['username'] . "'
ORDER BY 4

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Blimey!
0
 
pingeyegAuthor Commented:
I have now run into another issue here.  How would you make this possible to make it where if you answered a question or if you answered a how to, that block would be displayed on the screen?
<h3 class="title">History of my contributions</h3>
                	<?php
                		$grabContribs = "SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id, user_question.make, user_question.model
                                FROM answers
                                LEFT JOIN user_question ON answers.qID = user_question.id
                                WHERE answers.user = '" . $_SESSION['username'] . "'
                                UNION
                                SELECT how_to.user, how_to.howto, how_to.id, how_to.viewable, how_to.date, how_to.question, how_to.make, how_to.model
                                FROM how_to
                                WHERE how_to.user = '" . $_SESSION['username'] . "'
                                ORDER BY 5";
                        $contribsGrabbed = mysql_query($grabContribs) or die("Grab did not work because: " . mysql_error());
                        	while($c = mysql_fetch_array($contribsGrabbed)) {
                    ?>
                    <div class="repository">
                    	<span style="color: #076bdc"><?= $c['user'] ?></span> answered the Question "<span class="question-link"><a href="/view-question.php?qID=<?= $c['id'] ?>"><?= $c['question'] ?></a></span>"<br/>[<span class="question-control"><a href="/category-questions.php?make=<?= $c['make'] ?>&model=<?= $c['model'] ?>"><?= $c['make']; ?> <?= $c['model'] ?></a></span>] [<span class="question-control"><a href="/recategorize.php?qID=<?= $c['id'] ?>">Recategorize</a></span>] <?= (!empty($c['answer'])) ? ("[<span class='question-control'><a href='/edit-question.php?qID={$q['id']}'>Edit</a></span>]") : ("") ?><?= (!empty($c['updated'])) ? ("<div class='posting-left'>Answered: " . date('F, j Y', strtotime($c['updated'])) . " " . date('g:i a', strtotime($c['updated'])) . "</div>") : ("") ?><div class="spacer"></div>
                    </div>
                    <?php
                    		}
                    ?>

Open in new window

0
 
pmessanaCommented:
pingeyeg - I would suggest marking this as closed and then opening a new question on how to display, it would be the easiest way to get it answered, often times experts will answer one part but to tag on more and more is a bit much and I am exhausted already ;)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.