Query Problem.

[edwardoit]

Guys! I am having problem with my codes here.

I want to display a table like this:

Phone Number

0

1

2

...23

023002000007979

003430

017979027979237979

023002340007349012579027979237279

 

 

 

how can i achieve a result like this... anyone?

[rajivgonsalves]

I cannot understand your problem... where do you want to display the table... in mysql command prompt ?

[edwardoit]

Actually this is part of my code:

 

<?php

$hournum = range(0, 23);

$query = mysql_query('SELECT caller from test_table GROUP BY caller');

while ($rs = mysql_fetch_assoc($query)) {

echo '<tr>';

# phone number
echo '<td>' . $rs['caller'] . '</td>';

# loop hours
foreach ($hournum as $hour) {

	$sql2   = 'SELECT
		  duration, LEFT(time_call, 2) hour, time_call, date_call
		  FROM test_table
		  WHERE LEFT(time_call, 2) = ' . $hour;
	$query2 = mysql_query($sql) or die(mysql_error());

	while ($rs2 = mysql_fetch_assoc($query2)) {

		echo '<td>';

		        # display call at specific hour
          echo $rs['time_call'];
		            echo '<br />';
		echo '</td>';

	}



}

echo '</tr>';

}

?>

 

 

But im having problem with the result. The hours displayed on the first phone number was the same

on the next phone number(repeating).

I want to display call summary on evry specific hour of each phone number(caller).

I cant figure out if it was on the query or with the loop.

[rajivgonsalves]

This is to do with structuring your html table properly with your records... u will have to structure it properly...

[mrbigdog]

Hi,

 

I have re-written your query so you can see how to correctly put it into a table. I only just figured out this myself, so am not an expert. There may be some errors in this (be warned) but the most important thing is you can see the structure of how the query can be displayed into a table correctly and the code you need:

 

<?php

 

$hournum = range(0, 23);

 

$query = mysql_query("SELECT caller from test_table GROUP BY caller");

 

$sql2   = "SELECT duration, LEFT(time_call, 2) hour, time_call, date_call

  FROM test_table WHERE LEFT(time_call, 2) =.$hour";

 

$query2 = mysql_query($sql);

 

while($rs = mysql_fetch_array($query, MYSQL_ASSOC)) {

 

foreach($row as $k=>$v) // This is the code you need

$$k = $v;

 

 

?>

 

   

        <td width="26%" class="normal_caps"><? echo $rs['caller']?></td>  // Your table

        <td width="21%" class="normal_caps"><? echo $rs['time_call'] ?></td>

</td>

      </tr> <?

++$i; // Your code for displaying/looping those results in the table.

}

echo "";

 

?>

    </table>

  </div>

 

I hope this helps a little. If you would like me to send my original code so you can adapt it - let me know.

 

Hope you get it sorted.

 

Paul.

[edwardoit]

I'm glad to have your original code.

I can't follow the code you provided here.

I'm no expert also. 

 

Do we have the same logic here? Mine is I want to display the phone number and the time calls were made so I can see if it really falls to the specific time. Uhmmm... if it works well, then I will change the code from displaying the specific time to total calls made on that specific time.

 

 

 

This is actually a simple view of  what I want to display. But if you guys know how to display call history per hour much better...I'll just revise it to achieve my need.

 

 

 

+--------+----+----+----+------+

| fone #  | 00  | 01  | 02 | ...23  |   00 -> 23 hours

+--------+----+----+----+------+

| 123323 |  5   | 01  | 62 |   0    |

+--------+----+----+----+------+

| 534513 |  1   | 9   | 3   |   5    |

+--------+----+----+----+------+

| 34522   | 0   | 1    | 54 |   1    |

+--------+----+----+----+------+

| 45678   | 7   | 0    | 0   |  4     |

+--------+----+----+----+------+

 

 

but i got this...

+--------+----+----+----+------+

| fone #  | 00  | 01  | 02 | ...23  |  00 -> 23 hours

+--------+----+----+----+------+

| 123323 |  5  | 01  | 62 |  0    |

+--------+----+----+----+------+

| 223324 |  5  | 01  | 62 |  0    |

+--------+----+----+----+------+

| 323325 |  5  | 01  | 62 |  0    |

+--------+----+----+----+------+

| 423326 |  5  | 01  | 62 |  0    |

+--------+----+----+----+------+

 

 

 

 

[rajivgonsalves]

post the modified code you have its hard to tell anything....

[edwardoit]

im having idea that it's a cross-tab.   

 

I still did not solve this problem. But I've read maybe same solution to mine...

---this article> http://www.phpfreaks.com/forums/index.php/topic,119398.0.html

somebody can help me explain about this., especially the database query.

thanks in advance...

[edwardoit]

additional resources. http://dev.mysql.com/tech-resources/articles/wizard/page5.html

but its a perl script. i don't know this...  ???