pingeyeg
asked on
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)) {
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
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
ASKER
So technically I don't even need the GROUP BY statement.
When you say same location, are you meaning Alias them?
When you say same location, are you meaning Alias them?
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
ASKER
Unfortunately, that didn't make any difference.
What is the error you are receiving?
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Blimey!
ASKER
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
}
?>
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 ;)
ASKER
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