Jump to content


Photo

Holy SQL Batman!


  • Please log in to reply
21 replies to this topic

#1 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 25 November 2005 - 01:25 PM

Does anyone know how i can optimize this code so that it does not use 4 querys for every news post?

You can see the final output at http://acidvat.com/av2/index.php

<?php

$pagetitle .= "News";

$getnews = mysql_query_2("SELECT * from `items` WHERE `cat`=21 AND `chkd`='1' ORDER BY `id` DESC");
while ($news = mysql_fetch_array($getnews)) {

$getbody = mysql_query_2("SELECT * from `comments` WHERE `id`=$news[firstcomment]");
$body = mysql_fetch_array($getbody);

$getmember = mysql_query_2("SELECT * from `members` WHERE `id`=$body[bymember]");
$poster = mysql_fetch_array($getmember);

$getcat = mysql_query_2("SELECT * from `cats` WHERE `id`=$news[cat]");
$incat = mysql_fetch_array($getcat);

$numcomments = mysql_query_2("SELECT inreplyto FROM comments WHERE inreplyto=$news[firstcomment]");
$numcomments = mysql_num_rows($numcomments);


$newsdate = date("j M y", $news[date]);

$postersicon = groupicon($poster[group]);

$content .= "
<div align=center>
<table border=0 cellpadding=5 cellspacing=1 width=100% id=topback>
  <tr>
  <td valign=top width=80% id=newscell><strong>$news[title]</strong><br />
  Written by $postersicon<a href=index.php?view=member&name=$poster[name]>$poster[name]</a><br />
  $newsdate, in <a href=index.php?$news[cat]>$incat[title]</a></td>
  <td align=right valign=top width=20% id=newscell><a href=index.php?view=member&name=$poster[name]>Posters Avatar</a></td>
  </tr>
  <tr>
  <td valign=top width=80%>$body[thecomment]</td>
  <td align=right valign=top width=20%>$news[prev] & $news[prev2]</td>
  </tr>
  <tr>
  <td valign=top width=80% id=newscell><a href=index.php?$news[firstcomment]>Read the Full Story</a></td>
  <td align=right valign=top width=20% id=newscell><a href=index.php?$news[firstcomment]>$numcomments comments</a></td>
  </tr>
</table>
</div>
<br />
<br />
<br />
";
}

?>


or anyone have a link to a tutorial about outputing to flat files?

Edited by Detail, 25 November 2005 - 02:24 PM.


#2 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 25 November 2005 - 02:48 PM

Disclaimer, I'm writing this off the top of my head...

I would probably do something like this:

$bodyIds = '0';
$memberIds = '0';
$catIds = '0';
$comments = '0';

$getnews = mysql_query_2("SELECT * from items WHERE cat=21 AND chkd='1' ORDER BY id DESC");

while ($news = mysql_fetch_assoc($getnews))
{
$bodyIds .= ',' . $news['firstcomment'];
$catIds .= ',' . $news['cat'];
}

$newsCount =mysql_num_rows($getnews);

$getbody = mysql_query_2("SELECT * , COUNT(inreplyto) AS numComment from comments WHERE id IN ($bodyIds) GROUP BY inreplyto");
while ($body = mysql_fetch_assoc($getbody))
{
$bodies[] = $body;
$memberIds .= ',' . $body['bymember'];
$numComments [] = body['numComment'];
};

$getmember = mysql_query_2("SELECT * from members WHERE id IN $memberIds)");
while ($poster = mysql_fetch_assoc($getmember))
{
$posters[] = $poster;
};

$getcat = mysql_query_2("SELECT * from cats WHERE id IN ($catIds)");
while ($incat = mysql_fetch_assoc($getcat))
{
$categs[] = $incat;
};

and do a for() over $newsCount, building a separate news item in each step... Or something like that.

This way, the whole page uses 4 queries instead of 4*n+1.

Btw, use fetch_assoc() :D

Edit: Heh, typical. 5 seconds after I post, I get a better idea.

Edited by DCoder, 25 November 2005 - 02:55 PM.

Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#3 Guest_ImmoMan_*

Guest_ImmoMan_*
  • Guests

Posted 25 November 2005 - 03:03 PM

Why use fetch_assoc instead of fetch_array? The result is really the same except for the numeric indexes...

#4 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 25 November 2005 - 03:10 PM

http://www.php.net/m...array.php#22361

Benchmark on a table with 38567 rows:

mysql_fetch_array
MYSQL_BOTH: 6.01940000057 secs
MYSQL_NUM: 3.22173595428 secs
MYSQL_ASSOC: 3.92950594425 secs

