php - 在带有 MySql 的 PHP 中插入相同结果之前和之前选择

Question

晚上好从表中选择一些数据时出现问题。所以，首先，我调用了一个返回表最后一行的选择。在此之后，我在该表中插入新数据，现在再次调用该选择并给我与第一次选择相同的结果。我想要的是，当我插入时，最后一个选择返回插入的数据。如果我执行另一个选择，已经给我最后插入的值。我的代码是用 PHP 编写的，我使用 mysqli 接口(interface)。我已经使用了事务，刷新，在php.ini中将mysqli的cahce设置为0。谢谢。

EDITED 2:我做了一个测试:

<?php
include 'conf.php';

$query="INSERT INTO assinante.comentarios (id_cmnt,id_user,comentario,now_data) values (null,4,'coiso',NOW());";
$res=  mysqli_query($link,$query);

$query="SELECT * FROM $db_assin.comentarios;";
$res=  mysqli_query($link,$query);
$linha = mysqli_fetch_assoc($res);
print_r($linha);

$query="INSERT INTO assinante.comentarios (id_cmnt,id_user,comentario,now_data) values (null,3,'ola',NOW());";
$res=  mysqli_query($link,$query);

$query="SELECT * FROM $db_assin.comentarios;";
$res=  mysqli_query($link,$query);
$linha = mysqli_fetch_assoc($res);
print_r($linha);

它返回:

Array ( [id_cmnt] => 29 [id_user] => 3 [comentario] => coiso [now_data] => 2014-01-29 02:10:24 ) Array ( [id_cmnt] => 29 [id_user] => 3 [comentario] => coiso [now_data] => 2014-01-29 02:10:24 )

好像是保存在buffer之类的。如果您能提供帮助，我将不胜感激。

已编辑:代码

function getComentarios(){
    include 'conf.php';
    $query = 
    "   SELECT  utili.username, cmnt.comentario , cmnt.now_data , cmnt.id_cmnt
        FROM ".$db_assin.".comentarios cmnt, ".$db_assin.".utilizadores utili
        WHERE utili.id_user = cmnt.id_user
        ORDER BY cmnt.id_cmnt DESC
        LIMIT 1;"; 
    $resultado = mysqli_query($link,$query);
    if($resultado){
        $linha = mysqli_fetch_array($resultado);
        $json = json_encode($linha);
        echo $json;
    }
    else{
        echo mysqli_error($link);
    }
        mysqli_free_result($resultado);
}

function ins_comment($id_user,$comment){
    include 'conf.php';
    $data = date('Y-m-d H:i:s');
    mysqli_autocommit($link, FALSE);
    mysqli_query($link,"BEGIN");
    $query_ins = "INSERT INTO ".$db_assin.".comentarios (id_cmnt,id_user,comentario,now_data) "
            . "values (null,$id_user,'$comment',NOW());";
    if (!$res_ins = mysqli_query($link,$query_ins)){
        echo mysqli_error ($link);
        mysqli_rollback($link);
    }
    else{
        mysqli_commit($link);
    }
    mysqli_autocommit($link, TRUE);
    mysqli_refresh($link, MYSQLI_REFRESH_HOSTS);
    return $res_ins;
}

function comentar(){
    include 'conf.php';
    $user_c=$_POST['user_c'];
    $comment=$_POST['comment'];
    $query =  "SELECT u.id_user "
            . "FROM ".$db_assin.".utilizadores u "
            . "WHERE u.username='$user_c';";
    if($resultado = mysqli_query($link, $query)){
        if (($linha = mysqli_fetch_array($resultado, MYSQLI_ASSOC)) != NULL){
            $id_user=$linha['id_user'];
            $query =  "SELECT c.comentario "
            . "FROM ".$db_assin.".utilizadores u, ".$db_assin.".comentarios c "
            . "WHERE u.id_user=c.id_user AND u.id_user='$id_user';";
            if($resultado = mysqli_query($link, $query)){
                if (($linha = mysqli_fetch_array($resultado, MYSQLI_ASSOC)) != NULL){
                    if(strcmp($linha['comentario'],$comment)!=0){
                        if($res=ins_comment($id_user, $comment))
                                echo "Comentário submetido";
                    }
                    else{
                        echo "O mesmo comentário não pode ser submetido duas vezes!";
                    }
                }
                else{
                    if($res=ins_comment($id_user, $comment))
                        echo "Comentário submetido";
                }
            }
        }
        mysqli_free_result($resultado);
    }
    else echo mysqli_error($link);
}

Answer 1

您的第二个查询没有order by。尝试

SELECT c.comentario
FROM ".$db_assin.".utilizadores u, ".$db_assin.".comentarios c "
WHERE u.id_user=c.id_user AND u.id_user='$id_user'
ORDER BY c.id_cmnt DESC
LIMIT 1;

顺便说一句，你应该学会使用显式连接语法。这个查询最好写成:

SELECT c.comentario
FROM ".$db_assin.".utilizadores u join
     ".$db_assin.".comentarios c
     on u.id_user = c.id_user
WHERE u.id_user = '$id_user'
ORDER BY c.id_cmnt DESC
LIMIT 1;