SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question on how to generate a list from a DB

    Hi,

    I have a list of properties in a table(properties) in a database. Each of them has a state (Florida, Wyoming, Missouri...etc) field with the abbreviation as the data.

    Say I have a list of auctions in a different table(events) to sell properties. I want to pull an auction and display all the states (full name not abbreviation) from the properties associated with that auction.

    I'm confusing myself more and more, I think, on how to do this. Would anyone like to point me in the right direction or advise on a way to do this?

    Thanks!
    No, I REALLY dislike having to use Joomla.

  2. #2
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have a single auction, which is asscoiated to many properties.
    are you asking how to create this relationship in a database? or have you already created the association, and need help with a join query?

    as far as converting an abbreviation to a full name:
    -make an array, with the abbreviation as the key, and the full name as the value.
    -or make a table for this. each record holds a field for abbreviation and field for full value.

  3. #3
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I know if the association has already been made? I believe they are so help with the join would be cool.

    as far as the array...

    PHP Code:
    $state = array(
    'AL' => Alabama,
    'AK' => Alaska
    ); 
    and so on for each state, correct?
    No, I REALLY dislike having to use Joomla.

  4. #4
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Almost ...

    PHP Code:
     $state = array(
    'AL' => 'Alabama',
    'AK' => 'Alaska'
    ); 
    As far as the asscociation goes, what does your database look like? Having associations already made typically means a table like ...

    Code:
    Table: Properties2States
    
    ID | StateID | PropID
     1 |    1    |    1
     2 |    1    |    2
     3 |    1    |    3
     4 |    1    |    7
     5 |    2    |    4
     6 |    2    |    5
     7 |    3    |    8
     8 |    4    |    9
     9 |    4    |   11
    ... where ID is just a general ID reference (primary key), StateID is an ID reference for the state in the States table (you could also use the state letters here, like AL etc.), and PropID is the ID reference of the Property for the Properties table. So to get the properties available in, say, Alabama you could do ...

    Code:
    SELECT s.StateName,p.PropertyName
    FROM States s
    INNER JOIN Properties2States p2s
    ON p2s.StateID=s.StateID
    INNER JOIN Properties p
    ON p2s.PropID=p.PropID
    WHERE s.StateID=1
    ORDER BY p.PropertyName
    This example assumes you have tables called States and Properties, where States contains the fields StateID (e.g. AL) and StateName (e.g. Alabama), and Properties contains the fields PropertyID and PropertyName
    Ian Anderson
    www.siteguru.co.uk

  5. #5
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for correction guru...

    my DB has the tables:

    events - the auctions where event_id is the prime key

    properties - where prop_id is the prime key

    and event_properties - holds event_fk and prop_fk, which associates the two previous tables.
    No, I REALLY dislike having to use Joomla.

  6. #6
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    Code:
    SELECT s.StateName,p.PropertyName
    FROM States s
    INNER JOIN Properties2States p2s
    ON p2s.StateID=s.StateID
    INNER JOIN Properties p
    ON p2s.PropID=p.PropID
    WHERE s.StateID=1
    ORDER BY p.PropertyName
    This example assumes you have tables called States and Properties, where States contains the fields StateID (e.g. AL) and StateName (e.g. Alabama), and Properties contains the fields PropertyID and PropertyName
    what are the letters in the query? s? p? p2s?

    Don't know if this matters, but it's the query being used for displaying the current/next Event(auction) on the front page.

    PHP Code:
    $sql9 "SELECT * FROM events WHERE event_date >= '".date("Y-m-d")."' ORDER BY event_date ASC LIMIT 1;"

    my code for the states:

    PHP Code:
    $sql "SELECT DISTINCT properties.state ";
    $sql .= "FROM properties ";
    $sql .= "ORDER BY state;";

    $result mysql_query($sql);

    while (
    $row mysql_fetch_assoc($result)){
    $state $row['state'];

    $statelist = array(
    'AL' => 'Alabama',
    'AK' => 'Alaska',
    'AZ' => 'Arizona',
    'AR' => 'Arkansas',
    'CA' => 'California',
    'CO' => 'Colorado',
    'CT' => 'Connecticut',
    'DE' => 'Delaware',
    'FL' => 'Florida',
    'GA' => 'Georgia',
    'HI' => 'Hawaii',
    'ID' => 'Idaho',
    'IL' => 'Illinois',
    'IN' => 'Indiana',
    'IA' => 'Iowa',
    'KS' => 'Kansas',
    'KY' => 'Kentucky',
    'LA' => 'Louisiana',
    'ME' => 'Maine',
    'MD' => 'Maryland',
    'MA' => 'Massachusetts',
    'MI' => 'Michigan',
    'MN' => 'Minnesota',
    'MS' => 'Mississippi',
    'MO' => 'Missouri',
    'MT' => 'Montana',
    'NE' => 'Nebraska',
    'NV' => 'Nevada',
    'NH' => 'New Hampshire',
    'NJ' => 'New Jersey',
    'NM' => 'New Mexico',
    'NY' => 'New York',
    'NC' => 'North Carolina',
    'ND' => 'North Dakota',
    'OH' => 'Ohio',
    'OK' => 'Oklahoma',
    'OR' => 'Oregon',
    'PA' => 'Pennsylvania',
    'RI' => 'Rhode Island',
    'SC' => 'South Carolina',
    'SD' => 'South Dakota',
    'TN' => 'Tennessee',
    'TX' => 'Texas',
    'UT' => 'Utah',
    'VT' => 'Vermont',
    'VA' => 'Virginia',
    'WA' => 'Washington',
    'WV' => 'West Virginia',
    'WI' => 'Wisconsin',
    'WY' => 'Wyoming'
    );

    echo 
    $statelist[$state]."<br />\n";


    No, I REALLY dislike having to use Joomla.

  7. #7
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How can I tell if this is correct and not displaying more states than should be displayed?

    PHP Code:
    $EVENTsql "SELECT event_id ";
    $EVENTsql .= "FROM events ";
    $EVENTsql .= "WHERE event_date >= '".date("Y-m-d")."' ";
    $EVENTsql .= "ORDER BY event_date ASC LIMIT 1;";
    $EVENTresult mysql_query($EVENTsql);

    if (
    mysql_num_rows($EVENTresult)!=0) { 
        
    $Q_evt=mysql_fetch_assoc($EVENTresult);
      
      
    $event $Q_evt['event_id'];
      
    $name $Q_evt['name'];
    }
    echo 
    $event."<br />\n";
    echo 
    $name."<br />\n";

    $sql "SELECT DISTINCT properties.state ";
    $sql .= "FROM properties ";
    $sql .= "INNER JOIN event_properties ";
    $sql .= "ON properties.prop_id = event_properties.prop_fk ";
    $sql .= "INNER JOIN events ";
    $sql .= "ON event_properties.event_fk = events.event_id ";
    $sql .= "WHERE event_id = $event ";
    $sql .= "ORDER BY properties.state;";

    $result mysql_query($sql);

    while (
    $row mysql_fetch_assoc($result)){
    $state $row['state'];

    $statelist = array(
    'AL' => 'Alabama',
    'AK' => 'Alaska',
    'AZ' => 'Arizona',
    'AR' => 'Arkansas',
    'CA' => 'California',
    'CO' => 'Colorado',
    'CT' => 'Connecticut',
    'DE' => 'Delaware',
    'FL' => 'Florida',
    'GA' => 'Georgia',
    'HI' => 'Hawaii',
    'ID' => 'Idaho',
    'IL' => 'Illinois',
    'IN' => 'Indiana',
    'IA' => 'Iowa',
    'KS' => 'Kansas',
    'KY' => 'Kentucky',
    'LA' => 'Louisiana',
    'ME' => 'Maine',
    'MD' => 'Maryland',
    'MA' => 'Massachusetts',
    'MI' => 'Michigan',
    'MN' => 'Minnesota',
    'MS' => 'Mississippi',
    'MO' => 'Missouri',
    'MT' => 'Montana',
    'NE' => 'Nebraska',
    'NV' => 'Nevada',
    'NH' => 'New Hampshire',
    'NJ' => 'New Jersey',
    'NM' => 'New Mexico',
    'NY' => 'New York',
    'NC' => 'North Carolina',
    'ND' => 'North Dakota',
    'OH' => 'Ohio',
    'OK' => 'Oklahoma',
    'OR' => 'Oregon',
    'PA' => 'Pennsylvania',
    'RI' => 'Rhode Island',
    'SC' => 'South Carolina',
    'SD' => 'South Dakota',
    'TN' => 'Tennessee',
    'TX' => 'Texas',
    'UT' => 'Utah',
    'VT' => 'Vermont',
    'VA' => 'Virginia',
    'WA' => 'Washington',
    'WV' => 'West Virginia',
    'WI' => 'Wisconsin',
    'WY' => 'Wyoming'
    );

    echo 
    $statelist[$state]."<br />\n";


    Last edited by crowden; Nov 30, 2006 at 09:46. Reason: changed code
    No, I REALLY dislike having to use Joomla.

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Take the array out of the while() loop - you only need to define it once, not every time through the loop.

    What does the echo give out? What data is in the database? (It's impossible for us to say if it is working right when we have no info on the DB structure and data contained).
    Ian Anderson
    www.siteguru.co.uk

  9. #9
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, I'm ordering by state ASC. Why would Arkansas be listed before Arizona?
    No, I REALLY dislike having to use Joomla.

  10. #10
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    Take the array out of the while() loop - you only need to define it once, not every time through the loop.

    What does the echo give out? What data is in the database? (It's impossible for us to say if it is working right when we have no info on the DB structure and data contained).
    There's really too many properties to give all the data (about 1200 rows) for that table. I'll look through all the properties and compare states.

    I guess that'll be what I have to do.

    it echoes

    Code:
    Alabama
    Arkansas
    Arizona
    Colorado
    Florida
    Georgia
    Maine
    Michigan
    Missouri
    North Carolina
    North Dakota
    New Mexico
    Nevada
    Oklahoma
    South Carolina
    Tennessee
    Texas
    Utah
    Virginia
    No, I REALLY dislike having to use Joomla.

  11. #11
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, everything is working and accurate.

    Last thing: How do I code to determine the last state and place an 'and' in front of it instead of a comma as well as keeping the comma off the end of the list?

    PHP Code:
    echo $statelist[$state].",\n"
    echoes:

    Alabama, Arkansas, Arizona, Colorado, Florida, Georgia, Maine, Michigan, Missouri, North Carolina, North Dakota, New Mexico, Nevada, Oklahoma, South Carolina, Tennessee, Texas, Utah, Virginia,
    No, I REALLY dislike having to use Joomla.

  12. #12
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $a_states = array();
    while (
    $row mysql_fetch_assoc($result)){
        
    $a_states[] = $statelist[$row['state']];
    }
    $last array_pop($a_states);
    $a_states[] = 'and ' $last;
    echo 
    implode(",\n"$a_states); 

  13. #13
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    clamcrusher -

    thanks! Those are some pretty handy functions.
    No, I REALLY dislike having to use Joomla.

  14. #14
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question on how to divide query results

    I want to place an HR after every 5 records.

    I'll use an example from an earlier post:

    PHP Code:
    $sql "SELECT DISTINCT properties.state ";
    $sql .= "FROM properties ";
    $sql .= "ORDER BY state;";

    $result mysql_query($sql);

    while (
    $row mysql_fetch_assoc($result)){
    $state $row['state']; 

    echo 
    $state;

    How would I count the records so that after every 5th one, it inserts a horizontal rule? If there's a function, or more, for this, please let me know and I'll look it up in the manual to see if I can understand how it's done.

    Thanks!
    No, I REALLY dislike having to use Joomla.


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
  •