Saving a search result in Mysql with jQuery/Javascript

Question

I'm trying to save google's API search results in a Mysql database in localhost.

My problem

I'm using a for loop to show every possible result from book research, such as "Harry Potter".
I do this with AJAX and then I cycle the results.
The main problem is that if I want to save one of the results, what is saved is just the last one because every variable is overwritten during every cycle.

Here is the code, saveBook.php is just a .php file where I do an insert into query.

$.ajax({
        url:"https://www.googleapis.com/books/v1/volumes?q=" + search,
        dataType: "json",
        type: 'GET',
        success: function(data){

            for(i=0;  i < data.items.length; i++)
            {


               title = $( '<p> ' + data.items[i].volumeInfo.title + '</p>');
               author = $('<p> ' + data.items[i].volumeInfo.authors + '</p>');
               img = $('<img><a href=' + data.items[i].volumeInfo.infoLink + 
                       '><br></br><button>Read More</button></a>' );
               url= data.items[i].volumeInfo.imageLinks.thumbnail;
               save = $('<br><button onclick="save()">Save</button></br> 
                        </br>');


          img.attr('src',url);
          title.appendTo("#result");
          author.appendTo("#result");
          img.appendTo("#result");
          save.appendTo("#result");

        }  
    },

    });
    } 
    }

    function save(){
        $.ajax({


            type:'POST',
            data: {
                            url: url,
                            title: title,
                            author: author,
                        }, 
                        url: "saveBook.php",
                        success: function(data){
                            alert("Success!");
                            location.replace("home.php");

            }
    })}

Another problem is:

how should I use this JSON

data: {
          url: url,
          title: title,
          author: author,
      },

to actually return a string with title and author, because url is correctly saved.

Possible duplicate of [JavaScript closure inside loops – simple practical example](https://stackoverflow.com/questions/750486/javascript-closure-inside-loops-simple-practical-example) — Brahma Dev, May 11 '19 at 13:23
Why not build each `save()` call to contain the parameters that you intend to post? Otherwise store the data items array as globally accessible and pass `i` like `save(i)`. There are probably at least 5 different ways to perform this task. Also, you should use css to position your elements instead of bloating the dom with a bunch of `
` tags. — mickmackusa, May 12 '19 at 05:41
Hey, thank you for the answer. I have tried to pass the i to save(), but, when I do so, it says that the variable is declared but never read. If it helps to understand the problem, the first AJAX call (the one that appends title, author, etc to #result) is inside an if-else, because I want to check if the user puts an empty string and clicks the button. — Wertal0645, May 12 '19 at 05:55
Are you declaring `let data={},` as a global variable (outside/before your ajax calls?) If you want me to be notified of your message, you have to ping me with `@mickmackusa`. If you are reloading the page after `save()`, why not have a regular form submission and use php redirects? — mickmackusa, May 12 '19 at 06:48
No, I only declared url, img, title, author and save as var as global variables. I only get data from the AJAX function at the moment. @mickmackusa I don't know how to do that to be honest. Should i put a submit instead of button in my html? — Wertal0645, May 12 '19 at 06:53
You could create individual forms for each row and pass hidden inputs for the submission. — mickmackusa, May 12 '19 at 07:01
Just to check if I got it right: should I go for and for urls, title, etc? Thank you for your help, I know those are kinda dumb and easy question to answer. @mickmackusa — Wertal0645, May 12 '19 at 07:08
It is not that this is easy or dumb, it is just that there are so many ways to do it. Yeah, something like that. Be sure to escape double quotes in the incoming data so that the `value` declaration isn't truncated. Be sure to validate each value on the receiving php script and use a prepared statement with placeholders. I am not available to post a proper answer right now. — mickmackusa, May 12 '19 at 07:47
Does your project have an important reason to use ajax for either task? It seems to me that this can be very simply executed without ajax/javascript. This way you don't need to toil with inserting new html elements after page load time. — mickmackusa, May 12 '19 at 09:32
Hey, thank you again for answering @mickmackusa. Yes, I should use Ajax and Javascript to do the query in .php, but if you have any suggestion I'll be glad to know! — Wertal0645, May 12 '19 at 09:45

score 0 · Answer 1 · answered May 12 '19 at 14:54

Problem solved thanks to @mickmackusa. I have declared the variable "save" as a global array and, then, I have passed every string with a proper escape. Now it's working fine! Thank you very much.

save[i] = $('<button onclick="save((\'' + data.items[i].volumeInfo.authors + '\'),(\'' + data.items[i].volumeInfo.title + '\'),(\'' + data.items[i].volumeInfo.infoLink + '\'))">Save</button><br><br>')

mickmackusa · Accepted Answer · 2019-05-12T23:00:16.213

I've had some time to think over your project. While I'm not sure of all of your project requirements, a fundamental pursuit should be to minimize the calls to the api. For this reason, let your users search via a standard (non-ajax) search input/form and load the api results into the html with php, then use your ajax call to allow multiple save calls from a single load of search results (if that makes sense for your project). Basically, I suppose I am discouraging the auto-reload after saving a single item; otherwise you can avoid ajax again.

Also, as far as I know authors is an array, so you'll need to join/implode the values into a single string.

foreach (json_decode($apijson, true) as $item) {
    echo '<div class="book">';
        // your other elements in the row
        echo "<button onclick=\"save('" , 
            htmlspecialchars($item['id'], ENT_QUOTES, 'UTF-8') , "','" ,
            htmlspecialchars($item['volumeInfo']['title'], ENT_QUOTES, 'UTF-8') , "','" ,
            htmlspecialchars(implode(', ', $item['volumeInfo']['authors']), ENT_QUOTES, 'UTF-8') , "','" ,
            htmlspecialchars($item['volumeInfo']['imageLinks']['thumbnail'], ENT_QUOTES, 'UTF-8') , "');\">Save</button>";
    echo '</div>';
}

Then these parameters can be directly fed to your ajax function.

Be sure to validate and use sensible data sanitizing techniques and use a prepared statement when you INSERT.

You can return a success message from your ajax, and then hide() the saved row/book.

You can return a vague (not exact) error message if the insert did not make an "affected row".

Saving a search result in Mysql with jQuery/Javascript

2 Answers2