Complicated PHP memberlist viewer from mySql

spikypunker · January 28, 2008

Hi people, you've helped me in the past and i need some help again!

This time it's pretty complicated and the reason i need so much help on it is because i am updating a clients website and this is how thier current memberlist works. There's a mysql database holding the stuff and the following code links to it. As i'm building a new site for them i have had to lift the code from the old page and put it in a new page, and also copy all the mysql data into a new DB. So i thought it would be very easy however it's not working correctly. It's showing the data, but A. Not sorting B. When you click the next button or search etc it doesn't do antyhing.

Here's the original page: http://www.alcd.org.uk/memberslist.php

Here's the page i've done, you can see how it's not working: http://alcd.mhp.co.uk/memberlist.php

Here's the code....I've also tried putting the original page on my server and testing that and it does the same thing!!!


?
include_once "db_mysql.inc";
$s = new DB_Sql;
$limit_records = 50;
$q = new DB_Sql;
// Get users group contacts list
// Set the sql order
switch($order) {
case "name_up":
	$cont_order = 'Surname';
	break;
case "name_dn":
	$cont_order = 'Surname DESC';
	break;
case "town_up":
	$cont_order = 'Town,Surname';
	break;
case "town_dn":
	$cont_order = 'Town DESC,Surname';
	break;	
case "county_up":
	$cont_order = 'County,Surname';
	break;
case "county_dn":
	$cont_order = 'County DESC,Surname';
	break;
default:
	$cont_order = 'Surname';
	break;
}

// Set the crtieria
if ($search_string && $search_string != "") {
switch($search_type) {
	case "Surname":
	$where = ' WHERE Surname LIKE "'.$search_string.'%" ';
	break;

	case "Town":
	$where = ' WHERE Town LIKE "'.$search_string.'%" ';
	break;

	case "County":
	$where = ' WHERE County LIKE "'.$search_string.'%" ';
	break;
	}
}			

// Get total records for query
$sql = "SELECT COUNT(Name) AS count FROM diarylst $where";
$q->query($sql);
$q->next_record();
$count = $q->f('count');

if(!$offset) $offset = 0;  // start point for LIMIT statement
// Get the required records
$sql = "SELECT * FROM diarylst
	$where
	ORDER BY $cont_order
	LIMIT $offset, $limit_records
	";
/*
$sql = "SELECT CONCAT(cont_firstname,' ',cont_lastname) AS cont_name, contacts.*,comp_name,comp_tel1,comp_fax FROM contacts,company
	WHERE (comp_id = cont_comp_id)
	AND cont_personal = '0'
	ORDER BY $cont_order
	LIMIT $offset, $limit_records"; 
*/
$q->query($sql);

// record limit controls
$records_to = (($offset + $limit_records) > $count)? $count : ($offset + $limit_records);
$message = "[Records ".($offset + 1)." to ".$records_to." of ".$count."]";
$next = ($count > $offset + $limit_records)? ($offset + $limit_records) : 0;
$previous = $offset - $limit_records ;
$rem = ($count%$limit_records == 0)?  $limit_records : $count%$limit_records; // Records on last page
$last = $count - $rem;

