problem joining tables

enkidu72 · July 19, 2008

Hello all ,

I have a problem joining two tables ...

Here there are :

mysql> select id,l_index_id,titolo,is_active from page where l_index_id=4;

+----+------------+----------------+-----------+

+----+------------+----------------+-----------+

| 4 | 4 | toscana | 1 |

| 5 | 4 | prova_immagine| 0 |

+----+------------+----------------+-----------+

2 rows in set (0.01 sec)

How you can see "is_active" in record 4 is set to 1...

Second table :

mysql> select * from menu where l_index_id=4;

+----+------------+------+-----------+----------------+----------+

+----+------------+------+-----------+----------------+----------+

| 4 | 4 | 0 | | toscana | 1 |

| 5 | 4 | 2 | | prova immagine | 0 |

+----+------------+------+-----------+----------------+----------+

2 rows in set (0.01 sec)

and then :

mysql> select menu.menu_title , menu.menu_link , menu.peso , menu.is_title , menu.l_index_id ,page.is_active ,page.l_index_id from menu ,page where page.is_active='0' and menu.l_index_id=page.l_index_id and page.l_index_id=4 order by peso asc;

+----------------+-----------+------+----------+------------+-----------+------------+

+----------------+-----------+------+----------+------------+-----------+------------+

| toscana | | 0 | 1 | 4 | 0 | 4 |

| prova immagine | | 2 | 0 | 4 | 0 | 4 |

+----------------+-----------+------+----------+------------+-----------+------------+

2 rows in set (0.00 sec)

mysql>

Now is active is set to 0 ?

and :

mysql> select menu.menu_title , menu.menu_link , menu.peso , menu.is_title , menu.l_index_id ,page.is_active ,page.l_index_id from menu ,page where menu.l_index_id=page.l_index_id and page.l_index_id=4 order by peso asc; +----------------+-----------+------+----------+------------+-----------+------------+

+----------------+-----------+------+----------+------------+-----------+------------+

| toscana | | 0 | 1 | 4 | 1 | 4 |

| toscana | | 0 | 1 | 4 | 0 | 4 |

| prova immagine | | 2 | 0 | 4 | 0 | 4 |

| prova immagine | | 2 | 0 | 4 | 1 | 4 |

+----------------+-----------+------+----------+------------+-----------+------------+

4 rows in set (0.00 sec)

Can someone help me with this ?

Many thx in advance

David

enkidu72 · July 19, 2008

Some more infos :

mysql --version

mysql Ver 14.12 Distrib 5.0.51b, for slackware-linux-gnu (i486) using EditLine wrapper

mysql> describe page ; describe menu ;

+---------------+-------------+------+-----+---------+----------------+

+---------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| is_news | int(10) | YES | | NULL | |

| l_index_id | int(11) | YES | | NULL | |

| is_active | int(10) | YES | | NULL | |

| primo_piano | int(10) | YES | | NULL | |

| categoria | int(10) | YES | | NULL | |

| sub_categoria | int(10) | YES | | NULL | |

+---------------+-------------+------+-----+---------+----------------+

11 rows in set (0.00 sec)

+------------+-------------+------+-----+---------+----------------+

+------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| l_index_id | int(11) | YES | | NULL | |

| peso | int(11) | YES | | NULL | |

| is_title | int(1) | YES | | NULL | |

+------------+-------------+------+-----+---------+----------------+

