Will this be too taxing for my database?

[mididelight]

Hello,

The following code runs a while loop that queries the database X number of times (no more than 4 times). This will happen for several different pages on my site. I was wondering if running queries like this is too taxing for a database to handle? The queries are fetching normal select data, the where claus is determined by a comma delimited list of ID's of other rows.

Here is the code, what do you think?



<?php if ($current_category["related"] <> NULL){ ?>
<div class="right-module-style">
<h4>Artists Other Users Preferred</h4>
<ul>

<?php
// Get Related Artists
$related = $current_category["related"];
$related = explode(",",$related);

$x = 0;
$size = sizeof($related);
while ( $x < $size ) {
$result = mysql_query("
SELECT id, name
FROM dir_categories
WHERE dir_categories.id = '$related[$x]' ");

$row = mysql_fetch_row($result);

$artist = $row[1];
$artist = strtolower($artist);
$artist = ereg_replace(" |,|/|&|&amp;|'|\.","_",$artist);
echo '<li><a href="/pages/'.$row[0].'/'.$artist.'" title="'.$row[1].'">'.$row[1].'</a></li>'."\n";

$x++;
}
?>
</ul>
</div>
<?php } ?>

[russell]

All depends on the traffic to your site and the db design. That said, 4 queries is probably not too much unless you have a high-volume site, or are doing lots of inserts too.

However, why not do it all in one query?

WHERE dir_categories.id = '$related[$x]' "

becomes

WHERE dir_categories.id in ( '$related[$x]' )

explode your array and pass in all of the IDs at once...

[sridhar_423]

create an index for the column "id" on dir_categories table if the table is too large.

[mididelight]

i like your idea using IN, but how do I get the data now, I am having trouble.

[mididelight]

nevermind i figured it out...thanks for your help, using the IN was a big help

[sridhar_423]

$related="'".str_replace(",","','",$related)."'";

SELECT id, name FROM dir_categories WHERE dir_categories.id in($related);

(if your table size is large, i would suggest you to create an index on "id" column.)