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..

Please take a look here: stackoverflow.com/questions/845021/… — hakre
– hakre, Commented May 20, 2014 at 22:21
As mentioned in the @developerwjk's answer. Add $DataID = $_GET['DataID']; above the sql query. In your actual sql query add WHERE id = '$DataID' — TomFirth
– TomFirth, Commented May 20, 2014 at 22:35

John Guan · Accepted Answer · 2014-05-21 12:30:28Z

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
}

edited May 21, 2014 at 12:30

answered May 20, 2014 at 22:38

John Guan

7443 gold badges12 silver badges26 bronze badges

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

6 Comments

Nasir Shiraz Over a year ago

hi im getting the following error Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /var/www/xxx.php on line 24 any suggestions .. thanks

John Guan Over a year ago

My bad, I made a typo it should be :id instead of :uid on the line $query->bindParam(':uid', $_GET['DataID']); I modified the code

John Guan Over a year ago

Haha, really sorry for that once again. I made a mistake in the query. It happens when I answer this when I am half awake.

Nasir Shiraz Over a year ago

no luck sorry again.. 192.168.1.111/yyy.php?DataID=2 im still getting a blank screen

Nasir Shiraz Over a year ago

1 gas_id int(16) No None AUTO_INCREMENT Change Change Drop Drop More Show more actions 2 timestamp timestamp on update CURRENT_TIMESTAMP No 0000-00-00 00:00:00 ON UPDATE CURRENT_TIMESTAMP Change Change Drop Drop More Show more actions 3 value int(20) No None Change Change Drop Drop More Show more actions 4 datetime datetime No None Change Change Drop Drop More Show more actions

|

JParkinson1991 · Accepted Answer · 2014-05-20 22:38:02Z

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.

Community · Accepted Answer · 2017-05-23 10:24:15Z

0

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?

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered May 20, 2014 at 22:20

developerwjk

8,6342 gold badges20 silver badges33 bronze badges

3 Comments

Nasir Shiraz Over a year ago

thanks for the reply, im still getting a blank screen :-( does the code look ok ?

Nasir Shiraz Over a year ago

i cant find the php.ini file do you know where it is located ?

developerwjk Over a year ago

under the installation path for PHP.

user1299518 · Accepted Answer · 2014-05-20 22:40:05Z

0

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");

answered May 20, 2014 at 22:40

user1299518

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related