Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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?
0
Nathan Riley
Asked:
Nathan Riley
  • 9
  • 8
1 Solution
 
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!
0
 
Nathan RileyFounder/CTOAuthor Commented:
do I need to have another one of these loop deals?

while ($line= mysql_fetch_array($result))
0
 
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Nathan RileyFounder/CTOAuthor 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.
0
 
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.
0
 
Nathan RileyFounder/CTOAuthor 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))

?
0
 
Nathan RileyFounder/CTOAuthor 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

0
 
Nathan RileyFounder/CTOAuthor 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

0
 
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
0
 
Nathan RileyFounder/CTOAuthor 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?
0
 
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/
0
 
Nathan RileyFounder/CTOAuthor 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?
0
 
Ray PaseurCommented:
I don't think we can know without seeing the CREATE TABLE statements.
0
 
Nathan RileyFounder/CTOAuthor Commented:
Ah here are both.
posts.sql
topics.sql
0
 
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

0
 
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.
0
 
Nathan RileyFounder/CTOAuthor Commented:
Ended up changing the query all together, thanks for the help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now