Help w Multiple Queries during Display

[doubledee]

Not sure where to begin describing ths one?!   

 

On my website I have Articles.  Beneath each Article, Users can leave one or more Comments.

 

Next to each User's Comment, I have the following in the left margin...

- Username

- User Online Status

- User Photo

- User Location

- User # of Posts

 

 

At the top of my file, I have my PHP code and I start off by running a Prepared Statement looking for the Article the User is requesting in the URL.

 

If the Article is found, I store the Results, and then run another Prepared Statement looking for Comments. 

 

If Comments are found, I store the Results.

 

Then down in the HTML section, I display the Article and beneath it I loop through the Article's Comments like this...

// ********************************
// Display Comments on Article.		*
// ********************************
while (mysqli_stmt_fetch($stmt2)){
	// Display User Info.	*

	// Display User Comments.	*
}

 

This seems to be working nicely, except that there is one last piece of User Info that is messing things up...

 

I can get all of the User and Comments data in this query...

// ************************
// Build Comments Query.	*
// ************************

// Build query.
$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label, 
			m.location, m.created_on,	m.logged_in, m.last_activity,
			c.created_on, c.body, c.status
		FROM member AS m
		INNER JOIN comment AS c
		ON m.id = c.member_id
		WHERE c.status="Approved" AND c.article_id=?
		ORDER BY c.created_on';

 

However, in order to get the User's "Number of Posts" I need another query.  And I'm not sure how to get the "Post Count" for each User and then mesh it back into what I currently have?!

 

Does that make sense?!

 

Also, it is conceivable that later I might add additional User Info that comes from yet another Query/Source.  (If I was using OOP and PDO and had a clear separation of "Presentation" from "Business Logic" I'm sure would be easier, but for now I need to work with the structure I have since any of those three things are way beyond the scope of this question and my current abilities!!!!)

 

Thanks,

 

 

Debbie

 

[xyph]

You may want to dump your structure with some sample data as well.. IE:

 

-- phpMyAdmin SQL Dump
-- version 3.4.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 11, 2012 at 01:32 PM
-- Server version: 5.5.20
-- PHP Version: 5.3.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `db`
--

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val1` int(11) NOT NULL,
  `val2` int(11) NOT NULL,
  `val3` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `val1`, `val2`, `val3`) VALUES
(1, 2, 4, 5),
(2, 1, 3, 9),
(3, 54, 2, 55),
(4, 34, 3, 4);

-- --------------------------------------------------------

--
-- Table structure for table `markers`
--

CREATE TABLE IF NOT EXISTS `markers` (
  `ApID` int(11) NOT NULL,
  `lat` decimal(20,18) NOT NULL,
  `lng` decimal(20,18) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `markers`
--

INSERT INTO `markers` (`ApID`, `lat`, `lng`) VALUES
(33236, '39.538699913335220000', '-0.120849609375000000');

-- --------------------------------------------------------

--
-- Table structure for table `table`
--

CREATE TABLE IF NOT EXISTS `table` (
  `date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `table`
--

INSERT INTO `table` (`date`) VALUES
('2012-02-15 11:00:00'),
('2012-02-15 08:00:00'),
('2012-02-16 04:00:00'),
('2012-02-17 05:00:00'),
('2012-02-17 11:00:00'),
('2012-02-18 14:00:00'),
('2012-02-19 19:00:00'),
('2012-02-21 16:00:00');

 

Please only dump related tables.

[doubledee]

You may want to dump your structure with some sample data as well.. IE:

 

I have no clue what you are trying to tell me?!

 

 

Debbie

 

[xyph]

You've asked us to solve a database issue you're having without providing us with your database structure, or sample data.

 

This makes your issue hard to solve.

 

We aren't standing behind you, you need to provide us with as much information as possible. We're not going to waste time trying to piece together a puzzle to try and get the whole picture.

[doubledee]

You've asked us to solve a database issue you're having without providing us with your database structure, or sample data.

 

I NEVER asked anything about my database, because there is nothing wrong with it...

 

My question is about how to piece together data from multiple sources/queries and merge it with my Comments using Prepared Statements and PHP.

 

What I have currently works just fine, but when I need to pull data from other queries (e.g. "# of Posts", "Interests", "Friends") then that messes up my current PHP code.

 

Right now I just run an Inner Join on the MEMBER and COMMENT table, take that dataset, loop through it, and display the User Details and corresponding User Comment for each record in my results-set.

 

But to get something like "# of Posts" I would likely need to create another query like this...

SELECT COUNT(member_id)
FROM comment
WHERE member_id=<member_id>;

 

So if "CowboyBob", member_id=34 has a total of 275 Posts, then how do I merge that piece of data together with...

 

Cowboy Bob

<online indicator>

<Bob's Photo>

El Paso, TX

I really like this article.  I was just thinking about the same thing the other day, and it is good to know that I am not alone!

 

Hopefully that makes more sense?!

 

 

Debbie

 

 

[xyph]

You 'piece' the data together by getting it all in one query. You can do it your way, but that involves queries in a loop.

 

You're right though. Manipulating data from a database isn't a database question. I suppose what you're trying to accomplish is beyond my understanding and abilities. Hopefully someone with the knowledge (and the patience) you desire.

[redsmurph]

To make it simple, just make a new query just checking for the amount of comments for a certain user, as I imagine/hope you've tagged each comment with the user ID (user table row rather than user name preferably), and all comments are in one table.

 

Not everything has to be done in a single SQL query.

[xyph]

Not everything has to be done in a single SQL query.

 

So you're suggesting she perform a query for each user that's commented? Or create a redundant query using some sort of slow modifier like IN()?

 

She's already querying the comments table, why should she query it again in a separate query?

 

Taking the easy/fast way out can be a good business decision. It doesn't really have a place in an academic setting though, unless the goal to teach bad practise.

[redsmurph]

Agreed, as she needs the comment count for all the users. If it had been only one user I would have gone for a separate call to start with so I get a working solution.

[doubledee]

Not everything has to be done in a single SQL query.

 

So you're suggesting she perform a query for each user that's commented? Or create a redundant query using some sort of slow modifier like IN()?

 

She's already querying the comments table, why should she query it again in a separate query?

 

Taking the easy/fast way out can be a good business decision. It doesn't really have a place in an academic setting though, unless the goal to teach bad practise.

 

Well, your questions are some of my questions as well.

 

The best answer would not only be to answer *my* question, but to look at likely future scenarios.

 

First, looking at my immediate issue and trying to address the comments above...

 

Here are snippets of my tables...

 

member

- id
- email
- username
- password
- first_name
- photo_name
- location

 

 

comment

- article_id
- member_id
- body
- status

 

 

article

- id
- slug
- heading
- body

 

 

Notes:

1.) This is a simple Many-to-Many relationship. 

