retrieving a particular value with a sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

retrieving a particular value with a sql query

Post by boo_lolly »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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: Select all

<?php
		$sql = "SELECT incID FROM my_reg_table 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);
?>
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.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Last edited by boo_lolly on Tue Nov 28, 2006 2:06 pm, edited 2 times in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Why are you building an "AUTO_INCREMENT command" when mysql already has that feature?
There are a couple of issuses with you implementation.
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Post by evilchris2003 »

Your query is incorrect
there are several examples here

http://www.techonthenet.com/sql/max.php
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

volka wrote:Why are you building an "AUTO_INCREMENT command" when mysql already has that feature?
There are a couple of issuses with you implementation.
i tried to, but it did not work. i have no idea why. it gave me a MySQL Error: 1075. i couldn't figure out what i was doing wrong. when i researched the issue, i found nothing wrong with the way i was implementing the command in my sql command line. so, i decided to do it using php. can you please shed some light on BOTH the php AND the sql command line problem?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

boo_lolly wrote:tried to, but it did not work. i have no idea why. it gave me a MySQL Error: 1075
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html wrote:Error: 1075 SQLSTATE: 42000 (ER_WRONG_AUTO_KEY)

Message: Incorrect table definition; there can be only one auto column and it must be defined as a key
What did your table definition look like?


Max() does not test for "is this the maximum value", it returns the maximum value.
There's no need to count the number of records.
You have to lock the table from the beginning up until after the INSERT or you might run into problems when there are concurrent requests.
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

thank you for the information. what does 'locking' the table mean? why is it important? and how would i do it?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You really shouldn't. Use the auto_increment attribute for the field.
Lockings means other requests have to wait until the object (table) is unlocked. You have 3 operations, 2xSELECT and 1xINSERT. This chain can be interrupted by other requests damaging the result, see http://www.google.de/search?&q=race%20condition
Post Reply