Jump to content

OldWest

Members
 View Profile  See their activity

  • Posts

    296

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by OldWest

    Email form including reply to (from) of ftp username and correct email address

    in PHP Coding Help

    Posted

    Hello,

    I am working with a SMTP class to send an email. It's all working perfectly fine, but when the email is received (sent from the online form), and I open my email and click reply, there are two email addresses. The correct one (which I entered when I sent the email), and my ftp username for the site??

     

    I've got three files that could effect this, but I was unable to locate any problems:

    mailer.php ( smtp send mail class)

    mail.php ( submission data: title, subject, etc. )

    contact_form.php ( form for submission )

     

    I am only including the file which I think is applicable (mail.php), but let me know if you would also like to see the mailer.php class.

     

    Current output:

     

    reply-to ftpusername@domainname.com,

    correct_from_email@fromemail.com

     

    mail.php:

     

    <?php
  set_time_limit(120);
  
  function sendHTMLmail($from, $to, $subject, $message)
  {
      $message = wordwrap($message, 70);
      
      // To send HTML mail, the Content-type header must be set
      $headers = 'MIME-Version: 1.0' . "\r\n";
      $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
      
      // Additional headers
      $headers .= "From: $from\r\n";
      
      // Mail it
      mail($to, $subject, $message, $headers);
  }
  
  function sendHTMLmail2($fromid, $to, $subject, $message)
  {
      echo $fromid;
      echo $to;
      echo $subject;
      echo $message;
      include_once("mailer.php");
      $mail = new PHPMailer();
      $mail->IsMail();
      // SMTP servers
      $mail->Host = "localhost";
      $mail->From = $fromid;
      $mail->FromName = $fromid;
      $mail->IsHTML(true);
      $mail->AddAddress($to, $to);
      $mail->Subject = $subject;
      $mail->Body = $message;
      $mail->AltBody = "Please enable HTML to read this";
      $mail->Send();
      exit;
  }
  function isValidEmail($email)
  {
      return eregi("^[_.0-9a-z-]+@([0-9a-z][0-9a-z-]+.)+[a-z]{2,3}$", $email);
  }
  
  
  class smtp_mail
  {
      var $host;
      var $port = 25;
      var $user;
      var $pass;
      var $debug = false;
      var $conn;
      var $result_str;
      var $charset = "utf-8";
      var $in;
      var $from_r;
      //mail format 0=normal 1=html
      var $mailformat = 0;
      function smtp_mail($host, $port, $user, $pass, $debug = false)
      {
          $this->host = $host;
          $this->port = $port;
          $this->user = base64_encode($user);
          $this->pass = base64_encode($pass);
          $this->debug = $debug;
          $this->socket = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);
          if ($this->socket) {
              $this->result_str = "Create socket:" . socket_strerror(socket_last_error());
              $this->debug_show($this->result_str);
          } else {
              exit("Initialize Faild,Check your internet seting,please");
          }
          $this->conn = socket_connect($this->socket, $this->host, $this->port);
          if ($this->conn) {
              $this->result_str = "Create SOCKET Connect:" . socket_strerror(socket_last_error());
              $this->debug_show($this->result_str);
          } else {
              exit("Initialize Faild,Check your internet seting,please");
          }
          $this->result_str = "Server answer:<font color=#cc0000>" . socket_read($this->socket, 1024) . "</font>";
          $this->debug_show($this->result_str);
      }
      
      function debug_show($str)
      {
          if ($this->debug) {
              echo $str . "<p>\r\n";
          }
      }
      
      function send($from, $to, $subject, $body)
      {
          if ($from == "" || $to == "") {
              exit("type mail address please");
          }
          if ($subject == "")
              $sebject = "none title";
          if ($body == "")
              $body = "none content";
          
          $All = "From:$from;\r\n";
          $All .= "To:$to;\r\n";
          $All .= "Subject:$subject;\r\n";
          if ($this->mailformat == 1) {
              $All .= "Content-Type:text/html;\r\n";
          } else {
              $All .= "Content-Type:text/plain;\r\n";
          }
          $All .= "Charset:" . $this->charset . ";\r\n\r\n";
          $All .= " " . $body;
          
          $this->in = "EHLO HELO\r\n";
          $this->docommand();
          
          $this->in = "AUTH LOGIN\r\n";
          $this->docommand();
          
          $this->in = $this->user . "\r\n";
          $this->docommand();
          
          $this->in = $this->pass . "\r\n";
          $this->docommand();
          
          if (!eregi("235", $this->result_str)) {
              $this->result_str = "smtp auth faild";
              $this->debug_show($this->result_str);
              return 0;
          }
          
          $this->in = "MAIL FROM: $from\r\n";
          $this->docommand();
          
          $this->in = "RCPT TO: $to\r\n";
          $this->docommand();
          
          $this->in = "DATA\r\n";
          $this->docommand();
          
          $this->in = $All . "\r\n.\r\n";
          $this->docommand();
          
          
          if (!eregi("250", $this->result_str)) {
              $this->result_str = "Send mail faild!";
              $this->debug_show($this->result_str);
              return 0;
          }
          
          $this->in = "QUIT\r\n";
          $this->docommand();
          socket_close($this->socket);
          return 1;
      }
      
      function docommand()
      {
          socket_write($this->socket, $this->in, strlen($this->in));
          $this->debug_show("Client command:" . $this->in);
          $this->result_str = "server answer:<font color=#cc0000>" . socket_read($this->socket, 1024) . "</font>";
          $this->debug_show($this->result_str);
      }
  }
