Jump to content

php_begins

Members
 View Profile  See their activity

  • Posts

    259

  • Joined

  • Last visited

 Content Type 

Posts posted by php_begins

    print dates in descending order without affecting the query

    in PHP Coding Help

    Posted

    i am printing the dates from the last 30 days in ascending order and then comparing them in my query to print the results. How can I can print the below array in descending order without affecting my query?

       
  $thirtydaysago = time() - (30 * 24 * 60 * 60);
  
  $oneday=24 * 60 * 60;
  for($i=0;$i<31;$i++)
  {
     $d[$i]= $thirtydaysago + ($i*$oneday);
 echo date('Y-m-d',$d[$i])."<br>";
  }
  for($i=0;$i<31;$i++)
  {
     $postsql=mysql_query("SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') AS FmtDate, COUNT(postid) AS PostCnt FROM post " . 
              "WHERE dateline < '" . $d[$i+1] . "' AND dateline >= '" . $d[$i] . "' GROUP BY DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') DESC") or die(mysql_error());
   

   if($postsql_rows=mysql_fetch_assoc($postsql))
   {
    $data_date[$i]["date"]=$postsql_rows['FmtDate'];
$data_postcount[$k][$i]["postcount"]=$postsql_rows['PostCnt'];
   }
}
$k++;
}

    unable to print distinct dates and display in a specific format..

    in PHP Coding Help

    Posted

    That looks like a unix timestamp not a datetime (which is an SQL data type). Hopefully, you have that defined as an integer and not a varchar (or other string type).

     

    There are a couple of issues with your code that I would cleanup. Do not use 'select *' and mysql_num_rows() to determine how many rows exist, use 'select count(*)' instead. You are wasting a lot of time and resources that way. Also, do not run queries in a loop if you can avoid it. I would suggest writing that (first) process along these lines:

     

    $sql = "SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) AS FmtDate, COUNT(postid) AS PostCnt
FROM post WHERE dateline >='$limit' 
GROUP BY DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d')) DESC";
$res = mysql_query($sql);
if ($res !== false) {
  while ($row = mysql_fetch_assoc($res)) {
    echo 'Date: ' . $row['FmtDate'] . '  Posts: ' . $row['PostCnt'];
  }
}

     

    That's just off the top of my head. You will have to add all of your HTML and stuff where the "echo" is; but that should give you an idea.

     

    I don't know why you were using ">=", unless it was because of the TIME portion of the timestamp. The query above should give you post counts by date, regardless of time. Also, I do not see where you are using the $acronym variables to limit the queries; so I'm not sure how you would incorporate that.

     

    This concept should work for your other tables, look into JOINs and aggregate functions (in mySql) to see how to work that. If you get stuck  on the other queries, post your work and we can take a look.

     

    THanks so much..the above code works good...but it shows only the last 30 dates where there have been posts..I want to display all last 30 dates and if there are no posts on that particular date, it should echo the post count as 0..

    unable to print distinct dates and display in a specific format..

    in PHP Coding Help

    Posted

    Hi I have been trying to populate my data from 3 tables and display like this:

     

                      DATE            $acronym1                        $acronym2

    POSTS  1/9/2011          10 (this is no of posts)                  3

    POSTS  31/8/2011        20                                                10

    and so on. the threads and user table will be similar as well.

    I am not able to populate the distinct dates since the dateline contains time as well. So when I try to print out the just dates(without time) , it prints  with the dates repeated. it prints something like this:

    1/9/2011 1

    1/9/2011 1

    and so on..

    How can populate and print the the date and number of posts in the above format. Here is my complete code below:

     

     

    <?php
error_reporting(-1);
//ini_set('display_errors',1);
include("db.php");
//$thirty_days_ago = strtotime("-30 days");
$limit = strtotime("-1 month");
$sql=mysql_query(("SELECT * from new_site"),$con) or die(mysql_error());
while($row=mysql_fetch_assoc($sql))
{
   $include=$row['include'];
   $forumurl=$row['forumurl'];
   $url=$row['url'];
   $acronym=$row['acronym'];
   include("$include");
   //echo $include."<br>";
   $configdbname=$config['Database']['dbname'];
   $configdbconport=$config['MasterServer']['servername'].":".$config['MasterServer']['port'];
   $configusername=$config['MasterServer']['username'];
   $configpassword=$config['MasterServer']['password'];
   $configprefix=$config['Database']['tableprefix'];
   /* Connect to the required database */
   $con2=mysql_connect($configdbconport, $configusername, $configpassword);
   if (!$con2)
   {
       die('Could not connect: ' . mysql_error());
   }
   mysql_select_db($configdbname, $con2);
   $postdate=mysql_query("SELECT DISTINCT dateline,postid from post WHERE dateline >='$limit' ORDER by dateline DESC") or die(mysql_error());
   while($postdate_results=mysql_fetch_assoc($postdate))
   {
      $postdate_record=$postdate_results['dateline'];
     // echo $postdate."<br>";
      $postdate_formatted=date('M dS  Y ',$postdate_results['dateline']);
      $post_count=mysql_query("SELECT * from post WHERE dateline >='$postdate_record'");
      while($post_count_results=mysql_fetch_assoc($post_count))
      {
         //$postdate_formatted=date('M dS  Y ',$post_dateline_results['dateline']);
         $posts=mysql_num_rows($post_count) or die(mysql_error());
         //echo $acronym.":POSTS:".$posts."<br>";
         echo '<table border="1">';
         echo "<tr>";
         echo "<th>Category</th>";
         echo "<th>".$acronym."</th>";
         echo "</tr>";
         echo "<tr>";
         echo "<td>POSTS:DATE:".$postdate_formatted."</td>";
         echo "<td>".$posts."</td>";
   echo "</tr>";
      }
     
   $threaddate=mysql_query("SELECT * from thread WHERE dateline >='$limit' ORDER by dateline DESC") or die(mysql_error());
   while($threaddate_results=mysql_fetch_assoc($threaddate))
   {
      $threaddate_record=$threaddate_results['dateline'];
            $threaddate_formatted=date('M dS  Y ',$threaddate_results['dateline']);
            $thread_count=mysql_query("SELECT * from thread WHERE dateline='$threaddate_record'");
           while($thread_count_results=mysql_fetch_assoc($thread_count))
           {
              $threads=mysql_num_rows($thread_count) or die(mysql_error());
             //echo $acronym.":THREADS:".$threads."<br>";
             echo "<tr>";
             echo "<td>THREADS:DATE:".$threaddate_formatted."</td>";
             echo "<td>".$threads."</td>";
             echo "</tr>";
             $userdate=mysql_query("SELECT * from user WHERE joindate >='$limit' ORDER by joindate DESC") or die(mysql_error());
             while($userdate_results=mysql_fetch_assoc($userdate))
    {
        $userdate_record=$userdate_results['joindate'];
        $userdate_formatted=date('M dS  Y ',$userdate_results['joindate']);
                 $user_count=mysql_query("SELECT * from user WHERE joindate='$userdate_record'");
                 while($user_count_results=mysql_fetch_assoc($user_count))
                 {
                    $users=mysql_num_rows($user_count) or die(mysql_error());
                  //echo $acronym.":USERS REGISTERED:".$users."<br>";
                    echo "<tr>";
                    echo "<td>REGISTERED USERS::DATE:".$userdate_formatted."</td>";
                    echo "<td>".$users."</td>";
                   echo "</tr>";
       }
              }

            }
         }
  
       }
     }

