?
Solved

Group statement

Posted on 2009-12-27
12
Medium Priority
?
204 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:pingeyeg
  • 5
  • 5
  • 2
12 Comments
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128430
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
 
LVL 9

Expert Comment

by:pmessana
ID: 26128436
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
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128439
So technically I don't even need the GROUP BY statement.

When you say same location, are you meaning Alias them?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 9

Expert Comment

by:pmessana
ID: 26128443
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
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128453
Unfortunately, that didn't make any difference.
0
 
LVL 9

Expert Comment

by:pmessana
ID: 26128455
What is the error you are receiving?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128458
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
 
LVL 71

Accepted Solution

by:
Qlemo earned 1000 total points
ID: 26128466
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
 
LVL 9

Assisted Solution

by:pmessana
pmessana earned 1000 total points
ID: 26128480
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 26128515
Blimey!
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128522
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
 
LVL 9

Expert Comment

by:pmessana
ID: 26128548
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question