How to SELECT from a table, part II.
TAGS: SQL, SELECT, PHP, MySQL


As promised in the last post we are now to select a single row from the database, and display values from that row. We will make a list of our contacts in a SELECT box. Then we can choose one person, and retrieve all the information we have about that person, from our database. So, lets get started. We have to make the webpage, and by making the SELECT we will use what we learned in the previous post – reading through all content.
Put the code in a file named 'select.php' and call it in your browser.

http://127.0.0.1/select.php


<?php 

if (isset($_POST['readme']))  { 
doread(); 
} 

function doread() { 
// This function is called when selecting a person from the SELECT
$IDnummer = $_POST['contact']; 

$con = mysqli_connect('127.0.0.1', 'root', 'Your_Password', 'dbname'); 
if (mysqli_connect_errno()) { 
  echo 'Error connecting to DB'; 
} 
$SQL_String = mysqli_query(
$con, 'SELECT * FROM contacts 
WHERE AutoID=' . $IDnummer . '');
 
$row = mysqli_fetch_array($SQL_String); 

echo "You selected: " . $row['Firstname'] . " " . $row['Lastname']; 
mysqli_close ($con); 
} 

?>

<HTML> 
<BODY> 
<FORM METHOD='POST' ACTION=''> 
<TABLE> 
<TR> 
<TD>Our contacts: </TD> 
<TD><SELECT NAME='contact'><OPTION>Make your choice 

<?php 
// Here we populate the SELECT with our contacts
$con = mysqli_connect('127.0.0.1', 'root', 'Your_Passwords', 'dbname'); 

if (mysqli_connect_errno()) { 
  echo 'Error connecting to DB'; 
} 

$SQL_String = mysqli_query($con, 'SELECT * FROM contacts ORDER BY AutoID ASC'); 

while($row = mysqli_fetch_array($SQL_String))  { 
  echo '<OPTION VALUE=' . 
  $row['AutoID'] . '>' . 
  $row['Firstname'] . ' ' . 
  $row['Lastname']; 
} 
mysqli_close ($con); 
?>

</SELECT> 
</TD> 
</TR> 
<TR> 
<TD></TD> 
<TD><INPUT TYPE='submit' NAME='readme' VALUE='Okay, select'> 

</TR> 
</TABLE> 
</FORM> 
</BODY> 
</HTML> 

As you can see, the code handling the database, inside the HTML, is looping through the rows of your table. This is for populating the SELECT.
Then, when submitting our choice, we call the function 'doread' in the top of the page. And here we only extract one row from the table. That one row is selected by its AutoID. What is done in the SQL query;

.. SELECT * FROM contacts WHERE AutoID= . $IDnummer ..

We have, just before that, read the AutoID from the SELECT, that was done by;
$IDnummer = $_POST['contact']; 

Where 'contact' is the name of the SELECT field.
And with that, we get the value of the person in the contact list that we chose, so now we can dig out that row from the table.

See the next posting for mere more exciting MySQL stuff, using PHP.