How do I select one row from a MySQL table?

Question

If I know the query only returns one row, and more specefically, in this case one value,

$full_name = database::query("SELECT concat (fname, ' ', lname) from cr WHERE email='$email'");

how do I get that value into a single variable. Database::query returns the value form mysql_query. The query above insert "Resource ID 4" in to mysql table.

Credentials(cr)

Tweets(tw)

What class is that you're using? You should almost certainly be using PDO to access a database - http://www.php.net/pdo — Adam Hopkinson, Jul 25 '11 at 21:31
@nick rulez - don't point Chris in the direction of less safe methods — Adam Hopkinson, Jul 25 '11 at 21:31
Sorry @nick, I didn't mean to be so harsh. PDO works on prepared statements, so protects the server from SQL injection attacks — Adam Hopkinson, Jul 25 '11 at 21:35
As an (important) aside, the code as written is very vulneralbe to SQL Injection. If you're not familiar with that topic, suggest you have a look at http://stackoverflow.com/questions/6786034/can-parameterized-statement-stop-all-sql-injection — Eric J., Jul 25 '11 at 21:40
@adam. You're right but I hope he does some kind of input validation before pass it to the query. — Nicola Cossu, Jul 25 '11 at 21:41
this is just a prototype I'm trying to get up and running, I'll add security from there. msyql_real_escape_string() is built in to my post class though. — , Jul 25 '11 at 21:46

score 4 · Accepted Answer · answered Jul 25 '11 at 21:31

4

I don't know about database::query(), but mysql_query() does indeed return a resource.

Once you have that resource, you must fetch your data from it, using, for example, mysql_fetch_array() :

$data = mysql_fetch_array($full_name);

Note that there are several mysql_fetch_* function, depending on what kind of result your want (array, object, ...) :

answered Jul 25 '11 at 21:31

Pascal MARTIN

395,085
80
655
663

An array, an object, and an associative array, what about a single value, i.e. the simplest case? I keep seeing the array example and I know I could use it but I keep thinking there is one just for a single value? – Jul 25 '11 at 21:35
@Chris In any case, you must fetch the row *(and a row generally contains several values -- one being the exception)* ; then, you can access each data *(one, in your case)* that's in that row ;;; if you want to see what's in `$data`, you can use `var_dump($data);` to get a dump of that variable's content. – Pascal MARTIN Jul 25 '11 at 21:37
So all "grabs" from the table are row based? – Jul 25 '11 at 21:44
Yep :-) *(actually, even if your select only returns one line of one column, that's still sent as... well, a row)* – Pascal MARTIN Jul 25 '11 at 21:48

score 2 · Answer 2 · answered Jul 25 '11 at 21:33

2

$query = mysql_query("SELECT concat (fname, ' ', lname) from cr WHERE email='$email'");
$row = mysql_fetch_row($query);
echo $row[0];

answered Jul 25 '11 at 21:33

Nicola Cossu

54,599
15
92
98

score 1 · Answer 3 · answered Jul 25 '11 at 21:32

I'm not sure what else your database class has to offer, but using built in php mysql function you could use:

$full_name = database::query("SELECT concat (fname, ' ', lname)
     from cr WHERE email='$email'");
$full_name = mysql_fetch_row($full_name);
$full_name = $full_name[0];

How do I select one row from a MySQL table?

3 Answers3