选择auto_increment字段在php中返回空白

Question

I am working on a php script that stores message ids (Msg_ID, Ref_ID) in their corresponding user account tables.

What I've is, the Msg_ID is properly written, but the Ref_ID is always blank. How ever when I run the query separately it works, but doesn't work in the script for some odd reason.

Here is the code :

    $qry = "SELECT Ref_ID FROM Chat WHERE Msg_ID = " .$MsgID. ")";
    $resp = mysqli_query($con, $qry);

    $xx = mysqli_fetch_array($resp);
    $ref_id = $xx['Ref_ID'];

    foreach ($Array as $user){
        $query = "Insert into ".$user."(POST_ID, REF_ID) values ('". $MsgID . "', '" .$ref_id. "')";
        mysqli_query($con, $query);
    }

The $ref_id is always blank and as a result, the blank value is written to the respective database. Some help with what is wrong will be helpful.

Here is the full code :

<?php
function PostMainThread($Heading, $Message, $Author, $MarkedList){
    $con=mysqli_connect("mysql.serversfree.com", "u521497173_root", "123456", "u521497123_mydb");
    $Array = explode(',', $MarkedList);
    if (mysqli_connect_errno()){
        $response["success"] = 0;
        $response["message"] = "Connection Failed.";
        echo json_encode($response);
    }else{
        here:$MsgID = rand(1, 9999999);
        $query = "Insert into Chat(Msg_ID, Header, MsgBody, Author) values (". $MsgID . "," . "'" . $Heading . "' ," .
            "'" . $Message . "', '". $Author . "')";
        $result=mysqli_query($con, $query);
        if (!$result){
            goto here;
        }else{
            //Put the MsgID in the respective user tables.
            $qry = "SELECT Ref_ID FROM Chat WHERE Msg_ID = " .$MsgID. ")";
            $resp = mysqli_query($con, $qry);

            $xx = mysqli_fetch_array($resp);
            $ref_id = $xx['Ref_ID'];

            foreach ($Array as $user){
                $query = "Insert into ".$user."(POST_ID, REF_ID) values ('". $MsgID . "', '" .$ref_id. "')";
                mysqli_query($con, $query);
            }

            $response["success"] = 1;
            $response["message"] = "Submission successful.";
            mysqli_close($con);
            echo json_encode($response);
        }

    }
}

function PostReplyToThread($PostID, $Author, $Reply){
    $con=mysqli_connect("mysql.serversfree.com", "u521497123_root", "123456", "u521497123_mydb");
    if (mysqli_connect_errno()){
        echo 2;
    }else{
        $query = "Insert into Chat(Msg_ID, Header, MsgBody, Author) values (". $PostID . "," . "'" . " " . "' ," .
            "'" . $Reply . "', '". $Author . "')";
        $result=mysqli_query($con, $query);
        if ($result){
            echo 3;
        }else{
            echo 4;
        }
        mysqli_close($con);
    }
}

if (isset($_POST['what_to_do'])){
    if ($_POST['what_to_do'] == 0){
        if ((isset($_POST['Title'])) &&(isset($_POST['Body']))&&(isset($_POST['Marked']))&&(isset($_POST['_Author']))){
            PostMainThread($_POST['Title'], $_POST['Body'], $_POST['_Author'], $_POST['Marked']);
        }
    }else if ($_POST['what_to_do'] == 1){
        if ((isset($_POST['Thread_ID'])) &&(isset($_POST['Answer']))&&(isset($_POST['_Author']))){
            PostReplyToThread($_POST['Thread_ID'], $_POST['_Author'], $_POST['Answer']);
        }
    }
}else{
    $response["success"] = 0;
    $response["message"] = "Unspecified action";
    echo json_encode($response);
}

Definition of the Chat table :

Create table Chat(Ref_ID INT Auto_Increment, Msg_ID INT, Header varchar(50), MsgBody varchar(500
), Author varchar(30), Primary Key(Ref_ID, Msg_ID));

Answer 1

$xx = mysqli_fetch_array($resp);

Will only return a numerically indexed array, as in $xx[0], $xx[1].

To use the column names, you need to use:

$xx = mysqli_fetch_array($resp, MYSQLI_ASSOC);

Or the shorter version:

$xx = mysqli_fetch_assoc($resp);

As a side note, don't forget security, when inserting data that comes from outside the function and could possibly have a quotes or SQL, it needs to be escaped!

$Heading = mysqli_real_escape_string($con, $Heading);

Otherwise it will come back to bite you.