SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Showing More Data

    Hi all! I have a bit of problem here

    Code:


    while ($db->next_record()) {
    $games[$db->f(Game)] = $db->f(Url);

    }

    ksort($games);

    while (list ($key, $val) = each ($games)) {
    $color = ($i++ % 2 ) ? '#000000' : '#333333';
    $tmp3 = "<tr bgcolor='$color'><td width='30%'><a href='$val'>$key</a></td></tr>";
    $t->set_var('dreamcastdata', $tmp3);
    $t->parse('H1Block', 'HBlock', true);
    }


    The above code works, but i would like to know how to show other data in the tables such as $Date, $Publisher, $Developer.

    I would like them to be shown together with this part of the coding:


    <tr bgcolor='$color'><td width='30%'><a href='$val'>$key</a></font></td></tr>


    so that it will show on the html pages:


    <tr bgcolor='$color'><td width='30%'><a href='$val'>$key</a></td><td width='20%'>$Date</td><td width='20%'>$Publisher</td><td width='30%'>$Developer</td></tr>";


    So any help? I would like to maintain the sorting of the games in alphabetical order too.
    Last edited by XploreR; May 27, 2001 at 01:50.

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can order your results alphabetically by game in the SQL query - thus saving you from messing around ordering arrays. Something along the lines of this should do the trick:

    $sql = "SELECT * FROM tablename ORDER BY Game ASC";

    Change the ASC to DESC to reverse the order. If you post your SQL query here I may be able to help[ you further.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe, I think I helped XploreR write some of this code, including th ksort($games). The reason for the ksort is that XploreR is essentially creating a UNION of two tables in the database by putting the results of two SQL queries into the one array and then sorting the array. This could have been done in the SQL if MySQL supported UNION.

    However, thats about all I remember. As Skunk has alluded to the best way to tackle this it to go back to the original SQL commands and code used to create array $games. If we fiddle with the code here we can come up with a way of getting all the data you require into the array $games. What I'm thinking of is making $games a two-dimensional array. Each element will have a key being the name of the game, and a value which will be an array of all the other data you want to display about the game.

    If you could post the relevent code I'm sure someone will be able to give more assistance.

  4. #4
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again.

    Here's my "orginal" sql query code:

    Code:

    $tables = array("PlaystationAction", "PlaystationAdventure", "PlaystationFighting", "PlaystationPlatform", "PlaystationPuzzle", "PlaystationRacing", "PlaystationRpg", "PlaystationSimulation", "PlaystationSports", "PlaystationStrategy");

    foreach ($tables AS $table) {

    $sql = "SELECT Game, Url, FROM $table
    WHERE Alpha='a' ORDER BY Game ASC";

    $db->query($sql);



    While i actually tried out this which sort of works (not working well):


    $tables = array("PlaystationAction", "PlaystationAdventure", "PlaystationFighting", "PlaystationPlatform", "PlaystationPuzzle", "PlaystationRacing", "PlaystationRpg", "PlaystationSimulation", "PlaystationSports", "PlaystationStrategy");

    foreach ($tables AS $table) {

    $sql = "SELECT Game, Url, Date, Publisher, Developer FROM $table
    WHERE Alpha='a' ORDER BY PlaystationAdventure.Game ASC";

    $db->query($sql);
    while ($db->next_record()) {


    $Date = $db->f(Date);
    $Publisher = $db->f(Publisher);
    $Developer = $db->f(Developer);
    $games[$db->f(Game)] = $db->f(Url);


    while (list ($key, $val) = each ($games)) {

    $color = ($i++ % 2 ) ? '#000000' : '#333333';
    $tmp3 = "<tr bgcolor='$color'><td>&nbsp;<font size='2' face='Verdana, Arial'><a href='$val'>$key</a></font></td><td>&nbsp;<font size='2' face='Verdana, Arial'>$Publisher</font></td><td>&nbsp;<font size='2' face='Verdana, Arial'>$Developer</font></td><td>&nbsp;<font size='2' face='Verdana, Arial'>$Date</font></td></tr>";
    $t->set_var('dreamcastdata', $tmp3);
    $t->parse('H1Block', 'HBlock', true);
    }
    }


    But i think that there's gotta be a better way to do that. i think my code is quite bad tho, the key and val part are useless here? And i removed the ksort statement because it won't work in my coding as i want the games by alpha order. Perhaps some help here will be greatly appreciated.
    And how do i do it in mulit array? I'm sure it'll look better but i don't know how


    Basically what i want do to is still the same, i have lots of tables and i want to generate a page where all the tables with (example) column Alpha=A to show up. I have the url included too as the values. And then they will be sorted in order by the alpha. it was done via ksort the last time.
    Now i would like more data to be shown but the sorting remains, that is by the game names
    Last edited by XploreR; May 27, 2001 at 01:52.

  5. #5
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    db Class Code as requested by James :


    <?php
    /*
    * Session Management for PHP3
    *
    * Copyright (c) 1998-2000 NetUSE AG
    * Boris Erdmann, Kristian Koehntopp
    *
    * $Id: db_mysql.inc,v 1.2 2000/07/12 18:22:34 kk Exp $
    *
    */

    class DB_Sql {

    /* public: connection parameters */
    var $Host = "localhost";
    var $Database = "xiccc";
    var $User = "xiccc";
    var $Password = "gmaxkim";

    /* public: configuration parameters */
    var $Auto_Free = 0; ## Set to 1 for automatic mysql_free_result()
    var $Debug = 0; ## Set to 1 for debugging messages.
    var $Halt_On_Error = "yes"; ## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
    var $Seq_Table = "db_sequence";

    /* public: result array and current row number */
    var $Record = array();
    var $Row;

    /* public: current error number and error text */
    var $Errno = 0;
    var $Error = "";

    /* public: this is an api revision, not a CVS revision. */
    var $type = "mysql";
    var $revision = "1.2";

    /* private: link and query handles */
    var $Link_ID = 0;
    var $Query_ID = 0;



    /* public: constructor */
    function DB_Sql($query = "") {
    $this->query($query);
    }

    /* public: some trivial reporting */
    function link_id() {
    return $this->Link_ID;
    }

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

    /* public: connection management */
    function connect($Database = "", $Host = "", $User = "", $Password = "") {
    /* Handle defaults */
    if ("" == $Database)
    $Database = $this->Database;
    if ("" == $Host)
    $Host = $this->Host;
    if ("" == $User)
    $User = $this->User;
    if ("" == $Password)
    $Password = $this->Password;

    /* establish connection, select database */
    if ( 0 == $this->Link_ID ) {

    $this->Link_ID=mysql_pconnect($Host, $User, $Password);
    if (!$this->Link_ID) {
    $this->halt("pconnect($Host, $User, \$Password) failed.");
    return 0;
    }

    if (!@mysql_select_db($Database,$this->Link_ID)) {
    $this->halt("cannot use database ".$this->Database);
    return 0;
    }
    }

    return $this->Link_ID;
    }

    /* public: discard the query result */
    function free() {
    @mysql_free_result($this->Query_ID);
    $this->Query_ID = 0;
    }

    /* public: perform a query */
    function query($Query_String) {
    /* No empty queries, please, since PHP4 chokes on them. */
    if ($Query_String == "")
    /* The empty query string is passed on from the constructor,
    * when calling the class without a query, e.g. in situations
    * like these: '$db = new DB_Sql_Subclass;'
    */
    return 0;

    if (!$this->connect()) {
    return 0; /* we already complained in connect() about that. */
    };

    # New query, discard previous result.
    if ($this->Query_ID) {
    $this->free();
    }

    if ($this->Debug)
    printf("Debug: query = %s<br>\n", $Query_String);

    $this->Query_ID = @mysql_query($Query_String,$this->Link_ID);
    $this->Row = 0;
    $this->Errno = mysql_errno();
    $this->Error = mysql_error();
    if (!$this->Query_ID) {
    $this->halt("Invalid SQL: ".$Query_String);
    }

    # Will return nada if it fails. That's fine.
    return $this->Query_ID;
    }

    /* public: walk result set */
    function next_record() {
    if (!$this->Query_ID) {
    $this->halt("next_record called with no query pending.");
    return 0;
    }

    $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) {
    $this->free();
    }
    return $stat;
    }

    /* public: position in result set */
    function seek($pos = 0) {
    $status = @mysql_data_seek($this->Query_ID, $pos);
    if ($status)
    $this->Row = $pos;
    else {
    $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");

    /* half assed attempt to save the day,
    * but do not consider this documented or even
    * desireable behaviour.
    */
    @mysql_data_seek($this->Query_ID, $this->num_rows());
    $this->Row = $this->num_rows;
    return 0;
    }

    return 1;
    }

    /* public: table locking */
    function lock($table, $mode="write") {
    $this->connect();

    $query="lock tables ";
    if (is_array($table)) {
    while (list($key,$value)=each($table)) {
    if ($key=="read" && $key!=0) {
    $query.="$value read, ";
    } else {
    $query.="$value $mode, ";
    }
    }
    $query=substr($query,0,-2);
    } else {
    $query.="$table $mode";
    }
    $res = @mysql_query($query, $this->Link_ID);
    if (!$res) {
    $this->halt("lock($table, $mode) failed.");
    return 0;
    }
    return $res;
    }

    function unlock() {
    $this->connect();

    $res = @mysql_query("unlock tables");
    if (!$res) {
    $this->halt("unlock() failed.");
    return 0;
    }
    return $res;
    }


    /* public: evaluate the result (size, width) */
    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);
    }

    /* public: shorthand notation */
    function nf() {
    return $this->num_rows();
    }

    function np() {
    print $this->num_rows();
    }

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

    function p($Name) {
    print $this->Record[$Name];
    }

    /* public: sequence numbers */
    function nextid($seq_name) {
    $this->connect();

    if ($this->lock($this->Seq_Table)) {
    /* get sequence number (locked) and increment */
    $q = sprintf("select nextid from %s where seq_name = '%s'",
    $this->Seq_Table,
    $seq_name);
    $id = @mysql_query($q, $this->Link_ID);
    $res = @mysql_fetch_array($id);

    /* No current value, make one */
    if (!is_array($res)) {
    $currentid = 0;
    $q = sprintf("insert into %s values('%s', %s)",
    $this->Seq_Table,
    $seq_name,
    $currentid);
    $id = @mysql_query($q, $this->Link_ID);
    } else {
    $currentid = $res["nextid"];
    }
    $nextid = $currentid + 1;
    $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
    $this->Seq_Table,
    $nextid,
    $seq_name);
    $id = @mysql_query($q, $this->Link_ID);
    $this->unlock();
    } else {
    $this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
    return 0;
    }
    return $nextid;
    }

    /* public: return table metadata */
    function metadata($table='',$full=false) {
    $count = 0;
    $id = 0;
    $res = array();

    /*
    * Due to compatibility problems with Table we changed the behavior
    * of metadata();
    * depending on $full, metadata returns the following values:
    *
    * - full is false (default):
    * $result[]:
    * [0]["table"] table name
    * [0]["name"] field name
    * [0]["type"] field type
    * [0]["len"] field length
    * [0]["flags"] field flags
    *
    * - full is true
    * $result[]:
    * ["num_fields"] number of metadata records
    * [0]["table"] table name
    * [0]["name"] field name
    * [0]["type"] field type
    * [0]["len"] field length
    * [0]["flags"] field flags
    * ["meta"][field name] index of field named "field name"
    * The last one is used, if you have a field name, but no index.
    * Test: if (isset($result['meta']['myfield'])) { ...
    */

    // if no $table specified, assume that we are working with a query
    // result
    if ($table) {
    $this->connect();
    $id = @mysql_list_fields($this->Database, $table);
    if (!$id)
    $this->halt("Metadata query failed.");
    } else {
    $id = $this->Query_ID;
    if (!$id)
    $this->halt("No query specified.");
    }

    $count = @mysql_num_fields($id);

    // made this IF due to performance (one if is faster than $count if's)
    if (!$full) {
    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);
    }
    } else { // full
    $res["num_fields"]= $count;

    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;
    }
    }

    // free the result only if we were called on a table
    if ($table) @mysql_free_result($id);
    return $res;
    }

    /* private: error handling */
    function halt($msg) {
    $this->Error = @mysql_error($this->Link_ID);
    $this->Errno = @mysql_errno($this->Link_ID);
    if ($this->Halt_On_Error == "no")
    return;

    $this->haltmsg($msg);

    if ($this->Halt_On_Error != "report")
    die("Session halted.");
    }

    function haltmsg($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);
    }

    function table_names() {
    $this->query("SHOW TABLES");
    $i=0;
    while ($info=mysql_fetch_row($this->Query_ID))
    {
    $return[$i]["table_name"]= $info[0];
    $return[$i]["tablespace_name"]=$this->Database;
    $return[$i]["database"]=$this->Database;
    $i++;
    }
    return $return;
    }
    }
    ?>
    Last edited by XploreR; May 27, 2001 at 01:54.

  6. #6
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I suggest trying this out:

    mysql_connect("localhost" ,"username", "password");
    mysql_select_db("db");
    $tables = array("PlaystationAction", "PlaystationAdventure", "PlaystationFighting", "PlaystationPlatform", "PlaystationPuzzle", "PlaystationRacing", "PlaystationRpg", "PlaystationSimulation", "PlaystationSports", "PlaystationStrategy");
    foreach ($tables AS $table) {

    $sql = "SELECT Game, Url, Date, Publisher, Developer FROM $table WHERE Alpha='a' ORDER BY PlaystationAdventure.Game ASC";

    $query = mysql_query($sql);
    while ($array = mysql_fetch_array($query)) {
    $i++;
    $color = ($i++ % 2 ) ? '#000000' : '#333333';
    $tmp3 = "<tr bgcolor='$color'><td> <font size='2' face='Verdana, Arial'><a href='$val'>$key</a></font></td><td> <font size='2' face='Verdana, Arial'>$Publisher</font></td><td> <font size='2' face='Verdana, Arial'>$Developer</font></td><td> <font size='2' face='Verdana, Arial'>$Date</font></td></tr>";
    $t->set_var('dreamcastdata', $tmp3);
    $t->parse('H1Block', 'HBlock', true);
    }
    }
    Remember to backup your old files first though. Also be sure to change the mysql login details etc at the top of that code.
    Last edited by James; May 27, 2001 at 01:52.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •