Help understanding MySql "Basics"

[Netwrkengeer]

Ok I'm trying to set up a DB using MySql and Php.

Well the thing is I don't understand how the auto increment feature works and why I would and would not use. "How do I take out info from the DB if I don't know the auto incremented #"

the second thing is, I understand the DB design aspect a little, and I Understand "DB normalization" enough to setup a DB, but I don't understand how to join the tables, or how to retrieve the full info.

Third (Sorry) I don't know how to utilize the info, in the "background" i.e. Lets say a user goes on my web site and does a search for XYZ, but my database has to find X then Y then Z. then put it together to output it.

Please help. Theory and code examples would be a great help, but what ever you can spare is cool.
Thanks

 

[rycamor]

Auto-increment is just that. You shouldn't really have to think about it. If you create a table with an auto-incremented primary key, then whenever you insert a record into that table, just don't insert anything into the primary key column, and it will automatically create a number that is one integer higher than the last record. You *can* explicitly enter numbers if you want, and then the next record will autoincrement from that number upwards. Usually, you will make a primary key to be UNIQUE also, which means that if you try to explicitly enter a number that is lower than the last, the database won't allow you, because that key already exists.

Now, let's say you just inserted a record, and you want to know what that auto-incremented primary key was. PHP provides a very nice little function called mysql_insert_id(), which retrieves that last id number for you. The reason these keys are so important is to establish an automated way to make sure that every record is unique. Otherwise, you have to really work at your code to make sure you don't have duplicate query results, or that the "Joe Shmoe" you are geting from the "users" table is really the Joe Shmoe you want. In other words, now you have an ID number for every single user in the table, and thus you have an easy way to relate other tables, such as "user_actions", or some-such.

I will come back later with some JOIN examples for you ;-).

 

[Netwrkengeer]

You are the man, you always give the best answers.

But i have more, let me eleborate a little on the situation.
The way I might use to explain the last question is. I have a program where a patient can schedule a doctors appointment, during the signup to use the service, the patient inputs their personal information, name, address, insurance, etc. But the part I can’t get to work, or even understand how to do. Is when the person inputs their address the program has to take that home address and convert it to the latitude and longitude equivalent, then store it in the DB with the rest of the information, which should occur without the user viewing the process. Then later on the program will need to compare 2 sets of latitude’s and longitude’s (The doctors address and the patients address) to decide what doctor in the DB is closest to the patient. And all the patient should see is the end result.

 

[rycamor]

That's quite a different question, and not really a MySQL issue. To do what you describe you will need two things:

1) The calculations needed to convert ZIP codes and addresses to coordinates.

2) The calculations needed to convert LAT/LONG coordinates to spherical cooredinates for distance calculations, taking into account the curvature of the earth.

I have done work in these areas, but I can't just provide a complete solution for free to you on the web. I will be glad to look over any attempts you have made, and help you troubleshoot them, though.

There are plenty of places on the web where you can find the trig formula for part 2. For part 1, you will need to spend some time on the US Postal Service website, and perhaps the U.S. Geological survery, at

http://www.usgs.gov.

 

[Netwrkengeer]

I would never expect you to do anything like that for free, I'm just confused on where it all goes.
But I have another question. I hope I'm not a pest.
Well, here it goes,
If I'm using the zip code info from a program (i have software from sourceforge) how does Php extract that info?
Thanks
Robert

Ps if I get any respectable code done, I would love for you to look at it, I'm sure there will be problems.
Thanks

 

[rycamor]

Nope, not a pest ;-)

Since I don't know the specifics of your ZIP code software from SourceForge, I don't know exactly what your question pertains to. Exactly what kind of software is this, and what does it do?

Obviously, PHP is just another programming language, and it has all the capability needed to do what you want. The only problem is in how to do something meaningful with the data.

Also, I need to know just to what degree of acuracy you need to establish your coordinates. ZIP codes alone can only give you very rough estimate of the coordinates, but if you need to establish close distances, for example from one town to the next, then you either need some sort of very large geographical database of cities in the US, along with coordinates, or you need to find some sort of online resource that you can query in real-time, that can take an address and give you back the latitude and longitude. I'm sure such services exist, whether they be government-run, or private. It wouldn't surprise me if the US Postal Service itself has provides this service.

 

[Netwrkengeer]

