max(id) and limit 10, but use them in different places

I have two tables, posts and sections. I want to get the last 10 posts WHERE section = 1, but use the 10 results in different places. I make a function:

     function sectionposts($section_id){
mysql_set_charset('utf8');
$maxpost1 ="SELECT max(id) from posts WHERE section_id = $section_id ORDER BY ID DESC LIMIT 20";
$maxpost12 =mysql_query($maxpost1);
while ($maxpost_rows = mysql_fetch_array($maxpost12 ,MYSQL_BOTH)){
$maxpost2 = $maxpost_rows[0];
}
$query = "SELECT * FROM posts WHERE id = $maxpost2";
$query2 = mysql_query($query);
return $query2;
}
$query2 = sectionposts(6);
while ($rows = mysql_fetch_array($query2)){
echo $rows['title'] . "<br/>" . "<br/>";
echo $rows['id'] . "<br/>" . "<br/>";
echo $rows['image_section'] . "<br/>";
echo $rows['subject'] . "<br/>";
echo $rows['image_post'] . "<br/>";
}

How can it take these ten results but use them in different places, and keep them arranged from one to ten.

this was the old case and i solve it but i found another problem, that, if the client had deleted a post as id = 800 "so there aren't id = 800 in DB" so when i get the max id minus $NUM from it, and this operation must be equal id = 800, so i have a programing mistake here, how can i take care of something like that.

    function getmax_id_with_minus ($minus){
    mysql_set_charset('utf8');
    $maxid ="SELECT max(id) FROM posts";
    $maxid1 =mysql_query($maxid);
        while ($maxid_row = mysql_fetch_array($maxid1)){
                $maxid_id = $maxid_row['0'];
                $maxid_minus = $maxid_id - $minus;
                }
    $selectedpost1 = "SELECT * FROM posts WHERE id = $maxid_minus";
    $query_selectedpost =mysql_query($selectedpost1);
        return ($query_selectedpost);

}
<?php 
$ss = getmax_id_with_minus (8);
while ($rows = mysql_fetch_assoc($ss)){
$main_post_1 = $rows;
?>

anyway "really" thanks again :) !

Answers


A few thoughts regarding posted code:

  • First and foremost, you should stop using mysql_ functions as they are being deprecated and are vulnerable to SQL injection.
$maxpost1 ="SELECT max(id) from posts WHERE section_id = $section_id ORDER BY ID DESC LIMIT 20";
  • When you SELECT MAX, MIN, COUNT, AVG ... functions that only return a single row, you do not need an ORDER BY or a LIMIT.

  • Given that you are only asking for the MAX(id), you can save work by combining your queries like so:

SELECT * FROM posts 
WHERE id = (SELECT MAX(id) from posts WHERE section_id = $section_id)

If I'm understanding what you're trying to do (please correct me if I'm wrong), your function would look something like:

function sectionposts($section_id) {
    $link = mysqli_connect("localhost", "my_user", "my_password", "world");

    $stmt = mysqli_prepare($link, "SELECT title, id, image_section, subject, image_post FROM posts " 
        . "WHERE section_id = ? ORDER BY id DESC LIMIT 10");
    mysqli_stmt_bind_param($stmt, $section_id);
    return mysqli_query($link, $stmt)
}

$result = sectionposts(6);
while ($row = mysqli_fetch_assoc($result)) {
    echo $rows['title'] . "<br /><br />";
    echo $rows['id'] . "<br /><br />";
    echo $rows['image_section'] . "<br />";
    echo $rows['subject'] . "<br />";
    echo $rows['image_post'] . "<br />";
}

Try this instead, to save yourself a lot of pointless code:

$sql = "SELECT * FROM posts WHERE section_id=$section_id HAVING bar=MAX(bar);"
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo ...;
echo ...;

The having clause lets you find the max record in a single operation, without the inherent raciness of your two-query version. And unless you allow multiple records with the same IDs to pollute your tables, removing the while() loops also makes things far more legible.


Seems like you want to store them in an array.

 $posts = array(); //put this before the while loop. 
 $posts[] = $row; //put this in the while loop

Need Your Help

Velocity: Is it better to use a Velocity Macro or #include(…) statement for a DIV block repeated many times on different pages?

java macros include velocity

I have about 10 standard blocks of dynamically generated HTML that get included on a large percentage of pages on my site (informational sidebars).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.