Multiple SQL Queries in PHP Code

I'm having troubles with some PHP code.  I want to be able to query 2 different result sets then use them in my PHP code.

This is the current code:
//Query the Needed Data
$query = "select author_name, count(*) as total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"group by author_name
order by total desc";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result))
	 {
	echo "Username: " .  $line['author_name'] . " ";
	echo "Points: " .  $line['total'];
	echo "</br>";
	}

Open in new window


I need to add another query in there, but not sure how it would work with the while loop? Something like this?

//Query the Needed Data
$query = "select author_name, count(*) as total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"group by author_name
order by total desc";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$query = "SELECT name, count(*) as total
FROM `topics`  t
inner join `members` m on t.starter_id = m.member_id
where FROM_UNIXTIME(start_date) >=" . "'" . $startdate . "'" .
"group by name
order by total desc";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result))
	 {
	echo "Username: " .  $line['author_name'] . " ";
	echo "Points: " .  $line['total'];
	echo "</br>";
	}

Open in new window


I'm guessing the 2nd query I need to change "$query" and "$result" to something else?
LVL 12
Nathan RileyFounderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
I need to change "$query" and "$result" to something else?
At least change $result since it will overwrite the resource location from the prior query!
Nathan RileyFounderAuthor Commented:
do I need to have another one of these loop deals?

while ($line= mysql_fetch_array($result))
Ray PaseurCommented:
I think you might be able to do this all in one query.  It does not look like any of the rows of the first query need to be used in the second query.  But there is ambiguity about the total column in the results set.  Do you want the total from the first query or the total from the second query?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Nathan RileyFounderAuthor Commented:
Well I need totals on both, that's why I split them in 2 queries.  I need to get the total posts made and the total topics made during that time span.
Ray PaseurCommented:
Let's try to organize this a little bit... Consider changing this:

$query = "select author_name, count(*) as total from `posts` ...
$query = "SELECT name, count(*) as total FROM `topics`...

If you wanted to do it this way you might remove the ambiguity

$query = "SELECT author_name, count(*) AS a_total, name, count(*) AS n_total FROM `posts`, `topics` ...

Now you can tell which is the author total and which is the name total.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nathan RileyFounderAuthor Commented:
That query doesn't get me the correct results.

I have this all setup:
//Query the Needed Data
$query = "select author_name, count(*) as total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" .
"group by author_name
order by total desc
limit 10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$query1 = "select starter_name, count(*) as total1
from `topics`
where from_unixtime(start_date) >" . "'" . $startdate . "'" .
"and from_unixtime(start_date) <" . "'" . $enddate . "'" .
"group by starter_name
order by total1 desc
limit 10";
$result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());

//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result))
	 {
	echo "Username: " .  $line['author_name'];
	echo "</br>";
	echo "Threads: " .  $line['total'];
	echo "</br>";
	echo "Posts: " .  $line['total'];
	echo "</br>";
	echo "Points: " .  $line['total'];
	echo "</br>";
	}

Open in new window

The problem is it only lets me use data I pull from the 1st $query

How do I include the $query1 and $result1 into the
//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result))

?
Nathan RileyFounderAuthor Commented:
I just tried this, but doesn't seem to work:

//Query the Needed Data
$query = "select author_name, count(*) as total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" .
"group by author_name
order by total desc
limit 10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$query1 = "select starter_name, count(*) as total1
from `topics`
where from_unixtime(start_date) >" . "'" . $startdate . "'" .
"and from_unixtime(start_date) <" . "'" . $enddate . "'" .
"group by starter_name
order by total1 desc
limit 10";
$result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());

//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result) && $line2= mysql_fetch_array($result1))
	 {
	echo "Username: " .  $line['author_name'];
	echo "</br>";
	echo "Threads: " .  $line['total'];
	echo "</br>";
	echo "Posts: " .  $line['total'];
	echo "</br>";
	echo "Points: " .  $line['total'];
	echo "</br>";
	}