?>

     

     

     

     

    Transfer of variable value from page1 to page 2

    in PHP Coding Help

    Posted

    Well I could understand get and post methods through books but I couldn't understand how do I approach this situation of two different submits.. That is the reason why I posted in the forum.

     

    You can easily call two separate submits. Just use different names for each of them and call them with isset() and wrap that in an if statement and you can trigger any actions with any amount of submit buttons on a page like:

     

    if (isset($_POST['NameOfSubmitButtonHere'])) {

do whatever code you want here..

}

     

    MY example could be much improved, but should get you a direction. Good luck!

     

    Transfer of variable value from page1 to page 2

    in PHP Coding Help

    Posted

    All right I will take that as a constructive criticism but can you tell me the reasons why you told me that so I can analyze my code from next time?

     

    Mainly because the questions you are asking or introduction level questions. Like passing a variable from one page to another using $_POST or $_GET globals. These are one of the very first things most basic books or lessons teach. I don't really have time to look through all of your code, but that's just my 2cents.

    Array help

    in PHP Coding Help

    Posted

    Are you saying the value of $a will be coming from a table?

     

    You might want to consider using array('value','value','value')

     

    And you can test the array with empty() ..

     

    And if you are pulling all of the data from a table, you will probably want to use foreach()

    Function Array Output is not Index as expected

    in PHP Coding Help

    Posted

    Ok think it clicked for me.. I see starting the counter at 0, 1 or 2 gets the same result. So it seems just inefficient to start the loop at 1, cause if the matching name is set at index 0, then it would need to do a full loop again. In a large array this would could be a resource hog I guess??

     

    If I am wrong, please set me straight!

    Function Array Output is not Index as expected

    in PHP Coding Help

    Posted

    Here is my function which almost works as expected:

     

    <?php
function find_value($array,$value)
{
	for($i=1;$i<sizeof($array);$i++)
	{
		if($array[$i] == $value)
		{
		echo "$i . $array[$i]<br />";
		return;
		}
	}
}
?>

     

    And I call the function like so:

     

    <?php
$names = array('Jason','Mike','Joe');
$name = 'Joe';

find_value($names,$name);
?>

     

    And the output is:

     

    2 . Joe

     

    According to my understanding of Arrays, Joe would be index 3 BECAUSE my counter starts at 1 in my for loop??? Why is the result like this? What am I not understanding here?

     

     

    While looped insert is not working as expected. No errors.

    in PHP Coding Help

    Posted 

                      $insert_city_query = "INSERT INTO sys_city_dev (ID, Mid, cityName, forder, disdplay, cid) VALUES
