How to access a field from mysql table from url with PHP

Question

Hi im trying to access a field from a mysql table results from a url eg localhost/test.php=id=2

btw im noob to xml and php so please excuse me for that..

any suggestions or tutorials would be gratefully appreciated..

heres the code im working on

<?php
 header("Content-Type: text/xml"); 
 $xmlBody = '<?xml version="1.0" encoding="ISO-8859-1"?>';
 $xmlBody .= "<XML>";

 $dbhost = 'localhost:3036';
 $dbuser = 'nas';
 $dbpass = 'root';
 mysql_select_db('db_home') or die("no database");

 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());

 $sql = mysql_query("SELECT * FROM table_gas ORDER BY datetime DESC LIMIT 0, 20");

while($row = mysql_fetch_array($sql)){

$id = $row["gas_id"]; 
$timestamp = $row["timestamp"];  
$value = $row["value"]; 
$datetime = strftime("%b %d, %Y", strtotime($row["datetime"])); 
$xmlBody .= '
<Data> 
<DataID>' . $id . '</DataID> 
<DataTime>' . $timestamp . '</DataTime>
<DataValue>' . $value . '</DataValue>
<DataTime>' . $datetime . '</DataTime>
</Data>';
} 
mysql_close(); 
$xmlBody .= "</XML>";
echo $xmlBody; 
?>

when i put this into the address bar local/xmltest.php?DataID=2 i get a blank screen..

Answer 1

First of all, you shouldn't use mysql_ anymore. I personally like PDO so here's my answer

As mentioned below, the url should be localhost/xmltest.php?DataID=2

header("Content-Type: text/xml");
$xmlBody = '<?xml version="1.0" encoding="ISO-8859-1"?>';
$xmlBody .= "<XML>";

$dbhost = 'localhost'; //No need to set the port if default is 3306
$dbuser = 'nas';
$dbpass = 'root';
$dbname = 'db_home';

// Check if ?id= is set or not
if ( isset($_GET['DataID']) ) {
    // Initiate the connect to the MySQL DB using PDO extention. You'll need to activate PDO if it's not done then restart Apache
    $db = new PDO('mysql:host='. $dbhost .';dbname='. $dbname, $dbuser, $dbpass);

    $query = $db->prepare("SELECT * FROM table_gas WHERE id = :id ORDER BY datetime DESC LIMIT 0, 20");
    $query->bindParam(':id', $_GET['DataID']); // Grab the value inside ?id= and pass it in the query
    $query->execute(); // Execute the query with the parameters
    $result = $query->fetchAll(); //Fetch everything and store it in a variable

    foreach ($result as $row) { // I like using foreach but while loop works too
        $xmlBody .= '
        <Data> 
        <DataID>' . $row["gas_id"] . '</DataID> 
        <DataTime>' . $row["timestamp"] . '</DataTime>
        <DataValue>' . $row["value"] . '</DataValue>
        <DataTime>' . strftime("%b %d, %Y", strtotime($row["datetime"])) . '</DataTime>
        </Data>';
    }
    $xmlBody .= "</XML>";
    echo $xmlBody; 
} else {
    echo "Invalid ID."; //Output error message if test.php?id= is not set
}

Answer 2

The URL localhost/test.php=id=2 is invalid. It should be localhost/test.php?id=2

The ? mark start the parameter list for a GET request. You retrieve the value of the parameter by $_GET['id']

If you get a blank screen, its probably a syntax error in your PHP, but you have error reporting off. For your development server you should turn error reporting on to see what that is. See How to get useful error messages in PHP?

Answer 3

Firstly fix your url as developerwjk suggests. so;

localhost/test.php?id=2

also im assuming DataID is the id of the table_gas entry you want to get it.

maybe try;

$DataID = mysql_real_escape_string($_GET['DataID']);
$sql = mysql_query("SELECT * FROM table_gas WHERE gas_id = ".$DataID." ORDER BY datetime DESC LIMIT 0, 20");

This will get only the row specified in the url.

I would suggest however moving onto MySQLi as MySQL is soon to be depreciated, and also move onto parametrized queries to improve security.

So something like.

$DataID = $_GET['DataID'];
$q = $mysqli->prepare("SELECT * FROM table_gas WHERE gas_id = ? ORDER BY datetime DESC LIMIT 0, 20")
$q->bind_param("i", $DataID);
$q->execute();
$q->bind_result($id, $timestamp, $value, $datetime);
while($q->fetch()){
    $xmlBody .= '
      <Data> 
          <DataID>' . $id . '</DataID> 
          <DataTime>' . $timestamp . '</DataTime>
          <DataValue>' . $value . '</DataValue>
          <DataTime>' . $datetime . '</DataTime>
      </Data>';
}

Hope this helps, and seriously, look into MySQLi, even if you dont use the OO approach with it.

Answer 4

Look here:

mysql_select_db('db_home') or die("no database");

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());

You're actually trying to select a database while you're not even connected to server yet.

Try the other way around instead:

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (mysql_error());
mysql_select_db('db_home') or die("no database");