Forum Moderators: open
I usually use flat file DB's, but have the need to relate notes to comments and this is a bit out of my MySQL knowledge spectrum for efficiency... I can make it work, but wonder what some people with more experience doing these type of selects think and if there are efficiency gains, so here goes:
1.) Parameters:
SELECT * Notes Info with a limit of 10, with the 10 being the most recent.
SELECT * Comments related by Note ID.
I know there are other ways I can do this, but I need to select all 10 Notes, with or without comments and want to only select comments that correspond to the notes selected...
2.) What I have considered.
In long hand using 2 SELECTs & PHP it looks something like: (I'm making this up as I go right now, so don't scrutinize my PHP too much, just go with the point. Thanks! I might have an error or two and there's a bit missing ;))
$Query="SELECT * FROM Notes WHERE UserID='".$UserID."' ORDER BY Time DESC LIMIT 10";
$noteIDs=array();
while($Result=mysql_fetch_array($Query)) {
$noteIDs[]=$Result['id'];
}
if(count($noteIDs)>1) {
$idsToGet=implode(" OR ",$noteIDs);
}
else { $idsToGet=$noteIDs[0]; }
$selComments="SELECT * FROM Comments WHERE noteID='".$idsToGet."' ORDER BY id";
Is there a more efficient way to do this with a single SELECT?
TIA for any advice...
EDITED: Couple of minor corrections.
$Query='select Notes.*,Comments.* from Notes,Comments' .
' where Notes.id=Comments.noteID and'
' Notes.UserID=' . $UserID .
' order by Notes.Time desc limit 10';
This will give you an array first of all Notes fields followed by all Comments fields. Note that numeric data types do not need to be quoted in a select - it does not hurt to quote them, but not necessary. If UserID is text, add quotes.
As for efficiency, unless you need **all** fields, select only what you want:
$Query='select Notes.UserID,Notes.UserName,Notes.Time,Comments.UserComment from Notes,Comments' .
' where Notes.id=Comments.noteID and'
' Notes.UserID=' . $UserID .
' order by Notes.Time desc limit 10';
I'll play around with it a bit... I had thought of using a join, but it's not my forte. I appreciate the suggestion! Thanks.
