Getting data from MySQL and inserting into a table

Help and Feedback Scripting Support
#1

Hi ok so,

I am grabbing table booking information from my webiste, but want to insert it into a table so GUIs can view bookings etc.

For some reason it doesn’t JSONDecode. If I do GetAsync it comes through?!

Anyway,
Here is what comes through when prompted

[{"user":"\"Music_Jaxx\"","starttime":"\"1600\"","endtime":"\"1700\"","tablenum":"\"1\"","host":"\"Music_Jaxx\"","day":"\"Monday\"","userid":"\"51803259\""}]

It is also what comes through when I print and unpack it.

Server:

local HttpService = game:GetService("HttpService")
 
local url = "[REDACTED]"
 
local response = HttpService:GetAsync(url)
 
local data = HttpService:JSONDecode(response)
local book = {}
for i,v in pairs(data) do
	table.insert(book, response)
end

for i,v in pairs(book) do
	print(unpack(book))
end

What I want to do is fetch a player’s booking and grab elements like table number (I assume it would be for i,v in pairs (book) do v[1] or something like that)

Aplogies if this doesn’t make any sense, I am confused as well.

1 Like
#2

So can you be more specific? Is it erroring, or what’s happening? Is the server sending a valid JSON response? I’m quite confused on what you’re doing here, more because you have data just being copied into another array using a for loop. – And I think I see your mistake reading now.

Change

for i,v in pairs(data) do
	table.insert(book, response)
end

to

for i,v in pairs(data) do
	table.insert(book, v)
end

OR

Just change the book variable to

local book = data; -- it's the same thing

So what went wrong?

You are totally skipping over the JSON here, you were adding the response as an array, when you should have been using the JSON response for the array.

Edit: I kept the stuff where I was sharing my thought process, so that’s where the – Oh I think I see the issue now came from.

1 Like
#3

Sorry for the confusion - here’s how it goes

The user makes a booking through a gui which I still need to make. The request goes to:

"[REDACTED]/create.php?user="..user.Name.."&starttime="..starttime.."&endtime="..endtime.."&tablenum="..tabnum.."&host="..p.Name.."&day="..day.."&id="..user.userId.."

The users need to grab bookings from:

"http://[REDACTED]/check.php

Which returns this:

[{"user":"\"Music_Jaxx\"","starttime":"\"1600\"","endtime":"\"1700\"","tablenum":"\"1\"","host":"\"Music_Jaxx\"","day":"\"Monday\"","userid":"\"51803259\""}]

My Goal:
Insert the above booking/s into a lua table so GUIs can access it so it returns a specific players booking details.

#4

Did you make the change that I described? That should fix it.
So just change the book variable to equal data.

#5

Yep, however it returns:

 table: 0x41487e745a1d63ce
#6

However I did use this template from Roblox’s developer thing (the one at the very bottom)

1 Like
#7

Yes, so I solved your problem? You can’t view a table by printing it. You’ll have to for loop through it to view the data like that.

1 Like
#8

Just gives me this:

Script timeout: exhausted allowed execution time

#9

Weird, what’s your code now?
over30chars

#10 
local HttpService = game:GetService("HttpService")
 
local url = "[REDACTED]"
 
local response = HttpService:GetAsync(url)
 
local data = HttpService:JSONDecode(response)
local book = date;

for i,v in pairs(book) do
	print(i,v)
end

Try this.

#11 
local response = HttpService:GetAsync(url)
 
local data = HttpService:JSONDecode(response)
local book = data;

for i,v in pairs(data) do
	table.insert(book, v)
end

for i,v in pairs(book) do
	print(i,v)
end

Still get the timing out error

#12

That means your server is not replying I believe.

#13

Here’s the check.php code:

<?php   
           $connect = mysqli_connect("REDACTED", "REDACTED", "REDACTED", "REDACTED");  
           $sql = "SELECT * FROM bookings";  
           $result = mysqli_query($connect, $sql);  
           $json_array = array();  
           while($row = mysqli_fetch_assoc($result))  
           {  
                $json_array[] = $row;  
           }  
           /*echo '<pre>';  
           print_r(json_encode($json_array));  
           echo '</pre>';*/  
           echo json_encode($json_array);  
           ?>
#14

You should have GET and POST methods on your server.

#15

This is my create.php with GET on (different files)
I only learnt this last week so still new to PHP

<?php

/* Change database details according to your database */
$dbConnection = mysqli_connect(REDACTED);

$user  = $_GET["user"];
$starttime   = $_GET["starttime"];
$endtime = $_GET["endtime"];
$tablenum = $_GET["tablenum"];
$host = $_GET["host"];
$day = $_GET["day"];
$userid = $_GET["userid"];

$query = "INSERT INTO `bookings` (`user`, `starttime`, `endtime`, `tablenum`, `host`, `day`, `userid`) VALUES ('$user', '$starttime', '$endtime', '$tablenum', '$host', '$day', '$userid')";

if (mysqli_query($dbConnection, $query)) {
    echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";
} else {
    echo "Error occurred: " . mysqli_error($dbConnection);
}

?>
#16

This is bad code, you should seriously consider prepared parameters. Without this you’re leaving your MySQL server susceptible to SQL Injeciton.

tl;dr attackers can drop your database, or get data out of it you don’t want them getting.

#17

Ahh ok, I will rewrite it, thank you.

#18

You’re welcome, make sure all your other PHP files are using prepared params too, if you don’t you’re still vulnerable.

1 Like
#19

Sorry for breaking the news, I know that feeling of having to re-do everything. I got started with web-development in PHP, lots of work. :rofl: And for the Roblox issue, if you go to the site and manually perform the request you’re trying to do on Roblox what does it say?

#20

:joy::joy: it just returns this

[{“user”:“"Music_Jaxx"”,“starttime”:“"1600"”,“endtime”:“"1700"”,“tablenum”:“"1"”,“host”:“"Music_Jaxx"”,“day”:“"Monday"”,“userid”:“"51803259"”}]