efficient

[sabret00the]

is this code efficient?

PHP Code:

                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

PHP Code:

            $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

PHP Code:

$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