1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Sql primary key help

Discussion in 'MySQL' started by joesgraphics, Jan 13, 2007.

0
  1. #1
    If i drop the existing primary key username will the database still work the same as befor i want to add a id Field what wll be the primary key instead and what is the sql to drop the existing primary key.

    THanks in advanced. :)
     
    joesgraphics, Jan 13, 2007 IP
  2. edr

    edr Guest

    Messages:
    784
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Create a copy of your original table then build the new table with an auto id column. simply pull in the old data and the key will autonumber - beware of existing codebase that might be referencing your old primary key, though.
     
    edr, Jan 13, 2007 IP
  3. joesgraphics

    joesgraphics Peon

    Messages:
    206
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Could you tell me if this is the correct way to insert the info into a nuther table

    query("INSERT INTO ratings_sites (id, username)

    Is that how i would insert their id and username in a ratings_sites table
    and if the id in the table was set to auto increment would that make the id go up the number of ids +1 when the id is set for that username

    This is the join up page code:

    <?php

if (!defined('ATSPHP')) {
  die("This file cannot be accessed directly.");
}

class join extends join_edit {
  function join() {
    global $FORM, $LNG, $TMPL;

    $TMPL['header'] = $LNG['join_header'];

    if (!isset($FORM['submit'])) {
      $this->form();
    }
    else {
      $this->process();
    }
  }

  function form() {
    global $CONF, $FORM, $LNG, $TMPL;

    if ($CONF['captcha']) {
      $TMPL['join_captcha'] = $this->do_skin('join_captcha');
    }
    else {
      $TMPL['join_captcha'] = '';
    }

    $TMPL['categories_menu'] = "<select name=\"category\">\n";
    foreach ($CONF['categories'] as $cat => $skin) {
      if ($TMPL['category'] == $cat) {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\" selected=\"selected\">{$cat}</option>\n";
      }
      else {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\">{$cat}</option>\n";
      }
    }
    $TMPL['categories_menu'] .= "</select>";

    if (!isset($TMPL['url'])) { $TMPL['url'] = 'http://'; }
    if (!isset($TMPL['banner_url'])) { $TMPL['banner_url'] = 'http://'; }

    if (isset($TMPL['url'])) { $TMPL['url'] = stripslashes($TMPL['url']); }
    if (isset($TMPL['title'])) { $TMPL['title'] = stripslashes($TMPL['title']); }
    if (isset($TMPL['description'])) { $TMPL['description'] = stripslashes($TMPL['description']); }
    if (isset($TMPL['category'])) { $TMPL['category'] = stripslashes($TMPL['category']); }
    if (isset($TMPL['banner_url'])) { $TMPL['banner_url'] = stripslashes($TMPL['banner_url']); }
    if (isset($TMPL['email'])) { $TMPL['email'] = stripslashes($TMPL['email']); }

    $TMPL['content'] = $this->do_skin('join_form');
  }

  function process() {
    global $CONF, $DB, $FORM, $LNG, $TMPL;

    $TMPL['username'] = $DB->escape($FORM['u'], 1);
    $TMPL['url'] = $DB->escape($FORM['url'], 1);
    $TMPL['title'] = $DB->escape($FORM['title'], 1);
    $FORM['description'] = str_replace(array("\r\n", "\n", "\r"), ' ', $FORM['description']);
    $TMPL['description'] = $DB->escape($FORM['description'], 1);
    $TMPL['category'] = $DB->escape($FORM['category'], 1);
    $TMPL['banner_url'] = $DB->escape($FORM['banner_url'], 1);
    $TMPL['email'] = $DB->escape($FORM['email'], 1);

    $TMPL['title'] = $this->bad_words($TMPL['title']);
    $TMPL['description'] = $this->bad_words($TMPL['description']);

    if ($this->check_input('join')) {
      $password = md5($FORM['password']);

      require_once("{$CONF['path']}/sources/in.php");
      $short_url = in::short_url($TMPL['url']);

      $join_date = date('Y-m-d', time() + (3600*$CONF['time_offset']));

      $DB->query("INSERT INTO {$CONF['sql_prefix']}_sites (username, password, url, short_url, title, description, category, banner_url, email, join_date, active, openid)
                  VALUES ('{$TMPL['username']}', '{$password}', '{$TMPL['url']}', '{$short_url}', '{$TMPL['title']}', '{$TMPL['description']}', '{$TMPL['category']}', '{$TMPL['banner_url']}', '{$TMPL['email']}', '{$join_date}', {$CONF['active_default']}, 0)", __FILE__, __LINE__);
      $DB->query("INSERT INTO {$CONF['sql_prefix']}_stats (username) VALUES ('{$TMPL['username']}')", __FILE__, __LINE__);

      if ($CONF['google_friendly_links']) {
        $TMPL['verbose_link'] = "";
      }
      else {
        $TMPL['verbose_link'] = "index.php?a=in&u={$TMPL['username']}";
      }
      $TMPL['link_code'] = $this->do_skin('link_code');

      $LNG['join_welcome'] = sprintf($LNG['join_welcome'], $TMPL['list_name']);

      if ($CONF['active_default']) {
        $TMPL['approve_message'] = '';
      }
      else {
        $TMPL['approve_message'] = $LNG['join_approve'];
      }

      $join_email = new skin('join_email');
      $join_email->send_email($TMPL['email']);

      if ($CONF['email_admin_on_join']) {
        $join_email_admin = new skin('join_email_admin');
        $join_email_admin->send_email($CONF['your_email']);
      }

      $TMPL['content'] = $this->do_skin('join_finish');
    }
    else {
      $this->form();
    }
  }
}
?>
    PHP:

    and i need on 1 page to get it to search that sql and find the user id by the username and i would be geting the username by {$username} and that is what i want it to search for if you could help me with that aswell it would realy help thanks.
     
    joesgraphics, Jan 13, 2007 IP
  4. The Stealthy One

    The Stealthy One Well-Known Member Affiliate Manager

    Messages:
    3,043
    Likes Received:
    54
    Best Answers:
    0
    Trophy Points:
    105
    #4
    Yes INSERT INTO should do the trick. :) You just use that in conjunction with a regular SQL query.
     
    The Stealthy One, Jan 13, 2007 IP
  5. joesgraphics

    joesgraphics Peon

    Messages:
    206
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Ok iv done that and i chage the sql query to $DB->query("INSERT INTO ratings_sites (id, username)
    VALUES ('{$TMPL['username']}", __FILE__, __LINE__); but when i submit the form i get this error:

    Fatal error: Database error in "/home/*****/public_html/****/sources/join.php" on line 98

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''this is the username i signed up with TEST' at line 2 in /home/*****/public_html/****/sources/sql/mysql.php on line 88

    this is the sign up form when not edited:

    <?php

if (!defined('ATSPHP')) {
  die("This file cannot be accessed directly.");
}

class join extends join_edit {
  function join() {
    global $FORM, $LNG, $TMPL;

    $TMPL['header'] = $LNG['join_header'];

    if (!isset($FORM['submit'])) {
      $this->form();
    }
    else {
      $this->process();
    }
  }

  function form() {
    global $CONF, $FORM, $LNG, $TMPL;

    if ($CONF['captcha']) {
      $TMPL['join_captcha'] = $this->do_skin('join_captcha');
    }
    else {
      $TMPL['join_captcha'] = '';
    }

    $TMPL['categories_menu'] = "<select name=\"category\">\n";
    foreach ($CONF['categories'] as $cat => $skin) {
      if ($TMPL['category'] == $cat) {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\" selected=\"selected\">{$cat}</option>\n";
      }
      else {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\">{$cat}</option>\n";
      }
    }
    $TMPL['categories_menu'] .= "</select>";

    if (!isset($TMPL['url'])) { $TMPL['url'] = 'http://'; }
    if (!isset($TMPL['banner_url'])) { $TMPL['banner_url'] = 'http://'; }

    if (isset($TMPL['url'])) { $TMPL['url'] = stripslashes($TMPL['url']); }
    if (isset($TMPL['title'])) { $TMPL['title'] = stripslashes($TMPL['title']); }
    if (isset($TMPL['description'])) { $TMPL['description'] = stripslashes($TMPL['description']); }
    if (isset($TMPL['category'])) { $TMPL['category'] = stripslashes($TMPL['category']); }
    if (isset($TMPL['banner_url'])) { $TMPL['banner_url'] = stripslashes($TMPL['banner_url']); }
    if (isset($TMPL['email'])) { $TMPL['email'] = stripslashes($TMPL['email']); }

    $TMPL['content'] = $this->do_skin('join_form');
  }

  function process() {
    global $CONF, $DB, $FORM, $LNG, $TMPL;

    $TMPL['username'] = $DB->escape($FORM['u'], 1);
    $TMPL['url'] = $DB->escape($FORM['url'], 1);
    $TMPL['title'] = $DB->escape($FORM['title'], 1);
    $FORM['description'] = str_replace(array("\r\n", "\n", "\r"), ' ', $FORM['description']);
    $TMPL['description'] = $DB->escape($FORM['description'], 1);
    $TMPL['category'] = $DB->escape($FORM['category'], 1);
    $TMPL['banner_url'] = $DB->escape($FORM['banner_url'], 1);
    $TMPL['email'] = $DB->escape($FORM['email'], 1);

    $TMPL['title'] = $this->bad_words($TMPL['title']);
    $TMPL['description'] = $this->bad_words($TMPL['description']);

    if ($this->check_input('join')) {
      $password = md5($FORM['password']);

      require_once("{$CONF['path']}/sources/in.php");
      $short_url = in::short_url($TMPL['url']);

      $join_date = date('Y-m-d', time() + (3600*$CONF['time_offset']));

      $DB->query("INSERT INTO {$CONF['sql_prefix']}_sites (username, password, url, short_url, title, description, category, banner_url, email, join_date, active, openid)
                  VALUES ('{$TMPL['username']}', '{$password}', '{$TMPL['url']}', '{$short_url}', '{$TMPL['title']}', '{$TMPL['description']}', '{$TMPL['category']}', '{$TMPL['banner_url']}', '{$TMPL['email']}', '{$join_date}', {$CONF['active_default']}, 0)", __FILE__, __LINE__);
      $DB->query("INSERT INTO {$CONF['sql_prefix']}_stats (username) VALUES ('{$TMPL['username']}')", __FILE__, __LINE__);
      

      if ($CONF['google_friendly_links']) {
        $TMPL['verbose_link'] = "";
      }
      else {
        $TMPL['verbose_link'] = "index.php?a=in&u={$TMPL['username']}";
      }
      $TMPL['link_code'] = $this->do_skin('link_code');

      $LNG['join_welcome'] = sprintf($LNG['join_welcome'], $TMPL['list_name']);

      if ($CONF['active_default']) {
        $TMPL['approve_message'] = '';
      }
      else {
        $TMPL['approve_message'] = $LNG['join_approve'];
      }

      $join_email = new skin('join_email');
      $join_email->send_email($TMPL['email']);

      if ($CONF['email_admin_on_join']) {
        $join_email_admin = new skin('join_email_admin');
        $join_email_admin->send_email($CONF['your_email']);
      }

      $TMPL['content'] = $this->do_skin('join_finish');
    }
    else {
      $this->form();
    }
  }
}
?>
    PHP:

    This is the edited bit were i put

    $DB->query("INSERT INTO ratings_sites (id, username)
    VALUES ('{$TMPL['username']}", __FILE__, __LINE__);



    <?php

if (!defined('ATSPHP')) {
  die("This file cannot be accessed directly.");
}

class join extends join_edit {
  function join() {
    global $FORM, $LNG, $TMPL;

    $TMPL['header'] = $LNG['join_header'];

    if (!isset($FORM['submit'])) {
      $this->form();
    }
    else {
      $this->process();
    }
  }

  function form() {
    global $CONF, $FORM, $LNG, $TMPL;

    if ($CONF['captcha']) {
      $TMPL['join_captcha'] = $this->do_skin('join_captcha');
    }
    else {
      $TMPL['join_captcha'] = '';
    }

    $TMPL['categories_menu'] = "<select name=\"category\">\n";
    foreach ($CONF['categories'] as $cat => $skin) {
      if ($TMPL['category'] == $cat) {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\" selected=\"selected\">{$cat}</option>\n";
      }
      else {
        $TMPL['categories_menu'] .= "<option value=\"{$cat}\">{$cat}</option>\n";
      }
    }
    $TMPL['categories_menu'] .= "</select>";

    if (!isset($TMPL['url'])) { $TMPL['url'] = 'http://'; }
    if (!isset($TMPL['banner_url'])) { $TMPL['banner_url'] = 'http://'; }

    if (isset($TMPL['url'])) { $TMPL['url'] = stripslashes($TMPL['url']); }
    if (isset($TMPL['title'])) { $TMPL['title'] = stripslashes($TMPL['title']); }
    if (isset($TMPL['description'])) { $TMPL['description'] = stripslashes($TMPL['description']); }
    if (isset($TMPL['category'])) { $TMPL['category'] = stripslashes($TMPL['category']); }
    if (isset($TMPL['banner_url'])) { $TMPL['banner_url'] = stripslashes($TMPL['banner_url']); }
    if (isset($TMPL['email'])) { $TMPL['email'] = stripslashes($TMPL['email']); }

    $TMPL['content'] = $this->do_skin('join_form');
  }

  function process() {
    global $CONF, $DB, $FORM, $LNG, $TMPL;

    $TMPL['username'] = $DB->escape($FORM['u'], 1);
    $TMPL['url'] = $DB->escape($FORM['url'], 1);
    $TMPL['title'] = $DB->escape($FORM['title'], 1);
    $FORM['description'] = str_replace(array("\r\n", "\n", "\r"), ' ', $FORM['description']);
    $TMPL['description'] = $DB->escape($FORM['description'], 1);
    $TMPL['category'] = $DB->escape($FORM['category'], 1);
    $TMPL['banner_url'] = $DB->escape($FORM['banner_url'], 1);
    $TMPL['email'] = $DB->escape($FORM['email'], 1);

    $TMPL['title'] = $this->bad_words($TMPL['title']);
    $TMPL['description'] = $this->bad_words($TMPL['description']);

    if ($this->check_input('join')) {
      $password = md5($FORM['password']);

      require_once("{$CONF['path']}/sources/in.php");
      $short_url = in::short_url($TMPL['url']);

      $join_date = date('Y-m-d', time() + (3600*$CONF['time_offset']));

      $DB->query("INSERT INTO {$CONF['sql_prefix']}_sites (username, password, url, short_url, title, description, category, banner_url, email, join_date, active, openid)
                  VALUES ('{$TMPL['username']}', '{$password}', '{$TMPL['url']}', '{$short_url}', '{$TMPL['title']}', '{$TMPL['description']}', '{$TMPL['category']}', '{$TMPL['banner_url']}', '{$TMPL['email']}', '{$join_date}', {$CONF['active_default']}, 0)", __FILE__, __LINE__);
      $DB->query("INSERT INTO {$CONF['sql_prefix']}_stats (username) VALUES ('{$TMPL['username']}')", __FILE__, __LINE__);
      $DB->query("INSERT INTO ratings_sites (id, username) 
	  VALUES ('{$TMPL['username']}", __FILE__, __LINE__);

      if ($CONF['google_friendly_links']) {
        $TMPL['verbose_link'] = "";
      }
      else {
        $TMPL['verbose_link'] = "index.php?a=in&u={$TMPL['username']}";
      }
      $TMPL['link_code'] = $this->do_skin('link_code');

      $LNG['join_welcome'] = sprintf($LNG['join_welcome'], $TMPL['list_name']);

      if ($CONF['active_default']) {
        $TMPL['approve_message'] = '';
      }
      else {
        $TMPL['approve_message'] = $LNG['join_approve'];
      }

      $join_email = new skin('join_email');
      $join_email->send_email($TMPL['email']);

      if ($CONF['email_admin_on_join']) {
        $join_email_admin = new skin('join_email_admin');
        $join_email_admin->send_email($CONF['your_email']);
      }

      $TMPL['content'] = $this->do_skin('join_finish');
    }
    else {
      $this->form();
    }
  }
}
?>
    PHP:

    I dont no if this file needs editing aswell this is mysql.php:


    <?php

if (!defined('ATSPHP')) {
  die("This file cannot be accessed directly.");
}

$database = 'MySQL';

class sql {
  var $dbl;
  var $debug;
  var $num_queries;
  var $queries;

  function connect ($host, $user, $password, $database, $debug = 0) {
    $this->dbl = mysql_connect($host, $user, $password)	;
    $db = mysql_select_db($database, $this->dbl);

    $this->num_queries = 0;
    $this->debug = $debug ? 1 : 0;
    $this->queries = array();

    return $db;
  }

  function query($query, $file, $line) {
    global $queries;

    if ($this->debug) { array_push($this->queries, $query); }

    $result = mysql_query($query) or $this->error($file, $line);
    $this->num_queries++;

    return $result;
  }

  // Executes a normal query and fetches the array in one line
  function fetch($query, $file, $line) {
    $result = $this->query($query, $file, $line);
    return $this->fetch_array($result);
  }

  function select_limit($query, $num, $offset, $file, $line) {
    if ($offset) { $limit = ' LIMIT '.$offset.','.$num; }
    else { $limit = ' LIMIT '.$num; }

    return $this->query($query.$limit, $file, $line);
  }

  function fetch_array($result) {
    return mysql_fetch_array($result);
  }

  function num_rows($result) {
    return mysql_num_rows($result);
  }

  function escape($value, $no_html = 0) {
    if (get_magic_quotes_gpc()) {
      $value = stripslashes($value);
    }
    $value = mysql_real_escape_string($value, $this->dbl);

    if ($no_html) {
      $value = strip_tags($value);
    }
    
    return $value;
  }

  function error($file, $line) {
    trigger_error('Database error in "'.$file.'" on line '.$line.'<br /><br />'."\n".@mysql_error($this->dbl), E_USER_ERROR);
  }

  function close() {
    mysql_close($this->dbl);
  }
}
?>
    PHP:
    If any one could help by iver telling me what i need to edit or what if you could edit the file so it should work it would help me so much if you need to no any other info just ask.

    Thanks in advanced
     
    joesgraphics, Jan 13, 2007 IP
  6. joesgraphics

    joesgraphics Peon

    Messages:
    206
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Its ok now i fixed it my self. :)
     
    joesgraphics, Jan 18, 2007 IP