How do I obtain data through a MySQL Database using GET in PHP?

Question

I've tried the solutions in this question, however mysql has been depricated for mysqli. Even with these changes it still doesn't return the information, instead returns an error, with nothing else (Nothing is heard from mysqli)

What i'm trying to do is kind of similar to the question linked, however it would look like this in the url: example.com?view-work=A01 It would search for A01 in the database, then return the Name, description, an image URL and date it was made live.

This is the code that i've been able to make using the answers from the question:

<?php
//Establishing a connection to the Artwork Database
mysqli_connect('localhost', 'dbuser', 'dbpassword');
mysqli_select_db('db');

$artworkidentifier = $_GET["view_work"];

//Returning the result, if there is one

$artworkidentifier = mysqli_real_escape_string($artworkidentifier);

$sql = "SELECT * FROM ArtDB WHERE art_refcode = '$artworkidentifier'";
$result = mysqli_query($sql);
if (!$result) {
    echo "Something's gone wrong! ".mysqli_error();
}

$data = mysqli_fetch_assoc($result);
echo $data["Artwork_Name"];
echo $data["Artwork_Description"];
echo $data["Artwork_URL"];
echo $data["DateUploaded"];
?>

You will have to [Read The Manual](http://php.net/manual/en/book.mysqli.php) Changing from `mysql_` to `mysqli_` is not just as simple as adding an `i` to each api call — RiggsFolly, Jun 23 '18 at 17:45
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jun 23 '18 at 17:46
Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Jun 23 '18 at 17:46

score 0 · Accepted Answer · answered Jun 23 '18 at 18:08

Seems like the cause of these errors was my own incompetence, also probably the fact I'm kind of new to PHP and MySQL in general. I learnt that I needed to reference my connection in some of the commands for them to successfuly process after adding the debug exception mentioned in the OP's comments.

As someone also pointed out, Yes this code is still vulnerable to other types of SQL injection, I'll be addressing these before the final version of the code goes live.

Fixed Code:

<?php
//Establishing a connection to the Artwork Database
$link = mysqli_connect('localhost', 'dbusr', 'dbpasswd', 'db');

//Exeptional Debugging

ini_set('display_errors', 1);
ini_set('log_errors', 1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

if (!$link) {
    echo "Error: Unable to connect to MySQL!";
    echo "Error No.".mysqli_connect_errno();
    echo "Error in question: ".mysqli_connect_error();
    exit;
}

$artworkidentifier = $_GET["view_work"];

//Returning the result, if there is one

$artworkidentifier = mysqli_escape_string($link, $artworkidentifier);

$sql = "SELECT * FROM ArtDB WHERE art_refcode = '$artworkidentifier'";
$result = mysqli_query($link, $sql);
if (!$result) {
    echo "Something's gone wrong!"; //This line will be changed later to sound more professional
}


$data = mysqli_fetch_assoc($result);
echo $data["Artwork_Name"];
echo $data["Artwork_Description"];
echo $data["Artwork_URL"];
echo $data["DateUploaded"];
?>

How do I obtain data through a MySQL Database using GET in PHP?

1 Answers1