Update Several Columns In A Row - Postal Code Or Zip Code
Ok - I have 125 records. They all have different postal codes. I have a column named "Region". I want to update all postal codes starting with "V" to region "WST".
How can I do this? I know how to update on MASS based on the column, but can I what I need to do?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Access SQL Code > MySQL Code (INNER JOINs)
I've basically created the following tables / queries as part of my first mySQL project : Candidates --------------- Candidate_ID (autonumber) Name (text) ... 1, Iain 2, Fi 3, Rob Vacancies -------------- Vacancy_ID (autonumber) Vacancy (text) ... 1, Cartographer 2, Gardener 3, Occupational therapist 4, Web designer 5, Recruitment manager Profiles ----------- Profile_ID (autonumber) Profile (text) ... 1, Map making 2, Web design 3, Gardening 4, Hand therapy 5, Recruitment 6, Aviation 7, Sport 8, Travel VacancyProfiles ---------------------- Vacancy_ID (number) Profile_ID (number) ... 1,1 1,2 4,2 2,3 3,4 5,5 5,6 CandidateProfiles -------------------- Candidate_ID (number) Vacancy_ID (number) ... 1,1 1,2 1,7 1,8 2,3 2,4 2,8 3,5 3,6 3,7 and from there created two queries CandidatesQuery -------------------- SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID, Profiles.Profile FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID = CandidateProfiles.ProfileID; 1, Iain, 1, Map making 1, Iain, 2, Web design 1, Iain, 7, Sport 1, Iain, 8, Travel 2, Fi, 3, Gardening 2, Fi, 4, Hand therapy 2, Fi, 8, Travel 3, Rob, 5, Recruitment 3, Rob, 6, Aviation 3, Rob, 7, Sport and Vacancies_Query ------------------- SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID, Profiles.Profile FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID = VacancyProfiles.ProfileID; ... 1, Cartographer, 1, Map making 1, Cartographer, 2, Web design 2, Gardener, 3, Gardening 3, Occupational therapist, 4, Hand therapy 4, Web designer, 2, Web design 5, Recruitment manager, 5, Recruitment 5, Recruitment manager, 6, Aviation Trouble is, I come from an Access background, and I think the syntax of mySQL SQL is different to Access SQL - in the case here for the INNER JOINs - could anyone help out with the syntax for the queries above in mySQL friendly SQL, as I'm a bit unsure of what it should be.
Java Code Or Pseudo Code For "Outer Join"
I want to implement the "outer join" functionality in Java. Can somebody explain the pseudo code for the same. OR what needs to be done to extend the hash-join Java code of equijoin.
Update Area Code With Paranthesis
I've got a table with phone numbers in the format: 03 1234 5678 and (03) 1234 5678 03 is the area code for all of them. What query could i use to put parenthesis around all the 03 area codes that don't yet have them? The column type is varchar(31).
Zip Code
Here is what I am trying to do. Maybe it's not possible, but I have a database for employees that I have created. It includes a name, photo, title and the zip code. Without bothering with selecting a state, I want to be able to type in a five digit number and if the number they search with is 200 or 300 numbers less than or greater than a zip code I have specified for an employee it retrieves the data.
SP Code
I am a SQL Server user and I tried to get a stored procedure to work with several iterations. Here is what i tried first: delimiter // g create procedure P() begin select * from ips_transactions; end // g This is what it returned: Failed to execute SQL : SQL delimiter // g create procedure P() begin select * from ips_transactions; end // g failed : Unknown command ''. Then I tried: delimiter // create procedure P() begin select * from ips_transactions; end // And got this, which I thought my be a confirmation that the procedure was created: Output from SQL command delimiter // create procedure P() begin select * from ips_transactions; end // .. No data returned I tried to call it call P() and got this: Failed to execute SQL : SQL call P() failed : PROCEDURE dev-demoasp3.P does not exist What am I doing wrong? I have a client that wants all of my SQL server SP moved over, but I cannot even do a slect statement much less create temp tables and the such
Zip Code
I have the zip codes from the USPS in an Access 2000 database available for immediate download for $2.50 via PayPal. Visit http://www.digital-recovery.com/zip_code_database.htm for additional info and to download a demo of the database.
Php Code
I have some fields that contain both text and some php code which is activated using the eval command. I need to search and replace this php code with nothing, I want to get rid of it from mysql fields, however, because I am submiting an item of php within this sql command it seems to be getting upset... [code] update `articles` set `content`='[php]<?php include('./1.php'); ?>' (`content`,' ');
Help Cleaning Code?
I'm not too familiar with MySQL code, I was hoping someone could help clean this code to make it importable in PHPMyAdmin, in it's current form, it produces an extreme amount of errors when I attempt to import it in PHPMyAdmin. — MySQL dump 10.9 – — Host: localhost Database: meneame — —————————————————— — Server version 4.1.11-Debian_4sarge2 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; – — Table structure for table `blogs` – DROP TABLE IF EXISTS `blogs`; CREATE TABLE `blogs` ( `blog_id` int(20) NOT NULL auto_increment, `blog_key` varchar(35) collate utf8_spanish_ci default NULL, `blog_type` enum(’normal’,'blog’) collate utf8_spanish_ci NOT NULL default ‘normal’, `blog_rss` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_rss2` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_atom` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `blog_url` varchar(64) collate utf8_spanish_ci default NULL, PRIMARY KEY (`blog_id`), UNIQUE KEY `key` (`blog_key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `categories` – DROP TABLE IF EXISTS `categories`; CREATE TABLE `categories` ( `category__auto_id` int(11) NOT NULL auto_increment, `category_lang` varchar(4) collate utf8_spanish_ci NOT NULL default ‘es’, `category_id` int(11) NOT NULL default ‘0′, `category_parent` int(11) NOT NULL default ‘0′, `category_name` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`category__auto_id`), UNIQUE KEY `category_lang` (`category_lang`,`category_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `comments` – DROP TABLE IF EXISTS `comments`; CREATE TABLE `comments` ( `comment_id` int(20) NOT NULL auto_increment, `comment_randkey` int(11) NOT NULL default ‘0′, `comment_parent` int(20) default ‘0′, `comment_link_id` int(20) NOT NULL default ‘0′, `comment_user_id` int(20) NOT NULL default ‘0′, `comment_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `comment_karma` smallint(6) NOT NULL default ‘0′, `comment_nick` varchar(32) collate utf8_spanish_ci default NULL, `comment_content` text collate utf8_spanish_ci NOT NULL, PRIMARY KEY (`comment_id`), UNIQUE KEY `comments_randkey` (`comment_randkey`,`comment_link_id`,`comment_user_id`), KEY `comment_link_id_2` (`comment_link_id`,`comment_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `friends` – DROP TABLE IF EXISTS `friends`; CREATE TABLE `friends` ( `friend_id` int(11) NOT NULL auto_increment, `friend_from` bigint(20) NOT NULL default ‘0′, `friend_to` bigint(20) NOT NULL default ‘0′, PRIMARY KEY (`friend_id`), UNIQUE KEY `friends_from_to` (`friend_from`,`friend_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `languages` – DROP TABLE IF EXISTS `languages`; CREATE TABLE `languages` ( `language_id` int(11) NOT NULL auto_increment, `language_name` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`language_id`), UNIQUE KEY `language_name` (`language_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `links` – DROP TABLE IF EXISTS `links`; CREATE TABLE `links` ( `link_id` int(20) NOT NULL auto_increment, `link_author` int(20) NOT NULL default ‘0′, `link_blog` int(20) default ‘0′, `link_status` enum(’discard’,'queued’,'published’,'abuse’,'duplicated’) collate utf8_spanish_ci NOT NULL default ‘discard’, `link_randkey` int(20) NOT NULL default ‘0′, `link_votes` int(20) NOT NULL default ‘0′, `link_karma` decimal(10,2) NOT NULL default ‘0.00′, `link_modified` timestamp NOT NULL default CURRENT_TIMESTAMP, `link_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `link_published_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `link_category` int(11) NOT NULL default ‘0′, `link_lang` int(11) NOT NULL default ‘1′, `link_url` varchar(200) collate utf8_spanish_ci NOT NULL default ‘’, `link_url_title` text collate utf8_spanish_ci, `link_title` text collate utf8_spanish_ci NOT NULL, `link_content` text collate utf8_spanish_ci NOT NULL, `link_tags` text collate utf8_spanish_ci, PRIMARY KEY (`link_id`), KEY `link_author` (`link_author`), KEY `link_url` (`link_url`), KEY `link_date` (`link_date`), KEY `link_published_date` (`link_published_date`), FULLTEXT KEY `link_url_2` (`link_url`,`link_url_title`,`link_title`,`link_content`,`link_tags`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `trackbacks` – DROP TABLE IF EXISTS `trackbacks`; CREATE TABLE `trackbacks` ( `trackback_id` int(10) unsigned NOT NULL auto_increment, `trackback_link_id` int(11) NOT NULL default ‘0′, `trackback_user_id` int(11) NOT NULL default ‘0′, `trackback_type` enum(’in’,'out’) collate utf8_spanish_ci NOT NULL default ‘in’, `trackback_status` enum(’ok’,'pendent’,'error’) collate utf8_spanish_ci NOT NULL default ‘pendent’, `trackback_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `trackback_date` timestamp NULL default NULL, `trackback_url` varchar(200) collate utf8_spanish_ci NOT NULL default ‘’, `trackback_title` text collate utf8_spanish_ci, `trackback_content` text collate utf8_spanish_ci, PRIMARY KEY (`trackback_id`), UNIQUE KEY `trackback_link_id_2` (`trackback_link_id`,`trackback_type`,`trackback_url`), KEY `trackback_link_id` (`trackback_link_id`), KEY `trackback_url` (`trackback_url`), KEY `trackback_date` (`trackback_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `users` – DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_id` int(20) NOT NULL auto_increment, `user_login` varchar(32) collate utf8_spanish_ci NOT NULL default ‘’, `user_level` enum(’normal’,’special’,'blogger’,'admin’,'god’) collate utf8_spanish_ci NOT NULL default ‘normal’, `user_modification` timestamp NOT NULL default CURRENT_TIMESTAMP, `user_date` timestamp NOT NULL default ‘0000-00-00 00:00:00′, `user_pass` varchar(64) collate utf8_spanish_ci NOT NULL default ‘’, `user_email` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, `user_names` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, `user_lang` int(11) NOT NULL default ‘1′, `user_karma` decimal(10,2) default ‘10.00′, `user_url` varchar(128) collate utf8_spanish_ci NOT NULL default ‘’, PRIMARY KEY (`user_id`), UNIQUE KEY `user_login` (`user_login`), KEY `user_email` (`user_email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; – — Table structure for table `votes` – DROP TABLE IF EXISTS `votes`; CREATE TABLE `votes` ( `vote_id` int(20) NOT NULL auto_increment, `vote_type` enum(’links’,'comments’) collate utf8_spanish_ci NOT NULL default ‘links’, `vote_date` timestamp NOT NULL default CURRENT_TIMESTAMP, `vote_link_id` int(20) NOT NULL default ‘0′, `vote_user_id` int(20) NOT NULL default ‘0′, `vote_value` smallint(11) NOT NULL default ‘1′, `vote_ip` varchar(64) collate utf8_spanish_ci default NULL, PRIMARY KEY (`vote_id`), KEY `user_id` (`vote_user_id`), KEY `link_id` (`vote_link_id`), KEY `vote_type` (`vote_type`,`vote_link_id`,`vote_user_id`,`vote_ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci PACK_KEYS=0; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Zip Code Radius
I am using the following function from phpclasses.org. How can I gues this to return also the distance for each zip code? PHP function inradius($zip,$radius) { $query2="SELECT * FROM zips WHERE zip='$zip'"; $row = $db->get($query2); $lat=$row["lat"]; $lon=$row["lon"]; $query2="SELECT zipcode FROM zips WHERE (POW((69.1*(lon-"$lon")*cos($lat/57.3)),"2")+POW((69.1*(lat-"$lat")),"2"))<($radius*$radius) "; $result = $database->query($query2); if($result->count()<>0) { while($row = $result->fetch()) { $zips[$i]=$row["zipcode"]; $i++; } } }else{ return array(); } return $zips; }
How Do I Code This In The WHERE Clause?
I have a form where people can click on checkboxes for their ethnicity. When they click on search it will take all those ethnicity id's and compare it to others in a database and pull out all members that have the same ethnicity. In the database when a user has put in their own ethnicity it puts all the ethnicity id's in one field (seperated by commas). So how do I code it so that I can take an array of ids and compare it to a field of ids in the databse?
Zip Code Query
Ok when using this I have it so even planners can find racers. The Racers opt to be searched and how far they are willing to travel to race amoung other things. When an event planner searches for Potential Racers he type in her zip code and I want it to find all racers that are willing to travel to his zip code. What I thought of for a solution is when the racer makes herself searchable to populate a TEXT Column with all the Zips that are in her radius. The problem with this is that there could be around 900 zip codes thats around 5,400 charaters. To me that seems like a lot. Is this still the best solution and if so when I get a list of all the zip codes in her radius how can I combine all the Rows to a single row so I can enter in into a single field.
Code Generators
Is there a FREE code generator program that will generate forms and listings and maybe a complete set of updates and edits forms?
Foreign Key Code
I'm trying to reference the two primary keys in the "choise" table in "clicklist" but just can't get it to work. I have tried one of them up to this point, but it doesn't work either. :( CREATE TABLE `Choice` ( `productID` int(11) NOT NULL, `Nickname` varchar(32) NOT NULL, `Quantity` int(11) NOT NULL, `TimeOfSelection` datetime NOT NULL, PRIMARY KEY (`productID`,`Nickname`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; create table `clicklist` ( `Nick` varchar(32) not null, `productID int(11) not null default '0', key `productID` (`productID`), constraint `productID` foreign key (`productID`) references `Choice` (`productID`)) engine = InnoDB default charset = latin1; ERROR (HY): Can't create table '.perlacasa/Clicklist.frm' (errno: 150)
MySQL Code
I am scripting In ASP and with a DSN connection to mysql. I need a script which can query the database to see if two fields (username or email address) which are both submitted by a user exist anywhere in the database.I have tried various scripts so far, but none seem to work. PATH> User submits (joebloggs, joebloggs@hissite.com) > Check DB to see if either of these exist anywhere in the file. Connection String : 'Init the DB Connection on error resume next dim adoConnstep dim adoRSstep dim strQuerystep set adoConnstep = Server.CreateObject("ADODB.Connection") adoConnstep.Open "DSN=mydsn"
Need Help Revising Some Old Code
Alright, I found this code in a much older post: PHP Code: $sql = mysql_query ( 'SELECT FOUND_ROWS() FROM table_name' ); $max = mysql_result ( $sql, 0 ); $sql = mysql_query ( 'SELECT * FROM table_name ORDER BY id ASC LIMIT ' . ( $max - $get ) . ', ' . $get ); while ( $t = mysql_fetch_assoc ( $sql ) ) { echo $t['id'] . '<br />'; } It's basically supposed to the newest 7 entries in a table in descending order for mysql versions that don't support sub-queries, like mine doesn't. I was wondering if someone could get this to work properly. First off, I'm not sure what value $get is supposed to have, because it was never made, and I'm also unsure why $max is set to 0.
Error Code 127
Where do I go to find out the meaning of this ( or err codes in general ) message. What is the solution to this problem - any ideas ?
One Line Of Code
First here is the line of code Im getting an error on... $q2 = MYSQL_QUERY("SELECT *, DATE_FORMAT(Date, '%M-%D-%Y') AS newdate FROM usercars WHERE User = '$user' AND Active = "T"") or die ("SQL Error"); Before I tried to add the Date_format the lines worked fine and read as follows. $q2 = MYSQL_QUERY("SELECT* FROM usercars WHERE User = '$user' AND Active = "T"") or die ("SQL Error"); Second, when working will this force the date to be 02-24-2005 or 02-24-05 ? I would prefer the second option, any idea how to force that format?
SIC Code List
I'm working on an app that needs to reference SIC codes. They basically map numbers to industry categories, making it easy to categorize businesses. I've spent plenty of time googling, and have only come up with SIC lookups, not full lists. The one list I did find only went out to 6 digits, and I need a list containing 8-digit codes. Does anybody here have a list of SIC codes in either a mysql database or csv file? Or have any idea where I can find one?
Zip Code Locator
Let's see the brain childs work this one out! I have a table that has a whole bunch of customers in there. One field that I am interested in is customer_zipcode. With that all of our customers have their zipcode listed.Now, what if I want our managers to be able to put the store zip code in a box and then they define how many miles they want to travel such as 25 miles (hard code if you need to make it easier is fine). Then what I would like to have returned is something similar to: customer_id | customer_fname | customer_lname How would I go about doing that? I figured that I would have to use a LEFT JOIN customers ON customers.zipcode = zip_code.zip_code .... but the rest I am kinda clueless on.
Code Generators
Is there a FREE code generator program that will generate forms and listings and maybe a complete set of updates and edits forms?
Error Code 28
I'm getting this error all the time, among others.. So I guess that I had run out of diskspace, so I did a "SHOW VARIABLES" to see where my data was stored, and found them to be: bdb_home /var/db/mysql/ bdb_tmpdir /var/tmp/ datadir /var/db/mysql/ so, next was to do a df to see if /var was all down.. heres my result: /dev/ad0s1e 257998 78556 158804 33% /var I can't seems to find out what is wrong here.. From what I can see the shoulb be 33% space left on this device..? How come MySQL gives me a error saying no space left?
Error Code 28
mySQL started to return error code 28 whenever I try to insert any data, create tables, etc. I used 'perror' on 28 and it returned: Code: No space left on device I then used 'dd' on the drive and it said that all partitions have at least 20% space left on it. What can I do to fix this?
Zip Code Column
I am capturing zip codes in my database. In the table, I have the type set to "int(5)". I am running into problems where a given zip code starts with a "0" (ex. 06539) in that the leading "0" is being stripped.I've got a workaround in place now but I'm wondering what the "right" way to fix this would be. Should I change the type of the zip code field to something like varchar?
Change The Code
when i put data in my mysql table on mine local webserver it appears only when i change the html code of the page that contains the php code. I already put the meta-tag 'no-chache' on mine site but it doesn't seams to work
How I Can DROP The Database Using PHP Code
I use this script to add database, this script successful to ceate the database fox <? $my_db = "fox"; mysql_create_db("$my_db"); ?> ------------------------------------------------------------------------------- When I use this script to drop database, this script fail to drop the database fox <? $my_db = "fox"; mysql_drop_db("$my_db"); ?> ------------------------------------------------------------------------------- How i can DROP the database fox?
I Cannot Get Php And Mysql To Talk, Help Please! My Code Is Below
Im on windows xp using mysql server 5.0, apache http server 2.0.59 and php 5.2.1.1 When I load the page it appears completely blank with just a white background. My code is below can anyone help please? <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Our List of Jokes</title> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> </head> <body> <?php // Connect to the database server $dbcnx = @mysql_connect('localhost', 'root', 'root'); if (!$dbcnx) { exit('<p>Unable to connect to the ' . 'database server at this time.</p>'); } // Select the jokes database if (!@mysql_select_db('ijdb')) { exit('<p>Unable to locate the joke ' . 'database at this time.</p>'); } ?> <p>Here are all the jokes in our database/p> <blockquote> <?php // Request the text of all the jokes $result = @mysql_query('SELECT joketext FROM joke'); if (!$result) { exit('<p>Error performing query: ' . mysql_error() . '</p>'); } // Display the text of each joke in a paragraph while ($row = mysql_fetch_array($result)) { echo '<p>' . $row['joketext'] . '</p>' } ?> </blockquote> </body> </html>
Is It Safe To Just Code For MySQL 4.1?
Is it safe to concentrate on just MySQL 4.1 in software that you offer for download / sell? Or should I still support MySQL 3.xx versions? If 4.1 went into production status in october 2004 then most hosters should have updatet their servers by now, but I cannot be sure about that. I never really looked at the new features 4.1 introduced up until now like Subqueries which are really useful and I would like to write just one DB driver.
Yet Another Zip Code Related Question
I'm new to MySQL and proximity searches so forgive my ignorance. I have the following query which works perfectly (which I got from a post here thank you very much) SELECT zip FROM zipcodes WHERE degrees(acos( sin( radians(zipcodes.latitude) ) * sin( radians(33.727807)) + cos( radians(zipcodes.latitude)) * cos( radians(33.727807)) * cos( radians(zipcodes.longitude - -84.41983) ) ) ) * 69.09 < 5 This returns a list of zipcodes Now I want to find every bookstore in that list. SELECT * FROM bookstores WHERE zipcode IN ( SELECT zip FROM zipcodes WHERE degrees(acos( sin( radians(zipcodes.latitude) ) * sin( radians(33.727807)) + cos( radians(zipcodes.latitude)) * cos( radians(33.727807)) * cos( radians(zipcodes.longitude - -84.41983) ) ) ) * 69.09 < 5) This gives me a syntax error.
Converting To Code MySQL
DROP TABLE VENDOR DROP TABLE PRODUCT CREATE TABLE VENDOR( VENDOR_CODE CHAR(2) NOT NULL CHECK(VENDOR_CODE IN('AR', 'MS', 'WW')), VENDOR_NAME VARCHAR(20) NOT NULL, VENDOR_ADDRESS VARCHAR(15) NOT NULL, VENDOR_CITY VARCHAR(15) NOT NULL, VENDOR_STATE CHAR(2) NOT NULL CHECK(VENDOR_STATE IN('WI', 'SD', 'WV')), VENDOR_ZIP_CODE CHAR(10) NOT NULL CHECK(VENDOR_ZIP_CODE LIKE'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), VENDOR_PHONE_NUMBER CHAR(12) NOT NULL CHECK(VENDOR_PHONE_NUMBER LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'), PRIMARY KEY(VENDOR_CODE) ) CREATE TABLE PRODUCT( PRODUCT_NO CHAR(4) NOT NULL PRIMARY KEY, PRODUCT_NAME VARCHAR(30) NOT NULL, PRODUCT_ON_HAND INTEGER DEFAULT 0, PRODUCT_COST MONEY DEFAULT 0, SELLING_PRICE MONEY DEFAULT 0, PRODUCT_VENDOR_CODE CHAR(2) NOT NULL CHECK(PRODUCT_VENDOR_CODE IN('AR', 'MS', 'WW')) REFERENCES VENDOR(VENDOR_CODE) ) INSERT INTO PRODUCT VALUES('CHO4','CHESS SET',11,26.75,28.90,'WW'); INSERT INTO PRODUCT VALUES('DI24','DINOSAURS',14,3.75,4.95,'MS'); INSERT INTO PRODUCT VALUES('GL18','GLOBE',2,27.50,29.95,'MS'); INSERT INTO PRODUCT VA...........
Need Help With Code Plz - Unkown Error
<?php $username ="ictcranfor"; $password="s29698"; $database ="ictcranfor"; mysql_connect ('mysql4.streamline.net',$username, $password) or die ("Unable to connect".mysql_error()); mysql_select_db ($database); $licencenumber_submitted=$_POST['licencenumber']; $title_submitted=$_POST['title']; $firstname_submitted=$_POST['firstname']; $surname_submitted=$_POST['surname']; $age_submitted=$_POST['age']; $gender_submitted=$_POST['gender']; $address_submitted=$_POST['address']; $postcode_subm.............................................................
Need Server To Test Code
Does anyone have a server where the dates can be tweaked to see if some code works. I need to see if the dates crossover into the next month when it gets close to the end of the month and we are looking a week out.
PHP MySQL Code Generators
I'm looking for a PHP code generator that will support forms that access data from lookup tables, sometimes two or three levels deep. All of the generators I've seen are very simple single table lookups. Which is no use to me. DBDesigner's simple frontend goes to one level but my design goes further.
Date Code Conversion
I have a data set that I inherited that was exported into a text CSV file, apparently from a MYSQL database. The file contains date code values that I would like to convert into date strings. What is the algorithm that MYSQL uses to create the date code? ie, how can I convert the value 1086066000 into "Jan 28, 2004"? I'd prefer to do it in something like Matlab that I'm fluent in, rather than MYSQL where I'd be starting from total zero.
Transaction Secure Code
When executing a specific query, I need to lock the db to ensure that no data is altered while the query executes. My question is: Do I need to take a ROLLBACK on the db to release the lock, or is it enought just to set autocommit=1 again? Simplified example of how I do it now: SET AUTOCOMMIT=0 BEGIN SELECT .......bla bla bla <execute some php code based on the result> ROLLBACK SET AUTOCOMMIT=1 Example of how I want it to be: SET AUTOCOMMIT=0 BEGIN SELECT .......bla bla bla <execute some php code based on the result> SET AUTOCOMMIT=1 Are there any downsides by not using ROLLBACK in the second example? The database has not been altered in any way.
HTML-code Storage?
When I insert HTML-code in a varchar or text field the < and > characters get surrounded by spaces. For example <b> is stored as < b > instead. What am I doing wrong?
Adding Table With PHP Code ?
If I use a constant rather than a variable, it works. Can anyone tell me why this doesn't work? I'm running this on a RH9.0 with PHP 4 and MYSQL 3.23.54. Code:
Does Table Exist Code?
I ran across this code to check if a table exists. I'm thinking there has to be a better way...maybe something like DoesTableExist("MyTable") = true or false. Comments? global $exists; $exists = 0; $dataset = mysql_query('SHOW TABLES') ; while( $row = mysql_fetch_row($dataset) ) { if ( $row[0] == "mos_tbd_tags" ) { $exists = 1; break; } }
Import Logic Code
I have been using PHPmyadmin to do daily imports... 1. empty the table... 2. import the text file I'd like to FTP all my files up and then create a statement that will do this for several tables... This will both automate the process and enable to deal with a SSH ftp connection instead of the 'clear' phpmyadmin... Can anyone point me to a resource for this logic OR a way to grab what PHPmyadmin is doing????
MySQL Password In PHP Code
I am not sure using md5 is correct in the code below: [CODE] <?php $Host ="localhost"; $User = "root"; $Password =md5("secretl"); $DBName="NewDatabase"; $TableName="Feedback"; $Link = mysql_connect($Host, $User, $Password) or die("Couldn't make connection: " . mysql_error()); //on failiure, post error message if($db = mysql_select_db($DBName, $Link)) { print "connected to NewDatabase"; } else { die("Couldn't select database:" . mysql_error()); } ?> [/CODE] The error message is as follows: Couldn't make connection: Client does not support authentication protocol requested by server; consider upgrading MySQL client.
CHANGE CODE FOR SEARCH
I have a search box on my site which works fine but I would like to improve. at the moment it will not return a result for a two word search unless those words are adjacent ie red box but not for return for red big box I would like to make it give results for multiple word searches when the words are found In this order all words in search adjacent all words in search (not neccesarily adjacent) any of the words I assume the following tells it what to look for and how to display results (I hope so any way) if ($_GET["page"]=='search') { $sql="SELECT C.category_description,C.category_name,A.article_title,A.article_author,A.article_snippet,A.article_category_id,A.article_id,A.article_views FROM article_master A,category_master C where A.article_category_id=C.category_id and C.category_status=1 and A.article_status=1 and (A.article_title like '%".$_GET["q"]."%' OR A.article_snippet like '%".$_GET["q"]."%' OR A.article_summary like '%".$_GET["q"]."%')order by A.article_title asc"; $cmdartcat = mysql_query($sql); $metatitle="Search Results For ".$_GET["q"]; .
Can't Add Field Error Code 13
I keep getting an error whenever I try to modify a table. Actual Error: "Couldn't add field te. MySQL said: Error on rename of '. est est.MYI' to '. est#sql2-368-47.MYI' (Errcode: 13)" I have checked the permissions on the MYSQL folder, mysql data folder, and each of the individual databases. I tried doing the same thing using the root user with the same result. Please Help PS: I am very religious about back ups. I have them clear up to last night at 5 pm. Should I just wipe the install and start over.
Create 5,050 Lines Of Code
Small problem, and probably a quick solution. (but I have little background in programming) I need to create 5,050 different lines of code for inserting data into one table. I have various large number sets I need to generate and place into every row under one particular column value... (basically, there's one particular field that has 100 values) I'm looking for a way to write a script to generate these numbers for me that I can either copy and paste, or generate to a .txt file.
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
Area Code Lookup
I'm looking for a table with area codes and states for the USA and Canada. To be used for validation in signup forms.
Separating Mysql From Code
I have an exorbitant amount of data that I would like to put into a MySQL database and eventually put into a LAMP-based CMS (by a professional developer) and eventually uploaded onto a website for all people to access. Is it possible to load information into MySQL before any coding is performed by a developer, before any parts of the CMS are scripted? Can MySQL operate on its own as a database independent from any coding or do I need a developer to set it up before data can be loaded onto MySQL? If this is possible, is there any sort of language I would need to be fluent in or can it be done using a graphic interface?
|
TRACKER
