Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    8

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    8
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    8

    thanks

    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;
    }

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by geitvdplas
    ... so you figure it out
    um.... no

    declined with thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •