How to retrieve particular data of a row based on its id in mysql?

Question

I would like to know how to retrieve the title of a row based on its id.

Its for href link based of next and previous buttons. The id will be determined by

current id-1= previous id ($pid) and current id+1=next id ($nid)

I have $nid and $pid, but now i want to get the title of that respective id in the database. Now i am getting only the current id title displayed in the url..

href="features/headlines/<?php echo $pid; ?>/<?php echo $title; ?>"

i want the $title to show the title corresponding to the $pid but now i am only getting the title of the current id.

I tried <?php echo $pid[title]; ?>

score 0 · Answer 1 · answered Sep 17 '15 at 23:24

It would be something to the effect of:

<?php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

$current_id = 6; // Whatever your current ID is here..

// Notice the subtraction from $current_id
$query = "SELECT `title` FROM `table` WHERE `id` = '".($current_id-1)."'";
// Would output:
// SELECT `title` FROM `table` WHERE `id` = '5'

$result = $mysqli->query($query);
$row = $result->fetch_assoc();

$previous_title = $row['title'];

?>

See: http://php.net/manual/en/mysqli-result.fetch-assoc.php

score 0 · Answer 2 · edited May 23 '17 at 12:14

You are trying to use a normalized database schema. This is good! (unless you are talking about some abstract data type in PHP)

If I'm understanding you correctly, you need a special sort of "key" on your column, specifically the primary key with the attribute AUTO_INCREMENT

How to make MySQL table primary key auto increment with some prefix

MySQL can solve any selection based on its id in O(logn) time using a simple BTREE index. Primary keys are keys that basically are a unique representation of each row (in this case, the id is the represenation of title). You would do a query like this:

SELECT id, title from table_1 WHERE id = '$id_to_select'

To parse the return result, you have an array of choices: mysqli_fetch_row mysqli_fetch_array() mysqli_fetch_assoc

I think the best one is mysqli_fetch_array since you can specify how to fetch the returning row: MYSQLI_NUM denotes the index, MYSQLI_ASSOC denotes an associative return.

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
print_r($row);
//Array(
    'id' => 1,
    'title' => 'lorem ipsum'
)

How to retrieve particular data of a row based on its id in mysql?

2 Answers2