Name: BigShow Date: December 27, 2007 at 15:49:20 Pacific Subject: database question OS: xp CPU/Ram: amd athlon 700mhz,256 k Model/Manufacturer: gateway
Comment:
I have a question on a database I am trying to build. I created a table called states that has an ID filed (numbers 1-51, primary key) name field and an abbrev filed.
On my site, when it goes up, i will have an option for a user to click on a state and when they do a list of college from that state will apear in the center of the page. How do I create this list? I can create another table with the same primary key ID but how do i create the list ?
You should create a second table for the colleges and have a column in that table for the state ID.
Let's assume your first table is called 'states' with the fllowing column name: 'id', 'name', 'abbr'.
For the 'colleges' table you would have, at a minimum, something like the following fields" 'id', 'name', 'state_id'.
The 'state_id' field is a foreign key linking the college record to a state record.
So, now let's assume that the user has chosen "California" as the state they want to see colleges for and that the state id for California is 5. To get a list of all the colleges in California you would run the following query:
Yes, that is very possible. There are several ways to do that. Not knowing how the rest of the site will be built I can't really provide a best practice method. But, let's say your college pages are built dynamically from additional data in the colleges table. Then you just need to the links to point to a college display page and pass the college ID on the query string.
However, if these links are supposed to link to those colleges' official pages then just include the URL as part of the data in the colleges table.
Basically, it is an html page that will have 25 state "buttons" donw the right and 25 down the left. When someone clicks a button, the list appears in the middle of the page.
If it's an HTML page how are you expecting to access the database? You need to use a server-side scripted page in order to access the database.
I would suggest the following: Don't create a state table. Instead just use the state abbreviation in the colleges table. Then create two state arrays (one for the left and one for the right). Then create the buttons so that they call the same page adding a state code parameter to the URL. You can then use that state code to detemine the colleges to display.
The example code below should have EVERYTHING you need to get started. All you would need to do is create the code to query the colleges table and display the list and format the output to your liking (NOTE: I have replace all BR and PRE tags with <-B-R-> & <-p-r-e-> because they are consumed by the forum, you will need to replace with valid tags.):
//Create the first set of buttons echo "<td style=\"width:120px;\">\n"; foreach ($states_ary1 as $abbr => $name) { echo "<button style=\"width:120px;font-size:8pt;\" onclick=\"window.location='".$_SERVER[PHP_SELF]."?state=$abbr'\">$name</button><-B-R->\n"; } echo "</td>\n";
//Create the content echo "<td style=\"width:460px;text-align:center;\">\n";
if (!in_array($_GET['state'], array_keys($states_ary))) { //No state selected echo "Please select a state.";
} else {
//Show colleges for the selected state $state_code = $_GET['state']; echo "You have selected the state of $states_ary[$state_code].\n"; echo "<-B-R-><-B-R-><-B-R->\n"; echo "Query the 'colleges' table using the following query<-B-R->and display the results:<-B-R-><-B-R->\n"; echo "<-p-r-e->\n"; echo "SELECT *\n"; echo "FROM colleges *\n"; echo "WHERE state = '$state_code'"; echo "</-p-r-e->\n";
}
echo "</td>\n";
//Create the second set of buttons echo "<td style=\"width:120px;\">\n"; foreach ($states_ary2 as $abbr => $name) { echo "<button style=\"width:120px;font-size:8pt;\" onclick=\"window.location='".$_SERVER[PHP_SELF]."?state=$abbr'\">$name</button><-B-R->\n"; } echo "</td>\n";
Michael, let me ask you something. I have the collegeid table with two fields, id (which links me back to the toher table) and collegelinks. I need to several links under each ID number.
For instance, under ID 2, which in table states is Alaske, I want to add 15 links into the collegelinks field. Where I am still learning this I cannot picture how to do this. I am going to go into the admin panel and create a query to do this, but how do I do it?
So I need it to go into the collegeid table under college links. Now I have at least 10 links to put under each state (ID).
I'm not really following what you just stated. If you have a states table with a record for Alaska and the ID is 2; then if you need to add 15 college records associated with Alaska, just add those 15 records to the colleges table with a state_id field value of 2.
In my first post I provided a logical format for the two tables. But, based upon your current needs I don't think you need tow tables - only one. You just need a table for the colleges. Because the number of states is not going to change any time soon there's no need to create a states table. (Did you run the code I provided above?)
I would suggest just a colleges table with the following fields: - id (auto-incrementing field) - name (name of the college) - state_abr (state abbreviation, e.g. CA) - link (the link to the site)
You could then get all of the college records for california with the query:
You are definately right about only needing one table, i understand that now and will make the adjustment, but I dont think I explained the question right.
Where i am getting confused is the adding of the links. I cannot picture this, i will create a mock diagram below to explain where I am lost.
the first row os the fields of the single college table
ID name abbrev collegelink 2 Alaska AK ?
the confusing part is how can I add multiple items to the field college link when the way i visualize it is there is only one space for one link. I know to you this sounds like a crazy question but to me I cannot grasp it and when I do I will understand a whole lot more.
In your example is "Alaska" the name of the college (e.g. Alaska State)? If not, and it represents the state name, then you need to go back and reread my previous posts. Each record (row) in the 'colleges' table will represent one college. Do you need to have more than one link per college?
Alaska Has 15 colleges.There are a total of 15 colleges and universities in Alaska that I wish to list. So does that mean I need 15 extra fields? If that is the case then for California there are 120 colleges I want to list?
How do I put these into the database. I have a file that I created with all the colleges so is there a way to insert them as a group?
Did you not see this sentence: "Each record (row) in the 'colleges' table will represent one college."? For some reason you think that each record is for a state. No. each record is for a college and one of the fields for each record is a state identifier.
This is a very easy problem. I have done 80% of the work for you above and am reluctant to do all of it for you. I know I have encouraged you in the past to find a good tutorial on PH & MySQL and work through it. I have a hard time believing you have done that.
Your 'colleges' table should look something like this:
Michael, I have done research on PHP and MySQL and have even signed up to take a couple classes in the winter but for now the interent and books is what I have to go by. I think I am either saying this wrong or you are giving me the answer and I just cant see it. I see the table you made and that is exactly how I pictured it but now, say under ID 1 which is AZ, ho do I put the other 75 colleges that are located within the state under the URL field?
ID 1 is NOT Arizona! Remember this is the 'colleges' table. Each record represents a COLLEGE not a STATE. However, each college record has a state identifier.
I already did the work for you in the script I posted up above. If you run that script and select one of the state buttons the query you would need to get a list of all colleges in that state is displayed on the screen. But, I guess I will explain it again.
Assuming you are working with the table above, you can get a list of ALL the colleges in Arizona with the following query:
SELECT * FROM colleges WHERE state_ab = 'AZ'
You will then have the list in a query result and you can then loop through all of the results to display the college and a link.
The code above, from what I can understand, will create the buttons on the page and then attach them to the database and make the hole thing work. Maybe im wrong. And again, maybe I just am not asking this the right way but you keep telling me how to access the college table and query it but if there isnt any colleges in the table to query then everything else is useless. I am trying to figure out how to insert all the college links into the college database.On the example you made abovem in the URL field, under the first ID, you have 1 college link listed, i have 15 others I need to store into that field, my understand is one link per field, how do I insert the other 15 links into the URL filed, I think this is impossible and I need to either create more fileds or tables. This is whats holding me up. I am not asking you to do this for me, i need to learn. This seems to come easy to you but I am no where near where you are intelectually when it comes to IT. I am doing everything I can to learn this.
Well, I had asked you previously if you needed more than 1 link per college. Is this the case? I.e. you would have 15 links for the university of Alabama. If this is correct, do the colleges always have 15 links each or are the number of links per college variable?
If the colleges can have multiple links then you will want to have two tables - one for the college records and another for their links. I could give some more info, but I would just be guessing until you answer the questions above.
Its not that there are several links per college, its that there are several links per state. i.e. Massachusetts has 45 colleges that will be listed in the table. Florida has 72 and california has 102 where alaska only has 7. So they are all different. So when someone clicks Massachusetts all the colleges that are within that state will appear.
Then I don't understand your confusion. Look at the mock table I posted above. Notice that there are several colleges listed for some states (ex: Alabama has three colleges listed, records 9 - 11).
I have stated this several times. EACH RECORD IN THE TABLE IS FOR A COLLEGE NOT A STATE. Enter a record for every college that you want. There will be 45 records for colleges that have a state identifier of MA, another 45 records with state identifiers of FL, etc.
You DO NOT put all the data for one state in a single record (it is a 'colleges' table - each record is for a COLLEGE). Simply create a table and enter your data in the format I displayed above and use the sample code I provided.
Michael I have been spending all night looking over what you said and I understand it now. Where I was getting confused was that, and you said this to me already, was that I was trying to use the ID as basically what I would need to search, I understand now that that means nothing because I can create one table and use the abbrev column to query my search, I understand perfectly now.
DID you write this yourself or find it on the net?
Alright Michael, so now I understand ow to build a general database using MySQL. I created the database for this project last night as you said to do using one table. I also copied and pasted the code from the test page you sent and now I am trying to put it all together.
First, can you break this down for me.. onclick="window.location I am pretty sure I know what this line means but I do not want to assume anything and go further. Also, from what I have read within the php file dont I have to attach to the database before anything will be output and if so why doesnt that show in the code from your test page.
I think I will be good after this, I hope so anyways?
The window.location is a javascript function to send the browser to a new page. That is the easiest way I know of to have a button call another page in this instance. However, that does require the visitor to have JavaScript enabled (95% of all users have JS enabled). Another approach would be to create 50 individual forms and put the state code in a hidden field and make the state buttons submit buttons.
As for your second question, yes you do need to connect to the database in order to run the query and output the results. As I already said I do not want to give it all to you as you would be learning nothing. Try to solve any remaining issues yourself. If you get stuck then post the code you are wortking, what you are wanting it to do and the problems you are having.
As for the link I posted, I created that in about 1/2 hour. I just used the code I provided to you previously as well as the add'l code to query the database and create the output list. Most of that time was used in getting the data for the database which I extracted off of here (http://www.utexas.edu/world/univ/alpha/) and parsed using Excel.
Thats funny Michael because I got the info for the database from the same site, bur I went through and did everythingmanually last night. I wish I had thought about using excel to parse it.
Michael, I am trying to figure this out. Connecting to the database isnt a proble, I have seen enough examples to do so. Do me a favor, roughly tell me what I am trying to do without giving me code. Also, where do I out the code.I know I have to open the database right after the body tag but then do I just write all my php there or do i put some there and more after. Its a weird question but I need it answered
Here is the code I have so far which is not working. I have my collegelist.php page which only contains the code from the test apge you made asking to click a button. When a button is clicked it brings you to college.php, this is the code I have so far (only the php)
<table width="400"> <?php // Connect to DB $dbh = mysql_connect('localhost','colleges','passwordhere') or die (mysql_error()); mysql_select_db("states') or die (mysql_error()); state_abb = $_GET['state']; //Create the query $query = "SELECT url FROM states WHERE state = 'state_abb'"; //Run the query $result = mysql_query($query) or die(mysql_error()); //Extract the data from the results $data = mysql_fetch_assoc($result);
Alright, I figured it out. It works fine I just need to make it appear better. My only question now is why does mine take a second to load but yours is lightning quick. I did notice that I used two different php pages. One when someone enters and the other to list the sites. Take a look and tell me what you think.
How do you make it so that it is only on one page, I have it so that when someone enters the page it is collegelist.php, when they click a state it turns into colleges.php.
How did you make it so it took the state and put it at the top, I assume that I could just reference it through the abbrev, I do have a statename filed. You know what I mean, how you have "You have chosen Massachusetts" when someone picks MA.
I there a way that I can put a full article in the BD so that when someone chooses from a list of articles to read that one pops up. I will gather the articles from the web, places such as ezine.com. Would I just use a VARCHAR(xxx) and place it in that and if so will it keeps its paragraph format?
"How do you make it so that it is only on one page, I have it so that when someone enters the page it is collegelist.php, when they click a state it turns into colleges.php."
The first sample code I provided did exactly that. So, I'm not sure how to respond.
"How did you make it so it took the state and put it at the top"
I will review the sample code and try and figure it out. AS far as the third question.
Basically, i will have pages that have several articles on a particular subject. When someone comes to the page, it will have an article displayed and a list of links from different articles on the side. When someone clicks an article, i want it to appear in the center of the page and for the other article to dissapear. I assume I can do the same thing and create a field for articles and do the onclick javascript to make it appear. I think my main question is say the article has three different paragraphs. How would that go into the database. As a whole document? Will it keep its format (i.e. seperated paragraphs) or will it all jumble up to one group of letters?
Yes, that could be produced using simlar funtionality as above, but you would probably query the database to generate the list of articles as opposed to having a hard-coded array.
As for how the data will be entered - the answer is it depends. If you were to enter the text as you woould in a text editor or in a text box, then the line breaks would not be interpreted correctely if you were displaying then on an HTML page.
The solution to that is to use the built in funtion nl2br() which converts newline characters into HTML BR tags.
Michael, can you take a look at the code below. I am tring to crete the navigation links on my index page. The links will go down the left side of the page. I am getting parse error. I find one and another pops up. It keeps saying im missing a "," or ";". The database is called mainlinks, there are 3 fields, id, linkurl, webpage. linkurl is the page url and webpage is the nake of the page that goes with that url.Thanks
<?php // Connect to DB $dbh = mysql_connect('p41mysq.secureserver.net','mainlinks','password') or die (mysql_error()); mysql_select_db('mainlinks') or die (mysql_error()); $query = "SELECT linkurl, webpage FROM links"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) {
The biggest problem I see is that you are not "escaping" the double quotes in your strings. If you use double quotes to define a string you need to escape the double quotes that are part of that string, and vice versa when using single quotes. The main difference between single and double quotes is that PHP variables within double quotes will be parsed:
Here is your code with the corrections that I see (there may be others since I am not running it):
<?php
// Connect to DB $dbh = mysql_connect('p41mysq.secureserver.net','mainlinks','password') or die (mysql_error()); mysql_select_db('mainlinks') or die (mysql_error());
$query = "SELECT linkurl, webpage FROM links"; $result = mysql_query($query) or die(mysql_error());
if (mysql_num_results($results)>0) { //Display tghe results
I get this error. I am looking up the function to see what exactly it does. From what I can gather it returns the number of rows after a SELECT statement is used. Why is it calling the following error. I did notice insode the brackets you put "$results", i switched this to "$result" but it still called the same error. Any ideas?
Fatal error: Call to undefined function: mysql_num_results() in /home/content/s/m/a/smartin1017mus/html/index.php on line 54
FYI: Don't use the "Alert Me" function when replying to a thread that I have already posted in. If you add a response to a thread I have already posted in it will flag it in my message list. Thanks.
Michael, now that I have figured out the college list, would you mind posting your full code so I can compare it to mine, for some reason mine is eal slow in loading and wyoming doesnt even list the colleges. Also, how do I play will the styles of the buttons. I have an external CSS file but it doesnt really take too well, is it better to incorporate all the css on the webpage of to have an external file?
I'll post the code when I get home. As I said before your site ran fine for me. Don't know why WY results wouldn't display - just make sure you are using the exact same 2 character code in the array and in the data.
I don't think this functionality has anything to do with the speed sssues you are having. I just went to your site againa and just navigating "standard" pages was excrutiatingly slow. Seems like you have an issue with the serer in general.
As for styles there's no reason an external CSS file wouldn't work. CSS doesn't care if the styles are defined externally, in the head, or in-line. You will get the greatest amount of flexibility be defining them externally.
The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this site. Computing.Net and Computing.Net, LLC hereby disclaim all responsibility and liability for the content of Computing.Net and its accuracy.
PLEASE READ THE FULL DISCLAIMER AND LEGAL TERMS BY CLICKING HERE
