Solved

Multiple SQL Queries in PHP Code

Posted on 2012-03-27
17
343 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

762 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

20 Experts available now in Live!

Get 1:1 Help Now