This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use the new forum.
Home » legacy support » symfony 1.0 » show how many records based on foreign key
show how many records based on foreign key [message #100322] Thu, 03 June 2010 13:09 Go to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
Hi

I have 2 tables: property and city

Property is just a list of houses, apartments etc each having a city_id, which is an FK to the City table

What I want to do is:

List all Cities and then say how many properties are in each.

For example

London (2)
Wales (2)
Scotland (3)
South East (0)

where the brackets are those properties that have the specific city_id.

If possible, I'd like this to in a component as I'd like to use it in a menu.

Thanks
Re: show how many records based on foreign key [message #100353 is a reply to message #100322 ] Thu, 03 June 2010 18:01 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
Do a joint on those tables e.g

city:
id
name

property:
id
title
city_id

your sql statement

$connection = Propel::getConnection();
$query = 'SELECT COUNT(cy.id) AS counter, cy.name AS city_name';
$query .= ' FROM city cy, property py';
$query .= ' WHERE cy.id = py.city_id';
$query .= ' GROUP BY cy.name';
$stmt = $connection->prepare($query);
$stmt->execute();
while($rs = $stmt->fetch())
{
$arrProperty[] = array('city' => $rs['city_name'], 'counter' => $rs['count']);
}



[Updated on: Thu, 03 June 2010 18:02]

Re: show how many records based on foreign key [message #100354 is a reply to message #100353 ] Thu, 03 June 2010 18:04 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
How would I then use this in a component?
Re: show how many records based on foreign key [message #100359 is a reply to message #100354 ] Thu, 03 June 2010 18:43 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
I used this to create tags on my previous site, it's a simple symfony experiment I am trying http://www.phpfeeds.co.za/


1)first in your model property,
-create a static function getPropertyListing()
-then from the code I wrote before return $arrProperty

2) create a module or use one you already have
and add components.class.php in the actions folder

class propertyComponents extends sfComponents
{
    public function executePropertyListing()
    {
      $this->property = PropertyPeer::getPropertyListing;
    }				
}


then create a partial called _propertyListing.php in your template folder
<div id="propertyListing" class="box">
<ul>  
 <?php foreach($property as $city =>$counter): ?>
   <li>City: <?php echo $city; ?>">:<?php echo $counter ?></li>
 <?php endforeach; ?>
</ul>								</div>

theb call the component from layout.php
<?php include_component('modulename', 'propertyListing') ?>


then you are done. I hope this helps

Re: show how many records based on foreign key [message #100363 is a reply to message #100322 ] Thu, 03 June 2010 18:59 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
I followed your example and i got the following error:

Fatal error: Undefined class constant 'getPropertyListing' in /var/www/work/ukbmvdeals/apps/frontend/modules/property/actions/components.class.php on line 75


Component Class
    
public function executePropertyListing()
    {
      $this->property = PropertyPeer::getPropertyListing;
    }	


Template:
<div id="propertyListing" class="box">
<ul>
 <?php foreach($property as $city =>$counter): ?>
   <li>City: <?php echo $city; ?>">:<?php echo $counter ?></li>
 <?php endforeach; ?>
</ul>								</div>


PropertyPeer:
        public static function getPropertyListing()
        {

            $connection = Propel::getConnection();
            $query = 'SELECT COUNT(cy.id) AS counter, cy.title AS city_name';
            $query .= ' FROM city cy, property py';
            $query .= ' WHERE cy.id = py.city_id';
            $query .= ' GROUP BY cy.title';
            $stmt = $connection->prepare($query);
            $stmt->execute();
            while($rs = $stmt->fetch())
            {
            $arrProperty[] = array('city' => $rs['city_name'], 'counter' => $rs['count']);
            }
            return $arrProperty;
        }


Any ideas?

[Updated on: Thu, 03 June 2010 19:01]

Re: show how many records based on foreign key [message #100369 is a reply to message #100363 ] Thu, 03 June 2010 19:54 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
Hi,
your mistake
class propertyComponents extends sfComponents
{
    public function executePropertyListing()
    {
      $this->property = PropertyPeer::getPropertyListing;
    }				
}


this line is suppose to be
$this->property = PropertyPeer::getPropertyListing();

[Updated on: Thu, 03 June 2010 19:56]

Re: show how many records based on foreign key [message #100405 is a reply to message #100369 ] Fri, 04 June 2010 10:41 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
Hi

Thanks for the help!

But that gives me:

Fatal error: Call to undefined method MySQLConnection::prepare() in /usr/share/php/symfony/symfony10/lib/addon/creole/drivers/sfDebugConnection.php on line 293
Re: show how many records based on foreign key [message #100424 is a reply to message #100405 ] Fri, 04 June 2010 14:13 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
can you give me a break down of your ENV

1) symfony version ?
2) php version ?
3) install php extensions (PDO ?)
4) windows / linux / mac ?
5) you level of experince with php? (junior,intermediate,senior,expert)

I need to know all the above to help you diagnose.
Re: show how many records based on foreign key [message #100426 is a reply to message #100424 ] Fri, 04 June 2010 14:40 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
I'm running the following:

symfony 1.0
PHP 5.2.6-3
I haven't installed the PDO extension
Mysql 5.0.75
I am an intermediate PHP developer.

I am running it on Ubuntu 9.04
Re: show how many records based on foreign key [message #100428 is a reply to message #100322 ] Fri, 04 June 2010 14:51 Go to previous messageGo to next message
halfer  is currently offline halfer
Messages: 9535
Registered: January 2006
Location: West Midlands, UK
Faithful Member
Note - for the version of Propel (1.2.1) that comes with symfony 1.0, you don't need PDO. From Propel version 1.3, you do.


Remember Palestine
Re: show how many records based on foreign key [message #100432 is a reply to message #100428 ] Fri, 04 June 2010 15:12 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
Ok.

I've installed pdo_mysql but I'm still getting the mysql connection error I stated before.
Re: show how many records based on foreign key [message #100438 is a reply to message #100428 ] Fri, 04 June 2010 16:25 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
I had to dig up my old code from symfony version 1.0, this should work without the pdo implementation

$con = Propel::getConnection();
$query = 'SELECT COUNT(cy.id) AS counter, cy.title AS city_name';
$query .= ' FROM city cy, property py';
$query .= ' WHERE cy.id = py.city_id';
$query .= ' GROUP BY cy.title';

$stm = $con->createStatement();
$rs = $stm->executeQuery($query);
while($rs->next()) {
$arrProperty[] = array('city' => $rs->getString('city_name'), 'counter' => $rs->getInt('counter'));

}


let me know
Re: show how many records based on foreign key [message #100439 is a reply to message #100432 ] Fri, 04 June 2010 16:27 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
Quote:


I've installed pdo_mysql but I'm still getting the mysql connection error I stated before.



the propel version on symfony 1.0 does not support PDO so that will not help.

a good reference point for symfony 1.0 http://www.symfony-project.org/askeet/1_0/en/

[Updated on: Fri, 04 June 2010 16:33]

Re: show how many records based on foreign key [message #100441 is a reply to message #100438 ] Fri, 04 June 2010 16:49 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
Am i then returning the array?

        public static function getPropertyListing()
        {

        $con = Propel::getConnection();
        $query = 'SELECT COUNT(cy.id) AS counter, cy.title AS city_name';
        $query .= ' FROM city cy, property py';
        $query .= ' WHERE cy.id = py.city_id';
        $query .= ' GROUP BY cy.title';

        $stm = $con->createStatement();
        $rs = $stm->executeQuery($query);
        while($rs->next())
                {

                $arrProperty[] = array('city' => $rs->getString('city_name'), 'counter' => $rs->getInt('counter'));

                }
                return $arrProperty;
        }


If so that returns me:

    *  City: 0">:Array
    * City: 1">:Array
    * City: 2">:Array
Re: show how many records based on foreign key [message #100444 is a reply to message #100441 ] Fri, 04 June 2010 17:15 Go to previous messageGo to next message
sonnybrilliant  is currently offline sonnybrilliant
Messages: 13
Registered: June 2010
Junior Member
can you debug the resultset,

while($rs->next())
{

$arrProperty[] = array('city' => $rs->getString('city_name'), 'counter' => $rs->getInt('counter'));
        var_dump($rs);
        exit;    
}

Re: show how many records based on foreign key [message #100446 is a reply to message #100444 ] Fri, 04 June 2010 17:23 Go to previous messageGo to next message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
object(MySQLResultSet)#207 (7) { ["fetchmode:protected"]=> int(1)
 ["conn:protected"]=> object(MySQLConnection)#144 (6) { 
["database:private"]=> string(5) "ukbmv" 
["transactionOpcount:protected"]=> int(0) ["dblink:protected"]=> 
resource(158) of type (mysql link) ["dsn:protected"]=> array(12) 
{ ["compat_assoc_lower"]=> NULL ["compat_rtrim_string"]=> NULL 
["database"]=> string(5) "ukbmv" ["encoding"]=> NULL 
["hostspec"]=> string(9) "localhost" ["password"]=> string(8) 
"password" ["persistent"]=> NULL ["phptype"]=> string(5) "mysql" 
["port"]=> NULL ["protocol"]=> NULL ["socket"]=> NULL 
["username"]=> string(4) "root" } ["flags:protected"]=> int(0) 
["lastQuery"]=> string(122) "SELECT COUNT(cy.id) AS counter, 
cy.title AS city_name FROM city cy, property py WHERE cy.id = 
py.city_id GROUP BY cy.title" } ["result:protected"]=> 
resource(230) of type (mysql result) ["cursorPos:protected"]=> 
int(1) ["fields:protected"]=> array(2) { ["counter"]=> string(1) 
"1" ["city_name"]=> string(6) "London" } 
["lowerAssocCase:protected"]=> bool(false) 
["rtrimString:protected"]=> bool(false) } 


Mod: fixed unwrapped content -- halfer

[Updated on: Tue, 08 June 2010 10:53] by Moderator

Re: show how many records based on foreign key [message #100575 is a reply to message #100322 ] Mon, 07 June 2010 18:19 Go to previous message
wigglesby85  is currently offline wigglesby85
Messages: 144
Registered: May 2009
Senior Member
Any further help on this?
Previous Topic:symfony command issue
Next Topic:Saving form data to the database
Goto Forum:
  

powered by FUDforum - copyright ©2001-2004 FUD Forum Bulletin Board Software