Need some more MySQL magic.

KuJoe · Jul 11, 2014

I'm trying to build a report in WHMCS that will generate a list of products sold this month and group them by server. The code I have now doesn't work, nor do I know why it doesn't work but I'm sure it's because I suck at MySQL. :lol:

Any assistance or guidance is appreciated.

Code:

$sresult = select_query("tblservers","",array("disabled"=>'0'),"id","ASC");
while ($sdata = mysql_fetch_array($sresult)) {
	$server = $sdata['name'];
	$reportdata["tablevalues"][] = array("**<strong>$server</strong>");
	$presult = full_query("SELECT COUNT(*) as 'cnt' FROM(SELECT DISTINCT packageid FROM tblhosting WHERE server = ".$sdata['id']." AND YEAR(regdate) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) AND domainstatus = 'Active')");
	while($pdata = mysql_fetch_array($presult)) {
		$nresult = select_query("tblproducts","",array("id"=>$pdata['packageid']),"id","ASC");
		$ndata = mysql_fetch_array($nresult);
		$package = $ndata['name'];
		$pnum = $pdata['cnt'];
		$reportdata["tablevalues"][] = array($package,$pnum);
	}
}

fizzyjoe908 · Jul 11, 2014

At first glance, you need to use an associative array. Use mysql_fetch_assoc() instead of mysql_fetch_array().

KuJoe · Jul 11, 2014

Yay! I figured it out. I had some mistakes in the original MySQL queries so when I ran them in the CLI the errors it outputted were helpful and brought me to the answer. I don't know why I didn't try that before opening this thread but here's the working code (feel free to suggest something better):

$sresult = select_query("tblservers","",array("disabled"=>'0'),"id","ASC");
while ($sdata = mysql_fetch_array($sresult)) {
$server = $sdata['name'];
$reportdata["tablevalues"][] = array("**<strong>$server</strong>");
$presult = full_query("SELECT packageid, COUNT(*) as 'cnt' FROM(SELECT packageid FROM tblhosting WHERE server = ".$sdata['id']." AND YEAR(regdate) = YEAR(CURDATE()) AND MONTH(regdate) = MONTH(CURDATE()) AND domainstatus = 'Active') AS package");
while($pdata = mysql_fetch_array($presult)) {
$nresult = select_query("tblproducts","",array("id"=>$pdata['packageid']),"id","ASC");
$ndata = mysql_fetch_array($nresult);
$package = $ndata['name'];
$pnum = $pdata['cnt'];
$reportdata["tablevalues"][] = array($package,$pnum);
}
}

fizzyjoe908 said:
At first glance, you need to use an associative array. Use mysql_fetch_assoc() instead of mysql_fetch_array().

It looks like it works with either.

KuJoe · Jul 11, 2014

And I spoke too soon. Looks like it only returns a single product for each server, I'll probably need another while loop in there somewhere.

KuJoe · Jul 11, 2014

Ok, 100% working now. Still welcoming any suggestions on how to improve on this as it seems like I'm taking the hard way to get my results:

Code:

$sresult = select_query("tblservers","",array("disabled"=>'0'),"id","ASC");
while ($sdata = mysql_fetch_array($sresult)) {
	$server = $sdata['name'];
	$reportdata["tablevalues"][] = array("**<strong>$server</strong>");
	$nresult = select_query("tblproducts","","","id","ASC");
	while($ndata = mysql_fetch_array($nresult)) {
		$presult = full_query("SELECT COUNT(*) as 'cnt' FROM(SELECT * FROM tblhosting WHERE server = ".$sdata['id']." AND packageid = ".$ndata['id']." AND YEAR(regdate) = YEAR(CURDATE()) AND MONTH(regdate) = MONTH(CURDATE()) AND domainstatus = 'Active') AS package");
		$pdata = mysql_fetch_array($presult);
		if ($pdata['cnt'] > '0') {
			$package = $ndata['name'];
			$pnum = $pdata['cnt'];
			$reportdata["tablevalues"][] = array($package,$pnum);
		}
	}
}

perennate · Jul 12, 2014

Like, why not just

SELECT tblservers.name, tblproducts.name, COUNT(*) FROM tblservers, tblhosting, tblproducts WHERE tblhosting.server = tblservers.id AND tblhosting.packageid = tblproducts.id AND YEAR(tblhosting.regdate) = YEAR(CURDATE()) AND MONTH(tblhosting.regdate) = MONTH(CURDATE()) AND tblhosting.domainstatus = 'Active' AND tblservers.disabled = 0 GROUP BY tblservers.id, tblproducts.id;
Edit: mysql_fetch_array returns array that has both string keys (from column names) as well as integer keys (from column indices).

KuJoe · Jul 12, 2014

perennate said:
Like, why not just