if($previous >= 0) {
$first_set = '<a href="'.$PHP_SELF.'?offset=0&order='.$order.'&search_string='.$search_string.'&search_type='.$search_type.'"><img src="./common/point_start_dk.gif" width="20" height="15" alt="First page" border="0"></a>';
}else{
$first_set = '<img src="./common/point_start_lt.gif" width="20" height="15" alt="No previous records" border="0">';
}
if($next) {
$next_set = '<a href="'.$PHP_SELF.'?offset='.$next.'&order='.$order.'&search_string='.$search_string.'&search_type='.$search_type.'"><img src="./common/point_right_dk.gif" width="20" height="15" alt="Next page" border="0"></a>';
}else{
$next_set = '<img src="./common/point_right_lt.gif" width="20" height="15" alt="No more contacts" border="0">';
}
if($previous >= 0) {
$previous_set = '<a href="'.$PHP_SELF.'?offset='.$previous.'&order='.$order.'&search_string='.$search_string.'&search_type='.$search_type.'"><img src="./common/point_left_dk.gif" width="20" height="15" alt="Previous page" border="0"></a>';
}else{
$previous_set = '<img src="./common/point_left_lt.gif" width="20" height="15" alt="No previous records" border="0">';
}
if($next) {
$last_set = '<a href="'.$PHP_SELF.'?offset='.$last.'&order='.$order.'&search_string='.$search_string.'&search_type='.$search_type.'"><img src="./common/point_end_dk.gif" width="20" height="15" alt="Final page" border="0"></a>';
}else{
$last_set = '<img src="./common/point_end_lt.gif" width="20" height="15" alt="No more contacts" border="0">';
}
?>
<html>
<head>
<title>ALCD: Existing Members List</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="alcd.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--

var winOpened = null;
function openCustomWindow(goto) {  	
  winOpened = window.open(goto,'winDetails','toolbar=no,status=yes,menubar=no,width=350,height=400');
}
function winLink(goto) {	
if (winOpened && winOpened.open && !winOpened.closed) {
	locArray = winOpened.location.href.split("/");
	if(locArray[locArray.length - 1] != goto){			
		winOpened.location.href=goto;
		}
		winOpened.focus();
}else{
	openCustomWindow(goto);
}
}
//-->
</script>
</head>

<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> 
<table width="759" border="0" cellspacing="0" cellpadding="0" class="stdText">
  <tr> 
    <td align="left" valign="middle" bgcolor="#FFFFFF"> </td>
    <td colspan="6" align="left" valign="top" bgcolor="#FFFFFF"> 
      <form name="form1" method="post" action="<? print $PHP_SELF ?>">
        Search <font size="1">(all or part of name)</font>    
        <input name="search_string" type="text" id="search_string" size="20">
          
        <input name="search_type" type="radio" value="Surname" checked>
        Surname 
        <input type="radio" name="search_type" value="Town">
        Town 
        <input type="radio" name="search_type" value="County">
        County  
        <input type="submit" name="Submit" value="Submit">
      </form>
  </td>
  </tr>
  <tr> 
    <td align="left" valign="middle" bgcolor="#FFFFFF">  </td>
    <td align="left" valign="middle" bgcolor="#FFFFFF"><? print "$first_set$previous_set$next_set$last_set" ?></td>
    <td align="left" valign="middle" bgcolor="#FFFFFF"> </td>
    <td bgcolor="#FFFFFF" colspan="4"><font size="1"><? print $message ?></font></td>
  </tr>
  <tr> 
    <td> </td>
    <td><br><Br><a href="<? print $PHP_SELF ?>?order=name_up"><img src="./common/point_up.gif" width="20" height="15" border="0" alt="Order by Name Ascending"></a><a href="<? print $PHP_SELF ?>?order=name_dn"><img src="./common/point_dn.gif" width="20" height="15" border="0" alt="Order by Name Descending"></a><strong>Name</strong></td>
    <td><Br><br><a href="<? print $PHP_SELF ?>?order=town_up"><img src="./common/point_up.gif" width="20" height="15" border="0" alt="Order by Town Ascending"></a><a href="<? print $PHP_SELF ?>?order=town_dn"><img src="./common/point_dn.gif" width="20" height="15" border="0" alt="Order by Town Descending"></a><strong>Town</strong></td>
    <td><Br><br><a href="<? print $PHP_SELF ?>?order=county_up"><img src="./common/point_up.gif" width="20" height="15" border="0" alt="Order by County Ascending"></a><a href="<? print $PHP_SELF ?>?order=county_dn"><img src="./common/point_dn.gif" width="20" height="15" border="0" alt="Order by County Descending"></a><strong>County</strong></td>
    <td align="center" valign="top"><strong><Br><br>Status</strong></td>
    <td align="center" valign="top"><strong><br><Br>Details</strong></td>
    <td width="9" align="center" valign="top"> </td>
  </tr>
  <?
