Solved

Using Query Results

Posted on 2000-03-12
9
187 Views
Last Modified: 2008-03-06
I've got a SELECT statement... and want to know how to print (to the screen) the result. Ideally I'd like the result stored as a variable.

e.g. $variable = "Result of 'Select x from y'";

I've tried a few things to no avail, but it's obviously just a matter of know-how.

Cheers,
Dave
0
Comment
Question by:dht1
  • 3
  • 2
  • 2
  • +1
9 Comments
 

Expert Comment

by:mrvithan
ID: 2610930
well.... it has serverl ways to do....
1. if you have your select like this -->
$db = mysql_query("SELECT count(*) FROM board");  you can get your answer by --> $count = mysql_result( $db, 0)

2. if you have your select like this --> $db->mysql_query("SELECT * from ....");
and your result may be a subtable.  You can get your answer by --> $result = mysql_fetch_array($db); and you can get each column by $result['columns name']

I hope this answer would help you...:)
0
 
LVL 12

Accepted Solution

by:
j2 earned 50 total points
ID: 2611518
Here is a snippet of code from a website i am administering.

$db = mysql_connect("localhost", "csweb", "web") or die("Unable to contactdb");
        mysql_select_db("cs")  or die("Unable to selectDB");
        $sql = "select * from artiklar order by artiklar_datum";
        $result = mysql_query($sql) or die("error during select");
        $number = mysql_numrows($result);

