How to get parameters from a URL and use them in a MySQL query in PHP

Question

So, I will have a page where a user lands with a /?ref=123 type ending on the URL. Then, what I want to achieve, is to use that ref 123 in a MySQL query, like this: SELECT foo FROM table WHERE table.ref = 123

The issue I have hit is that if i use $_GET to get the variable, it breaks my SQL query (obviously!)

SELECT foo FROM table WHERE table.ref = $_GET['ref']; falls over because $_GET is not a MySQL function.

Trouble is, I want to dynamically create page content based on the ref value but I can't figure how.

Any help gratefully accepted.

Timezone GMT+13 so replies will potentially be slow :)

**********EDIT**********

As I may not have given enough info in the OP, here's the code I'm struggling with:

<?php
global $wpdb;
header('Content-Type: text/html; charset=utf-8');
include "../../../wp-config.php";

$get_donation_amount = "select SUM(amount) AS total_donations from SaveContactForm7_1 where ref = 123 ";
$get_donation_amount_result = $wpdb->get_results($get_donation_amount);
$total_donation = isset($get_donation_amount_result[0]->total_donations) && $get_donation_amount_result[0]->total_donations !="" ? $get_donation_amount_result[0]->total_donations :"0" ;
?>

What I need to do is add a call to the URL for the value of ref and add it where shown with the SQL querycode. Then a particular donor who knows his 'ref' value will see results relevant to him alone.

Please take to time to read any of the existing "getting started" tutorials about programming based on php and mysql. They all explain and demonstrate what you ask. And make sure to understand the dangers in that. Learn about the benefits of using "prepared statements" in combination with "parameter binding". — arkascha
– arkascha, Commented Sep 23, 2017 at 7:02
thanks @arkascha - I tried a couple and got rather lost so came here to my old favourite SO. I'll keep Googling.... — Bevan
– Bevan, Commented Sep 23, 2017 at 23:50
"They all explain and demonstrate what you ask" ?? not ww3schools, lynda.com, not freewebmasterhelp, not tutorialspoint (although a very good reference on the related topics) nor mysqltutorial. So far, that's the first two pages of Google results. Would you care to post a link to the tutorial that covers getting a variable from the url and inserting it into a php MySql query string? Thanks in advance. — Bevan
– Bevan, Commented Sep 24, 2017 at 21:02
This is impressive. So no tutorial you could find explains how to use $_GET? How to concatenate a string to be used as a SQL statement executed as any other SQL statement in the existing database connection you have? Really? Although all such tutorials show exactly that? Sorry, but it looks as if you either are not really following any tutorials or you are simply not willing to understand what you are shown. Of course you can use a variable in an SQL query (whether you should is another question). What you yourself posted will work if done correctly (you do not show how you use it). — arkascha
– arkascha, Commented Sep 24, 2017 at 21:06
Again, thanks, but which tutorial shows me what you describe? I've just now tried searching for $_GET and for "How to concatenate a string to be used as a SQL statement executed as any other SQL statement in the existing database connection you have" and all I get is disjointed bits of results. If I could find what I was looking for, I would not be asking this on SO. Please recommend one such tutorial that is covered by your "Although all such tutorials show exactly that?" - I in my confused state cannot find one, or as you put it cannot understand what I am being shown. — Bevan
– Bevan, Commented Sep 24, 2017 at 21:17

JimL · Accepted Answer · 2017-09-23 07:27:51Z

2

Using PHP7 this could look something like this

$ref = $_GET['ref'] ?? null;

$sth = $dbh->prepare('SELECT foo FROM table WHERE table.ref = ?');
$sth->execute([$ref]);

$orders = $sth->fetchAll();

You should probably have some way of handling errors (ref is not set)

Note that the last variable (result of the query) is called orders. I didn't know what the result set you expected would be, but it was just to illustrate that it makes sense to call it something spesific (what it actually represents), instead of "rows", "result" or similar.

Note that PHP 7 introduces the so called null coalescing operator which simplifies isset statements

PHP7

$ref = $_GET['ref'] ?? null;

PHP < 7

$ref = isset($_GET['ref']) ? $_GET['ref']: null;

answered Sep 23, 2017 at 7:27

JimL

2,5411 gold badge20 silver badges20 bronze badges

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

5 Comments

Bevan Over a year ago

It's on PHP 5.5.9. I tried to add your code but no success.

Bevan Over a year ago

Maybe I should post more detail? I didn't want to confuse the question, and thus earn downvotes :)

Bevan Over a year ago

No. The page simply doesn't render at all.

JimL Over a year ago

@Bevan then you haven't set your development environment to show all errors. A blank page is the default configuration for 500 internal server errors, when developing locally you will want to show all errors

Bevan Over a year ago

I'm going to accept yours as the best answer: I upgraded to php7 and your code worked. The page is not working properly, but you answered the question as asked.

Collectives™ on Stack Overflow

How to get parameters from a URL and use them in a MySQL query in PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related