Converting GET variable to PHP string to be used in SQL query

Question

For a basic project, I'm attempting to simulate a vending machine with a MySQL database backend. The problem I have is the following: when the user presses a button for what they would like to buy, the price of the item should be displayed. However, when I am attempting to query the price from the database using the variable based on the button pressed, it does not display the price. However, when I insert the name of the item into the query it does work.

I have generated a table with this.

echo "<tr><td>" . $snackName . "</td><td>" . $price . "
      <a href = 'buying.php?varname= " . $snackName . "'><button>Buy</button></a>
      </td></tr>";

Then on the buying page, I'm attempting to do this.

$snackChoice = $_GET['varname'];
$sql = ("SELECT `price` FROM `snacks` WHERE `snackName` = '$snackChoice'");

However, when displaying this, nothing displays on the page. But when I substitute something such as 'Mars Bar' then the price for it does display. What should I do to display the price based on what button the user will press?

Is your error log showing anything when reaching the SQL query? — Adam, Feb 20 '19 at 20:10
Also, don't do it like that: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — AbraCadaver, Feb 20 '19 at 20:11
In your SQL query, make sure all of your quotes are the same. (`) is NOT the same as (') — Russ J, Feb 20 '19 at 20:12
Also, make sure that any spaces in $snackName are replaced with %20 — Russ J, Feb 20 '19 at 20:13

score 0 · Answer 1 · answered Feb 20 '19 at 20:12

0

drop the single apostrophes from around '$snackChoice' Just have:

"SELECT `price` FROM `snacks` WHERE `snackName` = $snackChoice"

or

"SELECT `price` FROM `snacks` WHERE `snackName` = '" . $snackChoice . "'"

answered Feb 20 '19 at 20:12

Peter Szalay

376
3
13

When replacing this, I get two errors: mysqli_num_rows and mysqli_fetch_assoc both expect 1 parameter to be a mysqi_result, but it says that a boolean is given. – Chirs Bonyes Feb 20 '19 at 20:18
$sql = "SELECT `price` FROM `snacks` WHERE `snackName` = '" . $snackChoice ."'"; Try without brackets. – Peter Szalay Feb 20 '19 at 20:22
1

I'm still catching the same error. Thank you for your responses Peter but I'm happy to say that I've got the correct answer up above. – Chirs Bonyes Feb 20 '19 at 20:28

score 0 · Accepted Answer · answered Feb 20 '19 at 20:19

I can recommend you to use an id instead of snakName to perform the query.

If you have it, if not add it as AUTO_INCREMENT to the table. This will be easier to manage, sanitize and query the database.

$snackId = $_GET['snackId'];

// perform validation, mitigate SQL injection

if(!is_numeric($snackId)){
    // redirect the user
    header('redirect:index.php');
    exit();

    // or change the value to 0...
    $snakId = 0;
}

$sql = ("SELECT `price` FROM `snacks` WHERE `snackid` = '$snackId'");

and in the form just replace the name with the id.

print "<tr>
          <td>" . $snackName . "</td>
          <td>" . $price . "
             <a href = 'buying.php?snackId= " . $snackId . "'><button>Buy</button></a>
          </td>
       </tr>";

This worked for me, thank you so much!! I should have just used the ID from the start I'm not sure why I chose to select based off of a string but many thanks once again for the help, I'll make sure to keep this in mind for the future. — Chirs Bonyes, Feb 20 '19 at 20:24
I tried to use numbers on parameters because are easier to validate and avoid sql injections and XSS., if it solve your problem, please mark as accepted answer. — Vidal, Feb 20 '19 at 20:27

Converting GET variable to PHP string to be used in SQL query

2 Answers2