(' ','" . $mid_id . "','" . $rows['city_name'] . "', '', '','')";
                  if (!$insert_city_query)
                      exit(mysql_error());
                }

     

    I don't see how record will be inserted.... mysql_query($insert_city_query) is not there

     

    Oh boy.. I haven't even started drinking egg nog yet and I do this? SOLVED!

    While looped insert is not working as expected. No errors.

    in PHP Coding Help

    Posted

    I cannot get this INSERT to work. Not records are being added to the sys_city_dev table. No query errors are being thrown.

     

    I am simply trying to add ALL records from all_illinois to sys_city_dev and Mid should have be add as 11 in all inserts. city_name of course will be field city_name from all_illinois.

     

    Here is my code:

     

    $query = "SELECT * FROM all_illinois";
      if ($results = mysqli_query($cxn, $query))
        {
          $row_cnt = mysqli_num_rows($results);
          echo $row_cnt . " Total Records in Query.<br /><br />";
          if (mysqli_num_rows($results))
            {
              while ($rows = mysqli_fetch_array($results))
                {
                  echo $rows['city_name'] . "<br />";
                  $mid_id = '11';
                  $insert_city_query = "INSERT INTO sys_city_dev (ID, Mid, cityName, forder, disdplay, cid) VALUES
(' ','" . $mid_id . "','" . $rows['city_name'] . "', '', '','')";
                  if (!$insert_city_query)
                      exit(mysql_error());
                }
            }
        }

     

    Here is my insert table structure and 5 records dump:

     

    --
-- Table structure for table `sys_city_dev`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=113970 ;

--
-- Dumping data for table `sys_city_dev`
--

INSERT INTO `sys_city_dev` (`ID`, `Mid`, `cityName`, `forder`, `disdplay`, `cid`) VALUES
(84010, 1, 'Dothan', 0, 0, 0),
(84011, 1, 'Alabaster', 0, 0, 0),
(84012, 1, 'Birmingham', 0, 0, 0),
(84013, 2, 'Flagstaff', 0, 0, 0),
(84014, 1, 'Auburn', 0, 0, 0);

     

    And the all_illinois dump w/ 5 records:

     

    --
-- Table structure for table `all_illinois`
--

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `all_illinois`
--

INSERT INTO `all_illinois` (`state_id`, `city_name`) VALUES
('135', 'Abingdon'),
('135', 'Adair'),
('135', 'Addieville'),
('135', 'Addison'),
('135', 'Adrian');

     

    MySQL INSERT. Cannot find why the insert is not happening.

    in PHP Coding Help

    Posted

    Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

     

    <?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

     

    Here is my all_illinois INSERT table structure:

     

    CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

     

    Here is my source table geo_cities structure:

     

    CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

     

     

    php while w/ MySQL set echoing correct but not setting the table records correct

    in PHP Coding Help

    Posted 

    $query = "SELECT id, cityName FROM sys_city_dev_2";
$resource = mysqli_query($cxn, $query) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query");

