Please login or register.

Login with username, password and session length
Advanced search  

News:

Latest sNews - sNews 1.7 - with its own forums - for discussion and user mods.

Pages: 1 [2]

Author Topic: New comments not showing  (Read 9392 times)

Joost

  • Guest
Re: New comments not showing
« Reply #15 on: September 06, 2008, 08:34:55 PM »

Most people who use cms, don't care about code.
 

Yes, you're right, for them, does not matter what is under hood if car is faster and stable... I did not referred them...  "Northern European technology" is very good but extremely complicate :P, not too god for custom area :P (at my level).

I understand. However, it is not that complicated, these are "simple" straightforward sql queries.

An explanation:
In older versions function retrieve was used extensively firing approximately 150 queries to the database which slows down performance tremendously and annoys hosting companies. That data was joined/processed on php level.
To avoid that we started writing queries that select all necessary data at once, no more no less.
Rows from two different tables can be joined in the FROM clause like

FROM articles, categories

However, in many cases several instances of the same table needs to be joined. This won't work:

   FROM articles, articles

... because mysql wants to know which is which. So an alias is made this way:

   FROM articles AS a, articles AS b

Now we have table a and table b instead of  two table articles.
For the selected (retrieved) data, mysql needs to know the difference between the id from table a and table b. Simply concatenate tablename and fieldname  in the SELECT clause.

SELECT a.id, b.id, a.title FROM articles AS a, articles AS b

Be aware that in the first example with different tables, concatenating is needed as well, if a field is retrieved that exist in both tables. To limit the length of the query, I prefer aliasing when this occurs:

Unaliased
SELECT prefix_articles.id, prefix_articles.seftitle, prefix_categories.id, prefix_articles.titleprefix_categories.seftitle FROM prefix_articles, prefix_categories

Aliased
SELECT a.id a.seftitle, b.id, a.title b.seftitle FROM articles AS a, categories AS b

Most queries do also have a WHERE clause, telling mysql where to look, in which row:
WHERE id= 3

This will return only one row, because an id is unique, there is only one with the value '3'
If the WHERE clause addresses more than 1 row ordering these rows might be useful.
Here we use category, because more than one article can exist in a category with the value 3 and we order by date starting with the most recent:

SELECT a.*, name  FROM articles AS a, categories as b WHERE category = 3 ORDER BY date DESC

note: You can prefix an * as the example shows. `name` is not prefixed as it exists only in table categories (the categories alias "b" is therefore not really needed).

Code like LIMIT 4 is (almost) selfexplaining. It tells mysql to return the first 4 results.

SELECT a.*, name  FROM articles AS a, categories as b WHERE category = 3 ORDER BY date DESC LIMIT 4



Back on topic:

Debugging is not that hard: If a standard (unmodified) query fails, it is most likely a incompatibility issue:
Mysql version < 4.1
Running strict mode.

In this case it was the former.

« Last Edit: September 06, 2008, 09:16:37 PM by Joost »
Logged

funlw65

  • Hero Member
  • *****
  • Karma: 96
  • Posts: 771
    • Country Lab
Re: New comments not showing
« Reply #16 on: September 06, 2008, 09:03:05 PM »

Thank you for explanation Joost, I begin to understand.  Of course, I need more training but is a beginning. Still too hard to follow but not impossible (need to use a pencil and a paper ;D ).
 
Logged
Pages: 1 [2]