?
Solved

Multiple table output

Posted on 2009-12-27
20
Medium Priority
?
314 Views
Last Modified: 2012-05-08
I am trying to display a statement of information per table pending when that table gets updated.  RIght now I think I have the two tables Unionized correctly, but the output is not what I'm expecting.  Below is my code and the output.

The output I'm getting right now is:

pingeyeg answered the Question "off"
[BMW BMW X-Series] [Recategorize] [Edit]
<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
Comment
Question by:pingeyeg
  • 14
  • 5
20 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26128602
can you clarify your need...
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128625
I'll try.  I have a page that displays your latest activity on the site.  There are two tables in the db, which houses questions and how to's.  I am trying to display the latest questions and how to's based on date from those tables.  Does that clarify better?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26128978
Any thoughts?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26129374
Kindly let me know the relationship between the table how_to and answers or user_question to fix it out..
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26131082
The relationship would be based on the username.

answers.user = $_SESSION['username']
how_to.user = $_SESSION['username']

The username is turned into a session variable once the user logs in.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26131112
Try this one:

SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id, user_question.make, user_question.model, 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 answers
JOIN how_to ON answers.user = how_to.user
LEFT JOIN user_question ON answers.qID = user_question.id
WHERE answers.user = '" . $_SESSION['username']
AND answers.date = ( SELECT MAX(date) FROM answers)
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26131192
Now I'm not having anything returned and zero error message.  I know there is one answered question in the database of mine so it should be outputting something.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26131214
Also there is nothing in the logs telling me anything is wrong.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26131324
I reverted back to my original query that I posted here and realized something.  The fields do not match up to their actual values:

user
pingeyeg      

answer
This is a test to check on the multi-input how to'...      

qID
31      

question
off      

date
2009-10-05 14:20:44      

id
I am testing to see how things are working.      

make
BMW      

model
BMW X-Series

The id and question are not matching up.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26131352
This is how my query reads right now:

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 = 'pingeyeg' 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 = 'pingeyeg' ORDER BY date

Also, I need to have the output change depending on which table is being output.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26132416
>> I know there is one answered question in the database of mine so it should be outputting something.

Does it satisfies the condition below:

answers.date = ( SELECT MAX(date) FROM answers)

>> Also, I need to have the output change depending on which table is being output.

Kindly explain this in more detail to help you out..
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26132456
Can the original query not work?

My intention is to have a box of information based on a div output that will explain what the latest action has taken place based on you the user's actions on the site.  Below is an example:

pingeyeg has answered the Question "Blah blah blah".
Answered: 12/28/09

pingeyeg has posted the How To "Blah blah blah".
Posted: 12/28/09

I want the latest post to be listed in order of date based on which table was just updated.  I'm not trying to have it updated in real-time, rather when you refresh the page.  I know this is possible, but just can't figure it out.  I know I will need a conditional statement to change, which output to displayed to the screen, but not sure what the condition should be based on.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26132459
Also, can you explain the query:

answers.date = ( SELECT MAX(date) FROM answers)

Does that mean grab the latest date or today's date?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26132763
Well, I lied about the connection with the how_to table against any other table.  There isn't a connection.  It's basically it's own table, but I still need to have any updates added to the page from that table as well.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26134919
>> Can the original query not work?

Yes.. We can fix it out too..
Kindly post the result set of that query along with expected result set so that I can help you in fixing that query..

>> Does that mean grab the latest date or today's date?

Yes.. It should..

>>  It's basically it's own table, but I still need to have any updates added to the page from that table as well.

Kindly let me know how to fetch records in this scenario..
Is it possible to add a Foreign key referencing to your how_to table to make it work..
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26139473
I've made some great headway here.  Based on what I have below, can you explain to me what I should base a conditional statement on to change the wording in the div block between "answered the Question" and "posted the How To".  Not sure how to make that happen through my query.
<?php
                		$user = $_SESSION['username'];
                		$grabContribs = "SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id AS uID, user_question.make, user_question.model
							FROM answers
							LEFT JOIN user_question ON answers.qID = user_question.id
							WHERE answers.user = '$user'
							UNION
							SELECT how_to.user, how_to.howto, how_to.id AS hID, how_to.question, how_to.date, NULL, how_to.make, how_to.model
							FROM how_to
							WHERE how_to.user = '$user'
							ORDER BY date DESC";
                        $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['uID'] ?>"><?= $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 1

Author Comment

by:pingeyeg
ID: 26139861
Any thoughts?
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26145427
Does anyone have any thoughts on this or am I on my own now?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26152618
Have been bit busy and hence the delay..
And I haven't neglected it and hence objecting Delete request..

Ok..Going by your approach / query..
Try this modified one.

SELECT answers.user, answers.answer, answers.qID, user_question.question, answers.date, user_question.id AS uID, user_question.make, user_question.model
FROM answers
LEFT JOIN user_question ON answers.qID = user_question.id
WHERE answers.user = '$user'
AND answers.date = ( Select Max(date) from answers)
UNION ALL
SELECT how_to.user, how_to.howto, how_to.id AS hID, how_to.question, how_to.date, NULL, how_to.make, how_to.model
FROM how_to
WHERE how_to.user = '$user'
AND how_to.date = ( SELECT Max(date) from how_to)
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26155671
Since I wasn't sure if I was going to get a response on this thread, I asked a separate question on a separate thread in which I was able to get the correct answer.  I will award you the points for trying.

Thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
The viewer will learn how to count occurrences of each item in an array.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 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