mySQL PHP print results from multiple rows

Question

This is a beginner PHP/SQL question.

Basically, I have a Database that looks like this

  Poem_id   |   Poem_content  |  Poem_by  |   Poem_hotscore
------------ ----------------- ----------- -----------------
     1      |  Blah Bleh<br>B.|     4     |  5342.3920349
------------ ----------------- ----------- -----------------
     7      |  Blah Bluu<br>F.|     4     |  5003.3920382
------------ ----------------- ----------- -----------------
     9      |  Blerp Bloop Foo|    34     |  4300.7281209

Each poem

Has a unique id
Has content
Is written by a member with a unique member_id
Has a unique hotscore calculated by a Cron Job using Reddit's hotscore ranking algorithm

My question is: How can I get the top three scores and display all the data from these poems?

The PHP code I have so far is this:

if ($rankerrows = $mysqli->prepare("SELECT * FROM poems ORDER BY poem_hotscore DESC")) {

    $rankerrows->execute();
    $rankerrows->store_result();


    while ($row = mysqli_fetch_assoc($rankerrows)) {
        print_r($rankerrows);
    }

}

How exactly do I get the data from each row? I know how to do it for one row, but I'm not sure how to do it for 3 rows.

Thanks in advance for any advice.

The data is in $row.

Barmar
– Barmar

2013-07-11 01:46:00 +00:00
Commented Jul 11, 2013 at 1:46 — Barmar
– Barmar, Commented Jul 11, 2013 at 1:46

Barmar · Accepted Answer · 2013-07-11 01:48:25Z

1

The data from each row is in $row, so print that, not $rankerrows.

while ($row = mysqli_fetch_assoc($rankerrows)) {
    print_r($row);
}

And you can add a LIMIT 3 clause to the end of your query so it will only return the top 3 rows.

answered Jul 11, 2013 at 1:48

Barmar

789k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2415992 Over a year ago

So- just to make sure I understand the concept here- everytime the program loops through the code, it goes through one row. Then, I can access a specific row's poem_content by using row['poem_content']?

Barmar Over a year ago

Exactly. Have you read the documentation of mysqli_fetch_assoc()? Isn't it clear about this? being a beginner is no excuse for not reading the manual.

user2415992 · Accepted Answer · 2013-07-11 06:41:09Z

The other answer didn't work but this did:

if ($rankerrows = $mysqli->prepare("SELECT * FROM poems ORDER BY poem_hotscore DESC LIMIT 3")) {

    $rankerrows->execute();

    $row = array();
    stmt_bind_assoc($rankerrows, $row);

    /* Store data from poems */
    $poem_id=array();
    $poem_title=array();

    $i=0;


    while ($rankerrows->fetch()) {
        $poem_id[$i]=$row['poem_id'];
        $poem_title[$i]=$row['poem_title'];
        $i++;
    }

Using the mysqli_fetch_assoc() in the other answer gave me the error that it could only be called on mysqli_result and not a stmt. Since I don't have the driver for get_result() and all that good stuff, this is the solution that worked

Collectives™ on Stack Overflow

mySQL PHP print results from multiple rows

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related