mysql_fetch_row: 2.35096800327 secs
mysql_fetch_assoc: 2.92349803448 secs

As you can see, it's twice as effecient to fetch either an array or a hash, rather than getting both.  it's even faster to use fetch_row rather than passing fetch_array MYSQL_NUM, or fetch_assoc rather than fetch_array MYSQL_ASSOC.  Don't fetch BOTH unless you really need them, and most of the time you don't.


With indexes you have to remember which column was 'firstcomment', and which was 'body_id'... And what if the DB column order gets changed (i.e., a new coder inserts a column in the middle of the table? Poof goes the latter half of your numeric indexes...)

Edited by DCoder, 25 November 2005 - 03:13 PM.

Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#5 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 25 November 2005 - 04:32 PM

I fixed all the bugs and have the below. How do i use it? :D
As you can see from the site now http://www.acidvat.com/av2/ i just have empty holes.

What are []'s ?
What are IN()'s?

while ($news = mysql_fetch_assoc($getnews)){
	$bodyIds .= ',' . $news['firstcomment'];
	$catIds .= ',' . $news['cat'];
}

$newsCount = mysql_num_rows($getnews);

$getbody = mysql_query_2("SELECT * , COUNT(inreplyto) AS numComment from comments WHERE id IN ($bodyIds) GROUP BY inreplyto");
while ($body = mysql_fetch_assoc($getbody)){
	$bodies[] = $body;
	$memberIds .= ',' . $body['bymember'];
	$numComments[] = $body['numComment'];
}

$getmember = mysql_query_2("SELECT * from `members` WHERE `id` IN ($memberIds)");
while ($poster = mysql_fetch_assoc($getmember)){
	$posters[] = $poster;
};

$getcat = mysql_query_2("SELECT * from cats WHERE id IN ($catIds)");
while ($incat = mysql_fetch_assoc($getcat)){
	$categs[] = $incat;
};


#6 Athena

Athena

    Embody the Truth

  • Undead
  • 6,946 posts
  •  Former Community Leader

Posted 25 November 2005 - 11:06 PM

I think [] are arrays (more info see here).

#7 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 26 November 2005 - 06:14 AM

$foo = array(1, 5, 8, 2);
$foo[] = 11;
Behold, $foo is now (1, 5, 8, 2, 11).

If you were to do
echo '<pre>';
print_r($bodies);
echo '</pre>';
, you'd see the structure of $bodies, and you should be able to work from there.


WHERE x IN (0,5,7) does the same as WHERE (x = 0) OR (x = 5) OR (x = 7) and looks much tidier and is easier to write.


And if you had used a more specific SELECTs instead of * (SELECT * is bad practice anyway) , I could have compacted _all_ that stuff into 1 query using JOINs. But now, since the column names repeat (id), I can't...

Hint, when debugging other people's SQL it's nice to know the table structure involved.

Oh, and one more thing, why do you SELECT from cat each time if your first query requires cat to be 21?
Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#8 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 26 November 2005 - 12:34 PM

http://dayonic.net/z...tablelayout.gif

Items are news, blogs, files, images, tutorials, everything the site has.
The first comment of an item is its description. You put the item in the catagory for orginisation. Catagorys have parents which acts like sub-forums.

Cat is basicaly used for the name of the catagory the item is in.
cat id 21 is 'General' (news)
cat id 3 is ' 'Red Alert 2 Voxels' (files)
cat id 20 is 'Half-Life 2' (files)
cat id 22 is 'Journals' (blogs)

you'd see the structure of $bodies

^_^ very helpful

All the colomuns can be changed, so if you know a way to get this to 1 query that would be very helpful, as the item system will be used all over the site.



Array
(
    [0] => Array
        (
            [id] => 1
            [bymember] => 2
            [date] => 1132161445
            [inreplyto] => 
            [thecomment] => body of news
            [numComment] => 1
        )

)
How do i get into the second level array?

Edited by Detail, 26 November 2005 - 01:00 PM.


#9 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 26 November 2005 - 04:21 PM

Eww, int(250) :p

Well, there is a question of "Do we really need to get the whole cat table for each item, given that all the items share the same category?"... Imagine, when you have 25 items / page, you get the same category data 25 times. Ouch.

I guess you don't need that, and we can fetch the cat stuff in a separate query, which would significantly reduce the redundant data transferred.
$category = "
SELECT
title, desc, parent, mime, ups
FROM cats WHERE id = 21
-- magic numbers
LIMIT 1
";

