boo_lolly Posted November 28, 2006 Share Posted November 28, 2006 for some reason or another, my AUTO_INCREMENT command won't work in my sql table for the column named 'ID'. i have no idea why. it SHOULD work, but it doesn't. anyway, that's not really much of an issue. however, i have decided to take this opportunity to learn something new with PHP (i'm a noob). i was wondering how to use the MAX() command in my sql query.i'm building a part of a CMS where the admin will be able to insert, retrieve, and update values from sql tables in my database. each time there is a row of values inserted, i'd like to retrieve the value of the largest number in the 'ID' column, and increment it, THEN insert that value AND the rest of the values that the admin wants to place in the table. i'm pretty sure i've got the logic of the idea correct, but i'm not exactly sure how to properly execute the code. here's what i have so far.[code]<?php $sql = "SELECT incID FROM my_reg_table WHERE MAX(incID)"; $inc_regTable = mysql_query($sql); $reg = "SELECT * FROM my_reg_table"; $result = mysql_query($reg); $num_rows = mysql_fetch_array($result); if($num_rows < 1){$incID = 0;} else{$incID = $inc_regTable++;} $reg_table = "INSERT INTO my_reg_table (incID, uID, category, item, qty_req, still_needs) ". "VALUES('$incID', '$uID', NULL, NULL, NULL)"; mysql_query($reg_table);?>[/code]like i said i'm pretty sure the logic is correct. altho i'm not sure if i need to use the WHERE command before the MAX() function.a couple of questions... if i'm using the MAX() function within a sql query, does the column type have to be INT? can it be VARCHAR? why or why not? second, in order to properly retrieve the MAX value of the column 'ID' do i have to use mysql_fetch_array()?? thanks in advanced for all help. Quote Link to comment Share on other sites More sharing options...
craygo Posted November 28, 2006 Share Posted November 28, 2006 instead of max you could use order by and limit one[code]$sql = "SELECT incID FROM my_reg_table ORDER BY incID DESC LIMIT 1";$res = mysql_query($sql) or die (mysql_error());$r = mysql_fetch_assoc($res);echo $r['incID'];[/code]$r['id] will be the largest number in the column. Add one to it and insert it in.Ray Quote Link to comment Share on other sites More sharing options...
Barand Posted November 28, 2006 Share Posted November 28, 2006 Doing it that way cannot guarantee a unique id, using auto_increment can.I would try to get the auto-increment working. Quote Link to comment Share on other sites More sharing options...
boo_lolly Posted November 28, 2006 Author Share Posted November 28, 2006 thank you BOTH for very informative posts. i did end up getting the auto-increment working. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.