SELECT tblservers.name, tblproducts.name, COUNT(*) FROM tblservers, tblhosting, tblproducts WHERE tblhosting.server = tblservers.id AND tblhosting.packageid = tblproducts.id AND YEAR(tblhosting.regdate) = YEAR(CURDATE()) AND MONTH(tblhosting.regdate) = MONTH(CURDATE()) AND tblhosting.domainstatus = 'Active' AND tblservers.disabled = 0 GROUP BY tblservers.id, tblproducts.id;
Edit: mysql_fetch_array returns array that has both string keys (from column names) as well as integer keys (from column indices).

I tried that code in the CLI and all it returned was the server names and 1 of the product names.

mikho · Jul 12, 2014

I read this on my phone so I can have misread the mysql syntax.

When having a COUNT(*) it aggregates (is this the correct word?) all the results.

Remove it and you should get all the results that you want, except the number of records.

dave · Jul 12, 2014

KuJoe said:
I tried that code in the CLI and all it returned was the server names and 1 of the product names.

From what I can see, it looks like perennate's query should work, though it will only return servers and products that had orders. If you want to return all of the servers and products regardless of whether they had orders, you'd need to use left outer joins.

If you want to list the table schema for the tables involved, I can take a closer look, though.

KuJoe · Jul 12, 2014

dave said:
From what I can see, it looks like perennate's query should work, though it will only return servers and products that had orders. If you want to return all of the servers and products regardless of whether they had orders, you'd need to use left outer joins.

If you want to list the table schema for the tables involved, I can take a closer look, though.

It doesn't show all of the orders though. All it shows me is this:

name COUNT(*)
tsetco 128MB
tsetfl 128MB

Unfortunately, my development database has 3 orders for the same product (packageid) so that output doesn't show me that.

It's possible I don't understand what he was trying to say but I interpretted it as "replace all your queries with just this one".

I decided to play around with @perenatte's query again and kind of got it working (closer than before but not 100% like my extremely long code):

SELECT tblproducts.name, COUNT(*), tblservers.name FROM tblservers, tblhosting, tblproducts WHERE tblhosting.server = tblservers.id AND tblhosting.packageid = tblproducts.id AND YEAR(tblhosting.regdate) = YEAR(CURDATE()) AND MONTH(tblhosting.regdate) = MONTH(CURDATE()) AND tblhosting.domainstatus = 'Active' AND tblservers.disabled = 0 GROUP BY tblservers.id, tblproducts.id;

If I could get it to show the server name it would be closer to 100%.

Flapadar · Jul 12, 2014

Wouldn't something like this do?

Untested.

Code:

SELECT P.NAME , COUNT(*) , S.name FROM tblproducts P
INNER JOIN tblhosting H ON H.packageid = P.id
INNER JOIN tblserver S ON H.server = S.id
GROUP BY S.id , P.id

Didn't bother throwing in all the checks/etc though - you'll need to re-add those if this is on the right lines.

dave · Jul 12, 2014

From what I can tell, the queries from perennate, Flapadar, and the last one listed by KuJoe are all basically the same.

perennate:

SELECT tblservers.name, tblproducts.name, COUNT(*)
FROM tblservers, tblhosting, tblproducts
WHERE tblhosting.server = tblservers.id
AND tblhosting.packageid = tblproducts.id
AND YEAR(tblhosting.regdate) = YEAR(CURDATE())
AND MONTH(tblhosting.regdate) = MONTH(CURDATE())
AND tblhosting.domainstatus = 'Active'
AND tblservers.disabled = 0
GROUP BY tblservers.id, tblproducts.id;

KuJoe:

SELECT tblproducts.name, COUNT(*), tblservers.name
FROM tblservers, tblhosting, tblproducts
WHERE tblhosting.server = tblservers.id
AND tblhosting.packageid = tblproducts.id
AND YEAR(tblhosting.regdate) = YEAR(CURDATE())
AND MONTH(tblhosting.regdate) = MONTH(CURDATE())
AND tblhosting.domainstatus = 'Active'
AND tblservers.disabled = 0
GROUP BY tblservers.id, tblproducts.id;

Flapadar (with WHERE clause added and a few naming corrections):

SELECT P.NAME , COUNT(*) , S.name
FROM tblproducts P
INNER JOIN tblhosting H ON H.packageid = P.id
INNER JOIN tblservers S ON H.server = S.id
WHERE YEAR(H.regdate) = YEAR(CURDATE())
AND MONTH(H.regdate) = MONTH(CURDATE())
AND H.domainstatus = 'Active'
AND S.disabled = 0
GROUP BY S.id , P.id;

I created these test tables and sample data from what I could gather from your post.

create table tblservers (id int, name varchar(255), disabled int);
insert into tblservers (id, name, disabled) values (1, 'server01', 0);
insert into tblservers (id, name, disabled) values (2, 'server02', 0);
insert into tblservers (id, name, disabled) values (3, 'server03', 1);
insert into tblservers (id, name, disabled) values (4, 'server04', 0);

create table tblproducts (id int, name varchar(255));
insert into tblproducts (id, name) values (1, 'ovz01');
insert into tblproducts (id, name) values (2, 'ovz02');

