Zip Code Database mysql
View statistics of clustered data in bar chart
In this article I see the statistics about how to make the data go, the cluster In the form of a bar chart shows how we will use PHP and MySQL. He is accompanied by a code how the operation can be performed Provides real-world scenarios to demonstrate.
Data View statistics of clustered bar chart is somewhat similar to the shape of a Of reality is a choice between two properties. But the actual Is much more complicated case. As with most cases, ER diagram, entity normalization rules for each situation, the stronger Obedience is often associated with other people. In addition, this In such cases, the data, as well as two-dimensional, but, instead, It is multidimensional.
What have we in the following Let ER diagram) in the form of a clustered bar chart Statistics show (now you can. How can we do? This article will attempt to describe is the solution.
Prerequisites
You are running in conjunction with PHP and MySQL Apache Web server, you must have. PHP is a fair amount of knowledge Also is required. With this article I of the demo-mail archive To provide.
The database consists of four tables.
- Student
- Program
- Subjects
- Score
The first three tables, each table and a lot of the markers Relationships are.
SQL statement to create this table Follows:
Mark Mark
Creating Tables Sign (
Id int (11) NULL having to auto_increment,
student_id int (11) default '0 'not NULL,,
Program int (11) default '0 ', NULL, do not,
Title int (11) Default '0 ', Null, not
Code of int (11) default is '0 'not NULL,,
entry_date date,
PRIMARY key (id)
);
Program Table
Tables Program (
Id int (11) NULL having to auto_increment,
Program varchar (255)
PRIMARY key (id)
Unique Programs (Program)
);
Student table
Creating Tables Student (
Id int (11) NULL having to auto_increment,
Name varchar (100)
PRIMARY key (id)
);
Courses Table
Create a table subjects (
Username int (11) NULL having to auto_increment,
Title (255) varchar,
PRIMARY key (id)
Unique Courses (subjects)
);
The user interface is HTML, all 4 Is used to populate the table. Fill the first program Subject table, then populate the student table and the final, to record Marks on the table to provide.
Nanyi the four tables I do not have a description can fit under the impression that This will be a trivial task as soon as the HTML in the form Has been filled. Rather, I see statistics that are created Should focus on how.
We have an input HTML form and PHP related Easy to handle in the same way that strengthened the code number is written. Users enter the code snippet for the form of three net-related Web Control is composed of data from three different tables. Now, these three controls are an array of Web controls:
"? in php
$ Query = "SELECT s.id, s.name from students in the order of the s s.name";
$ Result = mysql_query ($ query);
PHP is?>
"Select name =" "size = "10" Revenge of the [] and sel_id style = "background-color: # ffffe8;">
"? in php
($ Row = mysql_fetch_row ($ result)) while
Echo "" option value = ". $ Rows [0]. ">". $ Row [1]. "" / Option> "" I;
PHP is?>
"/ Select>
The remaining 2 Web controls in the same way also Will be filled:
"? in php
$ Query = ", p.program blood-order program p.program "p.id choice;
$ Result = mysql_query ($ query);
PHP is?>
"Select the name = "" Size = "5" Revenge of the [] and sel_program_id style = "background-color: # Ffffe8; ">
"? in php
($ Row = mysql_fetch_row ($ result)) while
Echo "" option Value = ". $ Row [0]."> ". $ Row [1]." "/ Option>" "I;
PHP is?>
"/ Select>
"? in php
$ Query = "SELECT s.id, s.subject of the s Subject to the order of the s.subject ";
$ Result = mysql_query ($ query);
PHP is?>
<select name= "sel_subject_id[]" Size = "5" many styles = "background-color: #ffffe8;">
"? in php
(While $ row = mysql_fetch_row ($ result))
Echo "" Option Value = ". $ Row [0]."> ". $ Row [1]." "/ Option>" "I;
PHP is?>
"/ Select>
In addition, a range of dates on the same page You can provide input. Code snippets are no longer Will have to explain it twice, once on a date (from) and Again for the date (for) has been repeated.
"? in php
$ Months = Array (1 => "Jan.", "February", "March", "April", "May", "June", "July", "8", "September", "October", "November", "12 Mon ");
PHP is?>
<TR> <Td colspan = 2 = 2 alignment center> "table border = 0>
<TR> <TD> Date (From) / td> <
<TD>
<selectui name="sel_day_from">
"? in php
($ I = 1; $ i "10; $ I + +)
Echo "<option of value = 0 ". $ i."> ". $ me." "/ option>" "I;
(About $ I = 10; $ i "32; $ I + +)
Echo "<optionui value=".$i.">". $ Me. "" / Option> "" I;
PHP is?>
"/ Select>
<Select the name = "sel_month_from">
"? in php
($ I = 1; $ i "10; $ I + +)
Echo "<optionui Value=0".$i.">". $ Month for $ i]. "" / Option> " "I;
($ I = 10; $ i "13; $ I + +)
Echo "<optionui value=".$i.">". $ Month for $ i]. "" / Option> "" I;
PHP is?>
"/ Select>
"? PHP is $ dt_y = intval (date (Y) two); PHP is?>
"Choose a name = "Sel_year_from">
"? in php
($ Before = $ dt_y; $ I> 2000; $ i -)
Echo "" Option value = ". Sign $, or. ">". $ Me. "" / Option> "" I;
PHP is?>
"/ Select>
"2 />" / no need> a td
This is just a way that provides more options. 2 You need very little processing is up. In addition, A new dimension to your bar chart will be processed.
So far We enter the form of the four kinds of user-selectable criteria Are:
- Student Name
- Subject
- Program
- And the range of dates
Our database is currently In preparation for a bar chart will be subjected to the following picture.
File handling in the PHP code, and select Loop 3 Collect_Data (....) and as Web - The input does not appear in multiple years old statistics_action.php Features of the support files, configuration control and to control one of the dates Can.
The fourth choice in the range of date 3 appears to select multiple controls to create more than one Can be Added. Choose one.
This code is processed Three nested loops in the same way about the requirements and is composed of. You increase or decrease their / new dimension to the drop Adds.
$ Dt_from = $ sel_year_from. $ Sel_month_from. $ Sel_day_from;
$ Dt_to = $ sel_year_to. $ Sel_month_to. $ sel_day_to;
$ Dt_valid = "false";
$ Back = "The <a href="" onclick="history.back()"> Back </>";
(Intval ($ sel_month_from), intval the ($ sel_day_from) checkdate (you! (
$ Sel_year_from)))
$ Date ...</ font> in the "=" <font color=red> the wrong message;
elseif a (! ((intval the ($ sel_month_to) checkdate, intval ($ sel_day_to)
$ Sel_year_to)))
$ Message = "<font 2 Date ...</ font> the "wrong color = red>;
Others
(
$ Message = formate_date ($ sel_year_from ."-".$ sel_month_from ."-".$ sel_day_from). "----".
formate_date ($ sel_year_to ."-".$ sel_month_to ."-".$ sel_day_to);
$ Dt_valid = "true";
)
Two global variables with global variables (in the (save as Date (per room) and the date of the previous code block) access to the visible Customize Wed funtion) inside.
$ Dt_valid flag roles. In fact, Then if you are in process, otherwise, the message Is displayed. We use () function, passing arguments If you have the wrong date, which returns true or false checkdate.
After all of the correct date if the date range in the $ message variable Are stored.
Echo "<table> <TR> the bgcolor = whitesmoke>" to <Td. $ Message. "..." . $ Back. "" 2 / "" / I need to> "/ Table> to> a td;
Next, our upper limit of Initializes three variables for three nested for loop:
$ Total_counts_student_id = Calculate ($ sel_id);
$ Total_counts_program_id = calculate ($ sel_program_id);
$ Total_counts_subject_id = calculate ($ sel_subject_id);
(($ Total_counts_student_id> 0) if & & ($ total_counts_program_id> 0) & & ($ total_counts_subject_id> 0) & & ($ dt_valid == 'true'))
(
Echo "table border = 0 cellpadding = 0 cellspacing = 0> Mark <caption> <h2> students </ h2> </ caption> "" I;
Input In the form of a list, each selected for the program, it is Since each student's program will scan the data :
($ C = 0 a, $ c on the dollar total_counts_program_id <; $ C + + to)
(
($ B is about = 0; $ B a "dollar total_counts_subject_id; $ a + + a B)
(
Data / / collection
($ = 0; $ <$ total_counts_student_id; $ + +)
(
Collect_Data ($ sel_id [$], $ sel_program_id [$ C], $ sel_subject_id [$ b to]);
sel_id loop) / / end
If ($ sum> 0) / / If the data is not ...
(
/ / Graph Drawing
$ A calculation = ($ student_data) total_counts;
($ I = 0; $ i "$ a is total_counts; $ I + +)
(
$ Bar_width = multi_factor a * ((100 * $ student_data) $ i]) / $ sum [;
$ Bar = ($ or% of the bar_counter). "_bar.gif";
Echo "<TR> <TD> The <img src='images/$bar' width=$bar_width height='10'>";
/ multi_factor number_format ($ bar_width Echo, 2. ','')."% (". student_name dollars dollars [I]. ",." Dollar program i]. ",." $ Title [U.S. $ m] Dollars [. ") / Td <> </ I need to>" "I;
)
Table type / / Display data
Echo "<TR> <TD> <table border = 1 bordercolor = blue>";
Echo "<TR> <th> the name" The car> <th> Program "/ 1> <th> title "/ a> <th> Mark" / a> <th> date "/ 1> "/ I need to>";
($ I = 0; $ i "$ a is total_counts; $ I + +)
(
Echo "<TR> <TD>". $ Student_name dollars I]. "" / td> <TD> ". $ program dollars, I]." </ td> <TD> ". $ title [$ I]. "</ Td>";
Dollars [(isset ($ student_data If])) echo "" <TD> . $ Student_data dollars I]. "</ Td>";
Others "No" </ td> <TD> Went Echo;
Echo "<TD>". formate_date a ($ student_dt) $ i]. "" / td> </ I need to>, "" I;
)
Echo "" 2 / table> "2 /" / I need to> "> a td; for $ X = 0; dollar totals = 0; echo "<TR> <TD> <hr> <2 /" / No need>> a td ";
($ I = 0; $ i "$ a is total_counts; $ I + +) / / / an array of purification ... All the better to cleanse the array .....
(Array_pop ($ student_data);)
)
subject_id loop) / / end
program_id loop) / / end
A) / / kkeutmyeon
The second loop consists of the last of three parts: Body's way:
- - The innermost loop of each student's name Repeat with. Collect_Data it to function (.. call) and store it as well An array variable to store the sum of 5: $ student_name [], $ program [], $ title [], $ Student_data [], and is $ student_dt. $ Total. As student_data $ amount Since the split dollar, we can calculate the ratio. The width of the image depends on the probability. In addition, the intellectual Using the code is a new bar every time the data Used to draw. If so, it is appropriate for a bar chart Draws.
- Iterating the information displayed in a tabular format and data Search by five array above.
- The three parts of the table Exit.
- This portion of the array to remove the above five. It is essential that the next iteration in the array is smaller Previous data may be recorded.
Features Collect_Data ($ A_no, $ program_id, $ subject_id)
(
Global $ student_name;
Global $ application;
Global $ title;
Global $ student_data;
Global $ student_dt;
Global $ Sum;
Global $ X;
Global $ dt_from;
Global $ dt_to;
The above variables are global, so the users of their values Defined functions can be accessed on the outside, otherwise, They can be local variables, because they render Declared out of the range.
$ Query = "select s.name the p.program, m.entry_date m.marks the sb.subject
Students with high marks on the inner surface of = m.student_id s.id Join uirobuteo s
Programs like blood in m.program = p.id inner join
Sb on the inside of the subject sb.id = m.Subject Join
Where s.id = a_no dollars and p.id = $ program_id and sb.id = $ subject_id and m.entry_date> = $ dt_from and m.entry_date <= $ dt_to ";
$ Result = Mysql_query ($ query);
($ Row = mysql_fetch_row ($ result) while)
(
$ Student_name of x] row [0] = $ a $ [;
$ Program x] row [1] = $ a $ [;
$ Title [dollar X] = $ row [2];
$ Student_data of x] of $ [= $ row [3];
$ Student_dt of x] row [4] = $ And $ [;
$ Sum + = $ row [3];
$ X + +; / / increase the array .... Counter
)
funciton of the) / / End Collect_Data ()....
We have a function in Four kinds of queries on the table inside - to extract data through join. You can add a table needs more party. Query 5 of 5 for the value stored in the global array is extracted. Now, $ x is Counter role. This all calls for an increase in You can find a record of its value to make sure.
Processing time is calculated based on the following code is :
Function getmicrotime ()
(
List ($ usec, $ sec) = explode ("", microtime ());
Return (() $ usec + () $ sec) float float;
)
$ Start_time = getmicrotime ();
$ Time_diff = () (getmicrotime () float - $ start_time);
Echo "<TR> the colspan = 6> <i> Processing time <td: - ". Number_format ($ time_diff, 3 ,'.','')." seconds "in the / I" "2 /" / I need to> "> a td;
Clustered bar chart We built a really simple, even if the multi-columnar information Have. How easy it is to the logic requirements Can be customized based organization. How many tables To implement this technology across closely Database Normalization In particular the idea and gave.
About the Author
phpinterviewquestion.com is a interview site , This is for those who want to
Learn about technology, preparation for interview; learn new things in php, .net, javascript, jquery, .htacess, mysql and many more.
phpinterviewquestion.com
MySQL's query design. Table 2 zone requires a search of service The way to do a few things to think about.?
I Database Have in the table 2. Table 1, the visible, county, state, zip code (Including the United States overall) in Table 2, the attorney's name, company name ,.... City State, county, zip code, county 1, County 2, County3, county 4 (can be a lawyer's service area - four counties), I code Lawyers for the zipper by trying to enter a search query, select the MySQL eulha Can be created. But the result, I am all in Would you like to see a zip code by zip code in the county Entered. I (a Chicago, Illinois is Cook County) Then every zip code in the county, all in Cook County Lawyers would like to show it is selected, including 60 601 .. Including 60 602 60 603 60 601 60 604 60 605, therefore, selected from 60,601, but lawyers .. Show all attorneys, including 60,605 with 60,604 zip codes 60602 60601 60603
It), use an alias (two tables are 14 times better normalization table Will be required to join (DB and Table 2) ... If not, - for each county 2 Table 1 Table Services in the county. You must Sign in with a single to Normalized DB
What's At Stake in the Big Data Revolution?
For practitioners, big-data success could be the key to survival or opening up vast new markets. Among vendors, the incumbents have the most to lose -- an unstated subtext of my recent Big Data feature -- but fresh survey data suggests that MySQL may be a spoiler. In case you missed "Fast and Big" in the August 9 issue of InformationWeek, you can download it here and read my trend analysis on in ...
Which is better? Calculate haversine distance on the fly, or precalculate distances?
I am creating a MySql query that requires distance calculations. E.G. Find all items within 20 miles of zip code...
I have a database of aprox. 43k cities with the long, lat and zip. There is a potential to have millions upon millions of posts within say, 500 miles (the largest search distance). My gut tells me that it is more pragmatic to precalculate all of the distances for anything 500 miles or less.
There are 900 million distances to calculate(roughly) if I do all of them. that is a large table to access if speed is an issue. But, If I limit all of those calculations based on the max search distance of 500 miles, I could reduce that by 70+%.
The question is... is it that much better to precalc the distances rather than having sql do that math on the fly?
So, you are saying that you equation is acceptable for 'on the fly' calculations vs. a precalculated look up table... or are you saying that I should use your equation and still precalc?
For these short distances you don't need haversine equation, just calculate as on a flat plane surface. The haversine formula is an equation important in navigation, giving great-circle distances between two points on a sphere from their longitudes and latitudes. Use x,y as Long, Lat where Long. is corrected by cos Lat. Then due Cartesian to Polar conversion. Where r = SQRT (X^2 + Y^2), you don't care about Theta = arctan (y/x).
Postcards are a wonderfully fast and efficient way of getting specific messages, like Zip Code Database Mysql, delivered in a permanent format to your own mailing list and other specific mailing lists you have purchased.