many to many, how far can I go with 1 query?

geitvdplas · #1 (**permalink**) 05-21-09, 09:29

Hello fellow humans!

I recently learned about MANY to MANY relationships. I want to link my website to a mysql database and get data from it with PHP.

I've set up 4 tables. chapters, subChapters, images and ,texts.
when clicking upon a link I have two variables.. $mainID and $subID. (mainchapter and subchapter)
What I need from the database is this:

ALL chapters, for the dropdownmenu
ALL subchapters that belong to the selected chapter, for the dropdownmenu
ALL images that belong to the selected subchapter
ALL texts that belong to the selected subchapter

I can accomplish this by sending a few mysql querys but as far as I've read that's a bad idea.

Is there a way I need to rearrange my tables or rearrange my query so that I can get all this information effectivly?
Are there some basic guidelines I should follow?
Is this a bad Idea in general?

I just want to learn it the right way from the start

A lot of thanks and love from me,

Geit

r937 · #2 (**permalink**) 05-21-09, 11:02

if there aren't too many chapters and subchapters, i would combine the first two into one query, in effect getting all chapters and all subchapters (but only showing the subchapters for the selected chapter)

images and texts should each be retrieved with a separate query

geitvdplas · #3 (**permalink**) 05-21-09, 11:17

Thanks for your quick reply

So when it's just a few menu items and ID's; It's faster to just send them all and just show a few with PHP?
That will really change my point of view cause I thought it was better to minimize the amount of queries and information you get back

Thanks again

r937 · #4 (**permalink**) 05-21-09, 14:59

Quote:

Originally Posted by geitvdplas

... cause I thought it was better to minimize the amount of queries and information you get back

in general, yes it is

if you're talking about a few kilobytes of data, though, the added complexity to save this meager amount of data transfer won't make a measurable difference and negatively impacts both the simplicity and the development time

geitvdplas · #5 (**permalink**) 05-24-09, 09:55

Lot's of thanks for your help

As a cookie, I kinda rushed this stylesheet that I think improves your simple SQL website's readability. I have only tested it with IE8 and google chrome, so you figure it out

Code:

body {
margin:0;padding:0;
	font-family: Arial, Helvetica, sans-serif;
	font-size:0.9em;
	line-height:120%;
}
h1 {
	margin:0;
	padding:1em 0;
	text-indent:1.1em;
	font-size:1.397em;
	word-spacing: 0.1em;
	color: #F7FAFD;
	display: block;
	width: 100%;
	letter-spacing: 0.1em;
	position: absolute;
	left: 0px;
	top: 0px;
	border-bottom: groove #CAD6E8 2px;
	background: #003366;
}
h2 {
	margin:0;
	padding:0;
	font-size:1.1379em;
}
h3 {
	margin:1em 0 0 -3em;
	padding:0.4em;
	font-size:1.0937em;
	color: #000;
	background: #F5F5F5;
	display: inline-block;
	text-indent: 2.7em;
	width: 200px;
}
h3:first-letter {
	color: #000;
	text-decoration:underline;
}
h4 {
	margin:0;
	padding:0;
	font-size:0.65em;
	text-align: right;
	position: relative;
	top: -2em;
	height: 0px;
}
h5 {
	margin:0;
	padding:0;
	font-size:0.739em;
	font-style:italic;
	display: inline;
}
p {
	margin:0;
	padding:0;
}
h5 + p {
	margin:0.379em 0;
	padding:0;
}
blockquote p {
	margin:0;
	padding:0;
}
p.by {
	text-align:right;
	margin-left:9.37%;
}
img {
	border:none;
}
dt {
	padding:3em 1em 1em 1em;
	border-top:solid 1px #F5F5F5;
}
dt:first-line {
	font-size:1.3em;
	color: #333;
	text-decoration:underline;
}
dd {
	margin:0;
	padding:0 0 0 0.937em;
}
div#content {
	margin:5em 0;
	padding:0 1em;
	color: #333;
	line-height: 120%;
	width: 700px;
}

r937 · #6 (**permalink**) 05-24-09, 15:37

Quote:

Originally Posted by geitvdplas

... so you figure it out

um.... no

declined with thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech