Please login or register.

Login with username, password and session length
Advanced search  

News:

You need/want an older version of sNews ? Download an older/unsupported version here.

Pages: [1] 2

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

Joost

  • Guest
About: "SELECT * FROM ".db('prefix') etc.
« on: December 14, 2007, 09:19:28 am »

sNews uses a variety of queries to search the database. When searching only one result it uses function retrieve. For other queries it uses SELECT * (etc.). '*' is a wildcard and retrieves every field in a row.
However, not all retrieved data is is used. function categories() for instance, uses two wildcard queries on two tables. The second query retrieves all data from articles, including text, whether  it is used or not.
If a database contains 8 categories, each containing 8 articles the size of 5kb (not unusual for an average site), that would mean a (temporary) memory storage of 320kb data for each page call.

Does this make sense or am I overlooking something? Is it worth looking into that?

Your feedback, please. :)
Logged

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #1 on: December 14, 2007, 12:50:22 pm »

You are on to something...
Now to your example:
Code: [Select]
CREATE TABLE categories (
  id int(8) primary key auto_increment,
  name varchar(100) NOT NULL,
  seftitle varchar(100) NOT NULL,
  description varchar(255) NOT NULL,
  published varchar(4) NOT NULL default 'YES',
  catorder smallint(6) NOT NULL default '0'
);

Code: [Select]
// DISPLAY CATEGORIES
function categories() {
$categorySEF = get_id('category');
$class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').'" title="'.l('home').'">'.l('home').'</a></li>';
$query = "SELECT * FROM ".db('prefix')."categories WHERE published = 'YES' ORDER BY catorder ASC";
$result = mysql_query($query);
while ($r = mysql_fetch_array($result)) {
$calc_num_query = "SELECT * FROM ".db('prefix')."articles WHERE position = 1 AND category = $r[id] AND published = 1";
$cm_result = mysql_query($calc_num_query);
$num_rows = mysql_num_rows($cm_result);
$category_title = $r['seftitle'];
$class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'];
echo (s('num_categories') == 'on' ? ' ('.$num_rows.')' : '').'</a></li>';
}}

For the first wildcard query, yes the wildcard is needed.  For the second, I am not sure, because all you need is the numerical value of the rows, so one can call just the id.

My question is, now that you brought it up, for the first query, why not use the below instead of the default, except when a connotation is needed (function center or search for example)
Code: [Select]
$result = mysql_query("SELECT * FROM ".db('prefix')."categories WHERE published = 'YES' ORDER BY catorder ASC");
Next, would be why not use COUNT in the query to count the rows in the query? or is the idea to let PHP do the work instead of mysql?  But isn't the cm_result, and $num_row variable, 2 database calls?
Logged

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #2 on: December 14, 2007, 03:32:32 pm »

I'm not in dev machine, but we can a special retrieve, something like this

Code: [Select]
// ANOTHER SMART RETRIEVE FUNCTION
function special_retrieve($sql, $table, $field, $value) {
$query = "SELECT $sqlFROM ".db('prefix')."$table WHERE $field = '$value'";
$result = mysql_query($query);
$list = explode(', ',$sql);
$special = array();
while ($r = mysql_fetch_array($result)) {
   for ($i = 0; $i < sizeof($list); $i++)
$special[$i]= $r[$list[$i]];}
return $special;
}

So if we call it, just
$value=special_retrieve('title, seftitle, category','articles','id',$id);
$title=$value[0];
$seftitle=$value[1];
$category=$value[2];

So We just make a call once to mySQL
I cannot try here I'm now.

Another way is see better code for, calling what fields we want
Code: [Select]
$query = "SELECT title,seftitle,category FROM ".db('prefix')."article WHERE id=1";
« Last Edit: December 14, 2007, 03:36:54 pm by Rui Mendes »
Logged
Need a Job on Europe. Linkdin - Facebook / Group

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #3 on: December 14, 2007, 07:22:23 pm »

Thanks for the feedback guys. :)
In my quest for knowledge, this question came to mind and you code-gurus jumped right at it. It seems that you think the same. I agree with Rui, that a special retrieve function is not really needed and would make the code difficult to read (Mika alreadyexpressed some doubt about function retrieve in snews1.6DE).
Personally I like Bakercad's solution, with the smart query.

I added an if statement to switch the second query on and off (not shure if it is needed or should be done smarter).

Code: [Select]
<?php
// DISPLAY CATEGORIES
function categories() {
$categorySEF get_id('category');
$class = empty($categorySEF) ? ' class="current"' '';
$base db('website');
echo '<li><a'.$class.' href="'.$base.'">'.l('home').'</a></li>';
$query 'SELECT id, seftitle, description, name, catorder FROM '.db('prefix').'categories WHERE published = "YES" ORDER BY catorder ASC'
$result mysql_query($query);
while ($r mysql_fetch_array($result)) {
$numbers s('num_categories');
if ($numbers == 'on') {
$num_rows mysql_fetch_assoc(mysql_query('SELECT COUNT(id) as num FROM '.db('prefix').'articles WHERE position = 1 AND category = '.$r['id'].' AND published = 1'));
}
$category_title $r['seftitle'];
$class $category_title == $categorySEF ' class="current"' '';
echo '<li><a'.$class.' href="'.$base.$category_title.'/" title="'.$r['description'].'">'.$r['name'];
echo ($numbers == 'on' ' ('.$num_rows['num'].')' '').'</a></li>';
}}
?>

I intend to look into this, next week. There are 24 wildcard queries and approximately 30 function retrieve queries. «hanging the wildcard  queries won't be much of a problem, as changes are only needed on one line for each. PHP5 will harass me with warnings if needed. I will only combine 'retrieve' queries when it doesn't effect the readability/moddability (new word?  ;) ) of the code to much.


Updated after turning on E_NOTICE
« Last Edit: January 30, 2008, 06:25:57 am by Joost »
Logged

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #4 on: December 14, 2007, 10:44:20 pm »

Baker, yes i like that way better!!

Rui,  can your fucntion allow for vairous AND's in the query call?
Logged

philmoz

  • High flyer
  • ULTIMATE member
  • ******
  • Karma: 161
  • Posts: 1988
    • fiddle 'n fly
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #5 on: December 15, 2007, 11:19:28 pm »

Another way is see better code for, calling what fields we want
Code: [Select]
$query = "SELECT title,seftitle,category FROM ".db('prefix')."article WHERE id=1";
I know Mika was loking at this some time back. I don't know how far he got with it.
Logged
Of all the things I have lost, it is my mind that I miss the most.

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #6 on: December 16, 2007, 06:55:09 am »

I have a dumb question on a subject i know little about, but the concept sounds good... why not use OOP? 

I am reading this series to give me some more understanding with PHP and i found this article below, that's what gave me the idea:
http://devzone.zend.com/article/638-PHP-101-part-7-The-Bear-Necessities
Logged

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #7 on: December 16, 2007, 11:44:28 am »

I have the solution, replace a retrieve function
Code: [Select]
// SMART RETRIEVE FUNCTION
function retrieve($parameters, $table, $field, $value){
$list = explode(',',$parameters);
if (sizeof($list)>1) $values = array();
$result = mysql_query("SELECT $parameters FROM ".db('prefix')."$table WHERE $field = '$value'");
if (mysql_num_rows($result)>0) {
while ($r = mysql_fetch_array($result)) {
if (sizeof($list)<=1) $values = $r[$parameters]; else for ($i = 0; $i < sizeof($list); $i++) {$values[$list[$i]]= $r[$list[$i]];}}
return $values;
}}

Now we can call the simple easy way for some fields
$value=retrieve('id,title,seftitle','articles','seftitle',$article);

