How do I dislay only one field from table?

[flynismo]

Hi everyone,

I am having a lot of trouble with this...

I am using PHP/MySQL. What I have is a dating website. One of the tables in the database is called "users" which has many fields in it, storing the usual info (id, name, email, etc).

What I want to do is this:

When you view a member's profile, I want to display the value of the field called "compat" from the users table. The value of compat is unique for each member (well, not unique, but it varies greatly), so what I need is to make sure that "compat" is being displayed for the correct member.

I tried following some tutorials, but each one ends up displaying every single record contained in the "compat" field...I dont want that, I only want to display the user specific value.

If it helps, the member id is the unique key on the table...so I know that when I perform this query, I should get the profile's id and compat values to ensure the correct value appears, I just cant figure out how to code this.

Please help!

Thank you.

[michelleradu]

The way I see this, you only need a SELECT query with a WHERE clause for a specific user:

//$id  - is the ID of the current user
$query = mysql_query("SELECT compat FROM users WHERE memberID='$id'");
$row = mysql_fetch_row($query);
$compat = $row['compat'];

That should do the job.

[flynismo]

If it turns out to be that simple, I am going to jump out of my window, lol...
I'm going to give it a go, thank you for the help!

[Rhyan]

Depending on php version and configuration mysql_fetch_row may return either a numerically or alphabetically indexed array, so $row may not return a value, as it may not find the array key requested.

If it happens - you have 2 options - either use mysql_fetch_assoc() wich returns an associative array (column names are array keys), or continue with fetch_row, but use $row[0] to access the first(and in your case the only) value in the array.

[flynismo]

Oky, still having trouble.

I tried the first response,but got no output...so I'm trying the next suggestion.
Maybe my code isn't right?

$query = mysql_query( ' SELECT compat FROM keystone_user WHERE id = $userid ',  array( USER_TABLE, $_GET['id'], 'compat' ));
  
      $row = mysql_fetch_row($query);
   	  
      $compat = $row['compat'];
	  $t->assign('compat',"$compat");
	  echo $compat;

The script also uses smarty, and USER_TABLE in the above code is predefined as the user table I am accessing.

I tried to simply echo $compat, as well as assign it in smarty and call it with {$compat}, but either way, no dice :(

[flynismo]

Here's an example of the script directly above the one I am entering; it is for searching for a user by his/her name, and this script works properly:

if( isset($_GET['username']) && $_GET['username'] != '') {
	$userid = $osDB->getOne( 'SELECT id FROM ! WHERE username = ? AND status = ? ', array( USER_TABLE, $_GET['username'], 'active' ) );

     $_REQUEST['id'] = $userid;
	if (!$userid || $userid =='') {
		/* No such user. Display error message and exit */
		$t->assign('error_message', get_lang('no_such_user'));
		$t->assign('errid',"999");
		$t->assign('rendered_page', $t->fetch('nickpage.tpl'));
		$t->display('index.tpl');
		exit;
	}
}

[michelleradu]

Instead of

$query = mysql_query( ' SELECT compat FROM keystone_user WHERE id = $userid ',  array( USER_TABLE, $_GET['id'], 'compat' ));

try

$query = mysql_query( " SELECT compat FROM keystone_user WHERE id = '$userid' ",  array( USER_TABLE, $_GET['id'], 'compat' ));

[flynismo]

Instead of

$query = mysql_query( ' SELECT compat FROM keystone_user WHERE id = $userid ',  array( USER_TABLE, $_GET['id'], 'compat' ));

try

$query = mysql_query( " SELECT compat FROM keystone_user WHERE id = '$userid' ",  array( USER_TABLE, $_GET['id'], 'compat' ));

No luck...

I'm starting all over from scratch..here's what I have so far.

Just to make sure I'm connected to the database and the basic queries are correct, I ran this...

$query = mysql_query("SELECT COUNT(*) AS `Rows`, `compat` FROM `keystone_user` GROUP BY `username` ORDER BY `id` LIMIT 0, 30 ");
  
      $row = mysql_fetch_assoc($query);
   	  
      $compat = $row['compat'];

	  $t->assign('compat',"$compat");

and call it from the .tpl file with {$compat}

That got me a step closer to my goal....the result of the above query fetched the value of compat from the very first row, and that's all.
Not exactly what I want, but at least now I'm getting some kind of output.

Could you advise me from here on what to tweak next? Everything I've tried after that is failing miserably.... :(

PS-

When I use mysql_fetch_row(), I just get a blank field, but when I use mysql_fetch_assoc(), I get the output...hope that helps!

[rajarajan2017]

Check your directly with your phpadmin sql editor and confirm whether the query returning rows.

[Rhyan]

Your mysql_query string is wrong. Mysql_query expects (querry string, link identifier), and in your query, you have an array, which is not a valid resource id.
http://bg.php.net/manual/en/function.mysql-query.php

Instead use this code and try again.

$query = mysql_query("SELECT compat FROM keystone_user WHERE id = '$userid'");
$result=mysql_fetch_assoc($query);

$compat = $result['compat'];
echo $compat;

Try and advise.

[flynismo]

Your mysql_query string is wrong. Mysql_query expects (querry string, link identifier), and in your query, you have an array, which is not a valid resource id.
http://bg.php.net/manual/en/function.mysql-query.php

Instead use this code and try again.

$query = mysql_query("SELECT compat FROM keystone_user WHERE id = '$userid'");
$result=mysql_fetch_assoc($query);

$compat = $result['compat'];
echo $compat;

Try and advise.

Believe it or not, that was one of the first things I tried, and to my surprise it didn't work...I thought for sure it would!

But after a LOT of frustration, I finally figured this out.

I had to change the syntax a little, and use $_GET to fix the issue with the userid...

Here's the complete code below:

$query = mysql_query( 'SELECT `compat` FROM `keystone_user` where id='."'".$_GET['id']."'");
$result=mysql_fetch_assoc($query);
$compat = $result['compat'];
echo $compat;

$t->assign('compat',"$compat");

Thanks everyone for your help and patience!!!

[Rhyan]

My second guess would be that the quotes did not work as expected.

However, although it works, your current code is vulnerable to sql injection. DO NOT EVER USE GET OR POST VARIABLES DIRECTLY IN DB REQUEST CODES!!!!

Instead, apply some code cleaning on the $_GET value, for example add slashes, encode special characters, trim empty spaces, even replacement of restricted characters in order to avoid vulnerability!

example

if (isset($_GET['id']) && $_GET['id'] != NULL)
  {
  $id = htmlspecialchars(trim($_GET['id']));
  }
else
  {
  print ("No value supplied as argument!");
  }

Glad it works. Good luck.

[flynismo]

My second guess would be that the quotes did not work as expected.

However, although it works, your current code is vulnerable to sql injection. DO NOT EVER USE GET OR POST VARIABLES DIRECTLY IN DB REQUEST CODES!!!!

Instead, apply some code cleaning on the $_GET value, for example add slashes, encode special characters, trim empty spaces, even replacement of restricted characters in order to avoid vulnerability!

example

if (isset($_GET['id']) && $_GET['id'] != NULL)
  {
  $id = htmlspecialchars(trim($_GET['id']));
  }
else
  {
  print ("No value supplied as argument!");
  }

Glad it works. Good luck.

Great advice, I will change the code accordingly.