I am working on a directory site that is still in beta. I have decided to create a url_base field (derived from the name in the database) to use in place of numerical ids in urls.

The reason I am making this change is to have more meaningful urls.

The database already has a fair bit of data in it, so I am trying to update the data in the table with the new field.

This is where I am having difficulties. The script is very short.

PHP Code:

$ask="SELECT * FROM org";
$result=mysql_query($ask) or die(mysql_error());
while ($data=mysql_fetch_assoc($result)) "INSERT into org.url_base VALUES ".strtolower(ereg_replace(" ","_",trim(ereg_replace("[^A-Za-z0-9 ]", "",$data['name']))))." WHERE orgid=".$data['orgid.']; 


I think maybe I am using the WHILE loop with the INSERT statement wrong.

Thanks for taking the time to look over this script.

d.

Can you please describe what exactly each construction is doing? Doesn't it seem suspicious to you that to execute the $ask query you do call mysql_query() and to execute the "insert" one you don't?

After that please read the manual on mysql INSERT and UPDATE statements and PHP's working with mysql in general. At the moment it seems that you *absolutely* do not understand what you are doing.

Just a few pointers. Get yourself a MySQL Object Class, this will make things a whole lot easier. A good MySQL Object will have a method to return an associative array. Replace the while loop with a foreach on the associative array. This will also allow you to check the contents of the returned array through print_r() prior to the foreach loop. Don't use ereg_replace, use preg_replace. Preg_replace is faster, although you will have to spend a few minutes learning the syntax because it is different than ereg_replace.

MySQL Object Class which you are welcome to use under the License:

PHP Code:

/*
Author: PHP Know How
http://www.phpknowhow.com/
Creative Commons Attribution 3.0 United States License
http://creativecommons.org/licenses/by/3.0/us/
*/
class MySQL {
  var $db_conn;
  var $db_name;
  var $last_result;
  var $last_sql;
  var $all_sql;
  var $count;
  function MySQL($host, $username, $password, $database) {
    register_shutdown_function(array(&$this, "__destruct"));
    $this->__construct($host, $username, $password, $database);
  }
  function __construct($host, $username, $password, $database) {
    try {
      if(!$this->db_conn=@mysql_connect($host, $username, $password))throw new Exception("MySQL database connection failed");
      if(!mysql_select_db($database))throw new Exception("MySQL database could not be selected");
      $this->db_name=$database;
      $this->all_sql=array();
      $this->count=0;
    } catch(Exception $e) {
    echo $e->getMessage(); die();
    }
  }
  function __destruct() {
    $this->close();
  }
  function close() {
    @mysql_close($this->handle);  
  }
  function query($query, $verbose=true) {
    if(!empty($query)) {
    $this->all_sql[]=$query;
    $this->last_sql=$query;
    $this->count++;
      if($verbose) {
        try {
          if(!$this->last_result=@mysql_query($query, $this->db_conn))throw new Exception(mysql_error());
        } catch(Exception $e) {
        echo $e->getMessage(); die();
        }
      } else {
      $this->last_result=@mysql_query($query, $this->db_conn);      
      }
    return $this->last_result;
    }
  }
  function fetchRow($result=false) {
    $result=($result)?$result:$this->last_result;
    return mysql_fetch_array($result, MYSQL_ASSOC);
  }
  function fetchRows($result=false) {
    $result=($result)?$result:$this->last_result;
    $rows=array();
    while($row=$this->fetchRow($result)) {
      $rows[]=$row;
    }
    return $rows;    
  }
  function numRows($result=false) {
    $result=($result)?$result:$this->last_result;
    return mysql_num_rows($result);
  }
  function numFields($result=false) {
    $result=($result)?$result:$this->last_result;
    return mysql_num_fields($result);  
  }
  function affectedRows($result=false) {
    $result=($result)?$result:$this->last_result;
    return mysql_affected_rows($result);
  }
  function insertId() {
    return mysql_insert_id($this->db_conn);
  }  
} 


Hope that helps.