Here is the original function title
Quote
function title() {
   echo '<base href="'.db('website').'" />';
   $categorySEF = get_id('category'); $articleSEF = get_id('article');
   $categoryName = retrieve('name', 'categories', 'seftitle', $categorySEF);
   $articleTitle = retrieve('title', 'articles', 'seftitle', $articleSEF);
   if (!empty($articleTitle)) {$title = $articleTitle.' - ';}
   if (!empty($categoryName)) {$title .= $categoryName.' - ';}
   $title .= s('website_title');
   echo '<title>'.$title.'</title>';
   echo '<meta http-equiv="Content-Type" content="text/html; charset='.s('charset').'" />';
   if (!empty($articleSEF)) {
      $query = "SELECT * FROM ".db(prefix)."articles WHERE seftitle = '$articleSEF'";
      $result = mysql_query($query);
      while ($r = mysql_fetch_array($result)) {$dmeta = $r['description_meta']; $kmeta = $r['keywords_meta'];
}
   }
   echo '<meta name="description" content="'.(!empty($dmeta) ? $dmeta : s('website_description')).'" />';
   echo '<meta name="keywords" content="'.(!empty($kmeta) ? $kmeta : s('website_keywords')).'" />';
   if ($_SESSION[db('website').'Logged_In'] == token()) {js();}
}
If you see the red code is a 2nd call to articles table (first is blue)


With my code one call to the articles
Quote
function title() {
   echo '<base href="'.db('website').'" />';
   $categorySEF = get_id('category'); $articleSEF = get_id('article');
   $categoryName = retrieve('name', 'categories', 'seftitle', $categorySEF);
   $r = retrieve('title,description_meta,keywords_meta', 'articles', 'seftitle', $articleSEF);
   if (!empty($r['title'])) {$title = $r['title'].' - ';}
   if (!empty($categoryName)) {$title .= $categoryName.' - ';}
   $title .= s('website_title');
   echo '<title>'.$title.'</title>';
   echo '<meta http-equiv="Content-Type" content="text/html; charset='.s('charset').'" />';
   if (!empty($articleSEF)) {$dmeta = $r['description_meta']; $kmeta = $r['keywords_meta'];}
   echo '<meta name="description" content="'.(!empty($dmeta) ? $dmeta : s('website_description')).'" />';
   echo '<meta name="keywords" content="'.(!empty($kmeta) ? $kmeta : s('website_keywords')).'" />';
   if ($_SESSION[db('website').'Logged_In'] == token()) {js();}
}

Rui,  can your fucntion allow for vairous AND's in the query call?

Yes, I think so(I never tried), here an example:
Quote
$categoryName = retrieve('name', 'categories', 'seftitle', $categorySEF.' AND published=YES');
if $categoryName has not publish yet, so the name will be empty.

You know retrieve function works only for one record.
« Last Edit: December 16, 2007, 12:52:45 pm by Rui Mendes »
Logged
Need a Job on Europe. Linkdin - Facebook / Group

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #8 on: December 16, 2007, 04:06:18 pm »

I have a dumb question on a subject i know little about, but the concept sounds good... why not use OOP?

the main (& probably only) reason that sNews is not using OOP......coding simplicity.  Luka wanted sNews to be easy for "beginner" developers.  OOP is not for beginners.  I'm sure you've seen the discussions about making sNews more "modular", doing so would require a lot more OOP and multiple files.  This is not what Luka's purpose was when he created sNews.  sNews is simple and, as you can see, highly expandable.

Again, the concept is what is intriguing. My knowledge of it is null.  Thanks for clarification
Logged

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #9 on: February 10, 2008, 07:58:02 am »

Thanks for the feedback guys. :)
In my quest for knowledge, this question came to mind and you code-gurus jumped right at it. It seems that you think the same. I agree with Rui, that a special retrieve function is not really needed and would make the code difficult to read (Mika alreadyexpressed some doubt about function retrieve in snews1.6DE).
Personally I like Bakercad's solution, with the smart query.

I added an if statement to switch the second query on and off (not shure if it is needed or should be done smarter).

