Fetching a single value from MySQL table

[MichaelHooker]

I've read a lot of guidance about using PHP and MySQL and so far I'm successfully retrieving all sorts of complicated joined stuff from my tables. But the established way of retrieving data which is in all the guidance seems way over the top when all you want to do is retrieve a single column from a single unique record - like using an ini file, or a name/value list in Delphi. Currently, to fetch a person's e-mail address when I know their name, I do this:

<?php>
    $query = "SELECT Email FROM Addresslist WHERE Name = '".$Searchstring."'";   
    $result = mysql_query ($query);
    $row = mysql_fetch_array($result);
    $address = $row[0];
    //$address is later used as a parameter to "mail()" as $row is used for another query before we get to that point
<?>

It works, but wouldn't it be neater if you didn't have to fetch an array of one item and then extract the value from the zero'th element?

Go on, tell me I've missed something simple, it wouldn't be the first time

Michael Hooker

 

[jpadie]

the way the optimisation works is that the mysql_fetch_* are faster than a direct access method.

but if you want, you can do it this way

$sql = "select email from AddressList where Name = '".mysql_real_escape_string(trim($Searchstring))."'";
$email = mysql_result(mysql_query($sql), 0, 0);

 

[MichaelHooker]

Thanks. I've been trying something like that, but without success - I shall try again.

I didn't bother with the mysql_real_escape_string(trim()) because the only way into this routine is by clicking a link - $Searchstring actually comes from another table, there's no opportunity for any user input.

Michael Hooker

 

[jpadie]

if the value comes from another table, have you considered whether a sub-select is a possibility?

 

[MichaelHooker]

if the value comes from another table, have you considered whether a sub-select is a possibility?

I might if I knew what that was Indeed I might have done without knowing I was doing it. I find the terminology for describing these things much harder than actually doing them.

Basically this is a contact form. I have 6,000 photos on my website, contributed by various photographers. The table of image details contains the jpg filename, which just happens to have the photographer's initials preceding the ".jpg", and when the image is displayed, the initials are looked up in the photographers table to display their name. If the name is clicked, this and the title of the image are passed to a new page which is the contact form. The person wishing to contact the photographer then completes his own name & email address, and a message, and is warned that his IP address will be sent to the photographer, to discourage abusive messages. When he clicks send, we move on to the send mail page, and at this point the name becomes $Searchstring. The code looks up the photographer's e-mail address in the photographer table, sends the message to him/her, confirms to the sender that this has been done and shows him exactly what IP address was included. At no point does the sender see the photographer's e-mail address, unless of course they choose to respond.

Yes, I'm sure I could extract the e-mail address from the photographer table at the same time as I extract the name and pass it on from page to page, and probably that would be safe enough, but I figure that it's simpler and probably less coding to just fetch it when needed. And possibly more secure: the e-mail address is only let out of the database for the millisecond between the send button being pressed, when it is fetched and when the $Searchstring and $row variables are cleared after the mail is sent. Yes, I know that sounds a bit daft but it will impress my photographers

Michael Hooker