1
$value = mysql_query("SELECT max(student_id) FROM student_profile");
$stud_id = mysql_fetch_array($value);
COMMIT;
mysql_query("INSERT INTO course_batch (student_id, course_id, batch_id)
VALUES('$stud_id','$course_id','$batch_id')") or die (mysql_error());

i need help on how to get the max id on student_profile table and store it in course_batch table. the student_id is auto incremented. TIA

fianne
  • 13
  • 3
  • What is the problem you are having? – u54r Aug 21 '13 at 20:40
  • 1
    [mysql_fetch_array](http://php.net/manual/en/function.mysql-fetch-array.php) returns an array. You might use [mysql_fetch_row](http://www.php.net/manual/en/function.mysql-fetch-row.php) instead. But you should not use `mysql_` functions at all. They are deprecated. Use [PDO](http://php.net/manual/en/book.pdo.php) or [mysqli](http://php.net/manual/en/book.mysqli.php) instead. – Brewal Aug 21 '13 at 20:41
  • Also `var_dump()` you variables to see what they are. – Brewal Aug 21 '13 at 20:47
  • You can get the same result with just one query. Try `INSERT INTO course_batch (student_id, course_id, batch_id) VALUES((SELECT max(student_id) FROM student_profile),'$course_id','$batch_id')` – hdvianna Aug 22 '13 at 16:12

2 Answers2

0

There are many options. You could use your current code and fetch the array as you are now and use the result properly, ie

$stud_id['student_id']

EDIT - try this

$value = mysql_query("SELECT max(student_id) AS StudId FROM student_profile");
$qryResultArray = mysql_fetch_array($value);
$stud_id = $qryResultArray['StudId'];

mysql_query("INSERT INTO course_batch (student_id, course_id, batch_id)
VALUES('$stud_id','$course_id','$batch_id')") or die (mysql_error());

if you specifically need to use commit (for whatever reason, you have other code I cannot see etc) then let me know.

The above is very basic too, it's not checking things are arrays, if there is a result, and has no security at all so sql injection is not protected (but then this code wont work soon anyway, when servers update their PHP)

Or you could greatly increase your security and usability and future proof your code by using mysqli or PDO, both of which while a learning curve are fairly easy to learn, and once learned no harder than what you are coding now. The functions you use now will soon cause you issues and the need to check the PHP version on a server before your code can work (which is not ideal in any scenario)

very basic PDO example:

$db = new PDO(details here);

$stmt = $db->query('select id from student_profile etc');
$stud_id = $stmt->fetchColumn(0);

//then use $stud_id in your update/insert/etc

you then just check if the result is false (among other sanity checking to ensure your code is clean and all outcomes are accounted for) You can use "order by limit 1", or "max, whatever

James
  • 4,644
  • 5
  • 37
  • 48
  • im a noob at php. ive tried learning mysqli but its too hard. and this is for a project at school and only have 10 days left to do it. its only a simple enrollment system without courses just programs. – fianne Aug 22 '13 at 04:49
  • Fair enough. You need what you need, but we also need to make sure you're aware of all info so you can decide for yourself and make decisions based on best practise :) I've edited my post above, try that code. – James Aug 22 '13 at 16:07
  • it worked! still a noob at php, but im trying my best to learn.. looked for days just to find an answer for this, but didnt find any. thank you so much! – fianne Aug 22 '13 at 19:18
0
$value = mysql_query("SELECT student_id FROM student_profile ORDER BY student_id DESC LIMIT 1");
$row = mysql_fetch_array($value);

$stud_id = $row['student_id'];

mysql_query("INSERT INTO course_batch (student_id, course_id, batch_id)
VALUES('$stud_id','$course_id','$batch_id')") or die (mysql_error());

That's what you want?

EDIT:

Read this: The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead

Community
  • 1
  • 1
Ygor Montenegro
  • 659
  • 1
  • 12
  • 26