[1cookie]

hi


edit:
This is all tied to a Drupal function, namely:

db_query_range(SELECT c.* FROM {comments} c WHERE
  c.status = %d ORDER by c.timestamp', COMMENT_NOT_PUBLISHED, 0,
    $num_posts);

But i'm figuring it's all just plain old SQL - if you catch my drift?
The function definition can be found here.







OK,
consider this:
db schema, specifically the 'comments' table.


where

cid = comment id
pid = post id

OK, things i know:

a '.' means a period or concatenation in php.
an asterix (*) in an SQL query means select All from a table set.

Im just looking for a little clarity in the following query:

$sql = (SELECT c.* FROM {comments} c WHERE
  c.status = %d ORDER by c.timestamp', COMMENT_NOT_PUBLISHED, 0,
    $num_posts);

Disregarding the

COMMENT_NOT_PUBLISHED

argument, i guess this is some kind of Drupal (Boolean) pre-defined constant.

I'm reading

SELECT c.* FROM {comments}

as SELECT ALL FROM the cid column from the {comments} table. Is this correct? And if someone could walk me through the:

WHERE
  c.status = %d ORDER by c.timestamp', COMMENT NOT PUBLISHED, 0,
    $num_posts);

query.

I mean i have an inkling that the ORDER by clause accepts:

c.timestamp', COMMENT NOT PUBLISHED, 0,
    $num_posts

(collectively) as arguments, though i'm not 100%?

Again

ORDER by c.timestamp

, in plain English, is this equivalent to saying: 'Order the cid column by most recent posts?'


help

This post has been edited by 1cookie: 11 June 2010 - 10:45 AM

[Martyr2]

SELECT c.* FROM {comments} c WHERE
  c.status = %d ORDER by c.timestamp

Select all columns from the table called "c" (which so happens to be the alias for the comments table). This means select all columns from the comments table where the 'status' column is equal to an specific integer (%d is a format specifier value for integer) and order the results by the comments table's timestamp column (aka sort by the comments date).

The result is a list of comments (showing all fields) for a specific status and ordered by their date. Drupal will limit this number of result rows to the value specified by $num_posts.

Hope that clears things up.

This post has been edited by Martyr2: 13 June 2010 - 08:29 PM

[1cookie]

Martyr2, on 13 June 2010 - 07:25 PM, said:

SELECT c.* FROM {comments} c

Select all columns from the table called "c" (which so happens to be the alias for the comments table).

hi

So am i correct in thinking that the choice of the letter 'c' is left to the programmers discretion? i.e. he/she could just as easily have used:

SELECT z.* FROM {comments} z

if desired? What's the purpose of an alias? Is it simply to create a sub-set of the {comments} table?

Quote

This means select all columns from the comments table where the 'status' column is equal to an specific integer (%d is a format specifier value for integer)

So

c.status = %d

is looking for either one of the arguments:

COMMENT_NOT_PUBLISHED, 0,

?

[1cookie]

Well

<?php



include_once('inc/db.inc');



$mysqli = new mysqli($host, $username, $passwd, $db);



/* check connection */

if (mysqli_connect_errno()) {

    printf("Connect failed: %s\n", mysqli_connect_error());

    exit();
    

}
//else {print 'connected';}

$sql = 'SELECT z.* FROM City z WHERE ID > 10';


if($result = $mysqli->query($sql)) {

   while ($row = $result->fetch_object()) {

      printf( "%s (%s) <br>", $row->Name, $row->Population);
   }


}

works fine, and returns all rows from the City table courtesy of the 'World.sql' dump.


Still a bit puzzled though, why not just use:

$sql = 'SELECT * FROM City WHERE ID > 10';

?

There must be a reason..