while ($r = mysql_fetch_array($result))
{
        $titel = $r["artiklar_namn"];
        $forfattare = $r["artiklar_forfattare"];
        $id = $r["artiklar_key"];
        $discus = $r["artiklar_diskutera"];
      $mail = $r['noveller_forfattare_email'];
        $publicerad = $r['artiklar_publicerad'];
        $uppdaterad = date("H:i  j/n-y", $r['artiklar_senast_uppdaterad']);
        echo "<tr>\n";

0
 
LVL 12

Expert Comment

by:j2
ID: 2611531
db = mysql_connect("localhost", "csweb", "web") or die("Unable to contactdb");

Connect to db, print error if it fails.
-----------------------------------

mysql_select_db("cs")  or die("Unable to selectDB");

select the database to use
-----------------------------------

$sql = "select * from artiklar order by artiklar_datum";

SQL Statement to run
-----------------------------------

$result = mysql_query($sql) or die("error during select");

get results into array.
------------------------------------

        $number = mysql_numrows($result);

get number of resulting rows
------------------------------------

The rest is a loop to extract the info.

$titel = $r["artiklar_namn"];

is how you would fetch the value from the column artiklar_name in the current line of the results from the query.

Hope it helps.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 8

Expert Comment

by:us111
ID: 2612045
Use the following from PHPLIB

class MySQL
{      var $Host     = "";
        var $Database = "";
        var $User     = "";
      var $Password = "";

        var $Link_ID  = 0;
      var $Query_ID = 0;
        var $Record   = array();
        var $Row;
      
        var $Errno    = 0;
        var $Error    = "";
        var $Empty        = true;
      
        var $Auto_free = 0;     ## Set this to 1 for automatic mysql_free_result()

      function MySQL($host, $database, $user = "", $password = "")
      {      $this->Host = $host;
            $this->Database = $database;
            $this->User = $user;
            $this->Password = $password;

            $this->connect();
      }

      function connect()
      {      if ( $this->Link_ID == 0 )
            {      $this->Link_ID = mysql_connect($this->Host, $this->User, $this->Password);
                  if (!$this->Link_ID)
                  {      $this->halt("Link-ID ====== false, pconnect failed");
                     }
                if (!mysql_query(sprintf("use %s",$this->Database),$this->Link_ID))
                  {      $this->halt("cannot use database ".$this->Database);
                }
               }
      }

      function query($Query_String)
      {      $this->connect();
              $this->Query_ID = mysql_query($Query_String,$this->Link_ID);
          $this->Row   = 0;
            
          $this->Errno = mysql_errno();
            if ($this-Errno <> 0)
                  $this->Empty = true;
                  
          $this->Error = mysql_error();

          if (!$this->Query_ID)
            {      $this->halt("Invalid SQL: ".$Query_String);
          }

          return $this->Query_ID;
      }

      function next_record()
      {      $this->Record = mysql_fetch_array($this->Query_ID);
          $this->Row   += 1;
          $this->Errno = mysql_errno();
          $this->Error = mysql_error();

          $stat = is_array($this->Record);
          if (!$stat && $this->auto_free)
            {      mysql_free_result($this->Query_ID);
                  $this->Query_ID = 0;
          }
          return $stat;
        }

      function seek($pos)
      {      $status = mysql_data_seek($this->Query_ID, $pos);
          if ($status)
            $this->Row = $pos;
          return;
      }

      function metadata($table)      
      {      $count = 0;
          $id    = 0;
          $res   = array();

          $this->connect();
          $id = @mysql_list_fields($this->Database, $table);
          if ($id < 0)
            {      $this->Errno = mysql_errno();
                  $this->Error = mysql_error();
                  $this->halt("Metadata query failed.");
          }
          $count = mysql_num_fields($id);
   
          for ($i=0; $i<$count; $i++)
            {      $res[$i]["table"] = mysql_field_table ($id, $i);
                  $res[$i]["name"]  = mysql_field_name  ($id, $i);
                  $res[$i]["type"]  = mysql_field_type  ($id, $i);
                  $res[$i]["len"]   = mysql_field_len   ($id, $i);
                  $res[$i]["flags"] = mysql_field_flags ($id, $i);
                  $res["meta"][$res[$i]["name"]] = $i;
                  $res["num_fields"]= $count;
          }
   
          mysql_free_result($id);
          return $res;
      }

      function affected_rows()
      {      return mysql_affected_rows($this->Link_ID);
      }

      function num_rows()
      {      return mysql_num_rows($this->Query_ID);
      }

      function num_fields()
      {      return mysql_num_fields($this->Query_ID);
      }

      function current_row()
      {      return $this->num_rows();
      }

      function field($Name)
      {      return $this->Record[$Name];
      }

      function field_value($Name)
      {      return $this->Record[$Name];
      }

      function halt($msg)
      {      printf("</td></tr></table><b>Database error:</b> %s<br>\n", $msg);
                printf("<b>MySQL Error</b>: %s (%s)<br>\n",
            $this->Errno,
            $this->Error);
          die("Session halted.");
      }
}
?>

Put this class in a file mysql.php3

then

<?
/* HOW TO USE */
include("mysql.php3");

$Conn = new MySQL("", "mydatabase");
$Conn->query("Select * from Mytable");
while($Conn->next_record())
{      printf("%s  -  %s<bR>",
    $Conn->field_value("cat0_num"),
    $Conn->field_value("Fr"));
}

$a = $Conn->metadata("Categories0");
print $a[0]["type"];


//$res[$i]["table"] = mysql
  //                $res[$i]["name"]  = mysql_field_name  ($id, $i);
      //            $res[$i]["type"]  = mysql_field_type  ($id, $i);
    //              $res[$i]["len"]   = mysql_field_len   ($id, $i);
      //            $res[$i]["flags"] = mysql_field_flags ($id, $i);
          //        $res["meta"][$res[$i]["name"]] = $i;
            //      $res["num_fields"]= $count;
            */
?>
0
 
LVL 12

Expert Comment

by:j2
ID: 2612076
i would also like to point out that PHPLIb is not included in PHP, but must be installed separately (http://phplib.netuse.de)
0
 
LVL 8

Expert Comment

by:us111
ID: 2612114
yep true.
anyway this solution works without all the phplib but I'd lie to point out
that it's part of the PHPLIB.
0
 

Author Comment

by:dht1
ID: 2613113
Woah!

I didn't realise there was so much anyone could write about it!!!

Thank you so much for all your input.

Dave
0
 

Expert Comment

by:mrvithan
ID: 2614425
i don't understand why dht1 accept j2 answer while he point the answer as same as me.. just because he say he is administrator on some website..
i don't care about point or anything...
but i really don't understand
0
 

Author Comment

by:dht1
ID: 2614610
mrvithan,

Just to clarify "why" I didn't accept your answer, it's because I'm not a very good programmer... and didn't understand your answer. I'm sure that syntactically your solution was fine, it's simply that I couldn't work out how to incorporate it.

I'm sorry, but as I infer above, I could rip-off j2's code, but I couldn't do likewise with yours.

Cheers,

Dave
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question