echo "</table>";
?>

    display result of last 30 days

    in MySQL Help

    Posted

    I have a post table that has some of these fields:

    i want to query the number of posts that happened in the last 30 days and display the date.i.e display posts that happened on each date and the also display the date.

    EG:

                NO  DATE

    POSTS 10  1/9/2011

    POSTS  20  31/8/2011

     

    i know i can do a select postid and then do a mysql_num_rows to get the number of posts. But I am not sure how to get the result of last 30 days and display that date. Here is my table structure

    postid  username userid   title             dateline

    1            test                3        member          1186945212

    3            tester              5          JM                  1187106827

    executing a query stored in a variable..

    in PHP Coding Help

    Posted

    Hi, In some cases I need to execute  a default query , so I am storing the SELECT statement in a variable like this and executing it:

     

    $default_query="SELECT * from user where userid='$userid'";
$user_query=mysql_query($default_query);

    The above code returns an empty results.

    But if I execute it without the variable it works fine.

    $user_query=mysql_query("SELECT * from user where userid='$userid'");

     

    Am I syntatically wrong somewhere?

    printing a specific number from an array

    in PHP Coding Help

    Posted

    Hello, i retrieve an array from the database that looks something like this:

     

    $var=a:1:{s:2:"cc";a:1:{i:22;s:11:"TESTING";}};

     

    I need to retrieve the number corresponding to the character i: and print it(11 in the first case). 

    For example ,

    If,

    $var=a:1:{s:2:"cc";a:1:{i:15;s:11:"TESTING";}};

    I would need to print 15.

    $var=a:1:{s:2:"cc";a:1:{i:3;s:11:"TESTING";}};

    I would need to print 3.

     

    Thanks in advance.

    printing a specific number from an array

    in PHP Coding Help

    Posted

    Hello, i retrieve an array from the database that looks something like this:

     

    $var=a:1:{s:2:"cc";a:1:{i:22;s:11:"TESTING";}};

     

    I need to retrieve the number corresponding to the character i: and print it(11 in the first case). 

    For example ,

    If,

    $var=a:1:{s:2:"cc";a:1:{i:15;s:11:"TESTING";}};

    I would need to print 15.

    $var=a:1:{s:2:"cc";a:1:{i:3;s:11:"TESTING";}};

    I would need to print 3.

     

    Thanks in advance.

    left join query very slow

    in MySQL Help

    Posted

    The structure of post table is as follows:

    Field Type                         Null Key Default Extra

    postid int(10) unsigned NO PRI     auto_increment

    threadid int(10) unsigned NO MUL 0

    parentid int(10) unsigned NO 0

    username varchar(100) NO

    userid int(10) unsigned NO MUL 0

    title varchar(250) NO MUL

    dateline int(10) unsigned NO MUL 0

    pagetext mediumtext YES

     

    The structure of thread table is as follows:

    Field  Type                Null Key Default Extra

    threadid int(10) unsigned NO PRI     auto_increment

    title varchar(250) NO MUL

    firstpostid int(10) unsigned NO 0

    lastpostid int(10) unsigned NO 0

    lastpost int(10) unsigned NO MUL 0

    forumid

     

    I want to join the the 2 tables based on the forum id.

    left join query very slow

    in MySQL Help

    Posted

    I am joining 2 tables post and thread. And I need to populate the values from the post table. I did the following left join, but it seems very inefficient and very slow. Is there an alternate way to make it more effiecient and fast?

     

    $getposts=mysql_query("SELECT post.username,post.dateline,post.postid,post.threadid,thread.threadid,thread.forumid from post LEFT JOIN thread ON post.threadid=thread.threadid WHERE thread.forumid='$id'  ORDER by postid DESC LIMIT $default_limit");

     

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.