Improve company productivity with a Business Account.Sign Up

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

Empty variable

I'm trying to figure out why $line['total1'] is empty when echo'd back?

$query = "select author_name, count(*) as total, 1 as type
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" . 
"and new_topic = 0
group by author_name
union
select starter_name, count(*) as total1, 2 as type
from `topics`
where from_unixtime(start_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(start_date) <" . "'" . $enddate . "'" . 
"group by starter_name
order by total desc";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

//Create the result set
while ($line= mysql_fetch_array($result))
	 {
	echo "Username: " .  $line['author_name'];
	echo "</br>";
	echo "Threads: " .  $line['total1'];
	echo "</br>";
	echo "Posts: " .  $line['total'];
	echo "</br>";
	echo "Overall Points: " .  (($line['total'] * $posts) + ($line2['total1'] * $topics));
	echo "</br>";
	echo "</br>";
	echo $line['total1'];
	}

Open in new window

0
Nathan Riley
Asked:
Nathan Riley
  • 5
  • 5
1 Solution
 
HainKurtSr. System AnalystCommented:
select 10 as total
union
select 20 as total1

>>>>

total
10
20

the name from first query will be used to name columns... there wont be a new column...
0
 
HainKurtSr. System AnalystCommented:
and use

if $line['type']=="1" echo "Threads: " . $line['total'];
if $line['type']=="2" echo "Posts: " . $line['total'];

instead of line 21-22-23
0
 
Nathan RileyFounderAuthor Commented:
I tried, but I get the same number for posts and topics.

Code Changed:
$query = "select author_name, count(*) as total, 1 as type
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" . 
"and new_topic = 0
group by author_name
union
select author_name, count(*) as total, 2 as type
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" . 
"and new_topic = 1
group by author_name
order by total desc";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

//Create the result set
while ($line= mysql_fetch_array($result))
	 {
	echo "Username: " .  $line['author_name'];
	echo "</br>";
	if ($line['type'] = "2")
	echo "Threads: " .  $line['total'];
	echo "</br>";
	if ($line['type'] = "1")
	echo "Posts: " .  $line['total'];
	echo "</br>";
	echo "Overall Points: " .  (($line['total'] * $posts) + ($line2['total1'] * $topics));
	echo "</br>";
	echo "</br>";
	echo $line['total1'];;
	}

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HainKurtSr. System AnalystCommented:
you will get a result like

author_name total type
aaaa 100 1
bbbb 125 1
ccccc 256 2
dddd 342 2

Open in new window


first 2 will be posts, last 2 will be threads...

and I guess you should use == not =

if ($line['type'] == "1") echo "Posts: " .  $line['total'];
if ($line['type'] == "2") echo "Threads: " .  $line['total'];
echo "</br>";
0
 
Nathan RileyFounderAuthor Commented:
Strange, if I change to:

if ($line['type'] == "1")
	echo "Threads: " .  $line['total'];
	echo "</br>";
	if ($line['type'] == "2")
	echo "Posts: " .  $line['total'];

Open in new window


It ends up giving the the post count for threads and nothing for posts.
0
 
HainKurtSr. System AnalystCommented:
what about this

$query = "select author_name, 
       sum(case when new_topic = 0 then 1 else 0 end) post_total,
       sum(case when new_topic = 1 then 1 else 0 end) thread_total
from `posts` 
where from_unixtime(post_date) >=" . "'" . $startdate . "'" .
"and from_unixtime(post_date) <" . "'" . $enddate . "'" . 
group by author_name
order by total desc";

with above query you will get

author_name post_total thread_total
HainKurt  234 512
SomeOne 102 253

Open in new window


one query returns all data...
0
 
Nathan RileyFounderAuthor Commented:
All that data looks good that it returns, how would I modify my "if" statements then?
0
 
Nathan RileyFounderAuthor Commented:
Ah it eliminates it, got it working, thanks for all the help!
0
 
HainKurtSr. System AnalystCommented:
I did not look at query closely at first, thought they are getting data from different tables :)
With new query, you will get all data in same row, so you dot need if in your code...
0
 
Nathan RileyFounderAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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