getting the next available date using mysqli and php

Question

I have a mysqli database with a table for markers and a table for people.

On a web page, I'm displaying a list of people's records from this year:

SELECT * FROM markers m,  people p WHERE m.MarkerID = p.MarkerID and p.Publish=1 and year(m.date) like '2015'

When you click a person from the list, you get a new webpage that displays that person's data using this query in php:

$name = $_POST['name']; 
$newname = str_replace("_", " ", $name);
$q = "SELECT * FROM people p, markers m where p.MarkerID = m.MarkerID and p.Name = '" . $newname . "'";

At the bottom of the page are arrows you can click for the next record and the last record.

How would I retrieve the next or last record please? The next record would be the one that comes next by date from the first query; the prior record would be the one that comes prior by date from the first query.

@Fred -ii- but on the person's page, I don't have that first query. I just have the query that pulls up the individual's data. — LauraNMS, May 13 '15 at 18:56
@Marc B: I'll addslashes. But I'm focused on this other problem right now. — LauraNMS, May 13 '15 at 18:57
addslashes is UTTERLY useless. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Marc B, May 13 '15 at 18:58

score 1 · Answer 1 · answered May 13 '15 at 21:41

This is currently untested .

Let me know what you think, ask any questions.

$recs = 20;  // number of records per page

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM markers m,  people p WHERE m.MarkerID = p.MarkerID and p.Publish=1 and year(m.date) like '2015'";

$next = intval($_POST['next']);
$last = intval($_POST['last']);
if($last > 0){
  $limit1 = $last - $recs;
  $limit2 = $last + 1; 
}
else{
  $limit1 = $next;
  $limit2 = $limit1 + $recs; 
}

$query = "$sql LIMIT $limit1 $limit2";
$results = mysqli_query($link,$sql);

   // do your existing page

$rows = mysql_num_rows($results);
if($rows == $recs)
  $result = mysqli_query($link,"SELECT FOUND_ROWS()");
  $more = mysqli_fetch_array($result , MYSQL_NUM);
}
$remaining = intval($more[0]);
$total = $remaining + $limit2;
$remaining -= ($limit1 + $recs);
$next += $recs + 1;
if($remaining > 0)
  echo <<<EOF

<form action="#' method="post">
<input type="hidden" name="next" value="$next"/>
<button type="submit">Next</button>
</form>
<form action="#' method="post">
<input type="hidden" name="last" value="$total"/>
<button type="submit">Last</button>
</form>

EOF;
}

score 1 · Answer 2 · edited May 14 '15 at 14:28

1

I think this will do it for prior record:

$q = "select * from people v, markers m 
where v.Publish = 1 
and m.MarkerID = v.MarkerID 
and m.date = (select max(m.date) 
from markers m, victims v where m.MarkerID = v.MarkerID 
and v.Publish = 1 
and m.date < '" . $this_date . "')"

edited May 14 '15 at 14:28

Misunderstood

5,534
1
18
25

answered May 14 '15 at 13:46

LauraNMS

2,720
7
39
73

Are you only showing one record at a time? Or just scrolling one record rather than a page of records? – Misunderstood May 14 '15 at 14:31
one record, not a page – LauraNMS May 14 '15 at 16:43

getting the next available date using mysqli and php

2 Answers2