Setting the innerHTML of a div to information retrieved from a MySQL database (using AJAX/PHP)?

Question

On my page (index.php), I have a content-editable div with id = "name" and a content-editable div with id = "info". I also have a button with id = "save" and a button with id = "lookUp".

in index.php:

<p> <div> Name: </div> <div id="name" contenteditable="true"> </div> </p>
<p> <div> Info: </div> <div id="info" contenteditable="true"> </div> </p>

<p>
    <button id="save" type="button" style="float: left;" class="buttonText"> Save </button>
    <button id="lookUp" type="button" style="float: left;" class="buttonText"> Look Up </button>
</p>

I would like the page to be able to do two things:

1) If the user clicks the 'Save' button and both content-editable divs are filled, the name and info entered into the content-editable divs by the user are saved to a two-column table (columns: "name" and "info") in a local MySQL database. I have already implemented this functionality with AJAX/PHP, and it works without error.

2) If the user clicks the 'Look Up' button, then I would like the text entered into the content-editable div with id = "name" (the innerHTML of the name div) to be looked up in the database. If the name is found, I would like the site to retrieve the text stored in the corresponding entry's 'info' column and enter it into the content-editable div with id = "info" (set the innerHTML of the 'info' div to the retrieved info).

So far I have partially implemented 2) with the following code:

in index.php:

<script type="text/javascript">
    $(document).ready(function(argument) 
    {
        $('#lookUp').click(function()
        {
            $name = $('#name').html();
            $info = $('#info').html();

            $.ajax(
            {
              url: 'lookup.php', type: 'post', data: {nameSubmit: $name}, datatype: 'html', success: function(rsp){alert(rsp);}
            });
        });
    });
</script>

in lookup.php:

<?php 

    $name = $_POST['nameSubmit'];
    $name = mysql_real_escape_string($name);

    $connect = mysql_connect( "localhost", "localUserName", "localPassword", "" );
    $selectedDB = mysql_select_db("nameInfoDatabase", $connect);

    $lookup = "SELECT * FROM nameInfoTable";
    $lookupResults = mysql_query( $lookup, $connect );

    $nameFound = FALSE;

    while($record = mysql_fetch_array($lookupResults))
    {
        if( $record['name'] == $name )
        {
            $nameFound = TRUE;
            $info = $record['info'];
        }
    }   

    if( $nameFound ){ echo "Account Located.  Info: " . $info; }
    else{ echo "FAILURE: Name not found!"; }

    // NOW WHAT?
?>

After a "Look Up" button-click, the information I want to be saved to the innerHTML of the div with id = "info" in index.php is saved to the variable $info in lookup.php. This $info is correctly printed (echoed) to the dialogue pop-up, but I want it to be entered into the content-editable, id = "info" div of the index.php page. How do I proceed?

I have tried things like echoing lines of javascipt, as is discussed here and here, but I have not been able to get it working with existing StackOverflow Q&As. I am sorry if this question is overly basic or has already been addressed in a way that I did not understand, I am very new to PHP and back-end development.

Thanks for the help! Jack

Answer 1

To properly use this data with JS you can send it in native JSON format. lookup.php

<?php
// here connection and escaping stuff
$lookup = "SELECT * FROM nameInfoTable WHERE name='".$name."' LIMIT 1";
$lookupResults = mysql_query( $lookup, $connect );
// no $nameFound or while loop needed
if (mysql_num_rows($lookupResults) == 1) { // one result obtained
    echo json_encode(mysql_fetch_array($lookupResults)); // fetch and send
}
else {
    // you can do nothing, just don't send anything and response will be empty
    // that's how you can understand that "name" was not found
}
?>

in index.php improve youк ajax success function:

success: function (rsp) {
    if (rsp) { // if data not empty
        var data = JSON.parse(rsp); // parse recieved data 
        $('#info').html(data.info); // properties' names are exactly as columns in your table or like query aliases
    }
}

EDIT Some suggestions that don't really answer your question but may be helpful

1. Try to use mysqli instead of mysql. It's already deprecated and this a good start to learn object oriented syntax

2. You're escaping post var - it's very good, but you don't check whether it exists neither on client-side (if ($name) { // do ajax request }) nor on back-end if (!empty($_POST['nameSubmit'])) { // do all other stuff }

3. I don't think you need so much variables, also some coders prefer another style of preparing queries.

4. Actually you need only info from your table, not all *

I'll try to show this all in one piece of code:

if (!empty($_POST['nameSubmit'])) { // check post variable
    // establish connection
    $mysqli = new mysqli("localhost", "localUserName", "localPassword", "nameInfoDatabase");
    // prepare query, sprintf is a little bit easier to read 
    $query = sprintf("SELECT info FROM nameInfoTable WHERE name='%s' LIMIT 1", mysql_real_escape_string($_POST['nameSubmit']));
    // query, actually you can put sprintf here
    $result = $mysqli->query($query);
    // result will have 0 or 1 in property here
    if ($result->num_rows) {
        // php Array("info"=>"...") will be converted to {"info": "..."} for JS
        echo jscon_encode($result->fetch_assoc());
    }
}