Database Output

[AndyFielder]

hello all,

I normally use:

        $query = "SELECT * FROM project_student WHERE ID = '$id'";
        $SQLquery = mysql_query($query);
        $num = mysql_numrows($SQLquery);

        mysql_close();

        $i = 0;
        while ($i < $num) {

            //get me information about the student
            $ID = mysql_result($SQLquery,$i,"ID");
            $FName = mysql_result($SQLquery,$i,"FirstName");
            $LName = mysql_result($SQLquery,$i,"LastName");

            //Display it
            echo("You are interviewing student ".$ID."<br>");
            echo($FName." ".$LName);
            $i++;
        } 

For displaying data from a database, how can i out put just one record from the database, hopefully not needing the while loop etc.

I am looking for a neat soloution if you can help!

Any other ways of connecting and displaying data for multiple rows would be good too, eg when i want to show all the data from the database.

Thanks,
Andy

[swdev]

This is completly untested code - so use at your own peril

// build query string
// depending on your database, you can see if there exists
// a function to concatenate FirstName and LastName
// as this will be more efficient that conatenating
// these two fields in PHP
// this assumes that $id is numeric
$qry = 'SELECT';
$qry .= '   ID, FirstName, LastName';
$qry .= ' FROM';
$qry .= '   project_student';
$qry .= ' WHERE';
$qry .= '   ID = ' . $id;

// run the query
$result = mysql_query($qry) or die(mysql_error());

$msg = '';
// display the results
while ($row = mysql_fetch_assoc($result))
{
  // build the message string to be displayed
  $msg .= 'You are interviewing ' . $row['ID'] . '<br>';
  $msg .= $row['FirstName'] . ' ' . $row['FirstName'] .'<br>';
}
echo $msg; 

I think it is quicker to build the message string and echo it at the end of the loop rather than echoing it within the loop

[AndyFielder]

I have done:

<?php

//reset output
$output = "";

//connect to the database
DBconnect();

//query and get a row from the database
$row = mysql_fetch_assoc(mysql_query("SELECT * FROM project_student WHERE ID = '$id'"));

$output .=<<<OUT
You are interviewing:<br>
{$row['ID']}<br>
{$row['FirstName']} {$row['LastName']}<br>
{$row['EntryYear']}<br>
OUT;

print $output; 
?>

can you see any problems with this??

could anyone suggest how i mite alter this to make it capable of getting more than one row from the database. Mite i select all from the database, count rows then add one to $row till the max numb of rows is reached?

[spikeZ]

s swdev said, this part of the code loops through the returned rows and displays them.

while ($row = mysql_fetch_assoc($result))
{
  // build the message string to be displayed
  $msg .= 'You are interviewing ' . $row['ID'] . '<br>';
  $msg .= $row['FirstName'] . ' ' . $row['FirstName'] .'<br>';
} 

Spike