while($result = mysqli_fetch_assoc($resource))
{
$fullString = implode(" ",(array_map("ucfirst", explode(" ", $result['cityName']))));
echo $fullString;
$setString = "UPDATE sys_city_dev_2 SET cityName='$fullString' WHERE id='".$result['id']."'";
mysqli_query($cxn, $setString);
}

     

    Man that is sweet and short. Very nice.. I have never used array_map() before, so going to study up on that.

    php while w/ MySQL set echoing correct but not setting the table records correct

    in PHP Coding Help

    Posted

    Your WHERE condition in the UPDATE query is a bit generic and would match every row at once that is not an empty string.

     

    I suspect that you intended to use a unique id field from the SELECT query to match the correct row in the UPDATE query or perhaps the original value in the cityName column.

     

    I think I get what you meant.. I got it working and I think the $id was the ticket. I guess on each iteration it checking the next id compared to the previous :shrug:

     

    The purpose of the script was to make each word of the city name start with a capital letter. I am sure my script could have been written a lot tidier and more elegant with for loops and more arrays..

     

    Can anyone make some recommendation to cut my script in half (in code size)?

     

    Here is what I have which IS working as expected: All cityNames are updated with ucfirst() first letter for and of 4 parts w/ explode(). I echoed it out to the screen to see my output when completed as well:

     

    $query = "SELECT id, cityName FROM sys_city_dev_2";
      $resource = mysqli_query($cxn, $query) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query");
      $i = 0;
      while ($result = mysqli_fetch_assoc($resource)) {
          $i++;
          $id = $result['id'];
          $cName = $result['cityName'];
          //$toUpper = ucfirst($cName);
          $pieces = explode(" ", $cName);
          if (isset($pieces['0'])) {
              $piece0 = ucfirst($pieces['0']);
              //$piece0 = $pieces['0'];
              //echo "$pieces[0]" . "<br />";
          } else {
              $piece0 = '';
          }
          if (isset($pieces['1'])) {
              $piece1 = ucfirst($pieces['1']);
              //echo "$pieces[1]" . "<br />";
          } else {
              $piece1 = '';
          }
          if (isset($pieces['2'])) {
              $piece2 = ucfirst($pieces['2']);
              //echo "$pieces[2]" . "<br />";
          } else {
              $piece2 = '';
          }
          if (isset($pieces['3'])) {
              $piece3 = ucfirst($pieces['3']);
              //echo "$pieces[3]" . "<br />";
          } else {
              $piece3 = '';
          }
          $fullString = "$piece0 $piece1 $piece2 $piece3";
          echo "$fullString<br />";
          $setString = "UPDATE sys_city_dev_2 SET cityName='$fullString' WHERE id='$id'";
          mysqli_query($cxn, $setString);
          //echo "<pre>";
          //print_r($pieces);
          //echo "</pre>";
      }

     

    php while w/ MySQL set echoing correct but not setting the table records correct

    in PHP Coding Help

    Posted

    It's pretty simple to see what I am trying to do here. For some reason all results in the table are the same exact cityName replacing all existing records. The echoed results are correct. I've include a small dump of my table as well.

     

    $query = "SELECT cityName FROM sys_city_dev_2";
      $resource = mysqli_query($cxn, $query) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query");
      
  while($result = mysqli_fetch_assoc($resource)) {

          $nox = $result['cityName'];

          $toUpper = ucfirst($nox);
	  echo "$toUpper" . "<br />";

          $setString = "UPDATE sys_city_dev_2 SET cityName = '" . $toUpper ."' WHERE cityName != ''";
          mysqli_query($cxn,$setString);


      }

     

    100 Records of table dump (pre running my script above):

     

    --
-- Table structure for table `sys_city_dev_2_backup`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev_2_backup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=113970 ;

--
-- Dumping data for table `sys_city_dev_2_backup`
--

