Page 2 of 2
Re: [solved]help with testing
Posted: Mon Nov 29, 2010 4:09 pm
by McInfo
I don't quite understand what you are describing, but I see what is wrong with the query now (I don't know why I missed it before).
Code: Select all
WHERE names.namesID = phone.phoneID
The names.namesID field is not related to the phone.phoneID field. The clause should be
Code: Select all
WHERE names.namesID = phone.namesID
Re: [solved]help with testing
Posted: Mon Nov 29, 2010 6:25 pm
by cybershot
that doesn't work. I get an error saying that phone.namesID is an unknown column.
Re: [solved]help with testing
Posted: Mon Nov 29, 2010 6:54 pm
by cybershot
I think what I am trying to do is simple, I just can't figure it out. Let me try and explain it again
I am trying to lay the foundation to learn how php works with databases. Most tutorials leave out the important stuff. so i am trying to figure it out myself with a test database. When you make a database in access, you define the primary and foreign keys. the primary key in the names table will link with the foreign key in the phone table. so it would look like this
names table
namesID = primary key
Phone table
namesID = foreign key
so in access, it looks like this
You can see that there is a relationship between the primary key in the names table and the foreign key in the phone table. A 1 to many relationship. The problem I am having in phpmyadmin is that there doesn't seem to be that relationship even though I am defining it. Here is the relationship showing in the relationship view
but the namesID field in the phone table shows up as null. I would think that it should have an index in it that matches to the names table.
so my thinking is that when i go to make my query in php, I want to select the names that have matching phones numbers or that have phone numbers. Or that I can select just name or ID for the names and all the other information will show up automatically. Eventually I want to be able to make an application to track bills. When that happens, I imagine that I will want to return
name, address, telephone, notes, balances...etc
I don't want to do a query for all of them, I want to do a query for name and have it return all the other information based on foreign keys.
Re: [solved]help with testing
Posted: Tue Nov 30, 2010 3:54 pm
by McInfo
cybershot wrote:You can see that there is a relationship between the primary key in the names table and the foreign key in the phone table.
The problem I am having in phpmyadmin is that there doesn't seem to be that relationship even though I am defining it.
The relationship in the MySQL database is the same as the relationship in the Access database. My guess is that there is something about the way Access presents things that is obscuring some details from you. Microsoft likes to do things magically so users become dependent on its products. Or maybe the way phpMyAdmin presents things is what is confusing.
cybershot wrote:the namesID field in the phone table shows up as null. I would think that it should have an index in it that matches to the names table.
If you don't specify a value for the phone.namesID field when inserting a new row in the phone table, the default value will be used. If the default value is NULL, it will not match the primary key of any rows in the names table.
cybershot wrote:I don't want to do a query for all of them, I want to do a query for name and have it return all the other information based on foreign keys.
Do you have an Access query that does that? This is one of those cases where Access magically simplifies things at the expense of your education. If you have the Access query, examine it in SQL View. The SQL you see there is very similar to the SQL you need to use in MySQL. Now, (still in Access) delete the relationship between the tables. Go back to the query and notice that the results have not changed. Conclude that defining a relationship has nothing to do with the results of a given SELECT query.
Re: [solved]help with testing
Posted: Tue Nov 30, 2010 4:43 pm
by cybershot
no, I don't have a query. I am still trying to figure it all out. What doesn't make sense is that how can you insert a foreign key value if you don't know what value to insert? I have been searching the web for a tutorial but there is no good tutorial out there that explains it. For example
What if I have a form that is inserting the values? How do I insert the foreign key value? I don't know what the primary key value will be. I want to make an application that asks for the name, address and so on. But this means that I will need multiple tables. How do you recommend getting that information from the form and inserting it into the database with foreign key values?
Re: [solved]help with testing
Posted: Tue Nov 30, 2010 6:21 pm
by McInfo
To indirectly determine the value to put in the foreign key field, you must query the table that the value comes from. For example, if you want to insert a new row in the phone table that links to a row in the names table where the firstName is "Joe", you must first query the names table to get the names.namesID for the row where firstName is "Joe". Then you insert that value in the phone.namesID field of the new row.
In a user interface for inserting a new row into the phone table, all rows from the names table might be fetched and used to populate a list box for choosing the value to insert into the phone.namesID field. The value of each option in the list box would come from the names.namesID field, and the label of each option might come from the names.firstName field or a concatenation of more/other fields.
The phpMyAdmin interface is similar and allows you to specify one field in addition to the primary key that will appear in the list box of a foreign key field on the "Insert" page. To specify the foreign value to show when inserting a phone.namesID field, go to the "Relation view" page for the names table and choose an option in the "Choose field to display:" list box. Go to the "Insert" page for the phone table and look at the options for the phone.namesID field.