3 Replies - 866 Views - Last Post: 18 June 2010 - 07:41 AM Rate Topic: -----

#1 1cookie  Icon User is offline

  • D.I.C Regular

Reputation: -5
  • View blog
  • Posts: 338
  • Joined: 19-October 06

sql query

Posted 11 June 2010 - 09:27 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: sql query

#2 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 4442
  • View blog
  • Posts: 12,314
  • Joined: 18-April 07

Re: sql query

Posted 13 June 2010 - 08:25 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 1cookie  Icon User is offline

  • D.I.C Regular

Reputation: -5
  • View blog
  • Posts: 338
  • Joined: 19-October 06

Re: sql query

Posted 14 June 2010 - 03:34 AM

View PostMartyr2, 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,
?

:)
Was This Post Helpful? 0
  • +
  • -

#4 1cookie  Icon User is offline

  • D.I.C Regular

Reputation: -5
  • View blog
  • Posts: 338
  • Joined: 19-October 06

Re: sql query

Posted 18 June 2010 - 07:41 AM

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..:)

:red_indian:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1