Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple SQL Queries in PHP Code

Posted on 2012-03-27
17
Medium Priority
?
386 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:Nathan Riley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 111

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 12

Author Comment

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

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

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 12

Author Comment

by:Nathan Riley
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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 12

Author Comment

by:Nathan Riley
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 12

Author Comment

by:Nathan Riley
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 12

Author Comment

by:Nathan Riley
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
 
LVL 111

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 12

Author Comment

by:Nathan Riley
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 111

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 12

Author Comment

by:Nathan Riley
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 111

Expert Comment

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

Author Comment

by:Nathan Riley
ID: 37772838
Ah here are both.
posts.sql
topics.sql
0
 
LVL 111

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 111

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 12

Author Closing Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
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.

715 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