Open in new window

Nathan RileyFounderAuthor Commented:
Got it:

//Query the Needed Data
$query = "select author_name, count(*) as total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" .
"group by author_name
order by total desc
limit 10";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

$query1 = "select starter_name, count(*) as total1
from `topics`
where from_unixtime(start_date) >" . "'" . $startdate . "'" .
"and from_unixtime(start_date) <" . "'" . $enddate . "'" .
"group by starter_name
order by total1 desc
limit 10";
$result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());

//Create the result set
$count = 0;
while ($line= mysql_fetch_array($result) and $line2= mysql_fetch_array($result1))
	 {
	echo "Username: " .  $line['author_name'];
	echo "</br>";
	echo "Threads: " .  $line2['total1'];
	echo "</br>";
	echo "Posts: " .  $line['total'];
	echo "</br>";
	echo "Points: " .  $line['total'];
	echo "</br>";
	}

Open in new window

Ray PaseurCommented:
It used to be author_name and name but now it's starter_name ?

Please post the CREATE TABLE statements for these two tables, thanks.

Also, "doesn't work" is not very illuminating.  You might try using var_dump() to print out the values in your variables so you do not have to guess what is going on.  Just a thought.
http://php.net/manual/en/function.var-dump.php
Nathan RileyFounderAuthor Commented:
Ah ok thanks.  I'm very new to PHP, so hard for me to explain problems.

What is the benefit of combining the two queries?  Just less code?
Ray PaseurCommented:
I think the benefit from combining the queries would come in less code and a simpler approach to organizing the data.  This search will turn up some interesting reading.
http://lmgtfy.com?q=Should+I+Normalize+my+Database

If you're looking to get a bit of a foundation in PHP and MySQL this book is great.  Easy to read with good examples and now in its fourth printing, it has been a permanent part of my professional library since Edition One.
http://www.sitepoint.com/books/phpmysql4/
Nathan RileyFounderAuthor Commented:
I'm looking I just can't get the 2 combined, I'm looking for 2 different counts based of different date fields.

I do see an issue though, the 2 queries are not joined on anything so my results in my while loop are funky.

How do I join the 2 query result sets together in the while loop based off author_name = starter_name?
Ray PaseurCommented:
I don't think we can know without seeing the CREATE TABLE statements.
Nathan RileyFounderAuthor Commented:
Ah here are both.
posts.sql
topics.sql
Ray PaseurCommented:
For the benefit of anyone looking at this, here are the statements in an easy-to-read format.
-- phpMyAdmin SQL Dump
-- version 3.4.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 27, 2012 at 12:57 PM
-- Server version: 5.0.95
-- PHP Version: 5.2.6

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `develope_forums`
--

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

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