2.) I think my tables are modeled just fine.  Things might be able to be broken out if I had 50 attributes for a Member, but for now, the way things are makes sense.

 

 

 

Without including "# of Posts" in the User Info section, I have been able to use this query to get everything working...

$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label, 
				m.location, m.created_on,	m.logged_in, m.last_activity,
				c.created_on, c.body, c.status
		FROM member AS m
		INNER JOIN comment AS c
		ON m.id = c.member_id
		WHERE c.status="Approved" AND c.article_id=?
		ORDER BY c.created_on';

 

That is towards the top of my file in the PHP section.

 

Then down below in the HTML section I display the User's Info and User's Comment like this...

// ********************************
// Display Comments on Article.		*
// ********************************
while (mysqli_stmt_fetch($stmt2)){
	// Set Photo Label.
	$photoLabel = (empty($photoLabel) ? $username : $photoLabel);

	echo '<div class="post">';

	// ********************
	// Display User Info.	*
	// ********************
	echo '	<div class="userInfo">
				<a href="#" class="username">
					<strong>' . nl2br(htmlentities($username, ENT_QUOTES)) . '</strong>
				</a>';

				AND SO ON AND SO FORTH...


	// ************************
	// Display User Comments.	*
	// ************************
	echo '	<div class="userComments">
				<p class="commentDate">Posted on: ' . date('Y-m-d g:ia', strtotime($createdOn)) . '</p>
				<p>' . nl2br(htmlentities($comments, ENT_QUOTES)) . '</p>
			</div>
		</div>';
	}
?>
</div><!-- End of COMMENTS SECTION -->

 

 

So, that is all fine and dandy, but in order to get "# of Posts" for a User, I would need another query (or have to create a complicated Sub-Query in my Query above).

 

But to take things to a possible future state...

 

What happens if I want even more disparate User data displayed which clearly cannot be gathered from my simple two table Inner Join above??

 

What if I want a listing of all of CowboyBob's Friends beneath his info?

 

What if I want a listing of all of CowboyBob's Likes/Dislikes beneath his info?

 

What if I want a listing of the "Last 5 Pages Visited" by CowboyBob beneath his info?

 

I find it hard to believe you could put all of that in one Query/Results-Set?!  (Of course I could be wrong!)

 

If I had to guess, though, I would think you would run whatever queries you need, put the data into variables, and then somehow piece it back together as you loop through each Comment?

 

