Limit number of records in table

Numskull

Hi all
I was wondering if there is a way to limit the maximum numbers of rows in a table? I have a table where there should be no more than 4 rows.

One more thing. How do you select 3rd field in row 4? Say that the collum name for the 3rd field is bread and the 3rd field contains the type of bread.

Thanks in advance.

tomhath

"I was wondering if there is a way to limit the maximum numbers of rows in a table? I have a table where there should be no more than 4 rows."

Rows don't appear by magic. If you only want 4 rows, only insert 4 rows.

"One more thing. How do you select 3rd field in row 4? Say that the collum name for the 3rd field is bread and the 3rd field contains the type of bread. "

Depends on the database (MySQL, Postgesql, mssql, ???). Whichever one you are using has PHP function calls that are well documented in the manual.

You did read the manual, didn't you?

Numskull

Nope, I havnt read the PHP manual. I got a book about PHP/mySQL, but the book is not totally clear about the questions I have posted.

tomhath

The only way I know of to limit the number of rows in a table is to enforce it in your code. There might be a way to limit it through the database, but you would still need code to handle the error if an attempt is made to insert another row.

As far as the second question, just select the row and column you want.

SELECT col3 FROM mytable WHERE row=4

Or select the entire row and access the column in php:

http://us2.php.net/mysql_fetch_array

Numskull

Ok, thanks a lot. I am quite new to mySQL.

Roger_Ramjet

Depending upon the db, you can limit the number of rows in a table. In Access one would impose a validation rule where eg only a,b,c,d were allowed in a column, and then create a unique index for that column. Once rows with a,b,c,d existed, no other rows could be inserted because that would create duplicate keys.

You can do the same thing in MySQL with InnoDB tables and foriegn key constraints . Here one creates the unique index a,b,c,d in one table and use it as a foreign key in the main table. Again, once a,b,c,d have been used no further rows can be added.

Numskull

Thank you. What I am trying to do is, to prevent a user from accidently adding a row to a sertain table. I will have a look at your suggestions.

robert_w_ark


$query = mysql_query("your_query LIMIT 4");
while($row = mysql_fetch_array($query))
{
/* Your stuff here for each item in table */
}