Page 1 of 1

A Looooooooop question

Posted: Wed Sep 11, 2002 2:56 am
by gijs
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

Posted: Wed Sep 11, 2002 4:46 am
by theChosen

Code: Select all

<?
	$Link=mysql_connect('host','user','pass');
	$Result=mysql_db_query('countries','SELECT * FROM country');
	if (mysql_num_rows($Result))&#123;
		while (list($id,$name)=mysql_fetch_row($Result))&#123;
			echo "$name :<br>";
			$Result2=mysql_db_query('countries',"SELECT region_name FROM region WHERE country_id='$id'");
			if (mysql_num_rows($Result2))&#123;
				while (list($region)=mysql_fetch_row($Result2))
					echo " $region<br>";
			&#125; else &#123;
				echo "No regions for country\n<br>";
			&#125;
		&#125;
	&#125; else echo "Database empty";
?>

Posted: Wed Sep 11, 2002 5:17 am
by Takuma
Do not use mysql_db_query... It won't work in PHP 4.2.1

So change theChosen's code to like this


Code: Select all

&lt;?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 :&lt;br&gt;"; 
         $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&lt;br&gt;"; 
         } else { 
            echo "No regions for country\n&lt;br&gt;"; 
         } 
      } 
   } else echo "Database empty"; 
?&gt;

Posted: Wed Sep 11, 2002 7:48 am
by theChosen
Do not use mysql_db_query... It won't work in PHP 4.2.1
mysql_db_query() WILL work on PHP 4.2.1 but will (sometimes) return false on correct queries. Since my code does not check for the result of the function it is safe to use it.

Posted: Wed Sep 11, 2002 7:51 am
by twigletmac
But it's not a good idea to use mysql_db_query() especially as the manual clearly states:
php 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.
Mac

Posted: Wed Sep 11, 2002 8:14 am
by gijs
Thanks Takuma,
Thanks theChosen,

I was a bit embaressed :oops: 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 ! :lol:

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