Puling largest from mySQL

[Gruessle]

I have a table which is supposed to look like following.
When I make a new entry I know what the reg_id is but what the highest progr_id is I don't know.
Just before inseting the data in to the DB or better at the same time I need to get the largest / next available progr_id
I believe my insert needs to look like this but how do I get the progr_id from the db without having a delay?

INSERT INTO table (reg_id, progr_id,Something)
VALUES (%s, $progr_id, %s)",
GetSQLValueString($HTTP_POST_VARS['reg_id'], "int"),
GetSQLValueString($HTTP_POST_VARS['Status'], "text"));

-------------------------------
id ¦ reg_id ¦ progr_id ¦ Something
-------------------------------
1 ¦ 3 ¦ 1 ¦ Something
------------------
2 ¦ 1 ¦ 1 ¦ Something
------------------
3 ¦ 3 ¦ 2 ¦ Something
-----------------
4 ¦ 2 ¦ 1 ¦ Something
------------------
5 ¦ 3 ¦ 1 ¦ Something
-----------------
6 ¦ 1 ¦ 2 ¦ Something
------------------
7 ¦ 3 ¦ 3 ¦ Something

[jatar_k]

before you do your insert you could do a

select max(progr_id) as topid from table

[Gruessle]

Thats it excelent, Thank you

[Gruessle]

Finaly hat time to try this but well it's not working.
Here is my best shoot at this:

if (isset($_POST['submit'])){
$db = mysql_connect(DB_HOST, DB_USER, DB_PASS);
$value = mysql_query("SELECT MAX (progr_id) AS maxid FROM type_doc");
echo "Value: $value <br> Max ID: $maxid <br> progr_id: $progr_id <br> ";
$values = array ("Documentation"=>$_POST['doc'], "progr_id"=>$maxid);
$error = InsertQuery ("type_doc", $values);
}

[coopster]

You setup your query statement, executed it, but you still need to fetch the row.

if (isset($_POST['submit'])) { 
 $db = mysql_connect(DB_HOST, DB_USER, DB_PASS); 
 $rows = mysql_query("SELECT MAX (progr_id) AS maxid FROM type_doc"); 
 $row = mysql_fetch_array [php.net]($rows); 
 $maxid = $row['maxid']; 
 echo "Max ID: $maxid <br>"; 
 $values = array ("Documentation"=>$_POST['doc'], "progr_id"=>$maxid); 
 $error = InsertQuery ("type_doc", $values); 
}

Take a close look here at how the statement is first created (you got that part correct). Next, time to execute it with the mysql_query. But then we need to assign the result set returned to a result set identifier that we use to fetch row(s) from. Each row is returned to an array which contains the columns of the table for that particular row returned. The link to the PHP docs can explain a bit more.

[Gruessle]

I had that in there at one point but it gives me an error so I took it out. ;)

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/lenders/index.php on line 13

Code:
if (isset($_POST['submit'])) {
$db = mysql_connect(DB_HOST, DB_USER, DB_PASS);
$rows = mysql_query("SELECT MAX (progr_id) AS maxid FROM type_doc");
$row = mysql_fetch_array($rows); // LINE 13 IS RHT HERE
$maxid = $row['maxid'];
$maxid++;
echo "Max ID: $maxid <br>";
$values = array ("Documentation"=>$_POST['doc'], "progr_id"=>$maxid);
$error = InsertQuery ("type_doc", $values);
}

[coopster]

Something else then. Have you checked for the usual suspects?

1. Make sure you are indeed connecting to your database.
2. Make sure the query statement is formatted correctly and being executed without error.
3. Make sure there is at least one row being returned in the result set before trying to fetch any data.

[Gruessle]

Hmm how do I do that?

I tried this:

if (!empty($error)){
echo "Error: $error<br>\n";
}
if (isset($_POST['submit'])) {
$db = mysql_connect(DB_HOST, DB_USER, DB_PASS);
$rows = mysql_query("SELECT MAX (progr_id) AS maxid FROM type_doc");
$row = mysql_fetch_array($rows);
$maxid = $row['maxid'];
$maxid++;
echo "Max ID: $maxid <br>";
$values = array ("Documentation"=>$_POST['doc'], "progr_id"=>$maxid);
$error = InsertQuery ("type_doc", $values);
echo "db: $db <br>"; echo "rows: $rows <br>"; echo "row: $row <br>"; echo "maxid: $maxid <br>"; echo "values: $maxid <br>"; echo "error: $error <br>";

}

And GOT this:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/mtg/index.php on line 13
Max ID: 1
db: Resource id #5
rows:
row:
maxid: 1
values: 1
error:

==============

The maxid is one because of $maxid++;

[Gruessle]

Sorry if I fixed the echo for values and then got this:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/mtg/index.php on line 13
Max ID: 1
db: Resource id #5
rows:
row:
maxid: 1
values: Array
error:

[Gruessle]

Well it sure took me a long time to figure this one out.
In case someone likes to know what the problem was:

This does not work
MAX (foo)

This will
MAX(foo)

SPACE!