Get primary from last entry of MySQL and pass it via email in link

Question

I have a PHP script which is supposed to send an email with a link which can be clicked to alter the last entry in the MySQL database. To do this I'm trying to include the primary key as a variable in the link but the variable is coming up blank or possibly just not sending correctly. I've never done this before so not sure what the problem is, probably something stupid but silly me has been stumped for a while.

The link in the email currently ends like this: feedback/approve/?id=

Here is the code, if you need more info let me know:

<?PHP
error_reporting(E_ALL);
require ('config.php');
require ('index.php');
/* Connection */
$table = "FeedbackWRW";
$conn = new mysqli (host, user, pass, db);
$sql = "INSERT INTO $table (answer1, answer2, answer3, answer4, name, detail1, detail3, title) VALUES ('$values')";
$primary = mysqli_fetch_row(mysqli_query($conn,"SELECT LAST(`primary`) FROM $table"));
$approvallink = "${clienturl}feedback/approve/?id=" . "$primary";

if(mysqli_query($conn, $sql)
&& 
mail("$testemail",'Feedback Entry',
"
Satisfied customer: $answer1 
Would recommend: $answer2 
Testimonial: $answer3
Suggestion box: $answer4
Name: $name
Email: $detail1
Newsletter Opt-in: $detail3

To approve this entry, please click this link or paste it into your browser window:
$approvallink


","From: $clientemail
Reply-To: $detail2
Return-Path: $detail2
X-Mailer: PHP
CC: 
BCC: 
")



)
{header('Location: thankyou.php');
exit();
}

else { echo "Error: " . $sql . "<br>" . mysqli_error($conn);}


?>

Dency G B · Answer 1 · 2015-05-11T12:07:28.517

Try $primary=$mysqli->insert_id; to get the last inserted.It will return the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute.

Also,make sure this is execeuted after your query. Currently you are fetching your $primary before the execution of query. That is why you are getting it as blank.

You may give a try with this code(NOT TESTED)

    <?PHP
    error_reporting(E_ALL);
    require ('config.php');
    require ('index.php');
    /* Connection */
    $table = "FeedbackWRW";
    $conn = new mysqli (host, user, pass, db);
    $sql = "INSERT INTO $table (answer1, answer2, answer3, answer4, name, detail1, detail3, title) VALUES ('$values')";
    mysqli_query($conn, $sql);
    $primary = $mysqli->insert_id;
    $approvallink = "${clienturl}feedback/approve/?id=" . "$primary";
    if($primary !=''
    && 
    mail("$testemail",'Feedback Entry',
    "
    Satisfied customer: $answer1 
    Would recommend: $answer2 
    Testimonial: $answer3
    Suggestion box: $answer4
    Name: $name
    Email: $detail1
    Newsletter Opt-in: $detail3

    To approve this entry, please click this link or paste it into your browser window:
    $approvallink


    ","From: $clientemail
    Reply-To: $detail2
    Return-Path: $detail2
    X-Mailer: PHP
    CC: 
    BCC: 
    ")



    )
    {header('Location: thankyou.php');
    exit();
    }

    else { echo "Error: " . $sql . "<br>" . mysqli_error($conn);}


    ?>

http://php.net/manual/en/mysqli.insert-id.php

Thanks very much for your response! Had to change a few things but it's working now! — Tom Holmes, Jun 01 '15 at 23:48

score 0 · Answer 2 · answered May 11 '15 at 12:01

You didn't include the table definition, I assume you have a "primary" column defined as auto-incremental. Double check your table and make sure it has non-empty values.

then use max(...) instead last(...) in your query to retrieve the last id inserted.

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

You can use DESC clause in the ORDER BY and LIMIT 1. That will also give you last record.

More information: Select last row in MySQL

edited May 23 '17 at 10:24

Community

1
1

answered May 11 '15 at 12:22

Humna Naeem Naeem

1

Get primary from last entry of MySQL and pass it via email in link

3 Answers3