I have MySQL Query, how do I get it into PHP?

[seabro]

Hi,

 

Here is my mysql query.  It works.

 

SELECT dstchannel,sum(duration) from cdr where calldate like '2009-03-03%' group by substring(dstchannel,3,6)

 

How can I display the result on my web page.  I am trying the following but it not working and I don't know how to fix it.

 

 

<?php

 

$connect = mysql_pconnect("localhost","user","pass") or Die("connect failed");

$selectdb = mysql_select_db("dbase") or Die("select failed");

 

$query  = "SELECT dstchannel,sum(duration) from cdr where calldate like '2009-03-03%' group by substring(dstchannel,3,6)";

 

$connect;

$selectdb;

 

mysql_query($query) or die ("query x failed");

$res = mysql_query($query,$connect) or die("failed");

 

while($qry = mysql_fetch_array($res)) {

 

$dstchannel=$qry["dstchannel"];

 

print "$dstchannel";

print "<br>";

print "$duration";

print "<br>";

 

};

 

?>

 

[cooldude832]

Add in a test for mysql_num_rows

 

if it is 0 then the while part is automatically failing

 

 

Also check you are reporting errors I've never seen a while loop closed with a ;

 

 

[John_S]

Hey there,

I think it doesn't work since it has MySQL specific functions like: sum(duration) or substring(dstchannel,3,6). Also, why are you using the variable $connect twice?

 

Plus there is a better way to do:

<?php
print "$dstchannel";
print "<br>";
print "$duration";
print "<br>";
?>

Using one and only display function, print "$dstchannel<br>$duration"; but I would suggest you to use "echos" instead of print, in my opinion echos' faster and plus they take multiple expressions.

 

I changed your code a bit, hope it helps.

<?php
$connect = mysql_pconnect("localhost","user","pass") or Die("connect failed");
mysql_select_db("dbase") or Die("select failed");

$query  = "SELECT dstchannel,sum(duration) from cdr where calldate like '2009-03-03%' group by substring(dstchannel,3,6)";

$res = mysql_query($query,$connect) or die("failed");
while($qry = mysql_fetch_array($res)) {
    $dstchannel=$qry["dstchannel"];
    echo "$dstchannel<br>$duration";
    };
?>

[cooldude832]

Hey there,

I think it doesn't work since it has MySQL specific functions like: sum(duration) or substring(dstchannel,3,6). Also, why are you using the variable $connect twice?

 

 

 

You can use mysql functions in a query...

 

Mysql_query as a php function only facilitates passing your query phrase to mysql for turning into a resource of the queries result and doing any action in it (like SUM)

 

Select is a mysql function

from is a mysql function

Where is a mysql function

 

So to speak of

[sasa]

try

<?php
$connect = mysql_pconnect("localhost","user","pass") or Die("connect failed");
$selectdb = mysql_select_db("dbase") or Die("select failed");
$query  = "SELECT dstchannel,sum(duration) from cdr where calldate like '2009-03-03%' group by substring(dstchannel,3,6)";
$res = mysql_query($query,$connect) or die("failed");
while($qry = mysql_fetch_array($res)) {
  $dstchannel=$qry["dstchannel"];
  $duration = $qry['sum(duration)'];
  print "$dstchannel";
  print "<br>";
  print "$duration";
  print "<hr>";
};
?>

[seabro]

Thanks to all who replied. it's working now, I appreciate your help.