MySQL auto_decrement & the lasr record

Anon

Hi,

Is there a possibility to use auto_decrement in MySQL?

& how can you print the last id if you use the "
id(3) int not null auto_increment
UNIQUE(id)
" ?!!

The problem is "I don't know which is the last id? How can you check which is the last id, so that you can use it as you wish, even in the reverse mode?

Thanks.

Mauro.

Anon

(1) There is no auto_decrement, only auto_increment.

(2) To retrieve the last value assigned by an auto_increment on your current connection, call mysql_insert_id().

Anon

is the value that mysql_insert_id() returns global or for the session only?

[deleted]

It's per database connection. (otherwise what would be the point? 🙂 )

Anon

🙂

Anon

Hi,

Thank you Vincent for the function. I saw that this is giving us a nice option as LAST_INSERT_ID() when you're using a AUTO_INCREMENT, but...

I'm affraid I've got a problem here. I always get a parse error or a non valid MySQL-Link resource. How should I use that function?

Usually we do things like this:

    $SQL = "SELECT * FROM test_table WHERE category = '$category' ";
$getid = mysql_db_query($db, $SQL, $cid);

# check for errors
if (!$getid) { echo( mysql_error()); }
else {
	# display results
	echo ("<P><DT><B>$category</B><BR>\n");
	while ($row = mysql_fetch_array($getid)) 
            {
		$row1 = $row["row1"];
		$row2 = $row["row2"];
                    $row3 = $row["row3"];
                    $id = $row["id"];
             print("$row1<br>$row2<br>$row3");     

            }
          }

With that LAST_INSERT_ID I've no idea how can you do that display of the last inserted id. Can you give me a hand please?

Thanks.

Mauro.

Anon

LAST_INSERT_ID (or the php function mysql_insert_id(), which returns the same information) is only useful after you do an INSERT of a new row into a table that has an auto-increment column defined.

If all you want to know is the highest value of a column at a particular moment in time, not to store it anywhere but just to display to the user, you can say SELECT MAX(col)...

Anon

Hi,

Thanks to you I succeded to make my first step on. More or less. There's a small problem in it. I tryed to do this (no errors and no warnings):

$SQL = " SELECT MAX(id) FROM links WHERE category = '$category'";

& that's displaying only the category name, & not the whole row with the id category row. ,... I tryed some other querries, but that was even worse. What's wrong here? I'm affraid that I just don't get it.

Thanks.

Mauro.

[deleted]

The easiest way to see how to do it is this:
run the query you posted. This will return the highest 'id' where category is $category.

Now you know which 'id' you want.
Now run another query to actually get the row where the id is the id you just got:

In untested code:
// Get the highest id
$sQuery = " SELECT MAX(id) AS id FROM links WHERE category = '$category'";
if (!$rResult = mysql_query($sQuery))
{
  // query failed, print the error
  //
  echo mysql_error();
}
else
{
  // query worked, get the highest id
  $aRow = mysql_fetch_row($rResult);
  $iID = $aRow['id'];
  mysql_free_result($rResult);

  // Now run a query to get the row that $iID points to
  $sQuery = "SELECT * FROM links WHERE id = $iID";
  if (!$rResult = mysql_query($sQuery))
  {
    // query failed, print the error
    //
    echo mysql_error();
  }
  else
  {
    // query worked, print the row
    $aRow = mysql_fetch_row($rResult);
    echo '<pre>';
    print_r($aRow);
    echo '</pre>';
    mysql_free_result($rResult);
  };
};

Anon

Thanks Vincent. I'll try to do that. Now I see what it might be e/o was a problem. I didn't say "AS id" that was missing. Thanks again.

Mauro