INSERT INTO `sys_city_dev_2_backup` (`ID`, `Mid`, `cityName`, `forder`, `disdplay`, `cid`) VALUES
(84010, 1, 'dothan', 0, 0, 0),
(84011, 1, 'alabaster', 0, 0, 0),
(84012, 1, 'birmingham', 0, 0, 0),
(84013, 2, 'flagstaff', 0, 0, 0),
(84014, 1, 'auburn', 0, 0, 0),
(84015, 1, 'florence', 0, 0, 0),
(84016, 1, 'gadsden', 0, 0, 0),
(84017, 1, 'huntsville', 0, 0, 0),
(84018, 1, 'mobile', 0, 0, 0),
(84019, 1, 'montgomery', 0, 0, 0),
(84020, 1, 'tuscaloosa', 0, 0, 0),
(84021, 2, 'mohave valley', 0, 0, 0),
(84022, 2, 'phoenix', 0, 0, 0),
(84023, 2, 'prescott', 0, 0, 0),
(84024, 2, 'sierra vista', 0, 0, 0),
(84025, 2, 'tucson', 0, 0, 0),
(84026, 2, 'yuma', 0, 0, 0),
(84027, 3, 'bakersfield', 0, 0, 0),
(84028, 3, 'chico', 0, 0, 0),
(84029, 3, 'fresno / madera', 0, 0, 0),
(84030, 3, 'gold country', 0, 0, 0),
(84031, 3, 'humboldt county', 0, 0, 0),
(84032, 3, 'imperial', 0, 0, 0),
(84033, 3, 'inland empire', 0, 0, 0),
(84034, 3, 'los angeles', 0, 0, 0),
(84035, 3, 'alhambra', 0, 0, 0),
(84036, 3, 'merced', 0, 0, 0),
(84037, 49, 'fayetteville', 0, 0, 0),
(84038, 49, 'fort smith', 0, 0, 0),
(84039, 49, 'jonesboro', 0, 0, 0),
(84040, 49, 'little rock', 0, 0, 0),
(84041, 49, 'arkadelphia', 0, 0, 0),
(84042, 49, 'texarkana', 0, 0, 0),
(84043, 3, 'modesto', 0, 0, 0),
(84044, 3, 'alta sierra', 0, 0, 0),
(84045, 3, 'alpine', 0, 0, 0),
(84046, 3, 'pedley', 0, 0, 0),
(84047, 3, 'redding', 0, 0, 0),
(84048, 3, 'alondra park', 0, 0, 0),
(84049, 3, 'sacramento', 0, 0, 0),
(84050, 4, 'canon city', 0, 0, 0),
(84051, 3, 'san luis obispo', 0, 0, 0),
(84052, 3, 'santa barbara', 0, 0, 0),
(84053, 3, 'stockton', 0, 0, 0),
(84054, 3, 'aliso viejo', 0, 0, 0),
(84055, 3, 'visalia', 0, 0, 0),
(84056, 3, 'yuba city', 0, 0, 0),
(84057, 4, 'boulder', 0, 0, 0),
(84058, 4, 'colorado springs', 0, 0, 0),
(84059, 4, 'denver', 0, 0, 0),
(84060, 4, 'applewood', 0, 0, 0),
(84061, 4, 'pueblo', 0, 0, 0),
(84062, 4, 'air force academy', 0, 0, 0),
(84063, 5, 'avon', 0, 0, 0),
(84064, 5, 'hartford', 0, 0, 0),
(84065, 5, 'new haven', 0, 0, 0),
(84066, 5, 'ansonia', 0, 0, 0),
(84067, 5, 'fairfield', 0, 0, 0),
(84068, 7, 'daytona beach', 0, 0, 0),
(84069, 7, 'sebastian', 0, 0, 0),
(84070, 5, 'wallingford center', 0, 0, 0),
(84071, 8, 'belvedere park', 0, 0, 0),
(84072, 7, 'sarasota springs', 0, 0, 0),
(84073, 7, 'sandalfoot cove', 0, 0, 0),
(84074, 7, 'san carlos park', 0, 0, 0),
(84075, 7, 'st. augustine', 0, 0, 0),
(84076, 7, 'tallahassee', 0, 0, 0),
(84077, 7, 'safety harbor', 0, 0, 0),
(84078, 7, 'ruskin', 0, 0, 0),
(84079, 8, 'athens-clarke county', 0, 0, 0),
(84080, 8, 'atlanta', 0, 0, 0),
(84081, 8, 'augusta-richmond county', 0, 0, 0),
(84082, 8, 'brunswick', 0, 0, 0),
(84083, 8, 'columbus', 0, 0, 0),
(84084, 8, 'americus', 0, 0, 0),
(84085, 8, 'acworth', 0, 0, 0),
(84086, 8, 'valdosta', 0, 0, 0),
(84087, 10, 'boise', 0, 0, 0),
(84088, 10, 'ammon', 0, 0, 0),
(84089, 10, 'moscow', 0, 0, 0),
(84090, 10, 'blackfoot', 0, 0, 0),
(84091, 10, 'twin falls', 0, 0, 0),
(84092, 10, 'meridian', 0, 0, 0),
(84093, 10, 'jerome', 0, 0, 0),
(84094, 10, 'idaho falls', 0, 0, 0),
(84095, 11, 'addison', 0, 0, 0),
(84096, 10, 'garden city', 0, 0, 0),
(84097, 10, 'eagle', 0, 0, 0),
(84098, 10, 'chubbuck', 0, 0, 0),
(84099, 10, 'caldwell', 0, 0, 0),
(84100, 12, 'bloomington', 0, 0, 0),
(84101, 12, 'evansville', 0, 0, 0),
(84102, 12, 'fort wayne', 0, 0, 0),
(84103, 12, 'indianapolis', 0, 0, 0),
(84104, 12, 'muncie / anderson', 0, 0, 0),
(84105, 12, 'lafayette / west lafayette', 0, 0, 0),
(84106, 12, 'south bend / michiana', 0, 0, 0),
(84107, 12, 'terre haute', 0, 0, 0),
(84108, 12, 'northwest indiana', 0, 0, 0),
(84109, 13, 'ames', 0, 0, 0);

     

    DELETE duplicate records where specific fields are equal

    in MySQL Help

    Posted

    I might have found something that works, but I think the issue lies now in my MySQL server timing out (have about 90,000 records I'm scanning through) when I run the query... It disconnects after about 3 mins with:

     

    DELETE FROM sys_city_dev_1_500
USING sys_city_dev_1_500, sys_city_dev_1_500 AS vtable
WHERE (sys_city_dev_1_500.Mid = vtable.Mid)
AND (sys_city_dev_1_500.cityName = vtable.cityName)

     

    I need to see if I can change the MySQL time out disconnect issue..

     

    Anyone any ideas? Back to hacking at it...

     

    DELETE duplicate records where specific fields are equal

    in MySQL Help

    Posted

    Here is the structure:

     

    --
-- Table structure for table `sys_city_dev_1`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `sys_city_dev_1`
--

    DELETE duplicate records where specific fields are equal

    in MySQL Help

    Posted

    I have been hammering at this for about 2 hours, searched Google to high hell, and have been unsuccessful getting the result I need  :'(

     

    Any tips welcome please!

     

    I have 1 table: sys_city_dev_1

    I need to remove all records WHERE cityName AND Mid are equal. But of course keep all existing records so as to only remove duplicates with that criteria.

     

    I thought this would do the trick:

     

    CREATE TABLE sys_city_dev_2 AS
SELECT * FROM sys_city_dev_1 WHERE 1 GROUP BY cityName

     

    , and another query I wrote froze my database up multiple times!

     

    DELETE FROM sys_city_dev_1
USING  sys_city_dev_1, sys_city_dev_1 AS a
WHERE (NOT sys_city_dev_1.Mid = a.Mid)
AND ( sys_city_dev_1.cityName = a.cityName)

     

    I've tried many variations of the aboves with no luck at all!

     

     

    Anyone? SOS

    Have no field for one table, but want auto_increment to apply to INSERT

    in MySQL Help

    Posted

    I am simply adding records but locations_all does not have an id field, and I just want to auto_increment into the ID field of sys_city from the last highest record ID. Here is what I have so far:

     

    INSERT INTO sys_city (ID, Mid, cityName) SELECT id, state_id, city_state FROM locations_all

     

    So I would like id from locations_all be set to (int)auto_increment like:

     

    INSERT INTO sys_city (ID, Mid, cityName) SELECT (int)auto_increment, state_id, city_state FROM locations_all

     

    The highest ID in sys_city is 2538, and I would like all records to continue from that number in the ID...

     

    I know something like this must be possible, but I cannot find a solution. Any ideas how to address this?

     

     

    New set of eyes on my Query - UPDATE, SET 2 Tables

    in MySQL Help

    Posted

    Hey mikosiko,

     

    Thanks for the great feedback :) I see now how it could have been a disaster had I needed to keep existing data in the locations_all.state_id field!

     

    All locations_all.state_id fields where blank. And from my manual review and check everything seems to have been updated accurately.

     

    It took a lot of guts for me to run this even on test tables!

     

    I'll call this one SOLVED!

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.