report help

[vj5]

i want to generate a report based on date, ie., when entering the date, based on the date a report should be generated online. The generated field should be broker name and group name. I want it in this formatbrokername1groupname1groupname1groupname1brokername2groupname2groupname2groupname2how to do a format like the above in php?

[justsomeguy]

PHP doesn't format anything, HTML does. I don't know where any of your data is coming from though so it's a little hard to give any advice. If your data is in a database and you have a table for brokers and one for groups then you would use a join to get the data and then loop through the result set and print it out. If your data is coming from somewhere else then you would get it a different way, I'm not sure what else to tell you.

[vj5]

PHP doesn't format anything, HTML does. I don't know where any of your data is coming from though so it's a little hard to give any advice. If your data is in a database and you have a table for brokers and one for groups then you would use a join to get the data and then loop through the result set and print it out. If your data is coming from somewhere else then you would get it a different way, I'm not sure what else to tell you.

it is coming from two databases. can you show me an example of the loop ?

[justsomeguy]

There's an example on this page. If it's two separate databases though you won't be able to get it all with one query, one query can only act on the tables in one database.http://www.php.net/manual/en/function.mysql-fetch-assoc.php

[vj5]

There's an example on this page. If it's two separate databases though you won't be able to get it all with one query, one query can only act on the tables in one database.http://www.php.net/manual/en/function.mysql-fetch-assoc.php

Thankyou. I have one question pertaining to the code given in that site:

<?php$conn = mysql_connect("localhost", "mysql_user", "mysql_password");if (!$conn) {	echo "Unable to connect to DB: " . mysql_error();	exit;}  if (!mysql_select_db("mydbname")) {	echo "Unable to select mydbname: " . mysql_error();	exit;}$sql = "SELECT id as userid, fullname, userstatus 		FROM   sometable		WHERE  userstatus = 1";$result = mysql_query($sql);if (!$result) {	echo "Could not successfully run query ($sql) from DB: " . mysql_error();	exit;}if (mysql_num_rows($result) == 0) {	echo "No rows found, nothing to print so am exiting";	exit;}// While a row of data exists, put that row in $row as an associative array// Note: If you're expecting just one row, no need to use a loop// Note: If you put extract($row); inside the following loop, you'll//	   then create $userid, $fullname, and $userstatuswhile ($row = mysql_fetch_assoc($result)) {	echo $row["userid"];	echo $row["fullname"];	echo $row["userstatus"];}mysql_free_result($result);

what does mysql_free_result($result); do?

[justsomeguy]

Check the reference page for it:http://www.php.net/manual/en/function.mysql-free-result.php

[vj5]

Is there a way to hide a <select> tag:

echo '<td><select id="selBroker1" name="selBroker1">';		emitBrokerOptions($selBroker1);		echo "</select></td></tr>";

I want to hide the value of the selection .

[vj5]

Is there a way to hide a <select> tag:

echo '<td><select id="selBroker1" name="selBroker1">';		emitBrokerOptions($selBroker1);		echo "</select></td></tr>";

I want to hide the value of the selection .

No Thanks. I found the trick. <Style =display-none> hides the selection.

[vj5]

Can someone let why I am not getting the managing_broker_id in the select statement?

function emitBrokerOptions($lngBrokerId)			{	global $brokerResult;								if (!strlen($lngBrokerId)) $lngBrokerId = 1;				mysql_data_seek($brokerResult, 0);				while ($b = mysql_fetch_object($brokerResult))				{  echo '<option value="' . $b->PersonID . '"';					if ($lngBrokerId == $b->PersonID) echo ' selected ';					echo '>' . $b->FullName . '</option>';				}		 }	//FORM 1: CANCEL FORMif (preg_match('/cancel/i',$formaction)) {   echo "<center>Confirmation Process Cancelled";   echo "</div> <!-- a-section --></td></tr></table>";   include '/www2/footer.php';   exit;}	 	if (!isset($formgrowth,$formtraditional) or preg_match('/Fix/i',$formgrowth,$formtraditional)) {				$brokerResult = mysql_query('SELECT PersonID, If(FirstName<>"", concat(LastName, ", ", FirstName), LastName) AS FullName												FROM Brokers													WHERE PersonID <> 0													ORDER BY If(FirstName<>"", concat(LastName, ", ", FirstName), LastName)', $objConn);   echo "<div id=pers-form>";	echo "<div id=a-text>";		echo "<form name='persall' action='pers-all.php' method='post'>";		echo "<table><tr>";		echo '<td><select id="selBroker1" name="selBroker1" style="display:none;" >';		emitBrokerOptions($selBroker1);		echo "</select></td></tr>";		echo "<tr><td id = a-text>Statement Date</td>";		echo "<td><input type=text name=stdate value='".$stdate."'></td></tr>";		echo "</table><br><br>";		echo "<input type=submit name='formgrowth' value='View Growth Report'><br>";		echo "<input type=submit name=formtraditional value='View Traditional Report'>   <input name='formgrowth' type='submit' value='Cancel'";		echo "</form><br><br>";			echo "</div> <!--a-text-->";		echo "</div> <!--pers-form-->";		 		}	if (preg_match('/Growth/i',$formgrowth)) {	if ($stdate) {					 $sql = 'SELECT ph.groupno, p.GroupName, ph.asof,				If(b.FirstName<>"", concat(b.FirstName, " ", b.LastName), b.LastName) AS FullName, 				ph.base, ph.current, ph.`gro/decline`				FROM dbBkrPol.Policies p INNER JOIN `database1`.persHorizon ph ON UCASE(p.GroupNumber) = UCASE(ph.groupno)				INNER JOIN database2.Brokers b ON p.managing_broker_id = b.PersonID			WHERE p.managing_broker_id = ' . $selBroker1 .' and ph.asof ="'.$stdate.'" ORDER BY ph.groupno';

