Hi,
I'm in my second week now learning PHP from a book called "PHP 4 Zonder stress" (= wihtout stress) but I'm getting very frustrated.
Discription of my problem:
I have 2 tables:
Table_1: country
fields:
countr_id | country_name
values:
ES | Spain
FR | France
Table_2: region
fields:
region_id | region_name | country_id
values:
CB | Costa Brava | ES
CD | Costa Dorada | ES
CA | Costa Azahar | ES
LA | Languedoc | FR
RO | Roussilon | FR
I want to display a list as follows:
Spain
Costa Brava
Costa Dorada
Costa Azahar
France
Languedoc
Roussillon
I succeded to display it like this, but not in a correct way:
Explanation:
I use 2 querys:
a) query of all the regions within Spain
b) query of all the regions within France
To display it is use mysql_fetch_arry like this
<tr>
<td>
SPAIN (is simply typed)
</td>
</tr>
<tr>
<td>
while () { ==> regions spain
</td>
</tr>
<tr>
<td>
FRANCE (is simply typed)
</td>
</tr>
<tr>
<td>
while () { ==> regions france
</td>
</tr>
Problem: manual work if a new country and regions are added.
I managed to solve that problem and getting a list like this:
Spain Costa Brava
Spain Costa Dorada
etc..
France Languedoc
ect.
By using a 2 column table and the mysql_fetch_array and using only 1 query
But thats not what I want it to look like.
Question:
How can I display it as mentionned above and still have the automatic update if regions or countries are added using as less code and queries as possible ?
Thanks in advance,
Gijs
A Looooooooop question
Moderator: General Moderators
Code: Select all
<?
$Link=mysql_connect('host','user','pass');
$Result=mysql_db_query('countries','SELECT * FROM country');
if (mysql_num_rows($Result)){
while (list($id,$name)=mysql_fetch_row($Result)){
echo "$name :<br>";
$Result2=mysql_db_query('countries',"SELECT region_name FROM region WHERE country_id='$id'");
if (mysql_num_rows($Result2)){
while (list($region)=mysql_fetch_row($Result2))
echo " $region<br>";
} else {
echo "No regions for country\n<br>";
}
}
} else echo "Database empty";
?>Do not use mysql_db_query... It won't work in PHP 4.2.1
So change theChosen's code to like this
So change theChosen's code to like this
Code: Select all
<?php
$Link=mysql_connect('host','user','pass');
mysql_select_db("db");
$Result=mysql_query('SELECT * FROM country');
if (mysql_num_rows($Result)){
while (list($id,$name)=mysql_fetch_row($Result)){
echo "$name :<br>";
$Result2=mysql_query("SELECT region_name FROM region WHERE country_id='$id'");
if (mysql_num_rows($Result2)){
while (list($region)=mysql_fetch_row($Result2))
echo " $region<br>";
} else {
echo "No regions for country\n<br>";
}
}
} else echo "Database empty";
?>- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
But it's not a good idea to use mysql_db_query() especially as the manual clearly states:
Macphp manual wrote:Note: This function has been deprecated since PHP 4.0.6. Do not use this function. Use mysql_select_db() and mysql_query() instead.
Thanks Takuma,
Thanks theChosen,
I was a bit embaressed
that I couldn't make theChosen 's solution work.
Especially as my book "Without Stress" has a similar example.
Now I know why.
Tested it and works great !
For me it is not that important that you gave me the solution, but more important to learn and understand
how it works.
So please, correct me if I'm wrong:
Basicly you combine 2 two loops within each other.
a) First you query * from the country-tabel and store it in the variable $result
b) Then you check if the variable $result contains records, else "database is empty"
c) You make a while-loop using list() to assign the list of variables (here:$id and $name),
who equals the fetched corresponding row (if there are no more rows = FALSE ==> stops)
d) You echo the $name before the second loop
e) The interesting part is in the second loop where you you use the variable $id in
the query to select onlu the regions corresponding with $id.
f) the rest is the same as the first.
It's been educational !
Thanks,
Gijs
Thanks theChosen,
I was a bit embaressed
Especially as my book "Without Stress" has a similar example.
Now I know why.
Tested it and works great !
For me it is not that important that you gave me the solution, but more important to learn and understand
how it works.
So please, correct me if I'm wrong:
Basicly you combine 2 two loops within each other.
a) First you query * from the country-tabel and store it in the variable $result
b) Then you check if the variable $result contains records, else "database is empty"
c) You make a while-loop using list() to assign the list of variables (here:$id and $name),
who equals the fetched corresponding row (if there are no more rows = FALSE ==> stops)
d) You echo the $name before the second loop
e) The interesting part is in the second loop where you you use the variable $id in
the query to select onlu the regions corresponding with $id.
f) the rest is the same as the first.
It's been educational !
Thanks,
Gijs