Jump to content

mySQL MAX function in php..


a1amattyj

Recommended Posts

Hi,

 

I have a database with 8 records. All these records have an ID which is auto increment. So obviously, the highest ID number is 8. Im wanting to display this in my panel, but i get the following result:

 

Resource id #5

 

With code:

<?php 
include("header.php"); 
include("config.php"); 
?>

<?
// Make a MySQL Connection

$query = "SELECT MAX(id) FROM reports"; 

$result = mysql_query($query) or die(mysql_error());

echo "$result";
?>

 

Thanks again.

Link to comment
https://forums.phpfreaks.com/topic/90705-mysql-max-function-in-php/
Share on other sites

The $result is an array. You need to pick out the specific fields that you want. What is your db structure?

 

CREATE TABLE `reports` (
  `id` int(11) NOT NULL auto_increment,
  `forumname` varchar(30) default NULL,
  `name` varchar(20) default NULL,
  `email` varchar(50) default NULL,
  `type` varchar(20) default NULL,
  `ip` varchar(100) default NULL,
  `date` varchar(100) default NULL,
  `status` varchar(20) default 'Pending',
  `comments` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

CREATE TABLE `reports` (
  `id` int(11) NOT NULL auto_increment,
  `forumname` varchar(30) default NULL,
  `name` varchar(20) default NULL,
  `email` varchar(50) default NULL,
  `type` varchar(20) default NULL,
  `ip` varchar(100) default NULL,
  `date` varchar(100) default NULL,
  `status` varchar(20) default 'Pending',
  `comments` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

You need to move through the array and grab out the data that you want.

 

For example:

<?php
$query = "SELECT MAX(id) FROM reports";
$result = mysql_query($query) OR DIE ("Error.");
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_assoc($result);
return $row["id"];
} else {
// do something
}
?>

 

The mysql_fetch assoc() is used when you know that there is only 1 row returned. In your case, it's 1 row with one entry in the array. If you had multiple rows returned, you would use a while loop.

 

Example:

<?php
$query = "SELECT * FROM reports'";
$result = mysql_query($query) OR DIE ("Error.");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {
	$id = $row["id"];
	$forumname = $row["forumname"];
	$email = $row["email"];
	// do something with that data here
}
}
?>

 

Make sense?

 

The mysql_fetch assoc() is used when you know that there is only 1 row returned. In your case, it's 1 row with one entry in the array. If you had multiple rows returned, you would use a while loop.

 

Example:

<?php
$query = "SELECT * FROM reports'";
$result = mysql_query($query) OR DIE ("Error.");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)) {
	$id = $row["id"];
	$forumname = $row["forumname"];
	$email = $row["email"];
	// do something with that data here
}
}
?>

 

 

Hi,

 

Thanks for the explanations. However, this database has multiple rows, all with a different ID number. From being 1,2,3,4,5,6,7,8 which will rise to 9 when a new row is made. So how do incorporate the max function into the above structure you created?

I used the MAX function in the first example (the one that answered your question). It returns a resource that contains 1 row since you asked for only the MAX id. Does the first example not answer. If not, I may not fully understand what you're trying to do ;)

 

The second example was when multiple rows are returned.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.