6 rows in set (0.00 se

bluejay002 · July 19, 2008

what is it that you want to achieve then?

you didn't specified what you want to get.

the table structure is enough info... just state what you want, including the rules for joining.

enkidu72 · July 19, 2008

Hello bluejay002

I want to get the result where :

page.is_active=1 and

page.l_index_id=menu.l_index_id

and page.l_index_id=some number .

The problem I have is that when I submit those queries I get even those pages which page.is_active=0 , I get all the pages where

page.l_index_id=menu.l_index_id ...

Dunno if I have explained it well sorry but I'm not a native english speaker ...

Thx

D

bluejay002 · July 19, 2008

try this:

SELECT field_name_and_whatever
FROM page
INNER JOIN menu ON menu.l_index_id = page.l_index_id
WHERE page.is_active = 1
AND page.l_index_id = some_number

cheers,

jay

enkidu72 · July 19, 2008

Doesn't work ...

mysql> select page.is_active,page.id,page.titolo,page.l_index_id,menu.menu_title,menu.l_index_id from page inner join menu on menu.l_index_id=page.l_index_id where page.is_active=1 and page.l_index_id=4;

+-----------+----+---------+------------+----------------+------------+

+-----------+----+---------+------------+----------------+------------+

| 1 | 4 | toscana | 4 | toscana | 4 |

| 1 | 4 | toscana | 4 | prova immagine | 4 |

+-----------+----+---------+------------+----------------+------------+

2 rows in set (0.00 sec)

While if i submit 2 differents queries :

mysql> select id,titolo , is_active,l_index_id from page where l_index_id=4 ;

+----+----------------+-----------+------------+

+----+----------------+-----------+------------+

| 4 | toscana | 1 | 4 |

| 5 | prova_immagine | 0 | 4 |

+----+----------------+-----------+------------+

2 rows in set (0.00 sec)

mysql> select * from menu where l_index_id=4 ;

+----+------------+------+-----------+----------------+----------+

+----+------------+------+-----------+----------------+----------+

| 4 | 4 | 0 | | toscana | 1 |

| 5 | 4 | 2 | | prova immagine | 0 |

+----+------------+------+-----------+----------------+----------+

2 rows in set (0.00 sec)

See ? Those two records have both l_index_id=4 , but just one page ( with id 4 ) has is_active=1 ... Result are mixed up ... :'(

enkidu72 · July 19, 2008

Nobody can help with these ?

I've already lost two days trying to figure it out ...

HELP !

bluejay002 · July 19, 2008

basing on the rule you specified:

page.is_active=1 and
page.l_index_id=menu.l_index_id

and page.l_index_id=some number

the above query should perfectly work, otherwise you want something else.

i think what you want to get is only:

+-----------+----+---------+------------+----------------+------------+
| is_active | id | titolo | l_index_id | menu_title | l_index_id |

+-----------+----+---------+------------+----------------+------------+

| 1 | 4 | toscana | 4 | toscana | 4 |

+-----------+----+---------+------------+----------------+------------+

am i right?

with the rules you specified, that's not possible, why? because both menu has an l_index_id = 4 which is your rule for joining and with always return both menu. if what you want is only toscana, then you may include in the WHERE clause the id of that menu. if its not possible to get the id at that certain time, i guess the problem is not because of the query but the design of the database itself. if you will, can you give an insight of what you want to achieve and maybe i can help sorting with the design of the database as well.

enkidu72 · July 20, 2008

Many many thx for explaining me this ...

The two tables represent a page of a web site and it's menu .

It works this way :

Every page has a family ( l_index_id ) . So when a page is shown all the links on the menu belonging ( with the same l_index_id ) to that family are shown .

This was working great until the customer has told me to put a flag active/inactive/draft for the page ( "Because sometime i don't finish to write the page so I don't want to show it" ) .

Some code :

		



$query2=" select  distinct page.is_active,page.id ,page.l_index_id,menu.menu_link,menu.peso,menu.menu_title,menu.is_title  
from page left join menu on  menu.l_index_id=page.l_index_id 
where menu.l_index_id='$l_index_id'  and page.is_active='1' order by peso asc"; 

                                [...]
			$result2=mysql_query($query2)or die(mysql_error());
			echo "<div id='side_menu'>";
			while ($row2=mysql_fetch_array($result2)){
				$menu_link=$row2['menu_link'];
				$menu_title=$row2['menu_title'];
				$peso=$row2['peso'];
				$is_title=$row2['is_title'];
				$page_id=$row2['id'];
			$menu_title=str_replace('\\','',$menu_title);
			$menu_title_replaced=str_replace(' ','_',$menu_title);

				echo "$primo_img <a $classe href='index.php?p=$menu_title_replaced' >$menu_title </a><br/>";
			}


			echo "</div>";
		}

Dunno if I managed to explain it

David

tivrfoa · July 20, 2008

your tables are bad related. then when you try to join them the result is the multiplication of the tables records.

