Please login or register.

Login with username, password and session length
Advanced search  
Pages: 1 [2]

Author Topic: About: "SELECT * FROM ".db('prefix') etc.  (Read 7588 times)

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #15 on: February 10, 2008, 04:33:40 pm »

I am not going to test it now. Maybe tomorrow. But it is two queries combined. So I expect a perfect combination of the best of both. I have rewritten it almost likewise, except more straight forwarded. Yours looks nicer. :)
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #16 on: February 12, 2008, 01:32:39 am »

function categories() rewritten for sNews 1.6 for better performance and e_notice proof.


Code: [Select]
<?php
function categories() {
$categorySEF get_id('category'); 
$class = empty($categorySEF) ? ' class="current"' '';
echo '<li><a'.$class.' href="'.db('website').'">'.l('home').'</a></li>';
if (s('num_categories') != 'on') { $sql ''; } 
else { $sql ', (SELECT COUNT(*) from '.db('prefix').'articles WHERE category = '.db('prefix').'categories.id AND position = 1 AND published = 1) AS cat_articles '; }
$result mysql_query('SELECT id, name, seftitle, description'.$sql.' FROM '.db('prefix').'categories WHERE published = "YES" ORDER BY id ASC');
while ($r mysql_fetch_array($result)) {
$category_title $r['seftitle']; $class $category_title == $categorySEF ' class="current"' '';
if (s('num_categories') != 'on') { $num ''; } else { $num ' ('.$r['cat_articles'].')'; }
echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'].$num.'</a></li>';
}}
?>
« Last Edit: February 12, 2008, 03:45:21 pm by Joost »
Logged

Fred K

  • Still trying to learn stuff
  • ULTIMATE member
  • ******
  • Karma: 130
  • Posts: 2728
    • Personal
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #17 on: February 12, 2008, 01:10:06 pm »

Quote from: baloo on ice
for better performance and e_notice proof.
So, is it foolproof also? 'Cause I was thinking of testing it... ;D
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #18 on: February 12, 2008, 01:33:52 pm »

Quote from: baloo in rehab
for better performance and e_notice proof.
So, is it foolproof also? 'Cause I was thinking of testing it... ;D

There is a manual about copy pasting. Google will show you. :D
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #19 on: February 12, 2008, 03:51:13 pm »

Not totally foolproof.... on my side. I had to fix it already.
Logged

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #20 on: February 13, 2008, 01:00:24 am »

How was it not foolproof and what are the fixes?
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #21 on: February 13, 2008, 01:12:23 am »

'Not foolproof' referred to my  stupidity, I made a mistake while cleaning up, just before publishing.

Was something like:

Code: [Select]
<?php
if (s('num_categories') != 'on') { echo ''; } else { $num ' ('.$r['cat_articles'].')'; }
echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'].isset($num).'</a></li>';
?>

is now
Code: [Select]
<?php
if (s('num_categories') != 'on') { $num ''; } else { $num ' ('.$r['cat_articles'].')'; }
echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'].$num.'</a></li>';
?>
Logged

invarbrass

  • Full Member
  • ***
  • Karma: 18
  • Posts: 117
    • http://snews.extremebittorrent.com
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #22 on: February 13, 2008, 04:39:51 am »

@Joost: You can take a look at the mod I sent you, I had already done the hard work.  :) In fact, I had optimized almost all of snews' SQL queries, take a look at it
« Last Edit: February 13, 2008, 04:49:41 am by invarbrass »
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #23 on: February 13, 2008, 04:58:41 am »

I noticed. However it doesn't cover all. Take a look at ours. They perform very good. Now I am trying to tackle function extra.
Logged

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #24 on: February 13, 2008, 05:01:23 am »

If you can tackle my subcategory sql optimization, then you are a god: http://snewscms.com/forum/index.php?topic=6870.msg47470#msg47470

I am looking for a way to do this:
Code: [Select]
id -- name -- seftitle -- subcatSEF -- subnum_rows -- art_num -- art_title -- art_seftitle
1 -- catName -- catname -- NULL -- NULL -- 2 -- My article -- my_article
1 -- catName -- catname -- NULL -- NULL -- 2 -- My second article -- my_second_article
1 -- subcatName -- catname -- subcatname -- 1 -- 1 -- My subarticle -- my_subarticle

I have everything but the article title and article seftitle

Code
Code: [Select]
select cat.id     
, cat.name
, cat.seftitle
, null as subcatSEF
, null as subnum_rows
, (select count(*) from articles where category = cat.id) as art_num
from categories as cat
where cat.subcat = 0
union all
select subcat.id     
, subcat.name
, cat.seftitle
, subcat.seftitle as subcatSEF
, (select count(*) from categories where subcat = cat.id) as subnum_rows
, (select count(*) from articles where category = subcat.id) as art_num
from categories as cat
inner
join categories as subcat
on cat.id = subcat.subcat
where cat.subcat = 0
order by id;
Logged
Pages: 1 [2]