Really newbie question about databases.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Really newbie question about databases.

Post by lazersam »

Hi all

I have one really newbie question....

I have a database of names. Each row has an ID number, first_name etc. If I want to retrieve the first_name for ID #2 for example - what is the syntax?

I already know the ID (primary) and want to retrieve just 1 record. I dont want to start a loop or anything. I cant seem to figure it out.

Thanks

Lawrence.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

Code: Select all

//connect to DB (you've probably already done this, so i won't type that part)

$sql = "SELECT `first_name` FROM `table_name` WHERE `ID` = '2'";
$name = mysql_result(mysql_query($sql), 0,0);
echo $name;
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Post by lazersam »

Thank you very much - That did it.

:D

Regards

Lawrence.
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Post by lazersam »

Just 1 more thing :)

I actually want to retrieve more than one field, following your advice I am using the following syntax

їphp]

$sql = "SELECT `first_name` FROM `users` WHERE `ID` = '$sponsor'";
$sfname = mysql_result(mysql_query($sql), 0,0);
$sql = "SELECT `last_name` FROM `users` WHERE `ID` = '$sponsor'";
$ssname = mysql_result(mysql_query($sql), 0,0);
echo "$sfname "."$ssname";

ї/php]

I was just wondering if there was an easier way to request the data, maybe from using one mysql_query?

Regards

Lawrence.

?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You can combine those two queries to:

Code: Select all

SELECT first_name, last_name FROM users WHERE ID = '$sponsor'
You could then continue to use mysql_result() or instead use [php_man]mysql_fetch_assoc[/php_man]():

Code: Select all

$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
$row = mysql_fetch_assoc($result);

echo $row['first_name'].' '.$row['last_name'];
Mac
User avatar
lazersam
Forum Contributor
Posts: 105
Joined: Sat Nov 15, 2003 4:07 am
Location: Hertfordshire, UK

Post by lazersam »

Cool thanks alot :D
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Post by mrvanjohnson »

Or you can select everything for that record and just display the fields you want using this as your Select

Code: Select all

SELECT * FROM users WHERE ID = '$sponsor'
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

mrvanjohnson wrote:Or you can select everything for that record and just display the fields you want using this as your Select

Code: Select all

SELECT * FROM users WHERE ID = '$sponsor'
But this is not the preferred option for production code - much better IMO is to specifically name the fields you are trying to return as it makes it much easier to deal with changes to the database. Plus if you have 15 columns and only want to use data from 5 of them you it's a waste to return everything.

Mac
Post Reply