Solved

Using Query Results

Posted on 2000-03-12
9
179 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:us111
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 12

Expert Comment

by:j2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now