PHP / SQL Query dynamic record from url

Question

I currently have this:

<?php
  $con = mysql_connect('localhost', 'root', 'dev');
  if(!$con) {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("myDB");
  $query = "SELECT * FROM pages where id=1";
  $result = mysql_query($query);
  $row = mysql_fetch_assoc($result);
  $contents = $row['content'];
  echo $contents;
?>

See this part: SELECT * FROM pages where id=1

1 is the record id and it's currently hardcoded. What I need to do is change it so it get's the record id from the url...for example: mysite.com/index.php?2 would show record id 2 ...

How do I go about doing this?

Jared · Accepted Answer · 2012-03-15 16:21:14Z

3

Turn that hardcoded value into a variable.

<?php
    //assumes you have a querystring like: http://mysite.com/index.php?id=3
    $id = $_GET['id'];


  $con = mysql_connect('localhost', 'root', 'dev');
  if(!$con) {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("myDB");


    //Make your variable safe for use with mysql
    $id = mysql_real_escape_string($id);
  $query = "SELECT * FROM pages where id=" . $id;
  $result = mysql_query($query);
  $row = mysql_fetch_assoc($result);
  $contents = $row['content'];
  echo $contents;
?>

edited Mar 15, 2012 at 16:21

answered Mar 15, 2012 at 15:43

Jared

12.5k1 gold badge37 silver badges39 bronze badges

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

1 Comment

Jon Egeland Over a year ago

You should explain how to make the URL look like that programatically.

seferov · Accepted Answer · 2012-03-15 15:41:47Z

2

let say the url is something like that: mysite.com/index.php?id=2

in your index.php:

<?php
$id = $_GET['id'];
// your sanitizing methods for id to avoid SQL injection

$con = mysql_connect('localhost', 'root', 'dev');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("diy");
$query = "SELECT * FROM pages where id = ".$id;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$contents = $row['content'];
echo $contents;
?>

Beware of SQL injection

answered Mar 15, 2012 at 15:41

seferov

4,1613 gold badges42 silver badges76 bronze badges

1 Comment

Jon Egeland Over a year ago

You should explain why the URL looks like that.

user1237595 · Accepted Answer · 2012-03-15 15:42:36Z

1

Basic example using mysite.com/index.php?id=x as your URLs where x is the Id:

$id = (int)$_GET['id'];

$query = sprintf("
    SELECT * 
    FROM pages 
    WHERE id = %d",
    mysql_real_escape_string($id)
);

With your connection lines included of course, you should also validate.

answered Mar 15, 2012 at 15:42

user1237595

Comments

Jon Egeland · Accepted Answer · 2012-03-15 15:48:36Z

1

URL data is interpreted using the GET method. First, you should look here for how to use it, and here for how to read it.

Basically, your URL will look like this:

mysite.com/index.php?id=2

Then, you could read in the URL variable like this:

$id = mysql_real_escape_string($_GET['id']);

mysql_real_escape_string() will help avoid SQL injection, but requires an existing connection, so your code would look like this:

<?php
  // Set up connection

  $id = mysql_real_escape_string($_GET['id']);
  $query = 'SELECT * FROM pages where id = '.$id;

  // Run the query
?>

answered Mar 15, 2012 at 15:48

Jon Egeland

12.7k8 gold badges49 silver badges64 bronze badges

Comments

Phil · Accepted Answer · 2012-03-15 15:50:22Z

0

You could use a regular expression to extract it from the URL.

$retval=preg_match( "@(\d+)$@", $_SERVER['REQUEST_URI'], $match );
$index=-1;
if( $retval ) { 
    $index = $match[1];
}

This approach allows you to continue using the URL scheme you described in the question without prepending id=. Whether that's a good idea or not is probably debateable.

edited Mar 15, 2012 at 15:50

answered Mar 15, 2012 at 15:43

Phil

8516 silver badges9 bronze badges

2 Comments

Jon Egeland Over a year ago

Why not use GET or POST data?

Phil Over a year ago

I answered the question that was asked, which did not include a variable identifier.

Bo Persson · Accepted Answer · 2012-03-15 20:08:16Z

-1

http://pastebin.com/NEZe7jjL

<?php

$dbh = new PDO('mysql:host=127.0.0.1;dbname=test', 'user', 'password', array(
    PDO::ATTR_EMULATE_PREPARES => true,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8',
));

$stmt = $dbh->prepare('SELECT * FROM `pages` WHERE `id` = :page');
$stmt->bindValue(':page', $_GET['page'], PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

?>

yoursite.com/index.php?page=2

edited Mar 15, 2012 at 20:08

Bo Persson

92.9k31 gold badges153 silver badges211 bronze badges

answered Mar 15, 2012 at 15:47

Miraage

3,4943 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

PHP / SQL Query dynamic record from url

6 Answers 6

1 Comment

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related