basic: fetch id and data in PHP MySQL

[earlxph8]

hello,

i have a basic code, and i want to fetch a data from mysql using PHP, and display in my html page; i just want to have a basic idea so i can add more details later on, like data of birth, nationality, profession etc.

im planning to make a simple out put that looks like a filled up application form.

for example:
(in a filled up registration form output, normally there should be an underline for the output)

Company: ___
Name: <td style="text-decoration:underline;">adam</td>
date of birth: _____
profession: engineer
nationality:____

my database:
===========
id | name | address |
---------------------
 1 | adam | sydney  |
 ---------------------
 2 | john | turkey  |

<?php               
mysql_connect("localhost","root","password");
mysql_select_db("employees_test");

$order = "SELECT * FROM data_employees";
$result = mysql_query($order);

while($data = mysql_fetch_row($result)){
    echo $data[0];

}   
?>

My Question:

1. how can i add ID in my database?
2. how to fetch data individually? is it only an object?

thanks

[simplypixie]

The id in your table needs to be set to Auto Increment and that will then mean every time you add a new record that database will assign the next appropriate id for you to that record.

With regard to selecting one record only

<?php
$id = // Assign the id you want to get from the database (I can't tell you how as not sure how you are sending data to this page
$order = mysql_query("SELECT * FROM data_employees WHERE id='$id' LIMIT 1");
$result = mysql_fetch_array($order);
$name = $result['name'];
$address = $result['address'];
?>

<p>Name: <span style="text-decoration:underline;"><?php echo $name; ?></p>
<p>Address: <?php echo $address; ?></p>

[earlxph8]

@simplypixie:

much appreciated, however, am really a newbie, can you provide me some basic options on what value i should put in $id = ?; line #3

for example, i have 10 list of names with address, and i just want to call or get by ID from my database so that it will display specific name and its address.

so far, it works! i tried to place a value of 0 - 2, and it displays the row of its name and address.

although i might use this in more complex code, where i have a big database and it will just display its own ID once i filled up the form.

thank you very much. this is very helpful.

[broj1]

To give you some ideas:

• as simplypixie said, use an autocrement type for the ID field in the database table; this way the IDs will get generated automatically, will be unique and you do not have to wory about them
• when you fetch the data use mysqli_fetch_array function which fetches each row in an associative array so you can address each field by it's associative index which is the same as the field name
• use newer mysqli extension instead of the older mysql exstension to deal with database

If you use a web form to fetch the data, users will usually input the name, nationality, profession or some similar data, not the ID. So the query will be something like:

// since the information comes from the untrusted source (a web form)
// you have to at least escape it using [mysqli_real_escape_string](http://php.net/manual/en/mysqli.real-escape-string.php)
$name = mysqli_real_escape_string($_POST['name']);

$query = "SELECT * FROM tablename WHERE name LIKE '$name'";

The fields in the form should have name attributes that are the same as field names in the table; that will make your life easier.

[simplypixie]

What I am thinking is that you will have a list of records from the database that you will click on to view further information, so in the first instance you would select all rows or a set number of rows (we will go for all for the time being as this is just to get things working as you require and you currently don't appear to have too many records).

First page - get all records from the db

<?php 
  $sql = mysql_query("SELECT * FROM data_employees ORDER BY name ASC");
  while($data = mysql_fetch_array($sql)){
?>
  <p><a href="details.php?id=<?php echo $data['id']; ?>><?php echo $data['name']; ?></a></p>
<?php } ?>

details.php

<?php
  if (isset($_GET['id'])) // Check id has been passed in URL
  $id = mysql_real_escape_string($_GET['id']); // Prevent injection
  $order = mysql_query("SELECT * FROM data_employees WHERE id='$id' LIMIT 1");
  $result = mysql_fetch_array($order);
  $name = $result['name'];
  $address = $result['address'];
?>
<p>Name: <span style="text-decoration:underline;"><?php echo $name; ?></p>
<p>Address: <?php echo $address; ?></p>

I agree with broj1 that you should look at using mysqli or PDO (I have not used here as just putting it in place will confuse you as your database connection needs to be relative to whichever you choose to use before you can use it for queries etc).