我在MySQL中有一个名为published_people的视图,如下所示:
PersonID Name LastName MarkerID date
-------- ---- -------- -------- ----
1198 Jane Doe Doe 1174 2015-05-20
864 John Doe Doe 863 2015-04-23
1187 Richard Roe Roe 1165 2015-05-21
1190 Sam Spade Spade 1167 2015-01-01
我有一个post变量,代表我正在查看其数据页面的人的标记ID。
我还有另一个post变量,代表我正在查看其数据页面的人的姓氏。
我希望能够遍历published_people。如果“姓氏”字段与变量匹配,我想在published_people中获取先前的记录(此记录之前的记录)。
到目前为止,这是我在php中的代码:
include_once ('constants_test.php');
$mysqli_prior = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s", mysqli_connect_error());
exit();
}
//get the year I'm looking for
$this_date = mysqli_real_escape_string($mysqli_prior, $_POST['this_date']);
$pieces = explode("-", $this_date);
$this_year = $pieces[0];
//find the last name of the person I'm looking for
$marker_id = $_POST['marker_id'];
$q_getLastName = "select LastName from published_people where MarkerID =" . $marker_id;
$result = mysqli_query($mysqli_prior,$q_getLastName);
$r = $result->fetch_row();
$thisLastName = $r[0];
//get all records from this year, alphabetized by last name
$q = "select * from published_homicides where year(date) = '" . $this_year . "' order by LastName";
$result = $mysqli_prior->query($q);
$allresults = array();
$num_rows = mysqli_num_rows($result);
if ($num_rows != 0) {
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
// How do I say this?
// if $row["LastName"] == $thisLastName then find the record
// PRIOR TO this one and do the following:
$results = array($row['Name'], $row['date']);
array_push($allresults, $results);
}
echo json_encode($allresults);
} else {
echo "nothing";
}
mysqli_close($mysqli_prior);
最佳答案
我最终创建了一个变量来保存上一条记录中的数据。我遍历了查询。当MarkerID等于我想要上一个记录的marker_id时,我停止了:
$priorname = ""; //initialize at nothing
//we'll go through the list of names in alphabetical order by year
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
//compare the row id to the posted id
if ($row['MarkerID'] == $marker_id) {
$results = array($priorname);
array_push($allresults, $results);
} else {
$priorname = $row['Name']; //save this data in $priorname
}
}
echo json_encode($allresults);
mysqli_close($mysqli_prior);
这样就获得了先前的记录。
我也想获得下一张唱片。
我分两部分处理了这个问题。首先,我进行了查询,计算了我所在的行。当我的名为marker_id的发布变量与数据库视图中的MarkerID匹配时,我停止了查询:
$marker_id = $_POST['marker_id'];
$q = "select MarkerID, Name, LastName, date from published_people order by year(date) desc, LastName asc";
$result = $mysqli_next->query($q);
$allresults = array();
$count = 0;
//we'll go through the list of names in alphabetical order by year
while($row = $result->fetch_array(MYSQLI_BOTH)) {
$count++; //keep track of what row you're on
//compare the row id to the posted id
if ($row['MarkerID'] == $marker_id) {
//if they're the same, stop this query - we have counted to the spot that they matched
break;
}
}
现在,我知道在哪里设置另一个查询的限制:
//make a new query with a limit of one record starting at the row # indicated by $count
$newq = "select MarkerID, Name, LastName, date from published_homicides order by year(date) desc, LastName asc LIMIT " . $count . ",1";
$result2 = $mysqli_next->query($newq);
while($row2 = $result2->fetch_array(MYSQLI_ASSOC)) {
$results = array($row2["Name"]);
array_push($allresults, $results);
}
echo json_encode($allresults);
mysqli_close($mysqli_next);
关于php - 从PHP中的MySQL查询获取前一行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30381265/