(OFF TOPIC:  This problem must pretty common for people who do "Decision Support Systems (DSS)" or "Dashboards", although they usually don't have to tie all of that disparate data back to 37 Comments, and instead just 1 User.  But hopefully you follow what I am saying...)

 

Sincerely,

 

 

Debbie

 

 

 

[xyph]

Did you not READ my first post? I provided a VERY SPECIFIC example of how you should provide us with your database information. In the way you've presented your snippets, I have to manually create each table, and manually populate it with sample data to test out any solutions I want to give you. This is going to take me more time to set up than it will be to solve, thus, I'm probably not going to bother.

 

If you want to change information your query returns, you're going to have to change your query. If this information is not available in the tables you're already querying, you may have to make radical changes to your query.

 

You're coming to conclusions on subjects you know little about. Why? (I find it hard to believe you could put all of that in one Query/Results-Set?!) MySQL is used for things exponentially more complex than what you're trying to do. Your problem is considered very trivial from that perspective.

 

Please, stop throwing random information around. Many-to-many means nothing in this context. DDS, Dashboards, etc is irrelevant. Please try to avoid cluttering your posts with erroneous information. It only serves to confuse those trying to help you.

[doubledee]

Did you not READ my first post?

 

Yep.  And it was incoherent, to be honest.  What it had to do with my Original Post is beyond me.

 

 

This is going to take me more time to set up than it will be to solve, thus, I'm probably not going to bother.

 

Good idea.

 

 

You're coming to conclusions on subjects you know little about. Why? (I find it hard to believe you could put all of that in one Query/Results-Set?!) MySQL is used for things exponentially more complex than what you're trying to do. Your problem is considered very trivial from that perspective.

 

Please, stop throwing random information around. Many-to-many means nothing in this context. DDS, Dashboards, etc is irrelevant. Please try to avoid cluttering your posts with erroneous information. It only serves to confuse those trying to help you.

 

The agony I put you through?!  (Next time I'll just pass out bamboo shoots instead...)

 

 

Debbie

 

[scootstah]

Did you not READ my first post?

 

Yep.  And it was incoherent, to be honest.  What it had to do with my Original Post is beyond me.

 

You provide code when you ask a coding question, why would you not provide database information when you ask a database question? And yes, this is a database question. Database queries may be implemented in code but are independent from it.

 

Having no samples to actually test, I have come up with this. I don't know if it's what you want or not:

// Build query.
$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label, 
			m.location, m.created_on,	m.logged_in, m.last_activity,
			c.created_on, c.body, c.status,
			(SELECT COUNT(*) FROM comment WHERE member_id = m.id) AS post_count
		FROM member AS m
		INNER JOIN comment AS c
		ON m.id = c.member_id
		WHERE c.status="Approved" AND c.article_id=?
		ORDER BY c.created_on';

[xyph]

Don't even try to reason with her. If you say anything other than "Congratulations, your logic is perfect. Please, let me know exactly how you would like the solution provided for you;" she'll argue and fight it.

 

If I was a Mod this thread would've been trashed, like every other one you start, the second you started arguing against solutions that don't follow your though patterns. You throw around meaningless technobable while basic terminology and logic goes soaring over your head.

 

We're now 13 replies in, and you still haven't provided anyone with an importable database dump. Something that was asked for in Reply #1. It took you until Reply #9 to even hint at your database structure. I say hint because there's a hell of a lot more to a column than it's name. To add insult to ignorance, it took you way longer to type out your structure in code tags than it would have if you'd simply exported your tables in SQL. Every major script/client supports this feature.

 

Your inability to comprehend the help you've asked for is entertaining the heck out of me.

 

Good luck in future attempts to "mesh it back into what [you] currently have." Most many-to-many paradigms require such functionality, especially when implementing Web 2.0 technologies through the Cloud. You have to watch out though, this can drastically effect SEO! In the end, I'm glad you've decided to stick with the OOP implementation, as most social media outlets suggest that. When you find your solution, be sure to Tweet it with my hashtag.

[doubledee]

If I was a Mod this thread would've been trashed, like every other one you start, the second you started arguing against solutions that don't follow your though patterns. You throw around meaningless technobable while basic terminology and logic goes soaring over your head.

 

But then you'd have nothing to whine about...

 

Like a handful here, you thrive on talking down to people, whining, and going off on maniacal rants.

 

Glad I can feed the disease. 

 

 

Debbie

 

P.S.  Thanks for trying to help, scootstah.  You're truly a good guy.  Unfortunately this thread is dead.

[Philip]

Unfortunately this thread is dead.

 

Indeed it is, and I'm going to lock it to prevent further flare ups.