All I have from the zip software is the zip code dump file, (zip.txt) and this is how it looks

11252,NY,Brooklyn,42.203900,-75.10100
11254,NY,Brooklyn,42.203900,-75.10100
11255,NY,Brooklyn,42.203900,-75.10100
11256,NY,Brooklyn,42.203900,-75.10100
11300,NY,Flushing,40.455500,-73.49000
I also have a website that I can query for similar info,

http://www.zipinfo.com/search/zipcode.htm

at this point I don't need something to accurate, but in the future I will.

So I have more questions,
1. How do I use the zip.txt file, Do I need to change the format to "delimited" or something else or can Php query the file the way it is?

2. Is it possible to have some one query my DB and ask "from 11300, What is every city in a 200 mile radius". One this question I have no idea how to do it.

3. And in the future ("after I’ve hired you full time&quot Is it possible for question #2 to become more accurate down to distances between addresses, and query driving distance not just a geographical radius, (a driving distance would have to compensate for lakes and rivers, i.e. if some one lived in north west Michigan and wanted to go to north east Wisconsin geographically that would be 30 miles but actual driving distance is 300 miles to compensate for the drive around lake Michigan)?

 

[Netwrkengeer]

In addition to the previous post I have more,
I took your advice and found sites to query
Here we go
The way I figure it, my focus is the USA right now, being that the accuracy of the distancing is not that critical I can use Lat, Long, coordinates to estimate distance.

here is my plan, I first need to get the zip+4 codes. I can query the us postal website, everytime a user enters info.
i.e. -

http://www.usps.com/ncsc/lookups/lookup_zip+4.html

(OK how do I query an Html form, and how do I extract the info from the Html results and put them in the Db?)

from that I need to get very accurate Lat Long points, that I got as well,

http://www.geocode.com/scripts/eagle/eagle.pl?cmd=td_i.

after I store the info from the postal website in the DB, I have to grab the zip+4 # from the Db and input it into the geocode site, I think with this site I can query it with the address and set up the zip+4 code as a variable
(but how do I extract the info from the Html results and put them in the Db?)

as for deciding on distance I will use the Lat and Long point a loose ruler (each decimal = an 1/8 mile, or what ever the average calculation might be.)

Well, This is everything I know and don't know at this point.
I'm not asking you to write a sample script for me, (although I wouldn't complain if you did) but just some pointers or maybe a sample of something similar from a site like zend.com or a past project or something like that. maybe even just a command or statement. I don't want to monopolize your time.
Thanks
Robert

 

[rycamor]

Whe

http://www....

This isn't by any means an ambitions project here ;-)

To answer your questions in order:

Question #1. Your text file is already comma-delimited. You should import it into a database (try this great tool at

http://phpmyadmin.sourceforge.net/)

and then you can have fast access to any zip code you want. PHP can work form your text file, but it will be a very slow search, compared to a database.

Question #2. Most databases don't have built-in geographic query techniques, although I think some do. But anyway the math to work out this method is not too hard, and can be done in the database or in PHP. See this link for a discussion of similar stuff:

http://forums.devshed.com/showthread.php?threadid=18942&forumid=4

Question #3. You're insane. Certifiable. So when can we get started? Seriously, though; that becomes a very big project. I believe it is possible to a degree, but we're talking about getting into the neighborhood of supercomputing, at that point. It would be one hell of a fun project to work on though.

Next post:

You can use PHP to "pretend" to be a browser and query an HTML form. See

http://zend.com/codex.php?id=77&single=1

for a simple example. If you really need more complex form posting and retrieving functionality, check out

http://php.net/curl.

All for now... have fun.

 

[Netwrkengeer]

You are the man, perfect answer, you score a 10.0
as for working for me with me on this project, would you consider moving to New York.

and I posted a question in the free bsd section,
maybe you can help me here with it.

How do I setup a cron job?
I will be accessing my server remotely. Can I setup a cron job 'Via' Ftp, or do I need to telnet?
What information is required to setup a cron job?
and what is the format?
also can I ftp into my server and change the servers time?
Well you have an Idea of what I'm doing so, the cron job would need to run once a day to match up the users that queried the Db earlier. ect.
Thanks
Rob

 

[rycamor]

Rob,

email me.

rick@brainscraps.com

 

[Netwrkengeer]

OK