create table tblhosting (server int, packageid int, regdate date, domainstatus varchar(32));
insert into tblhosting (server, packageid, regdate, domainstatus) values (1, 1, '2014-07-01', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (1, 1, '2014-07-02', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 1, '2014-07-03', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 1, '2014-07-04', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 1, '2014-07-04', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 1, '2014-07-04', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 2, '2014-07-04', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (2, 2, '2014-07-04', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (3, 1, '2014-07-05', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (3, 1, '2014-07-06', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (3, 2, '2014-07-07', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (4, 1, '2014-07-08', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (4, 1, '2014-07-09', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (4, 2, '2014-07-10', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (4, 2, '2014-07-11', 'Active');
insert into tblhosting (server, packageid, regdate, domainstatus) values (4, 2, '2014-07-12', 'Inactive');

Code:

mysql> desc tblservers;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| name     | varchar(255) | YES  |     | NULL    |       |
| disabled | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc tblproducts;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc tblhosting;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| server       | int(11)     | YES  |     | NULL    |       |
| packageid    | int(11)     | YES  |     | NULL    |       |
| regdate      | date        | YES  |     | NULL    |       |
| domainstatus | varchar(32) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

When I run the 3 queries, I get essentially the same results:

perennate:

+----------+-------+----------+
| name | name | COUNT(*) |
+----------+-------+----------+
| server01 | ovz01 | 2 |
| server02 | ovz01 | 4 |
| server02 | ovz02 | 2 |
| server04 | ovz01 | 2 |
| server04 | ovz02 | 2 |
+----------+-------+----------+
5 rows in set (0.01 sec)

KuJoe:

+-------+----------+----------+
| name | COUNT(*) | name |
+-------+----------+----------+
| ovz01 | 2 | server01 |
| ovz01 | 4 | server02 |
| ovz02 | 2 | server02 |
| ovz01 | 2 | server04 |
| ovz02 | 2 | server04 |
+-------+----------+----------+
5 rows in set (0.00 sec)

Flapadar:

+-------+----------+----------+
| NAME | COUNT(*) | name |
+-------+----------+----------+
| ovz01 | 2 | server01 |
| ovz01 | 4 | server02 |
| ovz02 | 2 | server02 |
| ovz01 | 2 | server04 |
| ovz02 | 2 | server04 |
+-------+----------+----------+
5 rows in set (0.00 sec)

It's possible that I've got the schema or table relationships wrong, though, so something different may be needed in that case.

AThomasHowe · Jul 12, 2014

You should move to the MySQLi (MySQL Improved/php5-mysqlnd) extension instead for PHP. Other than needing an explicit database link (so you need to save your mysqli_connect() to a variable, then do mysqli_query($dbLink, $query); ). It also has built in, good support for stuff like prepared statements.

mysqli has mysqli_real_escape_string, mysqli_fetch_array, mysqli_assoc_array etc so it's pretty painless to move over and once you get used to using prepared statements you will have some built in query safety.

It won't fix this problem but it's not advised to use php5-mysql where mysqlnd is possible anymore.

KuJoe · Jul 12, 2014

@AThomasHowe I'm coding reports for WHMCS so I am using their MySQL helpers, they don't support mysqli yet.

AThomasHowe · Jul 12, 2014

KuJoe said:
@AThomasHowe I'm coding reports for WHMCS so I am using their MySQL helpers, they don't support mysqli yet.

Then you've got a very long job ahead of you haven't you

Hah no though, that sucks. Even though I know what they're like over there it always shocks me how the leading billing software in the hosting industry is so behind technology. You could say that for a lot of the practices of a lot of hosts though... I mean for example, don't you still use a netbook?

just kidding. don't cap my port speed pls.

KuJoe · Jul 12, 2014

AThomasHowe said:
Then you've got a very long job ahead of you haven't you

Hah no though, that sucks. Even though I know what they're like over there it always shocks me how the leading billing software in the hosting industry is so behind technology. You could say that for a lot of the practices of a lot of hosts though... I mean for example, don't you still use a netbook?

just kidding. don't cap my port speed pls.

Luckily I'm using their MySQL helpers so when they do switch to mysqli I won't need to change any of my code.

I don't use a netbook anymore (I started playing PC games again and they are a little too demanding for my old netbook) but I do carry around my tablet with an HDMI adapter and wireless keyboard and mouse instead of a laptop.

Need some more MySQL magic.

KuJoe

Well-Known Member

fizzyjoe908

New Member

KuJoe

Well-Known Member

KuJoe

Well-Known Member

KuJoe

Well-Known Member

perennate

New Member

KuJoe

Well-Known Member

mikho

Not to be taken seriously, ever!

dave

Member

KuJoe

Well-Known Member

Flapadar

Member

dave

Member

AThomasHowe

New Member

KuJoe

Well-Known Member

AThomasHowe

New Member

KuJoe

Well-Known Member