How to display at least 10 data from MySQL Database?

Question

rolyestemonio 0 Light Poster

14 Years Ago

Hi Guys,

Can anyone help me out about displaying 10 data from my database?My codes goes like this. I have 4 fields (id, local_title, local_desc, local_date )

<?php
	
$last_date = "";
$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY local_date DESC"); 

	while ($row = mysql_fetch_array($result)) {
	for($i=0; $i<10; $i++){
    if ($row['local_date'] != $last_date) {
        print("<h2>News for ".date('F j, Y',strtotime($row['local_date']))."</h2>");
        $last_date = $row['local_date'];
    }
    print("<p><b>".$row['local_title']."</b></p>");
    print("<p>".$row['local_desc']."</p>");
	}
}
mysql_free_result($result);

?>

It was arrange by date. If the dates similar it will display in one category. I want to display at least 10 data from my database. But my code doesn't work. Can Anyone help me out?Thanks.

mysql php

Edited 14 Years Ago by rolyestemonio because: n/a

5 Contributors
20 Replies
171 Views
2 Days Discussion Span
Latest Post 14 Years Ago Latest Post by rolyestemonio

ko ko 97 Practically a Master Poster

14 Years Ago

$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY local_date DESC LIMIT 10");

vibhaJ 126 Master Poster

14 Years Ago

Here is some modification with ur code...

<?php	
$last_date = "";
$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY local_date DESC limit 0,10"); 
while ($row = mysql_fetch_array($result)) 
{
	if ($row['local_date'] != $last_date)
	{
		print("<h2>News for ".date('F j, Y',strtotime($row['local_date']))."</h2>");
		$last_date = $row['local_date'];
	}
	print("<p><b>".$row['local_title']."</b></p>");
	print("<p>".$row['local_desc']."</p>");
}
mysql_free_result($result);
?>

Try it out.

Edited 14 Years Ago by vibhaJ because: n/a

vibhaJ 126 Master Poster

14 Years Ago

Thanks. It really works. Can i ask one thing? How am i going to connect this one News for November that it will only display in that month data?

It can be achieved by this code.

<?php	
$last_month = "";
$result = mysql_query("SELECT *,monthname(local_date) as month FROM tbl_localnews ORDER BY local_date DESC limit 0,10"); 
while ($row = mysql_fetch_array($result)) 
{	
	if ($row['month'] != $last_month)
	{
		print("<h2>News for ".$row['month']."</h2>");
		$last_month = $row['month'];
	}
	print("<p><b>".$row['local_title']."</b></p>");
	print("<p>".$row['local_desc']."</p>");
}
mysql_free_result($result);
?>

vibhaJ 126 Master Poster

14 Years Ago

Below code will show list of months

<?php	
$result = mysql_query("SELECT distinct monthname(u_reg_date) as month FROM tbl_localnews"); 
while ($row = mysql_fetch_array($result)) 
{	
	print("<br><a href='news.php?month=".$row['month']."'>News for ".$row['month']."</a>");	
}
mysql_free_result($result);
?>

monthname(u_reg_date) directly gives you month name from database so you dont have to do date('F',strtotime($row))

news.php

<?
	$month = $_REQUEST['month'];
	$result = mysql_query("SELECT *,monthname(local_date) as month FROM tbl_localnews where monthname(local_date)='".$month."'  ORDER BY local_date DESC "); 
	echo '<h1>'.$month.'</h1>';
	while ($row = mysql_fetch_array($result)) 
	{			
		print("<p><b>".$row['local_title']."</b></p>");
		print("<p>".$row['local_desc']."</p>");
	}
	mysql_free_result($result);
?>

vibhaJ 126 Master Poster

14 Years Ago

Have you read this :

monthname(u_reg_date) directly gives you month name from database so you dont have to do date('F',strtotime($row))

check this for monthname detail.

for $row, month is not table field but it is alias created for monthname(local_date). I wonder u have not used 'AS' in sql query.

For error, use mysql_num_rows function as shown below whenever you use mysql_fetch_array.

if(mysql_num_rows($result))
	{
		while ($row = mysql_fetch_array($result)) 
		{			
			//.... whatever code....
		}
	}

Post ur result.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rolyestemonio 0 Light Poster · Answer 1 · 2010-11-15T11:32:16+00:00

$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY local_date DESC LIMIT 10");

