Page 1 of 1

Getting last record from the database

Posted: Thu Oct 18, 2007 6:07 am
by ashrafzia
I have the following table :

----------------------------------
Studnet_id | Reg_no | Std_name | etc....
----------------------------------

- Initially all records are Null.
- Student_id is Primary key.

I want to read last record from the field Student_id i-e NULL and then add +1 to it to get new Student_id.
This is the code:

Code: Select all

include "connection.php";
include "menu.php";
$tbname = "student_info";
$sql = "SELECT student_id FROM $tbname";
$result = @mysql_query($sql, $conn) or die (mysql_error());

if (!mysql_num_rows($result)){
	$student_id++;
	}
else {	
		while ($row = mysql_fetch_array($result)){
		$student_id = $row['student_id'];
		
		if ($student_id > 0) { $student_id++; }
		}
}

$form = "<body>
<form action='PHP_SELF' method='post' enctype='multipart/form-data'>
  <table width='394' border='0' align='center' cellpadding='5' cellspacing='5'>
    <tr>
      <td width='135'>Studnet ID:</td>
      <td width='218'><input name='student_id' type='text' size='5' value=$student_id></td>
    </tr>
<input type='image' name='register' src='images/register.gif'>
    etc......
</body>";

echo "$form";

if (isset($_POST['register_x'])){
$sql = "INSERT INTO $tbname (student_id, registration_no, student_name, father_name, programe, picture) VALUES
		('$_POST[student_id]', '$_POST[registration_no]', '$_POST[student_name]', '$_POST[father_name]', '$_POST[programe]',
		 '$_POST[picture]')";
$result = @mysql_query($sql, $conn) or die (mysql_error());
echo "Record Added Successfully.....";
}
Problems:

Whenever a Record is added, everything goes fine but Student_id doesn't change. It remains the same.
I want whenever someone enters a record, values should be added to database and then Student_id must be updated by reading last record/last value of Student_id and therefore incremented.
The above code is working fine but there's a problem with it, and it's this : Whenever a record is added the Student_id doesn't change for the first time and when Register button is clicked again it gives a primary key error and then it's updated. I don't know what's the problem?

I hope you got what i want to say.... If further any Question please ask.

Posted: Thu Oct 18, 2007 6:48 am
by N1gel
Have you thought of using auto_increment instead. then mysql will automatically add a new ID for you

Auto Increment

or if you do want a query to get the last id, you can try something like this

Code: Select all

$sql = "SELECT student_id FROM $tbname order by Student_id desc limit 1"; 

$result = @mysql_query($sql, $conn) or die (mysql_error()); 

if($row = mysql_fetch_row($result)
{
    $NextID = $row[0] + 1;
}