CREATE TABLE IF NOT EXISTS `posts` (
  `pid` int(10) NOT NULL auto_increment,
  `append_edit` tinyint(1) default '0',
  `edit_time` int(10) default NULL,
  `author_id` mediumint(8) NOT NULL default '0',
  `author_name` varchar(255) default NULL,
  `use_sig` tinyint(1) NOT NULL default '0',
  `use_emo` tinyint(1) NOT NULL default '0',
  `ip_address` varchar(46) NOT NULL default '',
  `post_date` int(10) default NULL,
  `icon_id` smallint(3) default NULL,
  `post` mediumtext,
  `queued` tinyint(1) NOT NULL default '0',
  `topic_id` int(10) NOT NULL default '0',
  `post_title` varchar(255) default NULL,
  `new_topic` tinyint(1) default '0',
  `edit_name` varchar(255) default NULL,
  `post_key` varchar(32) NOT NULL default '0',
  `post_htmlstate` smallint(1) NOT NULL default '0',
  `post_edit_reason` varchar(255) NOT NULL default '',
  `post_bwoptions` int(10) unsigned NOT NULL default '0',
  `pdelete_time` int(11) NOT NULL default '0',
  `post_field_int` int(10) default '0',
  `post_field_t1` text,
  `post_field_t2` text,
  PRIMARY KEY  (`pid`),
  KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
  KEY `author_id` (`author_id`,`post_date`,`queued`),
  KEY `post_date` (`post_date`),
  KEY `ip_address` (`ip_address`),
  KEY `post_key` (`post_key`),
  KEY `queued` (`queued`,`pdelete_time`),
  FULLTEXT KEY `post` (`post`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10579 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open in new window

-- phpMyAdmin SQL Dump
-- version 3.4.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 27, 2012 at 12:57 PM
-- Server version: 5.0.95
-- PHP Version: 5.2.6

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `develope_forums`
--

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

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

CREATE TABLE IF NOT EXISTS `topics` (
  `tid` int(10) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL default '',
  `state` varchar(8) default NULL,
  `posts` int(10) default NULL,
  `starter_id` mediumint(8) NOT NULL default '0',
  `start_date` int(10) default NULL,
  `last_poster_id` mediumint(8) NOT NULL default '0',
  `last_post` int(10) default NULL,
  `icon_id` tinyint(2) default NULL,
  `starter_name` varchar(255) default NULL,
  `last_poster_name` varchar(255) default NULL,
  `poll_state` varchar(8) default NULL,
  `last_vote` int(10) default NULL,
  `views` int(10) default NULL,
  `forum_id` smallint(5) NOT NULL default '0',
  `approved` tinyint(1) NOT NULL default '0',
  `author_mode` tinyint(1) default NULL,
  `pinned` tinyint(1) default NULL,
  `moved_to` varchar(64) default NULL,
  `topic_hasattach` smallint(5) NOT NULL default '0',
  `topic_firstpost` int(10) NOT NULL default '0',
  `topic_queuedposts` int(10) NOT NULL default '0',
  `topic_open_time` int(10) NOT NULL default '0',
  `topic_close_time` int(10) NOT NULL default '0',
  `topic_rating_total` smallint(5) unsigned NOT NULL default '0',
  `topic_rating_hits` smallint(5) unsigned NOT NULL default '0',
  `title_seo` varchar(250) NOT NULL default '',
  `seo_last_name` varchar(255) NOT NULL default '',
  `seo_first_name` varchar(255) NOT NULL default '',
  `topic_deleted_posts` int(10) NOT NULL default '0',
  `tdelete_time` int(11) NOT NULL default '0',
  `moved_on` int(11) NOT NULL default '0',
  `last_real_post` int(10) NOT NULL default '0',
  `topic_archive_status` int(1) NOT NULL default '0',
  PRIMARY KEY  (`tid`),
  KEY `topic_firstpost` (`topic_firstpost`),
  KEY `last_post` (`forum_id`,`pinned`,`last_post`,`state`),
  KEY `forum_id` (`forum_id`,`pinned`,`approved`),
  KEY `starter_id` (`starter_id`,`forum_id`,`approved`,`start_date`),
  KEY `last_post_sorting` (`last_post`,`forum_id`),
  KEY `start_date` (`start_date`),
  KEY `last_x_topics` (`forum_id`,`approved`,`start_date`),
  KEY `approved` (`approved`,`tdelete_time`),
  KEY `moved_redirects` (`moved_on`,`moved_to`,`pinned`),
  KEY `topic_archive_status` (`topic_archive_status`,`forum_id`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2768 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open in new window

Ray PaseurCommented:
One more comment then I have to leave for the PHP User Group meeting and I can look at this again in the morning.
How do I join the 2 query result sets together in the while loop based off author_name = starter_name?
Generally speaking you do not join query results sets in a while() loop.  You join tables in a query.  Buy that SitePoint book.  You will soon become familiar with the many terms of art that are used in PHP/MySQL programming.
Nathan RileyFounderAuthor Commented:
Ended up changing the query all together, thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.