MySQL data as mailto???

timgray

Hi,

This is my first time here. I am a newcomer to PHP, and have run into a brick wall. Here is my issue:

I have a MySQL database with a field called "Email". There are 25 records in the database.

I'd like to be able to write the PHP to take the data in the "Email" field and display it as a hyperlink, so when you visit the page and click on an email, you can send an email to that person using your default email client.

Anyone know how to do this?

Here is what I have so far....

<HTML>
<HEAD>
<TITLE>Contacts</TITLE>
</HEAD>
<BODY>

<?php

// Set the variables for the database access:

$Host = "www.yourdomain.com";
$User = "username";
$Password = "password";
$DBName = "database";
$TableName = "table";
$Link = mysql_connect ($Host, $User, $Password);
$Query = "SELECT * from $TableName";
$Result = mysql_db_query ($DBName, $Query, $Link);

// Create a table.

print ("<TABLE BORDER=1 WIDTH=\"35%\" CELLSPACING=2 CELLPADDING=2 ALIGN=CENTER>\n");
print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>ID</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Name</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Email</TD>\n");
print ("</TR>\n");

// Fetch the results from the database.

while ($Row = mysql_fetch_array ($Result)) {

print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[ID]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row[Name]</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>$Row mailto:[Email]</TD>\n");
print ("</TR>\n");

}

mysql_close ($Link);

print ("</TABLE>\n");

</BODY>
</HTML>

Any help you could offer would be great!

Thanks!

Tim

DoD

Replace:

with:

$ID=$Row["ID"];
$Name=$Row["Name"];
$Email=$Row["Email"];

echo "<TR ALIGN='CENTER' VALIGN='TOP'>
<TD ALIGN='CENTER' VALIGN='TOP'>$ID</TD>
<TD ALIGN='CENTER' VALIGN='TOP'>$Name</TD>
<TD ALIGN='CENTER' VALIGN='TOP'><a href='mailto:$Email'>$Email</a></TD>
</TR>";

The \n isnt nessecary. (i think)

Otherwise, should be fine

timgray

Hey, I just tried that out and it worked MUCH better! Thanks for helping me get past this obstacle.

Tim

DoD

No problems 🙂

Lol, im having loads of problems with such simple stuff... grrr!

timgray

I hate to bug you again (sorry!) but I used to know how to do this in ASP....

let's say you have your results, but not all the fields were filled in. Is there a way to populate an empty field with a placeholder?

Just wondering.

Thanks!

Tim

DoD

Place holder?

Like an N/A?

I think this is what you mean

if (!$ID) {$ID="N/A";}
if (!$Name) {$Name="N/A";}
if (!$Email) {$Email="N/A";}

Heres a tip:

Change

<a href='mailto:$Email'>$Email</a>

to just $Email

And

$Email=$Row["Email"];

$Email=$Row["Email"];
if ($Email) {$Email="<a href='mailto:$Email'>$Email</a>";} else {$Email="N/A";}

timgray

Yeah, just like an N/A, or even "Not available".

I tried this:

if (!$ID) {$ID="N/A";}
if (!$Name) {$Name="N/A";}
if (!$Location) {$Location="N/A";}

if (!$Email) {$Email="N/A";}

if ($Email) {$Email="<a href='mailto:$Email'>$Email</a>";
} else {
$Email="N/A";}

It works great with the text fields, but the Email one remains a link--it won't send you anywhere, but it does open up a new mail window.

This is the code as it stands:

<HTML>
<HEAD>
<TITLE>Retrieving Data from a Database</TITLE>
</HEAD>
<BODY>

<?php

// Set the variables for the database access:

$Host = "x";
$User = "x";
$Password = "x";
$DBName = "x";
$TableName = "x";
$Link = mysql_connect ($Host, $User, $Password);
$Query = "SELECT * from $TableName";
$Result = mysql_db_query ($DBName, $Query, $Link);

// Create a table.

echo ("<TABLE BORDER=1 WIDTH=\"50%\" CELLSPACING=2 CELLPADDING=2 ALIGN=CENTER>\n");
echo ("<TR ALIGN=CENTER VALIGN=TOP>\n");
echo ("<TD ALIGN=CENTER VALIGN=TOP>ID</TD>\n");
echo ("<TD ALIGN=CENTER VALIGN=TOP>Name</TD>\n");
echo ("<TD ALIGN=CENTER VALIGN=TOP>Location</TD>\n");
echo ("<TD ALIGN=CENTER VALIGN=TOP>Email</TD>\n");
echo ("</TR>\n");

