How do I store a value from a sql query into a variable?

Question

$resourcesbuilt = mysql_query("SELECT resourcesbuilt FROM user WHERE username = '$username' LIMIT 1");

if ($resourcesbuilt<=0 )
{
 mysql_query("INSERT INTO resources (username, dollars) VALUES ( '$username', '1000')") or die (mysql_error());
 mysql_query("UPDATE user SET resourcesbuilt = '1' WHERE username = '$username'");
}

Basically I have a column in a user table that acts as a flag that tells me whether a different table has been made for that particular user. Its 0 if it hasn't, and 1 if it has. However it seems I can't just store the query into a variable and check if its 0 or not, because the actual value from the query isn't stored in the var. When I echo the var it just says Resource id #3. My research into php and SQL has failed me. Does anyone know of a way to store the result form a sql query into a variable in php? Or can at least point me in the right direction? Thanks in advance.

Answer 1

The problem is that the result returned by mysql_query is not usable in this way. Instead, you have to call

$value = mysql_fetch_array($resourcesbuilt, MYSQL_ASSOC)
$value ["resourcesbuilt"]

This will give you the value of that field in the table. Look here for an example.

Answer 2

$ rs = mysql_query("UPDATE user SET resourcesbuilt = '1' WHERE username = '$username'");

will store the result into a recordset named rs you can retrieve the values then by calling

$myrow = mysql_fetch_array($rs)

will put the whole row into an array named myrow and retrieve their values by $myrow[column_name]

oh and another note as far as i remember you cannot store them since they are not select statements the only resource that you'll have is mysql_num_rows