$query = "
SELECT
i.id, i.title, i.cat, i.file, i.prev, i.prev2, i.chkd, i.dlnum, i.date, i.mem, i.firstcomment,
com.id cId, com.bymember, com.date comDate, com.inreplyto, COUNT(inreplyto) replyCount, com.thecomment,
m.id mid, m.name
-- add any other _needed_ columns
-- from members table here
-- with the m. prefix !
FROM items i
INNER JOIN comments com ON com.id = i.firstcomment
INNER JOIN members m ON m.id = com.bymember
WHERE i.cat = 21
-- magic numbers
GROUP BY inreplyto
ORDER BY i.id DESC ";
$cat = mysql_query($category);
$categ = mysql_fetch_assoc($cat);

echo '<h1>Category data</h1> <pre>';
print_r($item);
echo '</pre> <hr /> <br />';

$res = mysql_query($smallquery);
while ($item = mysql_fetch_assoc($res))
{
// build the news item here
echo '<h1>Item data</h1> <pre>';
echo '<pre>';
print_r($item);
echo '</pre> <hr />';
};


The attachment shows the sample row such a query returns.

Notice how I did the cross-table stuff -

SELECT tableAlias.columnName [optional columnAlias]
FROM tableName tableAlias
INNER JOIN tableName tableAlias

tableAlias is necessary in all cross-table queries, so sql knows which table which column should be fetched from.
columnAlias is only necessary when you have several columns with the same columnName.

Hmm, hope I didn't leave any silly mistakes in this... *crosses fingers*

Attached Thumbnails

  • joined.png

Edited by DCoder, 26 November 2005 - 04:24 PM.

Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#10 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 26 November 2005 - 05:36 PM

It only seems to get 1 item, when i have 2 news items. What do i add to make it get both news items in that while?

#11 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 27 November 2005 - 09:18 AM

No idea, cause that worked fine on my localhost. I suspect there is something wrong in the data in your DB.

Btw, my yesterday's code had a bug in replyCount calculation, use this instead:

SELECT i.id, i.title, i.cat, i.file, i.prev, i.prev2, i.chkd, i.dlnum, i.date, i.mem, i.firstcomment,
com.id cId, com.bymember, com.date comDate, com.inreplyto, com.thecomment,
COUNT( com2.inreplyto ) replyCount,
m.id mid, m.name
-- add any other _needed_ columns
-- from members table here
-- with the m. prefix !
FROM items i
INNER JOIN comments com ON com.id = i.firstcomment
INNER JOIN members m ON m.id = com.bymember
INNER JOIN comments com2 ON com2.inreplyto = com.id
WHERE i.cat =21
-- magic numbers == bad
GROUP BY com2.inreplyto
ORDER BY i.id DESC


Oh, if there is a possibility for an item to appear without the corresponding entries in the other tables, change all INNER JOIN to LEFT JOIN.
Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#12 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 27 November 2005 - 12:42 PM

Seems you can't cheat and have 1 comment appear in 2 items :lol:

Works great :p

Could you do me another example for a comment stream? So that i can see how the code is used in 2 situations. From that i should be able to understand it enough to use it myself.

Bottom right 'Comments' bit of http://www.acidvat.c...mber&name=David
 $getcomments = mysql_query_2("SELECT * from `comments` WHERE `type`='MP' AND `inreplyto`='$info[id]' LIMIT 10");
 	 while ($thecomment = mysql_fetch_array($getcomments)) {
    
    $getmember = mysql_query_2("SELECT * from `members` WHERE `id`=$thecomment[bymember]");
    $poster = mysql_fetch_array($getmember);
    
    $postersicon = groupicon($poster[group]);
    
    $commentdate = date("j M y", $thecomment[date]);
    
    $mycomments .= "Written by $postersicon<a 
    href=index.php?view=member&name=$poster[name]>$poster[name]</a>, $commentdate<br />
    $thecomment[thecomment]<br />
    <hr>";
    
 	 }

`type`='MP' is new, this works with the inreplyto. 'MP' for 'member profile'.
$info[id] is the id of the member you are you are looking at.
so [MP | 2] would be all the comments for member 2.

#13 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 27 November 2005 - 02:26 PM

Something like this, I would imagine:
$query = 
"SELECT c.*, m.*
FROM comments c
INNER JOIN members m ON m.id = c.bymember
WHERE c.type = 'MP' AND c.inreplyto = '{$info['id']}'
LIMIT 10 ";

