PHP and mysql query to retrieve name

Question

I have the following code which gets a users data from a table based on their log in details

//==========================================
//  CONNECT TO THE LOCAL DATABASE
//==========================================
$user_name = "xxxx";
$pass_word = "xxxxx";
$database = "xxxx";
$server = "xxxxxx";

$db_handle = mysql_connect($server, $user_name, $pass_word);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
    $SQL = "SELECT * FROM students WHERE L1 = '$uname' AND L2 = '" .md5 ($_POST['password'])."'";
    $result = mysql_query($SQL);
    $num_rows = mysql_num_rows($result);

    //====================================================
    //  CHECK TO SEE IF THE $result VARIABLE IS TRUE
    //====================================================

    if ($result) {
        if ($num_rows > 0) {
            $color="1";

            $result = mysql_query("SELECT * FROM entry, students   WHERE entry.studentName = students.studentName AND students.L1='$uname' ") or die(mysql_error());  

            echo "<p>Welcome "; echo $row[studentName];
            echo "<p>You records as of ";
            echo date('l jS \of F Y h:i:s A');  

            echo "<table border='1' cellpadding='2' cellspacing='0'>";
            echo "<tr> <th>Date</th><th>Student Name</th> <th>Tutor name</th> <th>Procedure name</th> <th>Grade</th><th>Student Reflection</th><th>Tutor Comments</th><th>Professionalism</th> <th>Communication</th> <th>Alert</th> <th>Dispute</th> </tr>";
            // keeps getting the next row until there are no more to get

            while($row = mysql_fetch_array( $result )) {
                if($color==1){
                    echo "<tr bgcolor=#DDD ><td>".$row['date']."</td><td>".$row['studentName']."</td><td>".$row['tutorName']."</td><td>".$row['procedureName']."</td><td>".$row['grade']."</td><td>".$row['studentReflection']."</td><td>".$row['tutorComments']."</td><td>".$row['professionalism']."</td><td>".$row['communication']."</td><td>".$row['alert']."</td><td>".$row['dispute']."</td></tr>";

                    // Set $color==2, for switching to other color
                    $color="2";
                }
                // When $color not equal 1, use this table row color
                else {
                    echo "<tr bgcolor='#CCC'><td>".$row['date']."</td><td>".$row['studentName']."</td><td>".$row['tutorName']."</td><td>".$row['procedureName']."</td><td>".$row['grade']."</td><td>".$row['studentReflection']."</td><td>".$row['tutorComments']."</td><td>".$row['professionalism']."</td><td>".$row['communication']."</td><td>".$row['alert']."</td><td>".$row['dispute']."</td></tr>";
                    // Set $color back to 1
                    $color="1";
                }
            }
            echo '</table>';
        }

What I want to do it is echo or print out the users name above the table but I am having difficulty doing so. The only want I can get it it to work is to add it to the while loop but then it prints it out as many times as the user has records.

Can you help?

I see an error in your code: you've declared $user_name and in your SQL query you've used $uname — anche
– anche, Commented Oct 23, 2012 at 14:03
this is not a problem. I can connect to the database fine and get the users records out but before the table is produced I want to echo out the the users full name which is held in the student table.The $uname is for the html log in type form I use for students to log in — user1132612
– user1132612, Commented Oct 23, 2012 at 14:05
Not that this will answer your question but if you are using PHP5, you shouldn't be using mysql_ commands as they are deprecated. You should switch to using mysqli or PDO. — Codeguy007
– Codeguy007, Commented Oct 23, 2012 at 14:05
$row['studentName']; notice the ' and '. Btw you have to fetch the SQL result before trying to use the $row. — Alex
– Alex, Commented Oct 23, 2012 at 14:05
thanks alex but the problem I am having is if I as it to fetch the $row result after fetch the only place I can add it is in the while loop which means it will be return for the number of times I have records for that particular student — user1132612
– user1132612, Commented Oct 23, 2012 at 14:08

GolezTrol · Accepted Answer · 2012-10-23 14:04:20Z

You will need to fetch the first row in order to gain access to the user information. That means you will have to call mysql_fetch_array once before the loop.

That will get you in trouble, because you will also need to call it in the loop. You could hack around that by using all kinds of boolean flags or copies of the row, but the best way is to change the structure of your code a little.

Use a do-while loop, combined with an if statement. This allows you to fetch a single row first, and take special action if none is found. After that, you got a loop that does what it does now, only it checks if there is a next row after the iteration instead of before, otherwise the first row would be skipped in the table output.

if ($row = mysql_fetch_array( $result )) {
  // Print user info
  // Print table header

  do {

    // Print table row

  } while ($row = mysql_fetch_array( $result ));

  // Print table footer
}
else
{
  // User not found. Print error or whatever.
}

Svetoslav · Accepted Answer · 2012-10-23 14:04:34Z

0

You have mistake at your logic... You are making table of studens and you want to echo Welcome to the student which is logged_in

But you use echo "<p>Welcome "; echo $row[studentName];

Where $row is not set... This name should come from a previous select ..

answered Oct 23, 2012 at 14:04

Svetoslav

4,6862 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

PHP and mysql query to retrieve name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related