Storing result to a variable

This site is best viewed in a modern browser with JavaScript enabled.

tenniskid493

How do you store the result set from a SELECT statement into a variable

This code works perfectly if I take out the assigning of the variable and just have it immediately echo the result. However, I need it to assign it to a variable and then echo the variable.

$link = mysql_connect('localhost', 'user_name', 'password');
$db_selected = mysql_select_db('Statistics', $link);
$result = mysql_query('SELECT `Name` FROM `Test Stats` WHERE 1 LIMIT 0, 30 ');
if (!$result) {
   die('Could not query:' . mysql_error());
}
echo "Name: ";
$nme = mysql_result($result, 0);
echo $nme
mysql_close($link);

mojo3120

in asp you would create a recordset,

connTemp.Open pDatabaseConnectionString
set rsTemp = server.createObject("adodb.recordset")
rsTemp.open mySQL, connTemp
pName=rsTemp("name")

not sure with php though,

also...are you missing a field in your sql statement that should be 1?
mysql=SELECT name FROM Test Stats WHERE 1 LIMIT 0, 30

tenniskid493

no..the select statement is correct. The only thing that doesn't work is when I store it to the variable and then try to echo the variable. It works fine if I just echo the result without storing it to a variable. Anyone else know what I can do?

NogDog

Might just be that you're missing the closing ";" at the end of the echo line?

Also, since your query is theoretically retrieving up to 30 rows, you could do something like the following to output them all:

$link = mysql_connect('localhost', 'user_name', 'password') or die("DB Connect Failed");
$db_selected = mysql_select_db('Statistics', $link) or die("DB Select Failed");
$query = 'SELECT `Name` FROM `Test Stats` WHERE 1 LIMIT 0, 30';
$result = mysql_query($query) or die("Query Failed: $query - " . mysql_error();
if(mysql_num_rows($result) > 0)
{
  echo "<h3>Names:</h3>\n<ul>\n";
  while($row = mysql_fetch_assoc($result))
  {
    echo "<li>" . $row['Name'] . "</li>\n";
  }
  echo "</ul>\n";
}
else
{
  echo "<p>ERROR: No names returned by query.</p>\n";
}

tenniskid493

woops, yea that was what I was missing. Thanks for the help. Also, I wasn't actually retrieving 30 names, thats just put in standard from my host. I was actually only retrieving one name. Thank you