Thanks. It really works. Can i ask 0ne thing? How am i going to connect this one News for September that it will only display in that month data?

rolyestemonio 0 Light Poster · Answer 2 · 2010-11-15T11:33:14+00:00

Here is some modification with ur code...

<?php	
$last_date = "";
$result = mysql_query("SELECT * FROM tbl_localnews ORDER BY local_date DESC limit 0,10"); 
while ($row = mysql_fetch_array($result)) 
{
	if ($row['local_date'] != $last_date)
	{
		print("<h2>News for ".date('F j, Y',strtotime($row['local_date']))."</h2>");
		$last_date = $row['local_date'];
	}
	print("<p><b>".$row['local_title']."</b></p>");
	print("<p>".$row['local_desc']."</p>");
}
mysql_free_result($result);
?>

Try it out.

Thanks. It really works. Can i ask one thing? How am i going to connect this one News for November that it will only display in that month data?

rolyestemonio 0 Light Poster · Answer 3 · 2010-11-15T11:55:20+00:00

It can be achieved by this code.

<?php	
$last_month = "";
$result = mysql_query("SELECT *,monthname(local_date) as month FROM tbl_localnews ORDER BY local_date DESC limit 0,10"); 
while ($row = mysql_fetch_array($result)) 
{	
	if ($row['month'] != $last_month)
	{
		print("<h2>News for ".$row['month']."</h2>");
		$last_month = $row['month'];
	}
	print("<p><b>".$row['local_title']."</b></p>");
	print("<p>".$row['local_desc']."</p>");
}
mysql_free_result($result);
?>

What do you mean by this monthname(local_date) as month?

I have a code like this :

<?php
$sql = "SELECT * FROM archive_data ORDER BY dates DESC";
$res = mysql_query($sql);

$current_month = 0;

while ($row = mysql_fetch_array($res))
{
    $cm = substr($row['local_date'], 0, 9);
    if ($cm != $current_month)
    {
        $current_month = $cm;
        echo "<a href='_archive.php'>News for " .date('F',strtotime($row['local_date'])) . "</a><br />";
    }
}
?>

This one display all the months in my database. And i want that if you click the month it will display the content on that month. How to do it?

Example :

This is the list of the Month found in my database.

News for November
News for October
News for September

Now if you click each of that month it will display data that belong to that Month

rolyestemonio 0 Light Poster · Answer 4 · 2010-11-15T13:44:39+00:00

Below code will show list of months

<?php	
$result = mysql_query("SELECT distinct monthname(u_reg_date) as month FROM tbl_localnews"); 
while ($row = mysql_fetch_array($result)) 
{	
	print("<br><a href='news.php?month=".$row['month']."'>News for ".$row['month']."</a>");	
}
mysql_free_result($result);
?>

monthname(u_reg_date) directly gives you month name from database so you dont have to do date('F',strtotime($row))

news.php

<?
	$month = $_REQUEST['month'];
	$result = mysql_query("SELECT *,monthname(local_date) as month FROM tbl_localnews where monthname(local_date)='".$month."'  ORDER BY local_date DESC "); 
	echo '<h1>'.$month.'</h1>';
	while ($row = mysql_fetch_array($result)) 
	{			
		print("<p><b>".$row['local_title']."</b></p>");
		print("<p>".$row['local_desc']."</p>");
	}
	mysql_free_result($result);
?>

Why is does not work? It gives an error Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given. What is this by the way monthname. And is this $row a fields?

rolyestemonio 0 Light Poster · Answer 5 · 2010-11-15T17:36:56+00:00

Have you read this :

check this for monthname detail.
for $row, month is not table field but it is alias created for monthname(local_date). I wonder u have not used 'AS' in sql query.
For error, use mysql_num_rows function as shown below whenever you use mysql_fetch_array.
if(mysql_num_rows($result))
	{
		while ($row = mysql_fetch_array($result)) 
		{			
			//.... whatever code....
		}
	}
Post ur result.

This code error : Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\getpinoy_automate\sample\index.php on line 23

<?php
	
$result = mysql_query("SELECT distinct monthname(u_reg_date) as month FROM tbl_localnews"); 
if(mysql_num_rows($result))
	{
while ($row = mysql_fetch_array($result)) 
{	
	print("<br><a href='news.php?month=".$row['month']."'>News for ".$row['month']."</a>");	
}
mysql_free_result($result);

}
?>