Code: [Select]
<?php
// DISPLAY CATEGORIES
function categories() {
$categorySEF get_id('category');
$class = empty($categorySEF) ? ' class="current"' '';
$base db('website');
echo '<li><a'.$class.' href="'.$base.'">'.l('home').'</a></li>';
$query 'SELECT id, seftitle, description, name, catorder FROM '.db('prefix').'categories WHERE published = "YES" ORDER BY catorder ASC'
$result mysql_query($query);
while ($r mysql_fetch_array($result)) {
$numbers s('num_categories');
if ($numbers == 'on') {
$num_rows mysql_fetch_assoc(mysql_query('SELECT COUNT(id) as num FROM '.db('prefix').'articles WHERE position = 1 AND category = '.$r['id'].' AND published = 1'));
}
$category_title $r['seftitle'];
$class $category_title == $categorySEF ' class="current"' '';
echo '<li><a'.$class.' href="'.$base.$category_title.'/" title="'.$r['description'].'">'.$r['name'];
echo ($numbers == 'on' ' ('.$num_rows['num'].')' '').'</a></li>';
}}
?>

I intend to look into this, next week. There are 24 wildcard queries and approximately 30 function retrieve queries. «hanging the wildcard  queries won't be much of a problem, as changes are only needed on one line for each. PHP5 will harass me with warnings if needed. I will only combine 'retrieve' queries when it doesn't effect the readability/moddability (new word?  ;) ) of the code to much.


Updated after turning on E_NOTICE



This would be better, 2 queries into 1
Code: [Select]
function categories() {
/*
$categorySEF = get_id('category');
$class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').'" title="'.l('home').'">'.l('home').'</a></li>';
$query = "SELECT * FROM ".db('prefix')."categories WHERE published = 'YES' ORDER BY catorder ASC";
$result = mysql_query($query);
while ($r = mysql_fetch_array($result)) {
$calc_num_query = "SELECT * FROM ".db('prefix')."articles WHERE position = 1 AND category = $r[id] AND published = 1";
$cm_result = mysql_query($calc_num_query);
$num_rows = mysql_num_rows($cm_result);
$category_title = $r['seftitle'];
$class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'];
echo (s('num_categories') == 'on' ? ' ('.$num_rows.')' : '').'</a></li>';
}
*/
$categorySEF = get_id('category'); $class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').'" title="'.l('home').'">'.l('home').'</a></li>';
$result = mysql_query("select cat.id, cat.name, cat.seftitle, cat.description, cat.catorder, (select count(*) from articles where category = cat.id AND position = 1 AND published = 1) as cat_articles from categories as cat WHERE published = 'YES' ORDER BY catorder,id ASC");
while ($r = mysql_fetch_array($result)) {
$category_title = $r['seftitle']; $class = $category_title == $categorySEF ? ' class="current"' : '';
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'].$num.'</a></li>';
}}
« Last Edit: February 10, 2008, 08:03:44 am by equilni »
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #10 on: February 10, 2008, 08:11:04 am »

telepathy?  ???
Logged

Joost

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #11 on: February 10, 2008, 09:48:53 am »

Quick and dirty

5 pages and 95 articles Total: 100
Categories: 10

Display number of articles next to a category: OFF
Script timer: 0.191394 seconds. Iteration: 80 (Bob, Joost)
Script timer: 0.313965 seconds. Iteration: 80 (Jason)

Display number of articles next to a category: ON
Script timer: 0.918460 seconds. Iteration: 80 (Bob, Joost)
Script timer: 0.305142 seconds. Iteration: 80 (Jason)
__________________________________________________

70 pages and 1330 articles Total: 1400
Categories: 10

Display number of articles next to a category: OFF
Script timer: 0.208885 seconds. Iteration: 80 (Bob, Joost)
Script timer: 0.479294 seconds. Iteration: 80  (Jason)

Display number of articles next to a category: ON
Script timer: 2.986294 seconds. Iteration: 80 (Bob, Joost)
Script timer: 0.474131 seconds. Iteration: 80  (Jason)

