• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

MySQL - getting a value from xf_user_field_value

#1
Hi!

I'm trying to retrieve a value from the said table but when it comes to MySQL, I'm pretty clueless. The table has all the custom field values, I'm trying to get user ID based on a custom field (customernumber). So right now it's looking like this:

Code:
$result_id = $db->fetchRow("SELECT COUNT(*) AS fetchid FROM xf_user_field_value WHERE field_value=" . $db->quote($customernumber) . " AND field_id='customernumber'");

$fetched_id = $result_id[1];
$customernumber is defined before the query.
 
#2
Your SQL only counts the number of matches.

Instead of:

SELECT COUNT(*)

you need:

SELECT user_id

Secondly, the user_id would be stored in the array at position 0 (not 1), so:

$result_id[0]

The other problem is that you need to query the database and then fetch the row, so it should be something like this:
Code:
$sql = "SELECT user_id AS fetchid FROM xf_user_field_value WHERE field_value=" . $db->quote($customernumber) . " AND field_id='customernumber'";
$query = $db->query($sql);
$result_id = $db->fetchRow($query);

$fetched_id = $result_id[0];