Page 1 of 1

query with multi table

Posted: Sun Feb 25, 2007 9:20 am
by keenlearner
Ok, let's go straight to the point with the simple example, let's say i have two tables namely html and link.
html table :

Code: Select all

create table html(
id int(4),
title varchar(255),
description varchar(255)
);
link table:

Code: Select all

create table link(
id int(4),
url varchar(255)
);
one page of website can have only one title and description, but can have many url which will be retrieved from link table, let's say I want the data to appear on page-one.php so i query with
"SELECT * FROM html LEFT JOIN link using(id) WHERE html.id = '2' "; it will output,

id | title aaaa| descriptionaaaaaaaa | url aaaaaaaaaaa |

2 | page one | This is the page one | http://www.link-1.com |
2 | page one | This is the page one | http://www.link-2.com |
2 | page one | This is the page one | http://www.link-3.com |

sorry, i can't draw table properly here.

So the problem is, i need the http://www.link-1.com, http://www.link-2.com and http://www.link-3.com, and i need the title and description only once but it was repeated for three times which i afraid it's not efficient to repeat the same thing so many times and i was thinking if there is a way to show the three url but only one time of title and description ? in other words is there a better query than this ? Thank you.[/syntax]

Posted: Sun Feb 25, 2007 10:12 am
by feyd
The query is fine. It's your code that needs to understand. Take a look at the first (and possibly the second) links in Useful Posts (which can be found in PHP - Code).

Posted: Sun Feb 25, 2007 10:26 am
by keenlearner
Thank's for your reply again, I have not built up the the page yet, but it might looks something like this.

Code: Select all

<?php 
$result = mysql_query("SELECT * FROM html LEFT JOIN link using(id) WHERE html.id = '2' ",$connection);

while($row = mysql_fetch_assoc($result)){
  $title = $row['title']; // title is all the same so there is no need to put in array
 $description = $row['description']; //same as title
  $url[] = $row['url']; // url are diffrent so put in array
}
?>


<html>
</head>
<title><?php echo $title; ?></title>
</head>
<body>
<?php echo $description; ?>
for($i=0; $i < sizeof($url); $i++){
<?php echo $url[$i].'<br>' ?>
}
</body>
</html>
My html table has much more field than that, i am afraid it can cause strain on the mysql query. I think i will separate the query into two with, "SELECT title,description FROM html WHERE id='2' " and "SELECT url FROM link WHERE id='2' " will it be more efficient ?

Posted: Sun Feb 25, 2007 11:43 am
by feyd
MySQL will be fine either way.