Solved

Multiple SQL Queries in PHP Code

Posted on 2012-03-27
17
351 Views
Last Modified: 2012-03-27
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
Comment
Question by:N R
  • 9
  • 8
17 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37771732
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
 
LVL 11

Author Comment

by:N R
ID: 37771744
do I need to have another one of these loop deals?

while ($line= mysql_fetch_array($result))
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37771760
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
 
LVL 11

Author Comment

by:N R
ID: 37771780
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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 37771933
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
 
LVL 11

Author Comment

by:N R
ID: 37772196
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
 
LVL 11

Author Comment

by:N R
ID: 37772216
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
 
LVL 11

Author Comment

by:N R
ID: 37772250
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37772271
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
 
LVL 11

Author Comment

by:N R
ID: 37772302
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37772410
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
 
LVL 11

Author Comment

by:N R
ID: 37772705
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37772776
I don't think we can know without seeing the CREATE TABLE statements.
0
 
LVL 11

Author Comment

by:N R
ID: 37772838
Ah here are both.
posts.sql
topics.sql
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37773899
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 37774079
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
 
LVL 11

Author Closing Comment

by:N R
ID: 37774089
Ended up changing the query all together, thanks for the help.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now