0

I have a postgresql view on which I'm running the following query :

select count(*) AS "Assigned Calls" ,to_timestamp(createdon)::date  from vw_issues  where to_timestamp(createdon)::date>='11/10/2009' and to_timestamp(createdon)::date<'11/11/2009'
group by to_timestamp(createdon)::date
order by to_timestamp(createdon)::date

when run in phppgadmin the result is displayed as
Assigned Calls to_timestamp
20 2009-10-12
13 2009-10-13
etc...
I need to create a page in php that displays the same results, can you help please?

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by saiprem
0

I have a postgresql view on which I'm running the following query :

select count(*) AS "Assigned Calls" ,to_timestamp(createdon)::date  from vw_issues  where to_timestamp(createdon)::date>='11/10/2009' and to_timestamp(createdon)::date<'11/11/2009'
group by to_timestamp(createdon)::date
order by to_timestamp(createdon)::date

when run in phppgadmin the result is displayed as
Assigned Calls to_timestamp
20 2009-10-12
13 2009-10-13
etc...
I need to create a page in php that displays the same results, can you help please?

Check the below code, I really didn't tried this, and hope it helps you to work with SQLITE

try
{
	$db = new PDO("sqlite:/path_to_your_db/sqlitedbname.db");

	$q = $db->query('select count(*) AS "Assigned Calls" ,to_timestamp(createdon)::date  from vw_issues  where to_timestamp(createdon)::date>="11/10/2009" and to_timestamp(createdon)::date<"11/11/2009" group by to_timestamp(createdon)::date
order by to_timestamp(createdon)::date')->fetchAll();
		if ($q === false) 
		{
			return false;
		}
		else 
		{
			echo "<pre>";
			print_r($q);
			echo "</pre>";
		}
}
catch (PDOException $e)
{
	 echo '<h4>Database Error: ',  $e->getMessage(),'</h4>';
}

Edited by saiprem: n/a

0

Thank you for your reply, I have used the below code and it works:

<table border="0" cellspacing="0" cellpadding="0">
            <tr>
                <td>
                    Assigned Calls
                </td>
                <td>
                    Day
                </td>

            </tr>
        <?php
        $db = pg_connect('host=localhost dbname=helpdesk user=****** password=******');

        $query1 = "select to_timestamp(createdon)::date, count(*)   from vw_issues  where to_timestamp(createdon)::date>='11/10/2009' and to_timestamp(createdon)::date<'11/11/2009'
group by to_timestamp(createdon)::date
order by to_timestamp(createdon)::date

";

        $result = pg_query($query1);
        if (!$result) {
            echo "Problem with query " . $query1 . "<br/>";
            echo pg_last_error();
            exit();
        }

        while($myrow = pg_fetch_assoc($result)) {
            printf ("<tr><td>%s</td><td>%s</td></tr>", htmlspecialchars($myrow['count']),htmlspecialchars($myrow['to_timestamp']) );
        }

        ?>


        </table>

Edited by Nick Evan: Fixed formatting

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.