Member Avatar for Bob Arctor

Hello,
I have this snippet of code to echo out the "mailer_id" with the largest value -

<?php 
$result = mysql_query('SELECT MAX(mailer_id) FROM mailer_directory')or exit(mysql_error()); 
echo 'the max mailer_id is ' . mysql_result($result, 0); ?>

My question is how do I echo out the corresponding email value for that field ?

  • 3 Contributors
  • 6 Replies
  • 115 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by Bob Arctor

Dani AI

Generated

Short answer: fetch the single row that holds the highest primary key and read its email column — no loop needed. As showed, return just one row and pull the email field. The attempt by to use an aggregate directly in a WHERE clause is invalid SQL; aggregate functions either belong in a subquery or in an aggregate context (HAVING, GROUP BY).

Use a modern DB API (PDO or mysqli) instead of the old mysql_* calls, validate the returned value, and escape it for HTML. Example using PDO and a single-statement subquery to get the email for the current maximum mailer_id:

$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$stmt = $pdo->query('SELECT email FROM mailer_directory WHERE mailer_id = (SELECT MAX(mailer_id) FROM mailer_directory)');
$email = $stmt->fetchColumn();

if ($email === false) {
    echo 'No records found';
} else {
    $email = trim($email);
    if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
        echo htmlspecialchars($email, ENT_QUOTES, 'UTF-8');
    } else {
        echo 'Stored value is not a valid email';
    }
}

Notes and troubleshooting:

  • Use UTF-8 on connection (see PDO charset option) so characters display correctly.
  • Check for an empty result to avoid notices.
  • filter_var confirms it's a valid email; htmlspecialchars prevents XSS when echoing to the browser.
  • Your mailer_id is a primary key, so indexed lookups are fast. If multiple writes happen, run this as a single query (above) to avoid a race between separate SELECTs.
  • If upgrading legacy code, convert mysql_* calls to PDO or mysqli — mysql_* was removed in recent PHP versions.

Further reference: PHP PDO manual.

Recommended Answers

All 6 Replies

Member Avatar for pritaeas 
<?php 
  $result = mysql_query('SELECT MAX(mailer_id) FROM mailer_directory')or exit(mysql_error()); 
  $row = mysql_fetch_array($result);
  echo 'the max mailer_id is ' . $row[0]; 
?>
Member Avatar for Bob Arctor

Thanks for the input.
I should have been more concise.
Here is the SQL table structure.

CREATE TABLE `mailer_directory` (
  `mailer_id` int(10) NOT NULL auto_increment,
  `name` varchar(24) NOT NULL default '0',
  `email` varchar(44) NOT NULL default '',
  `subject` varchar(24) NOT NULL default '',
  `message` varchar(200) NOT NULL default '',
  `submitted` date NOT NULL default '0000-00-00',
  `ip` varchar(16) NOT NULL default '0',
  `submission_id` varchar(36) NOT NULL default '0',
  `ticket_id` bigint(30) NOT NULL default '0',
  PRIMARY KEY  (`mailer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=500 ;

Essentially I want to echo out to the browser the corresponding email for SELECT MAX(mailer_id)

Member Avatar for pritaeas 
<?php
  $result = mysql_query('SELECT MAX(mailer_id),email FROM mailer_directory')or exit(mysql_error()); 
  $row = mysql_fetch_assoc($result);
  echo 'the max mailer_id is ' . $row['email']; 
?>

or:

<?php
  $result = mysql_query('SELECT email FROM mailer_directory ORDER BY mailer_id DESC LIMIT 1')or exit(mysql_error()); 
  $row = mysql_fetch_assoc($result);
  echo 'the max mailer_id is ' . $row['email']; 
?>
Member Avatar for ahmksssv 
<?php
  $result = mysql_query('SELECT MAX(mailer_id),email FROM mailer_directory')or exit(mysql_error()); 
  $row = mysql_fetch_assoc($result);
  echo 'the max mailer_id is ' . $row['email']; 
?>

or:

<?php
  $result = mysql_query('SELECT email FROM mailer_directory ORDER BY mailer_id DESC LIMIT 1')or exit(mysql_error()); 
  $row = mysql_fetch_assoc($result);
  echo 'the max mailer_id is ' . $row['email']; 
?>

Hi... try to do this....

$query="SELECT mailer_id,email FROM mailer_directory where mailer_id=MAX(mailer_id)"
$result = mysql_query('')or exit(mysql_error());
Member Avatar for Bob Arctor

Pritaeas,
Thank you - appreciated.
That does the job.
Pretty much followed your first example.
I was on the same line - just using an inappropriate 'WHERE' and using a loop.
I got a bit lost on your second example.
I'll work on that.
Cheers.

Member Avatar for Bob Arctor

Hi... try to do this....

$query="SELECT mailer_id,email FROM mailer_directory where mailer_id=MAX(mailer_id)"
$result = mysql_query('')or exit(mysql_error());

That looks promising.
I'll have a look at that.
Thank you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.