The (Bob, Joost) version is faster when number display is set to off, but much slower when set to on. As expected.
« Last Edit: February 12, 2008, 12:44:42 am by Joost »
Logged

H.A.C

  • Jr. Member
  • **
  • Karma: 20
  • Posts: 86
  • i love snews community for its great members
    • Chasoft Corporation
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #12 on: February 10, 2008, 12:27:59 pm »

Thanks all for cutting down memory used by the original categories function.

But how about MOD "Multiple Areas for Categories ". It's a extremely useful function.
Logged
I'm not available ....until the end of the year 2009!
----------------------------
The largest website powered by sNews is in its way to become the biggest!
My great personal website powered by sNews 1.6
http://www.chasoft.net
Come to see how it's great!

Rui Mendes

  • Development,Testing, Support
  • Hero Member
  • *****
  • Karma: 195
  • Posts: 1009
  • sNews1.7
    • Comunidade Portuguesa
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #13 on: February 10, 2008, 01:21:33 pm »

Jason really nice work.

Thanks Joost for Testing.

Thank you H.A.C. for using Multiple Areas for Categories
I think (I didn't try yet) just replace categories function - Only for who are using this mod Multiple Areas for Categories
Code: [Select]
<?php
// DISPLAY CATEGORIES
function categories($num$showhome$showcnt$start$size) {
if ($num==''$num=0;
if ($showhome==''|| $showhome!='false')$showhome='true'; else $showhome='false';
if ($showcnt=='' || $showcnt!='false')$showcnt='true'; else $showcnt='false';
$categorySEF get_id('category'); $class $category_title == $categorySEF ' class="current"' '';
if ($num==|| $showhome=='true') echo '<li><a'.$class.' href="'.db('website').'" title="'.l('home').'">'.l('home').'</a></li>';
$subqwr = !empty($start) && !empty($size) ? ' LIMIT $start, $size' '';
$result mysql_query('SELECT id, name, seftitle, description, catorder, (select COUNT(*) FROM '.db('prefix').'articles WHERE category = id AND position = 1 AND published = 1) AS cat_articles from '.db('prefix').'categories AS cat WHERE published = "YES" AND AND catnumber='.$num.' ORDER BY catorder,id ASC $subqwr');  
while ($r mysql_fetch_array($result)) {
      $category_title $r['seftitle'];
      $class $category_title == $categorySEF ' class="current"' '';
      echo '<li><a'.$class.' href="'.db('website').$category_title.'/" title="'.$r['description'].'">'.$r['name'];
      if ($showcnt=='true' && $r['showcount']=='YES') echo ' ('.$r['cat_articles'].')'; echo '</a></li>';
}}
?>

« Last Edit: February 10, 2008, 01:37:17 pm by Rui Mendes »
Logged
Need a Job on Europe. Linkdin - Facebook / Group

centered

  • Guest
Re: About: "SELECT * FROM ".db('prefix') etc.
« Reply #14 on: February 10, 2008, 02:14:28 pm »

Joost, may I ask to test once more?

Code: [Select]
function categories() {
$categorySEF = get_id('category');
$class = $category_title == $categorySEF ? ' class="current"' : '';
echo '<li><a'.$class.' href="'.db('website').'" title="'.l('home').'">'.l('home').'</a></li>';
// added
if (s('num_categories') != 'on') { $sql = ''; }
else { $sql = ", (select count(*) from articles where category = cat.id AND position = 1 AND published = 1) as cat_articles "; }
// changed
$result = mysql_query("select cat.id, cat.name, cat.seftitle, cat.description$sql from categories as cat WHERE published = 'YES' ORDER BY catorder,id ASC");
while ($r = mysql_fetch_array($result)) {
$category_title = $r['seftitle']; $class = $category_title == $categorySEF ? ' class="current"' : '';
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'].$num.'</a></li>';
}}
« Last Edit: February 10, 2008, 02:18:51 pm by equilni »
Logged
Pages: [1] 2