Send JSON data to MySQL database?

Question

I need to send JSON data to a MySQL database, but when I am trying to do this, my code only sends "{"0":"A" to the MySQL database.

Here is my code:

JavaScript

<span id="start_button_container">Send and start</span>

const allCards = {
    '0':'A &#9830;','1':'A &#9829;','2':'A &#9827;','3':'A &#9824;',
    '4':'10 &#9830;','5':'10 &#9829;','6':'10 &#9827;','7':'10 &#9824;',
    '8':'K &#9830;','9':'K &#9829;','10':'K &#9827;','11':'K &#9824;',
    '12':'Q &#9830;','13':'Q &#9829;','14':'Q &#9827;','15':'Q &#9824;',
    '16':'J &#9830;','17':'J &#9829;','18':'J &#9827;','19':'J &#9824;'
};

let userInTable = localStorage.getItem( 'saved_user' );
if (userInTable) { // Save user and find table onclick START
    saveUser.style.display = 'none';
    hello.textContent = "Hi " + userInTable;
    start.onclick = () => {
        if (userInTable) {
            let x = new XMLHttpRequest();
            let url = "php/findtable.php";
            let data = JSON.stringify(allCards);
            let params = "cards="+data+"&user="+userInTable;
            x.open("POST", url);
            x.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
            x.send(params);
            x.onreadystatechange = () => {
                if (x.readyState == 4 && x.status == 200) {
                    console.log(x.responseText);
                }
            }
        }
    }
}

Here is my PHP code:

if (isset($_POST["cards"],$_POST["user"])) {
    $cards = $_POST["cards"];
    $user = $_POST["user"];
    $query = "INSERT INTO tables (u_1,all_cards) VALUES (?,?)";
    if ($stmt = $conn->prepare($query)) {
        $stmt->bind_param("ss", $user, $cards);
        if ($stmt->execute()) {
            print_r($cards);
        }
    }
}

What am I doing wrong?

Can we see the PHP code which does the saving into the database please? — ADyson
– ADyson, Commented Jun 23, 2020 at 6:52
P.s. this is a bit of a mess. You're sending data in two formats - url-encoded as the main format but with JSON in the middle. It's better to be consistent. It would probably make more sense for this situation to send the whole payload as JSON. — ADyson
– ADyson, Commented Jun 23, 2020 at 6:53
Because of the Space. can you print_r the POST before you insert it. — Jovylle
– Jovylle, Commented Jun 23, 2020 at 6:58
@Gurami I didn't say just to change the content- type and nothing else. There would be more to it than that. You have to actually make the whole payload a single JSON object, and you have to alter the PHP to read it as JSON. — ADyson
– ADyson, Commented Jun 23, 2020 at 6:59

Peter Mortensen · Accepted Answer · 2021-07-07 13:03:18Z

1

The encodeURIComponent() function helped me a lot:

let data = JSON.stringify(encodeURIComponent(allCards));

edited Jul 7, 2021 at 13:03

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jun 23, 2020 at 8:10

Gurami

821 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Mortensen · Accepted Answer · 2021-07-07 13:02:41Z

0

If you/somebody still want to know why this happens, every ampersand (&) is a new input in a querystring. Meaning var1=value&var2=value&var3=value. Your JSON contains ampersands, so the parser thinks you are starting a new variable.

var1=value&var2={"a":"&2934;"}
                      ^ This one starts a new variable

var2 contains {"a":"1 and processes 2934;"} as a new variable name.

encodeURIComponent escapes the ampersands, so the query string parser does not use it for division of variables.

edited Jul 7, 2021 at 13:02

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jun 23, 2020 at 15:47

Roboroads

1,7571 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

Send JSON data to MySQL database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related