efficient

[sabret00the]

is this code efficient?

while ($project_info = $DB_site->fetch_array($projects))
{
$project_info['text'] = nl2br(stripslashes($project_info['text']));
extract($project_info);
$time_posted = vbdate('n-j-y, g:i:s a', $timestamp);
// selects the number of times each confeession was rated
$number_of_rates = $DB_site->query_first("SELECT COUNT(*) AS votes
FROM project_rate
WHERE projectid = $projectid
");
$num_rates = number_format($number_of_rates['votes']);
// selects the username of the person who made the confeession
$who_done_it = $DB_site->query_first("SELECT username
FROM user
WHERE userid = $project_info[userid]
");

[NTLDR]

You really need to get those two queries within the while loop removed.

[sabret00the]

Any suggestions on an alternative?

[NTLDR]

Whats the $projects query? Its possible it can be integrated with that.

[sabret00the]

projects is pulling the text out one by one
where as as i have the second table is the project_rate table where i store each rating the projct text gets so later as i display it, i'm hoping to display the number of ratings each project i had on the page

project
projectid text timestamp userid

project_rate
rateid projectid userid timestamp rate


kinda like where on the index of TS3 you got the number of comments displayed along with each of your news items.

[sabret00the]

heres the exact query
$projects = $DB_site->query("
SELECT projectid, text, timestamp
FROM projects
$clause
ORDER BY projectid DESC
$limit
");

i think i may be able to do a left join (the user table to the projects table) to irradicate the last query, but i heard that left joins are bad?

[Xenon]

why should left joins be bad?

but here, it's suggest a inner join :)

$projects = $DB_site->query("
SELECT COUNT(*) AS votes, project_rate.projectid,
projects.text, projects.timestamp, user.username
FROM project_rate
INNER JOIN projects ON (project_rate.projectid = projects.projectid)
INNER JOIN user ON (user.userid = projects.userid)
$clause
GROUP BY project_rate.projectid
ORDER BY project_rate.projectid DESC
$limit
");
while ($project_info = $DB_site->fetch_array($projects))
{
$project_info['text'] = nl2br(stripslashes($project_info['text']));
$project_info['num_rates'] = number_format($project_info['votes']);
extract($project_info);
$time_posted = vbdate('n-j-y, g:i:s a', $timestamp);

that should work i think

[sabret00the]

dunno just read in another thread that left joins would cripple any highly active server.

[Xenon]

nah, it depends on which tables you join.

for example thread and post table shouldn't be joined, as these are bother very big ones.
on the other hand the session table and the user table could always be joined, as the session table is normally a very small one.

also it's always better to join instead of looping a query a lot :)

[sabret00the]

oh ok, thanks for explaining that and i'm just gonna try out the code now :)

[sabret00the]

i'm not getting errors, but heres the block in it's entirety

// sorts projects for main page
$projects = $DB_site->query("
SELECT COUNT(*) AS votes, project_rate.projectid,
projects.text, projects.timestamp, user.username
FROM project_rate
INNER JOIN projects ON (project_rate.projectid = projects.projectid)
INNER JOIN user ON (user.userid = projects.userid)
$clause
GROUP BY project_rate.projectid
ORDER BY project_rate.projectid DESC
$limit
");

if (!$DB_site->num_rows($projects))
{
eval(print_standard_error('confessions_error'));
}
else
{
$totalconf = number_format($DB_site->num_rows($projects));
while ($project_info = $DB_site->fetch_array($projects))
{
$project_info['text'] = nl2br(stripslashes($project_info['text']));
$project_info['num_rates'] = number_format($project_info['votes']);
extract($project_info);
$time_posted = vbdate('n-j-y, g:i:s a', $timestamp);
eval('$projects_bits .= "' . fetch_template("projects_bit") . '";');
}
}
i dunno what i've done, but for some reason the page has stopped echoing out the $projects_bit template

[sabret00the]

actually now i'm getting the num_rows error, so i ran the query in phpmyadmin and it's not returning anydata, i 'm not quite sure how to fix it?

[Xenon]

the query will just return something, if there are votes in the DB i think.

so it may be needed to add one dummy vote for each project.

[sabret00the]

ok, i'll play with it :)

as it's not even returning results with votes.