vibhaJ 126 Master Poster · Answer 6 · 2010-11-15T17:48:13+00:00

OOpss..
u_reg_date field is from my testind end, it should be local_date for you.

change

$result = mysql_query("SELECT distinct monthname(u_reg_date) as month FROM tbl_localnews");

to

$result = mysql_query("SELECT distinct monthname(local_date) as month FROM tbl_localnews");

rolyestemonio 0 Light Poster · Answer 7 · 2010-11-15T17:57:55+00:00

OOpss..
u_reg_date field is from my testind end, it should be local_date for you.
change
$result = mysql_query("SELECT distinct monthname(u_reg_date) as month FROM tbl_localnews");
to
$result = mysql_query("SELECT distinct monthname(local_date) as month FROM tbl_localnews");

It only display one dates which is News for November. For September and October does not display.

vibhaJ 126 Master Poster · Answer 8 · 2010-11-15T18:33:53+00:00

Run below query in phpmyadmin sql tab and post output here.
SELECT monthname(local_date) as month FROM tbl_localnews

Awah Mohamed -5 Junior Poster · Answer 9 · 2010-11-16T20:15:05+00:00

you can use another easy one
no need for using for loop
check this

<?php
// do all he wanted connection , then use his to display the latest 10 datas
$reult = mysql_query(" SELECT*FROM tbale_name ORDER BY row_name DESC limit 0,10");
while($row = mysql_fech_array($result))
{
echo $row['row_name_1'];
echo $row['row_name_2'];
}
mysql_close();
?>

is his wt u mean or somehing else ????

rolyestemonio 0 Light Poster · Answer 10 · 2010-11-17T11:54:31+00:00

Run below query in phpmyadmin sql tab and post output here.
SELECT monthname(local_date) as month FROM tbl_localnews

I get it..Thanks. By the way, how about for a year?

Example :

News for 2010
November 10, 2010
October 10, 2010
News for 2009
November 10, 2009
October 10, 2009

vibhaJ 126 Master Poster · Answer 11 · 2010-11-17T12:01:19+00:00

If you get the logic for month then exact same way you can do for year also,
in sql query use YEAR function this is example.

rolyestemonio 0 Light Poster · Answer 12 · 2010-11-17T12:46:24+00:00

If you get the logic for month then exact same way you can do for year also,
in sql query use YEAR function this is example.

Is this a valid SQL?

SELECT distinct monthname(local_date) as month and YEAR(local_date) as year FROM tbl_localnews

rolyestemonio 0 Light Poster · Answer 13 · 2010-11-17T13:32:27+00:00

Hi guys can anyone help me out how to archive by month and year?
and this data came from my database MySql. And if you click that date it will display the content corresponds to that.

Example :

News for 2010
November 10, 2010
October 10, 2010

News for 2009
November 10, 2009
October 10, 2009

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 14 · 2010-11-17T13:49:40+00:00

pritaeas 2,194 ¯\_(ツ)_/¯

14 Years Ago

See other thread.

Edited 14 Years Ago by pritaeas because: n/a

rolyestemonio 0 Light Poster · Answer 15 · 2010-11-17T14:41:31+00:00

Hi guys can anyone help me out how to archive by month and year?
and this data came from my database MySql. And if you click that date it will display the content corresponds to that.
Example :
News for 2010
November 10, 2010
October 10, 2010

News for 2009
November 10, 2009
October 10, 2009

What is the wrong with this guys? Why is it that when i view the month of November 2010 it also view the month of 2009?Can anyone help me?

<?php include("db.php");

$query = mysql_query("SELECT distinct YEAR(local_date) as year FROM tbl_localnews order by local_date desc");
while($row = mysql_fetch_array($query)) {
      //$row = array_unique($r);
      $unique_year = $row['year'];
      echo($unique_year)."<br>";
      
      $query2 = mysql_query("select distinct monthname(local_date) as month from tbl_localnews where local_date like '$unique_year%' order by local_date desc");
      while($r2 = mysql_fetch_array($query2)) {
              //$row2 = array_unique($r2);
            $unique_month = $r2['month'];
			print("<a href='news.php?month=$unique_month'&year=$unique_year>News for $unique_month</a><br>");	
              //echo($unique_month); 
      }

}
?>