// Fetch the results from the database.
while ($Row = mysql_fetch_array ($Result)) {

$ID=$Row["ID"];
$Name=$Row["Name"];
$Location=$Row["Location"];
$Email=$Row["Email"];

if (!$ID) {$ID="N/A";}
if (!$Name) {$Name="N/A";}
if (!$Location) {$Location="N/A";}

if (!$Email) {$Email="N/A";}

if ($Email) {$Email="<a href='mailto:$Email'>$Email</a>";
} else {
$Email="N/A";}

echo ("<TR ALIGN='CENTER' VALIGN='TOP'>");
echo ("<TD ALIGN='CENTER' VALIGN='TOP'>$ID</TD> ");
echo ("<TD ALIGN='CENTER' VALIGN='TOP'>$Name</TD>");
echo ("<TD ALIGN='CENTER' VALIGN='TOP'>$Location</TD>");
echo ("<TD ALIGN='CENTER' VALIGN='TOP'>$Email</TD>");
echo ("</TR>");

}

mysql_close ($Link);

echo ("</TABLE>\n");

</BODY>
</HTML>

DoD

sorry, i was being dopey... remove this line

if (!$Email) {$Email="N/A";}

timgray

Thanks again! I had previously tried removing only PART of the if statements, but when I started getting parse errors I found myself getting frustrated.

Thanks for your help. Hopefully I will be able to offer advice/suggestions in time.

TMG

blata

hello Thank you for the info as I am having the same problem. I have "barowed" your code as it is much nicer then mine was.

Hear is my code:

<?php
//set variables for db access.
$host = "localhost";
$user = "crease";
$password = "xxxxxx";
$dbname = "goalie";
$tableName = "goalies";
$link = mysql_connect ($host, $user, $password);
$query = "SELECT * from $tablename";
$result = mysql_db_query ($dbname, $query, $link);

//Create a table
print ("<table border=1 Width=\"100%\" align=center>\n");
print ("<TR ALIGN=CENTER VALIGN=TOP>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>First Name</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Last Name</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Team</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>League</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>e-mail address</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Work number</TD>\n");
print ("<TD ALIGN=CENTER VALIGN=TOP>Home number</TD>\n");
print ("</TR>\n");

// Fetch the results from the database.
line 48 --->while ($row = mysql_fetch_array ($result)){

$first=$row["first"];
$last=$row["last"];
$team=$row["team"];
$team=$row["leauge"];
$email=$row["email"];
$work=$row["work"];
$phone=$row["phone"];

echo"<TR ALIGN=CENTER VALIGN='TOP'>
<TD ALIGN=CENTER VALIGN='TOP'>$first</TD>
<TD ALIGN=CENTER VALIGN='TOP'>$last</TD>
<TD ALIGN=CENTER VALIGN='TOP'>$leauge</TD>
<TD ALIGN=CENTER VALIGN='TOP'><a href='mailto:$email'>$email</a></TD>
<TD ALIGN=CENTER VALIGN='TOP'>$work</TD>
<TD ALIGN=CENTER VALIGN='TOP'>$phone</TD>
</TR>";
}
mysql_close ($link);
print("</table>\n");
?>

but I'm getting this error:
Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/goalie/test.php on line 48

I would apriciate any help.

cgraz

Change this part of your code

$link = mysql_connect ($host, $user, $password); 
$query = "SELECT * from $tablename"; 
$result = mysql_db_query ($dbname, $query, $link);

mysql_connect ($host, $user, $password) or die(mysql_error()); 
mysql_select_db($dbname) or die(mysql_error());
$query = "SELECT * from $tablename"; 
print $query . "<br>";
$result = mysql_query ($query) or die(mysql_error());

And tell us what that prints.

Cgraz

blata

Ok I replaced the code and now I get a

Warning: Supplied argument is not a valid MySQL-Link resource in /var/www/html/goalie/test.php on line 36

Warning: Supplied argument is not a valid MySQL result resource in /var/www/html/goalie/test.php on line 51

Warning: Supplied argument is not a valid MySQL-Link resource in /var/www/html/goalie/test.php on line 70

line 36
$result = mysql_db_query ($dbname, $query, $link);

line 51
while ($row = mysql_fetch_array ($result)){

line 70
mysql_close ($link);

cgraz

line 26 should be

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

and you don't need line 70 mysql_close($link)

Cgraz

blata

Hot damn Than k you vary much. I see how it works now.