I get an empty result set. And the p.managing_broker_id = . value is not showing up. can someone help?

[justsomeguy]

Are you saying the $selBroker1 variable is empty? I don't see you set that variable anywhere.

[vj5]

Are you saying the $selBroker1 variable is empty? I don't see you set that variable anywhere.

It is here:

echo '<td><select id="selBroker1" name="selBroker1" style="display:none;" >';		emitBrokerOptions($selBroker1);		echo "</select></td></tr>";

[justsomeguy]

No, it's not getting set there. It will get set on a line that starts with "$selBroker1 = ".

[vj5]

It is here:

echo '<td><select id="selBroker1" name="selBroker1" style="display:none;" >';		emitBrokerOptions($selBroker1);		echo "</select></td></tr>";

Actually, I am starting to think that I don't need the $selBroker1 since I am only doing a report based on stdate(asof). I need help with formatting:I want like the one below. Can you tell how to format?

BrokerNamegroupno  groupname   base  current gro/decline persistencyBrokernamegroupno groupname	base  current  gro/decline persistency

Right now, it shows only brokername:my code:

$strData .=  "<table><tr><th align=center id=a-text>".$arrBroker['FullName']."</th></tr>";	   "<tr><td align=center id=a-text >" .$arrBroker['groupno']."</td>" .	   "<td id=a-text>" .(strtoupper($arrBroker['GroupName']))."</td>" .	   "<td align=center id=a-text>" .$arrBroker['base']."</td>" .	   "<td align=center id=a-text>" .$arrBroker['current']."</td>" .	   "<td align=center id=a-text>" .$arrBroker['gro/decline']."</td>" .	   "<td align=right id=a-text >" .$pers."</td></tr></table";

[justsomeguy]

So the rest of the array is empty? Where are you building the $arrBroker array?

[vj5]

So the rest of the array is empty? Where are you building the $arrBroker array?

here is my code:

while ($arrBroker = mysql_fetch_array($result)) {	   $strData .=  "<table><tr><th align=center id=a-text>".$arrBroker['FullName']."</th></tr>";	   "<tr><td align=center id=a-text >" .$arrBroker['groupno']."</td>" .	   "<td id=a-text>" .(strtoupper($arrBroker['GroupName']))."</td>" .	   "<td align=center id=a-text>" .$arrBroker['base']."</td>" .	   "<td align=center id=a-text>" .$arrBroker['current']."</td>" .	   "<td align=center id=a-text>" .$arrBroker['gro/decline']."</td>" .	   "<td align=right id=a-text >" .$pers."</td></tr></table";}

I am storing the result set in the arrbroker array instead of:

$group_name = mysql_result($result, 0, "group_name");

[justsomeguy]

The mysql_result function only retrieves one field. Use mysql_fetch_assoc instead.

[vj5]

The mysql_result function only retrieves one field. Use mysql_fetch_assoc instead.

I tried and it is just the same result.

[justsomeguy]

Please post the code you're using.

[vj5]

Please post the code you're using.

if (preg_match('/Growth/i',$formgrowth)) {	if ($stdate) {					 $sql = 'SELECT ph.groupno, p.GroupName, ph.asof,				If(b.FirstName<>"", concat(b.FirstName, " ", b.LastName), b.LastName) AS FullName, 				ph.base, ph.current, ph.`gro/decline`				FROM database1.p INNER JOIN `database2`.pers ph ON UCASE(p.GroupNumber) = UCASE(ph.groupno)				INNER JOIN database1.Brokers b ON p.managing_broker_id = b.PersonID				WHERE ph.asof ="'.$stdate.'" ORDER BY ph.groupno asc';				$result = mysql_query($sql, $objConn);	$CNT = mysql_num_rows($result);	echo $sql;	$today=date("F j, Y");	 	  	$totalbase = 0;	$strData = '';	  while ($arrBroker = mysql_fetch_assoc($result)) {		  $asof = $arrBroker['asof'];					  			 $strData .=  "<table><tr><th align=center id=a-text>".$arrBroker['FullName']."</th></tr>";			 "<tr><td align=center id=a-text >" .$arrBroker['groupno']."</td>" .			  "<td id=a-text>" .(strtoupper($arrBroker['GroupName']))."</td>" .			 "<td align=center id=a-text>" .$arrBroker['base']."</td>" .			 "<td align=center id=a-text>" .$arrBroker['current']."</td>" .			 "<td align=center id=a-text>" .$arrBroker['gro/decline']."</td>" .			 "<td align=right id=a-text >" .$pers."</td></tr></table";	  }	  	  mysql_free_result($result);

[justsomeguy]

That should be working. If you want to see what is in the array you can use print_r:

while ($arrBroker = mysql_fetch_assoc($result)) {  print_r($arrBroker);  ...

[vj5]

That should be working. If you want to see what is in the array you can use print_r:

while ($arrBroker = mysql_fetch_assoc($result)) {  print_r($arrBroker);  ...

If I use print_r ($arrBroker);, it gives all the details. Then why isn't it printing the way it should display.

[justsomeguy]

I don't know, according to what you have in that while loop it should be printing fullname, group number, group name, base, current, and gro/decline. It wouldn't make sense if it's not doing that, that's exactly what the code says. View the generated HTML source code and make sure you see that information in the <td> elements of the table.