while ($q->next_record()) {
?>
  <tr> 
    <td align="left" valign="top" class="a2<? echo $class_done ?>"> </td>
    <td align="left" valign="top" class="a2<? echo $class_done ?>"> 
      <? $q->p("Name") ?>
    </td>
    <td align="left" valign="top" class="a2<? echo $class_done ?>"> 
      <? $q->p("Town") ?>
    </td>
    <td align="left" valign="top"> 
      <? $q->p("County") ?>
    </td>
    <td align = "center" valign="top"> 
      <? $q->p("Status") ?>
    </td>
    <td align = "center" valign="top"><a href="javascript:winLink('popup_details.php?id=<? urlencode($q->p('Name')) ?>')"><img src="./common/pencil.gif" width="20" height="15" alt="<? print $details ?>" border="0"></a></td>
    <td align = "center" valign="top"> </td>
  </tr>
  <? } ?>
  <tr> 
    <td width="84" align="left" valign="top" class="a2<? echo $class_done ?>"> </td>
    <td width="239" align="left" valign="top" class="a2<? echo $class_done ?>"> </td>
    <td width="147" align="left" valign="top" class="a2<? echo $class_done ?>"> </td>
    <td width="148" align="left" valign="top"> </td>
    <td width="55" align = "center" valign="top"> </td>
    <td width="77" align = "center" valign="top"> </td>
    <td align = "center" valign="top"> </td>
  </tr>
  <tr> 
    <td colspan="7"> </td>
  </tr>
</table>
</body>
</html>

[\code]

If someone can help this. i swear i will personally send you a lolly.

peace

spikypunker · January 28, 2008

Sorry this is the actual link to the original page!

http://www.alcd.org.uk/memberslist.php

spikypunker · January 29, 2008

Any ideas?

I've looked at it some more and it's basically the EXACT same code as the original, but it's not working on my area. You click a button like the NEXT button and it sends the php code in the URL back to itself, but it doesn't affect the data listed.

spikypunker · January 29, 2008

I've just realised that i should also show the "include" file


<?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 {
  
  /* MW 17/06/04 Trying to get it to work on our site ... replace the stuff below what our settings (if they are our settings ... don't have a clue what I'm doing!)*/
  /* public: connection parameters */
  /*var $Host     = "localhost";
  var $Database = "alcd";
  var $User     = "insert username here";
  var $Password = "insert password here";*/

  
  var $Host     = "mysql";
  var $Database = "alcd";
  var $User     = "mhp";
  var $Password = "GreyBells";

  
  /* 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;
  }
}
?>

rajivgonsalves · January 29, 2008

check your register_globals setting on the server it must be off..

spikypunker · January 29, 2008

Do you mean it must be off to work, or it sounds like it must be off now and it should be on to work?

rajivgonsalves · January 29, 2008

if its off it will not work

so

switch($order) {

should be

switch($_GET['order']) {

and so one for the other code

spikypunker · January 29, 2008

Hi there

Right have check this and you are right register_globals is set to off.

Is it just where the variables are first called that i'd need to change?

rajivgonsalves · January 29, 2008

yes just change them accordingly so get variable change to $_GET[variablename] or post variables to $_POST[variablename] this will give your site more portability

Sign In

Complicated PHP memberlist viewer from mySql

Recommended Posts

spikypunker

Link to comment

Share on other sites

spikypunker

Link to comment

Share on other sites

spikypunker

Link to comment

Share on other sites

spikypunker

Link to comment

Share on other sites

rajivgonsalves

Link to comment

Share on other sites

spikypunker

Link to comment

Share on other sites

rajivgonsalves

Link to comment

Share on other sites

spikypunker

Link to comment

Share on other sites

rajivgonsalves

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information