CODEWITHSUNDEEP
javascriptphpmysqlsqlajax
Caspar
Caspar

Reputation: 169

What is the most efficient way to get SQL table values?

I wrote some code a while back to retrieve a player's saved score when they logged into the website. It used AJAX, and looked a little something like this:

Javascript:

function getHighScore(user){
    $.get("getScore.php?userName="+user,function(data){
        console.log(data);
                output = data;
    });
}

PHP:

<?php
$username = strval($_GET['userName']);

$con = mysqli_connect('localhost','XXXX','XXXX','XXXX');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
} else {
$sql="SELECT * FROM users WHERE username = '".$username."'";
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {
    $wealth = $row['wealth'];

    echo $wealth;

    }
}

mysqli_close($con);
//return $wealth;
?>

I was faced with the fact today that I would have to grab a whole bunch of data from a database, so I had a look at the old high score code I wrote. I'll attach a screenshot of what the table looks like that I will be retrieving info from. Anyway, I need to grab info from 6 columns and 10 rows. I need to assign a PHP variable to the data from P1, P2, P3 etc on MON1; P1, P2, P3 etc on TUE1; so on and so forth until we reach P6 on FRI2. I'm really quite new to PHP, so what would be the best way to go around doing this?

I apologise if I worded the question strangely. Feel free to ask if you didn't understand something.

Thank you!

PS: Ignore the P#_WORK columns. I don't need to refer to them right now

https://ibb.co/gq8u5a

Upvotes: 1

Views: 57

Answers (1)

ablopez
ablopez

Reputation: 850

I'd suggest you use an object to store everything, using the "day" column as key, and as value the P* columns in another. Here's some code you can use right away:

<?php
$con = mysqli_connect('localhost','XXXX','XXXX','XXXX');
if(!$con) exit('Could not connect: ' . mysqli_error($con));

$username = $_GET['userName'];

$sql = "SELECT * FROM TABLE WHERE username = '%s'";
$results = $con->query(sprintf($sql, $con->escape_string($username))); // Always escape parameters to prevent SQL injection attacks

$data = new stdClass; // An empty object

while($result = $results->fetch_object()){
    $day = $result->day; // Use the day as the key of the object
    $data->{$day} = $result;
}

// Now we output the results below by accesing the data in a chain-like fashion by accesing the "data" object
echo $data->MON1->P1 . '<br>'; // will output "Geo E"
echo $data->FRI1->P4 . '<br>'; // will output "Maths"
echo $data->THU2->P6 . '<br>'; // will output "DT"

Be sure to replace "TABLE" in the SQL query with the actual table name, as that wasn't visible in the screenshot you attached.

Upvotes: 1

Related Questions