$res = mysql_query_2($query);
while ($comment = mysql_fetch_assoc($res))
{
$postersicon = groupicon($comment['group']);

$commentdate = date("j M y", $comment['date']);

// -- or you could just do an echo instead of appending a variable
// -- unless you want to mangle the output later

$mycomments .= "Written by $postersicon
<a href=\"index.php?view=member&amp;name={$poster['name']}\">{$comment['name']}</a>, $commentdate<br />
{$comment['thecomment']}<br />
<hr>";

};

Edited by DCoder, 27 November 2005 - 02:26 PM.

Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#14 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 27 November 2005 - 05:05 PM

Nice one, thank you :lol:

I'll be able to edit that to my own needs and slap it around all other the place now :p

#15 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 29 November 2005 - 01:09 PM

SELECT i.id, i.title, i.prev, i.prev2, i.dlnum, i.cat, i.date, i.firstcomment, com.id cid, COUNT( inreplyto ) replyCount, com.bymember, com.date comDate, com.thecomment, m.id mid, m.name, m.`group`, t.id tid, t.title ttit
FROM items i
LEFT JOIN comments com ON com.id = i.firstcomment
LEFT JOIN members m ON m.id = i.mem
LEFT JOIN cats t ON t.id = i.cat
WHERE `cat` =3
GROUP BY inreplyto
ORDER BY i.id DESC LIMIT 0, 20

That is supposed to show items info, including the ammout of comments the item has had. But replyCount = 1 for every item (1 for the first description). Even the items which have a few comments.

Anyone know why it's not working?

Edited by Detail, 29 November 2005 - 01:11 PM.


#16 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 29 November 2005 - 05:07 PM

I do believe you missed my post (#11 in this topic) where I said "this code has a bug in replyCount calculation" and provided a corrected version. :shiftee:
Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#17 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 29 November 2005 - 07:25 PM

it's call getting a bit confusing now :shiftee:

i have this:

SELECT i.id, i.title, i.cat, i.prev, i.prev2, i.chkd, i.date, i.mem, i.firstcomment, com.id cId, com.bymember, com.date comDate, com.inreplyto, com.thecomment, COUNT( com2.inreplyto ) replyCount, m.id mid, m.name, m.`group` , m.ava
FROM items i
LEFT JOIN comments com ON com.id = i.firstcomment
LEFT JOIN members m ON m.id = com.bymember
LEFT JOIN comments com2 ON com2.inreplyto = i.id AND com2.type = 'NP'
WHERE i.cat =21
GROUP BY com2.inreplyto
ORDER BY i.id DESC
LIMIT 0 , 30


I want it to get the ammount of replys where inreply = news item and type = NP (news post).
If i just have inreplyto by itself, then a member with the same id as a news item would get his profiles comments counted in the news.

That example does not work. How could i make it work?


another example of where this info is needed is the Browse, bottom of that link is the 'Intruder Fighter/Bomber', which is `item` ID 2.
Every time some posts a comment to my profile `member` ID 2, the 'Intruder Fighter/Bomber' gets ++.
That is why i added the type part.

MP = member profile
NP = news post
IC = item comment
JE = developer joural entery

Edited by Detail, 29 November 2005 - 07:52 PM.


#18 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 30 November 2005 - 06:09 AM

[u]items i[/u]
<snip>
JOIN comments com2 ON com2.inreplyto = [u]i[/u].id AND com2.type = 'NP'


I said
[u]comments com[/u]
<snip>
JOIN comments com2 ON com2.inreplyto = [u]com[/u].id


:shiftee:
Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum

#19 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 30 November 2005 - 10:30 AM

I said

<{POST_SNAPBACK}>

What you said does not work. If it did work, i'd be using it :grin:

#20 DCoder

DCoder

    One of the Few

  • Members
  • 331 posts
  • Location:Lithuania, Central Europe
  •  Old Grumpy Bastard

Posted 30 November 2005 - 10:41 AM

What I, err, wrote worked for me. I honestly can't see how what you wrote is supposed to work, cause you are counting the amount of comments that are written in response to the comment whose id is equal to the newsitem's id, IOW, your code only works if item.id = item.firstcomment, which is only guaranteed to be correct when you have only one news item.
I don't have time at the moment, but later I can draw a graph of how the tables are supposed to link by your description, and how they actually link by your code.
Ares - How do you want to improve YR today?
What's stopping you from migrating to Ares? We can't implement it unless you tell us!

ModEnc - C&C Modding Encyclopedia | Modders' Quote Database | Yet Another C&C Modding Forum




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users