Don't use page.is_active and page.l_index_id in your query, because you don't need them (at least in the code you post).

Você é brasileiro. huahuauha se for eu estou escrevendo em inglês aqui igual um mané. hehe

lescoutinhovr@gmail.com

enkidu72 · July 20, 2008

No , I'm not brasilian , I'm Italian

I suppose "peso" and "primo" have the same meaning in both languages ...

What query do you suggest ?

I need to relate them via l_index_id , which is the id of the family they belong to ...

maybe I can check with

if( $is_active == "1" ) {}

later ?

tivrfoa · July 20, 2008

post the result of this query:

select p.id, m.menu_link, m.peso, m.menu_title, m.is_title

from page p inner join menu m on p.l_index_id = m.l_index_id

where m.l_index_id = '$l_index_id' and p.is_active='1'

order by m.peso asc

you can't use this column(l_index_id) to union the tables. You need to add a foreign key in one of the tables.

enkidu72 · July 20, 2008

I got this :

mysql> select p.id, m.menu_link, m.peso, m.menu_title, m.is_title from page p inner join menu m on p.l_index_id = m.l_index_id where m.l_index_id = '4' and p.is_active='1' order by m.peso asc;

+----+-----------+------+----------------+----------+

+----+-----------+------+----------------+----------+

| 4 | | 0 | toscana | 1 |

| 5 | | 0 | toscana | 1 |

| 4 | | 2 | prova immagine | 0 |

| 5 | | 2 | prova immagine | 0 |

+----+-----------+------+----------------+----------+

4 rows in set (0.00 sec)

What do you mean to add a foreign key to a table ?

D.

bluejay002 · July 21, 2008

Just a question before going any further; what's the purpose of the field titolo in page and the menu_title in menu? what is it that you are trying to make inactive, the menu or the page (or both)?

if you can clarify what you are trying to achieve and the purpose if every field of that two tables, maybe we can give you a better idea what is lacking in your design.

tivrfoa · July 21, 2008

Do a page can have only one menu, but a menu can be used in many pages?

If the answer is yes, then your table page will be something like this:

create table page(

id int primary key,

l_index_id int,

titolo varchar(30),

is_active char(1),

more columns....

menu_id int,

foreign key(menu_id) references menu(id)

);

So your query is:

select p.id, m.menu_link, m.peso, m.menu_title, m.is_title

from page p inner join menu m on p.menu_id = m.id

where m.l_index_id = '$l_index_id' and p.is_active='1'

order by m.peso asc

enkidu72 · July 21, 2008

I'll try to explain

"titolo" is the title of the page , while menu_title is the visible link of the menu . They are more or less the same , but in a page the title can be very large , while in the menu has to be shorter ( example : page title "This is the title of page 1" , menu title "page 1 " ) If it's short can be the same .

The l_link_id is the family , the "subject" , of one ( or more ) pages .

Example :

parent -> Tuscany

children ->Tuscany

-> Florence ( active )

-> Pisa ( inactive )

-> Lucca ( active )

on the page Tuscany ( the default ) there will be the content of the page Tuscany , and on the menu the links to the page Tuscany and Florence and Lucca . Clicking on Florence will get you to the page Florence , with the same menu .

Pisa should not be visible because inactive .

many thx to both of you guys

Ciao

David

enkidu72 · July 21, 2008

I've tried this :

i've added a column pageid in the table menu and I add the id of the new inserted page there :

$query="insert into  page (l_index_id,titolo,testo,is_news,is_active,categoria) values ('$link_family','$titolo','$testo','0','$is_active','0')";
	mysql_query($query) or die(mysql_error());	
	$pageid=mysql_insert_id();		
	$query="insert into menu (l_index_id,peso,menu_link,menu_title,is_title,pageid) values ('$link_family','$peso','$link_menu','$link_desc','$is_title','$pageid')";
	mysql_query($query) or die(mysql_error());

Then I do :

$query_page="select titolo,id,is_active from page where l_index_id='$l_index_id' and is_active='1'";

and

$query2="select distinct * from menu  where menu.pageid='$page_id'";

and it works ...

It's correct ?

Ciao

David

Sign In

problem joining tables

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information