View unanswered posts | View active topics It is currently Tue Aug 09, 2022 9:46 am



Reply to topic  [ 5 posts ] 
 new to mysql 
Author Message
Junior Member

Joined: Fri Apr 17, 2009 12:06 pm
Posts: 8
Post new to mysql
:eek: and of course im in over my head. heres my problem: i have to create a database for the 50 states that contains monthly information in 5 categories. i know basic basic database stuff. when i signed on i wasnt supposed to be the one who created and updated and maintained everything but it looks like the tides have turned. cant anyone offer suggestions theories and/or hyperlinks as to how i should structure this. ill be using php to generate xml to send to flash. good times. thanks in advance. :eek:


Fri Apr 17, 2009 12:10 pm
Report this post
Profile
Junior Member

Joined: Fri Apr 17, 2009 12:06 pm
Posts: 8
Post 
Hi, thanks for your reply. My buddy turned me onto the data normalization aspect of it and whats where I got lost. I need to store -- I guess -- a table for states, a table for months, and separate tables for the different types of data. I'm not really sure where to start so any help is appreciated. Thanks!!


Tue Apr 21, 2009 12:31 am
Report this post
Profile
Junior Member

Joined: Fri Apr 17, 2009 12:06 pm
Posts: 8
Post 
Hi, thanks for the advice about the array. I'm going to need to update the months. Actually, I guess I should rephrase. I'll be working with dates, but it'll only be monthly data. The years will changes however. Also, the data is basically a bunch of different types of metrics -- delinquencies and what not. It'll be -- hopefully -- dealt with in percentages. The way its supposed to be set up is a state will show the monthly percent of a selected metric. Let me know if this sheds any light and thanks again for the response.


Tue Apr 21, 2009 11:18 am
Report this post
Profile
Junior Member

Joined: Fri Apr 17, 2009 12:06 pm
Posts: 8
Post 
Excellent! I will give it a shot. Thanks for your help!


Tue Apr 21, 2009 12:33 pm
Report this post
Profile
Junior Member

Joined: Fri Apr 17, 2009 12:06 pm
Posts: 8
Post 
Hi, I think this was what I needed...

[php]
<?php
$states = array('AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'HI', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA', 'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY');
$state = array();
$date = array();
$total = array();
for($i=0;$i<count($states);$i++){
$result = mysql_query("SELECT * FROM '$table' WHERE state='$states[$i]' ORDER BY id DESC LIMIT 14");
if($result) {
while ($row = mysql_fetch_object($result)) {
$state[] = $row->state;
$date[] = $row->date;
$total[] = $row->total;
}
}
}
echo "<?xml version='1.0' encoding='utf-8'?>\n";
echo "<items>\n";
for($j=0;$j<count($state);$j++){
echo "<state attribute='$state[$j]'>\n";
echo "<date attribute='$date[$j]'>\n";
echo "<total>" . $total[$j] . "</total>\n";
echo "</date>\n";
echo "</state>";
}
echo "</items>";
?>
[/php]Now, I can find the latest entries. I THINK what would be best for my needs is to just have different tables set up basically how you showed me and use $table to load the different tables. Thanks again for your help!

I'll format the xml correctly later


Tue Apr 21, 2009 4:31 pm
Report this post
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 5 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
© Copyright 2003-2008 www.php-editors.com. The ultimate PHP Editor and PHP IDE site.