PHP and MYSQL query not displaying data

[tonynoriega]

i cant figure out why this will not work, i that the query was in order....?
____________________________________________________________

function leadstablereport_display_addon() {
$security_admin = login::loginCheck('Admin', true);
$security_agent = login::loginCheck('Agent', true);
if ($security_admin === true ¦¦ $security_agent === true) {
global $config, $conn;
require_once($config['basepath'] . '/include/misc.inc.php');
$misc = new misc();

$sql = "SELECT nhm_associate, COUNT(nhm_associate) FROM registration_table GROUP BY nhm_associate";

while($row = mysql_fetch_array($sql)){
$display .='There are '. $row['COUNT(nhm_associate)'] .' '. $row['nhm_associate'] . ' items.<br />';

}
} else {
// DISPLAY A WARNING TO THE PAGE AS ADMIN/AGENT IS NOT LOGGED
$display .= 'YOU ARE NOT AUTHORIZED TO VIEW THIS PAGE.';
}
return $display;
}

[justgowithit]

$sql = "SELECT nhm_associate, COUNT(nhm_associate) FROM registration_table GROUP BY nhm_associate";

$result = mysql_query($sql, $conn);

while($row = mysql_fetch_array($result)){

etc. etc.

[tonynoriega]

Well, i tried that but to no avail...

Here is my current function below; which returns this below...how is i the letter "S" getting in there?:
_________________________________________________________
There are S S items.
There are S S items.
There are S S items.
There are S S items.
There are S S items.
_________________________________________________________

function leadstablereport_display_addon() {
$security_admin = login::loginCheck('Admin', true);
$security_agent = login::loginCheck('Agent', true);
if ($security_admin === true ¦¦ $security_agent === true) {
global $config, $conn;
require_once($config['basepath'] . '/include/misc.inc.php');
$misc = new misc();

$display .='';

$sql = 'SELECT nhm_associate, COUNT(nhm_associate) FROM registration_table GROUP BY nhm_associate';
// EXECUTE
$recordSet = $conn->Execute($sql);
// CHECK IF ERROR
if ($recordSet === false) {
$misc->log_error($sql);
}
// KEEP READING TILL THE END
while (!$recordSet->EOF) {

$display .='There are '. $sql["COUNT(nhm_associate)"] .' '. $sql["nhm_associate"] . ' items.<br />';

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}

} else {
// DISPLAY A WARNING TO THE PAGE AS ADMIN/AGENT IS NOT LOGGED
$display .= 'YOU ARE NOT AUTHORIZED TO VIEW THIS PAGE. PLEASE GO TO THE BOTTOM OF THE PAGE AND LOG IN!';
$recordSet->MoveNext();
}
return $display;
}

[jatar_k]

the S is coming from this, which is wrong

$display .='There are '. $sql["COUNT(nhm_associate)"] .' '. $sql["nhm_associate"] . ' items.<br />';

you can assign a varname like so

$sql = 'SELECT nhm_associate, COUNT(nhm_associate) as thebigcount FROM registration_table GROUP BY nhm_associate';

and then

$display .='There are '. $sql['thebigcount'] .' '. $sql["nhm_associate"] . ' items.<br />';

[tonynoriega]

ARGHHHHHHHH....

this is so simple, yet so frustrating....

Still no go....

ill keep trying...

[jatar_k]

what happens now?

hard to say how to dump the result using an abstraction layer but dumping the result to see what is returned would help.

if you have phpmyadmin you could run the query to understand it better

[tonynoriega]

Ok, i got it, i wasnt declaring the values in the $display statement......here is what is returned now:

There are 9 Candace Shan items.
There are 155 Debbie Orme items.
There are 75 Jesse Stoneberg items.
There are 58 Joe Grubiak items.
There are 9 Not Specified items.
______________________________________________________

What i want to do now, and maybe you can help me with is build upon this...

i want to put each agent in a separate row...in a table.

______________________________________________________

function leadstablereport_display_addon() {
$security_admin = login::loginCheck('Admin', true);
$security_agent = login::loginCheck('Agent', true);
if ($security_admin === true ¦¦ $security_agent === true) {
global $config, $conn;
require_once($config['basepath'] . '/include/misc.inc.php');
$misc = new misc();

$sql = 'SELECT nhm_associate, COUNT(nhm_associate) AS totalcount FROM registration_table GROUP BY nhm_associate';
// EXECUTE
$recordSet = $conn->Execute($sql);

// CHECK IF ERROR
if ($recordSet === false) {
$misc->log_error($sql);
}
// KEEP READING TILL THE END
while (!$recordSet->EOF) {
$tc = $misc->make_db_unsafe ($recordSet->fields['totalcount']);
$nhma = $misc->make_db_unsafe ($recordSet->fields['nhm_associate']);

$display .='There are '. $tc .' '. $nhma . ' items.<br />';

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}

} else {
// DISPLAY A WARNING TO THE PAGE AS ADMIN/AGENT IS NOT LOGGED
$display .= 'YOU ARE NOT AUTHORIZED TO VIEW THIS PAGE. PLEASE GO TO THE BOTTOM OF THE PAGE AND LOG IN!';
$recordSet->MoveNext();
}
return $display;
}

[jatar_k]

easy enough, you need to work with your while loop

echo '<table>'; // start your table before
while (!$recordSet->EOF) {
$tc = $misc->make_db_unsafe ($recordSet->fields['totalcount']);
$nhma = $misc->make_db_unsafe ($recordSet->fields['nhm_associate']);

$display .='There are '. $tc .' '. $nhma . ' items.<br />';

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}
echo '</table>'; // close your table after

and then you just play with your html in your output line

from
$display .='There are '. $tc .' '. $nhma . ' items.<br />';

to
$display .='<tr><td>There are '. $tc .' '. $nhma . ' items.</td></tr>';

obviously this is a very simple table but imagine you get my drift

[tonynoriega]

thank you much....
have a good weekend....

this is perfect... in case you were wondering, im trying to create a print out of all of our leads on a weekly basis...

may i continue this building process with you next week?

[jatar_k]

I'm always here :)

[tonynoriega]

jatar...welcome back...

Here is my next step.... the function below, now goes back 7 days, and checks to see how many records any agent has entered during that timeframe....

using the DATE_SUB function... i learned that at least!

Now, what i want to do is show a weekly calendar of the last seven days, and what day each entry was entered....
(yeah, basically....im sure that sounds dumb)

so it would look something similar to this:

---------------------------------------------------
Agent___7/7___7/8___7/9___7/10___7/11___7/12___7/13
---------------------------------------------------
Agent1___1_____1_____2______2______0_____9______1
Agent2___0_____0_____0______1______1_____1______1
---------------------------------------------------
How do you show the dates and 7 days prior, also calling how many entries were entered that day?

From my own thinking, i thought i would have to define a variable with a query for each interval day?

i.e. INTERVAL 7 DAY, INTERVAL 6 DAY, INTERVAL 5 DAY

and then place each of those in its own <td>?
___________________________________________________________
function leadstablereport_display_addon() {
$security_admin = login::loginCheck('Admin', true);
$security_agent = login::loginCheck('Agent', true);
if ($security_admin === true ¦¦ $security_agent === true) {
global $config, $conn;
require_once($config['basepath'] . '/include/misc.inc.php');
$misc = new misc();

$sql = 'SELECT nhm_associate, COUNT(nhm_associate) AS totalcount FROM registration_table WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= entry_time GROUP BY nhm_associate ';
// EXECUTE
$recordSet = $conn->Execute($sql);

// CHECK IF ERROR
if ($recordSet === false) {
$misc->log_error($sql);
}
// KEEP READING TILL THE END
$display .='<table width="100%" border="1" style=border-collapse:collapse; border-style:solid;>'; // start your table before
$display .='<tr><td><h1 align="center">Registration Leads Entered Last Week</h1></td></tr>';
while (!$recordSet->EOF) {
$tc = $misc->make_db_unsafe ($recordSet->fields['totalcount']);
$nhma = $misc->make_db_unsafe ($recordSet->fields['nhm_associate']);

$display .='<tr><td>' . $nhma . ' has entered ' . $tc . ' last week.</td></tr>';

// GO GET THE NEXT ONE
$recordSet->MoveNext();
}
$display .='</table>'; // close your table after

} else {
// DISPLAY A WARNING TO THE PAGE AS ADMIN/AGENT IS NOT LOGGED
$display .= 'YOU ARE NOT AUTHORIZED TO VIEW THIS PAGE. PLEASE GO TO THE BOTTOM OF THE PAGE AND LOG IN!';
$recordSet->MoveNext();
}
return $display;
}

[jatar_k]

you have the total per associate for last week, leave that, make it clickable and that could bring up your 7 day per associate

if you put them all in one table it may take a chunk of work

does the table have a column for date or store a timestamp, I assume it does

group by associate and sort by date, then loop through them to properly display the results

you might want to have this generated and store it as an html page/table if you are going to access this often

[tonynoriega]

ok, now obviously i dont want you to sit and code this for me, without properly compensating you...so i have a few more questions...

you have the total per associate for last week, leave that, make it clickable and that could bring up your 7 day per associate

so the original query, that only counts how many records that were entered the prior 7 days, make that a link to bring up the weekly per day count?

if you put them all in one table it may take a chunk of work

so make each agent as their own table right?

does the table have a column for date or store a timestamp, I assume it does

yes, the column name in the table is "entry_time" and is set to "datetime" datatype...i was told that is easier for calculations?

group by associate and sort by date, then loop through them to properly display the results

didnt follow you on this one...

you might want to have this generated and store it as an html page/table if you are going to access this often

yes, this is being displayed in a secure area for authorized users, on my web server.

[jatar_k]

you could select all records from the last seven days and then group them by associate and order by date

you could then loop through the result and count them and store them in an array

you might be able to do the whole thing in a query but I am not sure exactly what it would be