?
Solved

How to pull out information from database using php

Posted on 2008-10-14
4
Medium Priority
?
385 Views
Last Modified: 2013-12-13
I have a database of zipcodes in US. It was dumped in mysql. I wondering to make a php script that can pull out these informations and display to users.

User come to website >> Zipcode Search >>> Information gets pulled from database and being displayed to used.

For example.

http://www.zip-codes.com/zip-code/11369/zip-code-11369.asp

I have attached a code that was added in mysql.

Thanks


/* 1.) Drop Table if it Exists */
DROP TABLE IF EXISTS ZIPCodes;
 
 
/* 2.) Create Table */
CREATE TABLE ZIPCodes (
	ZipCode char(5) NOT NULL,
	PrimaryRecord char(1),
	Population int,
	HouseholdsPerZipcode int,
	WhitePopulation int,
	BlackPopulation int,
	HispanicPopulation int,
	AsianPopulation int,
	HawaiianPopulation int,
	IndianPopulation int,
	OtherPopulation int,
	MalePopulation int,
	FemalePopulation int,
	PersonsPerHousehold decimal(4, 2),
	AverageHouseValue int,
	IncomePerHousehold int,
	Latitude decimal(12, 6),
	Longitude decimal(12, 6),
	Elevation int,
	State char(2),
	StateFullName varchar(35) NULL,
	CityType char(1) NULL,
	CityAliasAbbreviation varchar(13) NULL,
	AreaCode varchar(55) NULL,
	City varchar(35) NULL,
	CityAliasName varchar(35) NULL,
	CountyName varchar(45) NULL,
	CountyFIPS char(5) NULL,
	StateFIPS char(2) NULL,
	TimeZone char(2) NULL,
	DayLightSaving char(1) NULL,
	MSA varchar(35) NULL,
	MSA_Name varchar(150) NULL,
	PMSA char(4) NULL,
	PMSA_Name varchar(150) NULL,
	CSA char(3) NULL,
	CSA_Name varchar(150) NULL,	
	CBSA char(5) NULL,
	CBSA_Type char(5) NULL,
	CBSA_Name varchar(150) NULL,
	CBSA_DIV char(5) NULL,	
	CBSA_DIV_Name varchar(150) NULL,
	Region varchar(10) NULL,
	Division varchar(20) NULL,
	MailingName char(1) NULL,
	PreferredLastLineKey varchar(10) NULL,
	ClassificationCode char(1) NULL,
	MultiCounty char(1) NULL,
	CityStateKey char(6) NULL
);
 
 
/* 3.) Create Indexes on most searched fields */
CREATE INDEX Index_ZIPCodes_ZipCode					 ON ZIPCodes (ZipCode);
CREATE INDEX Index_ZIPCodes_State					 ON ZIPCodes (State);
CREATE INDEX Index_ZIPCodes_CountyName				 ON ZIPCodes (CountyName);
CREATE INDEX Index_ZIPCodes_AreaCode				 ON ZIPCodes (AreaCode);
CREATE INDEX Index_ZIPCodes_City					 ON ZIPCodes (City);
CREATE INDEX Index_ZIPCodes_Latitude				 ON ZIPCodes (Latitude);
CREATE INDEX Index_ZIPCodes_Longitude				 ON ZIPCodes (Longitude);
CREATE INDEX Index_ZIPCodes_CityAliasName			 ON ZIPCodes (CityAliasName);
CREATE INDEX Index_ZIPCodes_PrimaryRecord			 ON ZIPCodes (PrimaryRecord);
CREATE INDEX Index_ZIPCodes_CityStateKey			 ON ZIPCodes (CityStateKey);
 
 
 
/* 4.) Create Extended Column Information */
ALTER TABLE ZIPCodes COMMENT = 'U.S. Zip Code Database  Deluxe (from www.zip-codes.com)';

Open in new window

0
Comment
Question by:nomie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 31

Expert Comment

by:Frosty555
ID: 22716593
Basic mysql in php:

1) Connect to the mysql server

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');

2) Select the database you want to use

mysql_select_db($dbname);

3) Execute a query against the database

$result = mysql_query("SELECT * FROM ZIPCodes");

4) Walk through the records

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo "Zipcode:{$row['zipcode']} <br>" .
         "City: {$row['city']} <br>";
     // process data however you want to, row by row.
}


Close the mysql connection.

mysql_close($conn);



A complete tutorial is available here that walks you through it from beginning to end.

http://www.php-mysql-tutorial.com/
0
 

Author Comment

by:nomie
ID: 22717862
I am reading the tutorial but my concern was search. I am very new at this and I am trying to learn how to fetch from database using search.
0
 
LVL 31

Expert Comment

by:Frosty555
ID: 22718174
search for records where a field matches a keyword

SELECT * FROM ZIPCodes WHERE sometextfield LIKE '%somekeyword%'

search for records where a field matches one of several keywords

SELECT * FROM ZIPCodes WHERE sometextfield LIKE '%somekeyword%'
OR sometextfield LIKE '%somekeyword2%'
OR sometextfield LIKE '%somekeyword3%'

search for records where a field contains ALL keywords

SELECT * FROM ZIPCodes WHERE sometextfield LIKE '%somekeyword%'
AND sometextfield LIKE '%somekeyword2%'
AND sometextfield LIKE '%somekeyword3%'

It is all fairly fast so long as your database doesn't grow into the millions of records. Then other searching methods, like keyword tables or fulltext indexes become something worth thinking about.
0
 
LVL 31

Accepted Solution

by:
Frosty555 earned 600 total points
ID: 22718177
More info on mysql pattern matching clauses:

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

Full-text search and full-text indexes:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question