SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: NewbieQ: Arrays

  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    NewbieQ: Arrays

    Hey Guys,

    Couple of questions ... but first let me show you my code (NB. it was written in 5 mins - and I've very new to PHP and MySQL...):-

    Code PHP:
     $con = mysql_connect( --- HIDDEN --- ) or die(mysql_error()); 
     mysql_select_db( --- HIDDEN --- ) or die(mysql_error()); 
     
     $sql = "DROP TABLE IF EXISTS `Location_County`";
     mysql_query($sql,$con);
     
     $sql = "CREATE TABLE IF NOT EXISTS `Location_County` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(100) NOT NULL default '',
      `friendly_url` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      KEY `friendly_url` (`friendly_url`),
      KEY `name` (`name`)
     ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3";
     mysql_query($sql,$con);
     
     $sql = "INSERT INTO `Location_County` (`id`, `name`, `friendly_url`) VALUES
      (1, 'Tyne and Wear', 'tyne_and_wear'),
      (2, 'Northumberland', 'northumberland'),
      (3, 'County Durham', 'county_durham')";
     mysql_query($sql,$con);
     
     $sql = "DROP TABLE IF EXISTS `Location_City`";
     mysql_query($sql,$con);
     
     $sql = "CREATE TABLE IF NOT EXISTS `Location_City` (
      `id` int(11) NOT NULL auto_increment,
      `id_county` int(11) NOT NULL default '0',
      `name` varchar(100) NOT NULL default '',
      `friendly_url` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      KEY `friendly_url` (`friendly_url`),
      KEY `id_county` (`id_county`),
      KEY `name` (`name`)
     ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3";
     mysql_query($sql,$con);
     
     $sql = "INSERT INTO `Location_City` (`id`, `id_county`, `name`, `friendly_url`) VALUES
      (1, 1, 'Newcastle', 'newcastle'),
      (2, 1, 'Gateshead', 'gateshead'),
      (3, 1, 'Sunderland', 'sunderland'),
      (4, 2, 'Hexham', 'hexham'),
      (5, 3, 'Durham', 'durham')";
     mysql_query($sql,$con);

    I want to put all the counties, cities etc into a nice array/class... but I'm a little unsure on the best way to do that and how to then iterate over them to fill in the MySQL data...

    Can someone point me in the right direction?

    The "hacky" way to do it would be to use:-
    Code PHP:
     $counties = array("Tyne and Wear", "Northumberland", "County Durham");
     $cities = array("Newcastle", "Gateshead", "Sunderland", "Hexham", "Durham");
     $city_county = array(1,1,1,2,3);

    But city_county should, ideally, be rolled into the "cities" part... and possibly I should also be rolling the cities into "counties" as a subarray?

    That is... I should really have something like:-

    Code PHP:
    ( "Tyne and Wear", ( "Newcastle", "Gateshead", "Sunderland", "Hexham", "Durham" ) ),
    ( "Northumberland", ( "Hexham" ) ),
    ( "County Durham", ( "Durham" ) )

    Can someone give me some advice on the best/cleanest way to set all this up? None of my PHP/MySQL books seem to have anything like this...???

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT Location_County.name AS county_name
         , Location_City.name AS city_name
      FROM Location_County
    LEFT OUTER
      JOIN Location_City
        ON Location_County.id = Location_City.id_county
    PHP Code:
    $data = array();
    while (
    $row mysql_fetch_assoc($result)) {
        
    $data[$row['county_name']][] = $row;
        
    // or $data[$row['county_name']][] = $row['city_name'];
    }
    print_r($data); 
    This will not retrieve orphanded cities, which do not have a county entry in the county table.

  3. #3
    SitePoint Wizard gRoberts's Avatar
    Join Date
    Oct 2004
    Location
    Birtley, UK
    Posts
    2,439
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    off topic:

    Welcome to the forum... You a geordie or just doing work based on the region?


  4. #4
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey there...

    I'm not a geordie... but I -have- lived up here for 15+ years so I guess I'm an "adopted geordie"... :-)

  5. #5
    SitePoint Wizard gRoberts's Avatar
    Join Date
    Oct 2004
    Location
    Birtley, UK
    Posts
    2,439
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your more so then I am, i've only been up here 9 years lol



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •