Get the biggest primary id in a table

[abch624]

Hi Guys,

 

I have a table with 10 fields and the productid is the primary key. If the table has 10 entries and all the productid's vary (i.e. 1, 4, 7, 9, 14, 15, 19, 20, 34, 55). I want a sql query that returns the number 55 as its the highest. I can use the mysql_num_rows(); but that only gives me the number of rows (i.e. 10).

 

Help guys...

 

 

[attachment deleted by admin]

[wildteen88]

Use the ORDER BY clause

 

SELECT * FROM your_table ORDER BY productid DESC LIMIT 1

[KPH71]

SELECT MAX(productid) AS MAXID from tablename

 

Then when you fetcharray this query the field will be "MAXID".

 

e.g.

 

$query = mysql_query("SELECT MAX(productid) AS MAXID from tablename");
$result = mysql_fetch_array($query);
echo $result['MAXID'];

[Mchl]

Why do you need it at all? In most cases you just shouldn't care about what's the highest primary key. If you do, you're likely doing something weird..

 

SELECT MAX(primaryID) FROM table

[abch624]

The reason is that I am trying to build a simple PHP image gallery and a user can upload images to view and hence I need to get the biggest number.

 

Cheers Guys

[Mchl]

I still do not understand why you need it.

[abch624]

$imageid = $_GET['imageid'];
if ($_GET['imageid'] == "") {
	$imageid = 1;
}
if ($_SESSION['loginflag'] == true) 
{
	$sql = "SELECT MAX(productid) AS MAXID from product";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$maxid = $row['MAXID'];

	$sql_products = "SELECT * FROM product WHERE productid = '$imageid'";
	$result_products = mysql_query($sql_products);
	$row_products = mysql_fetch_array($result_products);
	$image = $row_products['picture'];
	$imgdir = $_SERVER['DOCUMENT_ROOT']."/EComm/images/user";
	$imagenext = $imageid + 1;
	if ($imagenext == $maxid) {
		echo '<a href="catalogue.php?imageid=1">next</a>';
		break;
	}
	$sql_next = "SELECT * FROM product WHERE productid = '$imagenext'";
	$result_next = mysql_query($sql_next);
	$count_next=mysql_num_rows($result_next);

		While ($count_next == 0) {
			$imagenext = $imagenext + 1;
			$sql_imagenext = "SELECT * FROM product WHERE productid='$imagenext'";
			$result_imagenext = mysql_query($sql_imagenext);
			$count_next=mysql_num_rows($result_imagenext);
		}
		echo '<a href="catalogue.php?imageid='.$imagenext.'">next</a>';

	echo '<img src="images/shop/'.$image.'" alt="home" border="0" />';

}

I have attached the code that I am trying to build. I have the image displayed and a next link that takes me to the next image in the database. Now if I have reached the last image in the database then I would like the next to point to imageid 1...

 

Please do advise if you see ways of improving this.

 

Cheers

[Mchl]

Ok I get it now...

 

You're right. For such task it might be actually the best way. As long as your gallery stays simple, it should work.

[KPH71]

There is potential for SQL injections in this.

 

I have copied the relevant sections of code here:

 

$imageid = $_GET['imageid'];
   if ($_GET['imageid'] == "") {
      $imageid = 1;
   }
$sql_products = "SELECT * FROM product WHERE productid = '$imageid'";

 

What if they typed something like this into the $_GET['imageid']:

 

hi' or 1=1 '

 

It would get all the rows! Also they could try to add in other more annoying things in, so best to protect against it.

 

Two things you can do:

 

$imageid = addslashes($_GET['imageid']);
   if ($_GET['imageid'] == "" || !is_int(intval($_GET['imageid']))) {
      $imageid = 1;
   }

 

I've added the addslashes function to protect from injections. Also I've added the is_int into the validation. This will make sure that it is only an integer as well. Technically you don't need both but I like to be thorough!

[kenrbnsn]

You should use the function mysql_real_escape_string, not addslashes.

 

Ken

[KPH71]

You should use the function mysql_real_escape_string, not addslashes.

 

Ken

 

For the purposes of this I don't think it matters too much - there's always a debate as to which one to use. I do use real escape myself - but with this everything is covered by the is_int and intval I believe.

 

For abch624 you might as well use mysql_real_escape_string() but I would still use the is_int and intval as well.

[MadTechie]

Its an Int!!

 

$imageid = $_GET['imageid'];
   if ($_GET['imageid'] == "") {
      $imageid = 1;
   }
$sql_products = "SELECT * FROM product WHERE productid = '$imageid'";

to

$imageid = (int)(!empty($_GET['imageid']))?$_GET['imageid']:1; //default to 1 and forces to int
$sql_products = sprintf("SELECT * FROM product WHERE productid = %d ",$imageid);