How can I remove this ?no-duplicate? constrict ion?




I got this error massage:

Duplicate entry '135' for key 1 SQL=
INSERT INTO mos_comprofiler ( `id`,`user_id`,`approved` VALUES ( '135','135','1','1','

Its ok with me if id=id_user, How can I remove this no-duplicate constrict ion

Posted On: Thursday 25th of October 2012 11:13:17 PM Total Views:  147
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




How can I fix a "supplied argument is not a valid MySQL result" error?

PHP Code: //phpinfo(); \t\tinclude_once('json/JSON.php'); $json=newServices_JSON(); $con=mysql_connect("localhost","clas_installer","installerpass"); if(!$con) \t{ \tdie('Couldnotconnect:'.mysql_error()); \t} $selected=mysql_select_db("clas_registration",$con) \t\tordie("Couldnotselectregistration"); $arr=array(); $rs=mysql_query("SELECTcountyWHERE\t\tstateabbreviation='AL'FROMcities_countiesORDERBYcounty"); while($obj=mysql_fetch_object($rs)){ \t\t$arr[]=$obj; } \t\t\t\t\techo(mysql_error()); Echo'{"sample":'.$json->encode($arr).'}'; I get this error: Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/clas/public_html/acdirect/main/index.php on line 18 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 'WHERE stateabbreviation='AL' FROM cities_counties ORDER BY county' at line 1{[]} Line 18 is where the "while" line is I've done some research and I haven't found what's my mistake
VIEWS ON THIS POST

85

Posted on:

Wednesday 24th October 2012
View Replies!

How to store american DATE formate( like 27-02-2007) in mysql table???????????

hi, i need to know desperately the way of storing american DATE formate( like 27-02-2007) in mysql table.i searched on the web a lot,but didnt find anything.PLZ i need someone's help. , ok thanzzz to all......one thing.......if i hv a ORACLE server and a MYSQL server connected to a single network,then how could i do data operations among them since they store date in two different formats\t , so finally ......i take it that (forget about retrieving or any other thing)....in a MYSQL db table the only format DATE type takes is YYYY-MM-DD....isn't it\t\t\t , I think you are getting to technical Rudy, Yes the only date format that MySQL supports is YYYY-MM-DD.
VIEWS ON THIS POST

248

Posted on:

Wednesday 24th October 2012
View Replies!

Page 2 - How can i achieve this query best(quickest and most efficient)

Code: select game.username , S.score , ( select count(*) + 1 from score where score > S.score ) as rank from game inner join score as S on S.id = game.id where game.id = $Userid make sure there is an index on score.score
VIEWS ON THIS POST

248

Posted on:

Wednesday 24th October 2012
View Replies!

Database in use - can't open file (mydata.MYI) errno: 145

Hi , I'm using PhpMyAdmin - but the database table that I want to access keeps saying in use and then if I click on it, it says: PHP Code: SQLquery: SHOWKEYSFROM`mydata'; MySQLsaid:\t #1016-Can'topenfile:'mydata.MYI'.(errno:145) Any ideas\t , Simon,
VIEWS ON THIS POST

153

Posted on:

Wednesday 24th October 2012
View Replies!

Just installed mysql - cannot log in to change password

I am borderline about to throw my computer out the window....I have installed, removed, installed again, removed again mysql 5.0 on my brand new Fedora 5 core with little success. I am logged in as root, and can get mysqld to start, however when I attempt to change my password by the following: mysql -u root I get the following message: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) How can I be denied if I just installed the damm thing!! I do not get this at all. Any help would be greatly appreciated. I am logged in as root, I'm have just installed mysql - what do I do , Also check this thread if you haven't already. Another user has just gone through what sounds like a similar set of issues, you might find some clues there. , files ending .gz are usually a type of zip file. Sounds like it may have been downloaded but not unzipped. Can you describe exactly what you did to install mysql , Quote: Originally Posted by askjoe yum install mysql This is the exact command I used Are you certain you didn't install the mysql client alone Unless I'm mistaken, fedora packages mysql client and server in seperate packages. Try 'yum install mysql-server'. , Hmm. It's been awhile since I ran either Redhat or Fedora. What is the output of this command: Code: chkconfig --list | grep 'mysql' , Ok, so it indicates that it is installed and set to run at init level 5 (basic multiuser / X runlevel), so that's fine as long as you're logged in to X. I might change it to run in both '3' and '5', but it should be ok for now. Ok, how about trying these commands (as root or superuser) Code: /etc/init.d/mysqld restart and Code: netstat -autv | grep 'mysql' and finally Code: grep 'localhost' /etc/hosts That last command there, I'm curious if you're simply missing a 'localhost' entry in your /etc/hosts file. , Hmm, well as they say, "that's a good one". Everything seems to indicate that the server is installed, running and ready to accept connections, from any IP, on the default port. Have you run down the list of 'causes of access denied errors' I'm not certain where the log might be, have you checked /var/log Which version of mysql-server is installed Do you have the latest updates Have you had any success logging in as the MySQL 'root' user Have you tried simply logging in as a guest user, e.g. simply calling the `mysql` command without specifying a user with the -u switch Tried logging in as 'root' or the guest user with your 'other' hostname, e.g. the machine name Code: mysql -u root -h xyz Where 'xyz' is your hostname.
VIEWS ON THIS POST

172

Posted on:

Wednesday 24th October 2012
View Replies!

How can I still keep mysql querying once it hits an error?

I have a query: Quote: mysql_query("insert into $dbname.brands (brands) select distinct brands from $dbname.prodse_$mid where app=1 limit $views, $records"); On the insert table there is a unique index so that I only get one of each value. I have also used distinct on the select part of the query so that it doesn't throw up any errors when it can't insert a value due to it being a duplicate. Everything works fine apart from I need to keep these values that I have already inserted and only some of them get deleted that aren't used anymore. I have to update this table every day that has the inserts in, but when I try this it does not work, because it just throws up and error as it is trying to insert duplicates where it can't because I am using unique index. How can I stop the query from stopping once it hits a duplicate and then fails. How can I keep it going on to the next row of the select part of the query.
VIEWS ON THIS POST

288

Posted on:

Wednesday 24th October 2012
View Replies!

Why Scan All 38K Rows When I Limit 4?

I've been using EXPLAIN to better do my queries and spotted this: PHP Code: EXPLAINSELECTfilms.film_title,trailers.trailer_titleFROMfilms,trailersWHEREtrailers.film_id=films.film_idORDERBYtrailers.trailer_idDESCLIMIT4 The result I get is 48K rows must get scanned in the 'trailers' table. Why can't it just do the 4 and stop
VIEWS ON THIS POST

86

Posted on:

Thursday 25th October 2012
View Replies!

scanning repeatedly.

I am trying to make a multi-player game and I wanted to know whats the best way to go about this The game will need to scan the database continually using a chron job I assume. It may also need to add or remove data based on what it receives. Basically I wanted to know is it a wise idea to set up a database scan for every tenth of a second Will the computer even be able to process it that fast and modify information once the database reaches 100,000 members Does anyone have any experience in this Is there an article anyone can point me to which would help. I tried searching google but came up with nothing.
VIEWS ON THIS POST

91

Posted on:

Thursday 25th October 2012
View Replies!

How can "Some String" != "Some String" ?

I'm building a website that allows members to upload images. The images themselves are not stored in the database - just the image information (id, title, filename, thumbnail, date, ownerid). I created small test tables in PHPMyAdmin, and everything was working fine until I got my image upload script working. Apparently, SQL doesn't recognize strings that are inserted by my script. In other words: Code: SELECT * FROM 'image' Returns all records Code: SELECT * FROM 'image' WHERE 'title' LIKE "Some Title" Returns the row with "Some Title" if it was inserted by PHPMyAdmin. Code: SELECT * FROM 'image' WHERE 'title' LIKE "Some Title" Returns 0 records if "Some Title" was inserted through my PHP script. Code: If I copy and paste or type the words "Some Title" in PHPMYAdmin: SELECT * FROM 'image' WHERE 'title' LIKE "Some Title" Now returns the correct data. It *seems like I didn't change anything, and it still looks like the same string to me, but to SQL "Some Title" != "Some Title" for some mysterious reason. Incidentally, all of the other rows with strings have the same issue. Here's what I've tried: Code: SHOW CREATE TABLE: CREATE TABLE `image` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `thumbnail` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `date` date NOT NULL, `ownerid` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_bin Code: SHOW CREATE DATABASE: CREATE DATABASE `imagedb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ Code: Of course, when I connect to my database, I always run: !mysqli_set_charset($link, 'utf8') Code: of my html and php pages contain: Code: Someone suggested putting this at the top of my script: ini_set('default_charset', 'UTF-8'); It didn't help, so I removed it. I've already tried going through each table with PHPMyAdmin and setting the collation for each column manually. Code: I also tried running this script: SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8' Maybe it's not an encoding (collation) problem after all... But I'm stumped, so any help would be appreciated!
VIEWS ON THIS POST

73

Posted on:

Thursday 25th October 2012
View Replies!

PHP can't connect to MySQL

, I am working through Kevin Yank's book "Build Your Own Database-Driven Web Site", and I ran into a problem with the deletejoke file, which has been previously documented: http://www.sitepoint.com/forums/showthread.phpt=627677 At the suggestion of the forum members, I decided to uninstall PHP 5.2 and go with 5.3, according to the directions in the book as well as the updates/errata page for the book: http://www.sitepoint.com/books/phpmysql4/errata.php Now the server times out whenever I try to connect to the database. (Other aspects of PHP such as datetime and include files is working okay.) I'm not sure what the problem is. When I was running PHP 5.2 previously, I could connect to the server all right, but launching deletejoke would cause the server to crash. Here is the error message I am getting in my browser now: ================ Warning: mysqli_connect() [function.mysqli-connect]: [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\phpmysql-4\chapter4\connect\index.php on line 2 Warning: mysqli_connect() [function.mysqli-connect]: (HY000/2002): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\phpmysql-4\chapter4\connect\index.php on line 2 Fatal error: Maximum execution time of 30 seconds exceeded in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\phpmysql-4\chapter4\connect\index.php on line 2
VIEWS ON THIS POST

108

Posted on:

Thursday 25th October 2012
View Replies!

How can I group by this?

I have a table called table_A, and it has several columns and looks like this HTML Code: id s_id s_name s_type created_date 1 10 testA typeA 2009-01-01 2 10 testA typeA 2009-01-02 3 10 testA typeA 2009-01-03 4 10 testA typeA_1 2009-01-04 5 11 testB typeB 2009-01-05 6 11 testB typeB_1 2009-01-06 7 11 testB typeB_1 2009-01-07 8 12 testC typeC 2009-01-08 9 12 testC typeC 2009-01-09 10 12 testC typeC 2009-01-10 11 12 testC typeC 2009-01-11 : : : : : : : : : : s_type should be same for same s_id and s_name, so for s_id:10 and s_name:testA, s_type should be one type,either typeA or typeA_1, but data in the table is not. And I need to figure out what s_id and s_name have more than one set of s_type. In the above example, I would like to have following results from a query HTML Code: s_id s_name s_type count 10 testA typeA 3 10 testA typeA_1 1 11 testB typeB 1 11 testB typeB_1 2 Maybe it's okay not to show count if it's hard to implement. As you see, in the results, there should not have s_id:12 s_name:testC, because s_id:12 s_name:testC has one type of s_type which is typeC. I've tried several sql, but no success.
VIEWS ON THIS POST

92

Posted on:

Thursday 25th October 2012
View Replies!

sql statement works but takes forever - can anyone help?

I have the following sql statement: Code: SELECT item.item_id, item.itemaction, item.user_id, item.flagged, item.title, user.username, item.thumb, LEFT( item.details, 150 ) AS shortdetails, ROUND( distance, 2 ) AS distance FROM ( SELECT * , ( SQRT( ABS( POWER( ( Grid_E -246818 ), 2 ) + POWER( ( Grid_N -54564 ), 2 ) ) ) ) / 1609.344 AS distance FROM `postcode` ) AS postcode INNER JOIN user ON user.postcode = postcode.postcode INNER JOIN item ON item.user_id = user.user_id WHERE distance
VIEWS ON THIS POST

160

Posted on:

Thursday 25th October 2012
View Replies!

Sound easy I hope someone can help?

I have a website built on Drupal CMS, it has a blog and forum, but that's really not the real content of my site. I was contacted by Founder/Admin of a major Forum running Vbulletin. The Admin of that forum wants me to integrated my current pedigree database onto his website, can this be done
VIEWS ON THIS POST

100

Posted on:

Thursday 25th October 2012
View Replies!

can't says wots the problem

Tried this but can't says wots the problem with it. Code SQL: SELECT DISTINCT bids.FK_PRODUCT_ID,MAX(bid_price) FROM bids WHERE FK_USER_ID = 2 AND MAX(bid_price)
VIEWS ON THIS POST

101

Posted on:

Thursday 25th October 2012
View Replies!

host mysql server has innoDB enabled but can create innodb tables

My host indicates, and I have verified on phpmyadmin storage engines, that InnoDB is enabled. I am installing a program that checks for it in a database I already created and says, "InnoDB is not enabled in the database Could not determine if mysql has innodb support." How do I fix this
VIEWS ON THIS POST

206

Posted on:

Thursday 25th October 2012
View Replies!

Where can I get libmysql.dll v5.1.23-rc for PHP?

Hi Guys, Would dearly appreciate some help please, I'm totally lost! I'm running on Windows XP Pro sp2 MySQL Server version = 5.1.23-rc MySQL Client = 5.0.22 Apache = 2.2 PHP = 5.2 phpMyAdmin = 2.11.5 Message in phpMyAdmin warns of the mismatch in between the Server and the client version of MySQL and in addition I seem to have some weird problems which I think are probably related. I tried copying the libmysql.dll file from the MySQL installation directory into the PHP directory and sure enough the error disappeared in phpMyAdmin but then the Apache 2.2 server kept quitting unexpectedly! Even after a reboot, Apache was still a bit flakey and when I tried to load a PHP website residing locally, Firefox offered to download the PHP file and open it in an editor! After I restored the old libmysql.dll file that I had renamed, I checked the same local website again and all was well, but I'm back with the weird errors on my sites which use MySQL and the warning in phpMyAdmin. I've looked online for a file that might work with my MySQL version but can only find information about 'compiling' one! What's all that about Where can I get one pre-built (since not being a software programmer I have zero knowledge about compiling)
VIEWS ON THIS POST

217

Posted on:

Thursday 25th October 2012
View Replies!

can't work out SELECT and CASE

Before I confuse you with my description, here is what I currently do, and it works in a roundabout way. I removed some unrelated stuff. PHP Code: $get_auctions=mysql_query("SELECTa.id,a.seller,a.title,c.url_name,p.url_nameASparent_url_nameFROMauctionsa,categoriescLEFTJOINcategoriespONc.parent_id=p.idWHEREa.category=c.id");while($row=mysql_fetch_array($get_auctions)){\t\t\t\t$this_url_name=$row['url_name'];\t\t$this_parent_url_name=$row['parent_url_name'];\t\t\t\tif(empty($this_parent_url_name))\t\t{\t\t\t\t$this_parent_url_name=$this_url_name;\t\t\t\t$this_url_name='';\t\t\t\t}\t\t\t\t$title_link=''.$title.'';} trying to do something different, rather than handle the result set with php I want to get the right data from the query in the first place. I am selecting xx rows of auctions. Each auction has a category assignment, and I want to determine the category name, and if available the category parent name from the categories table. This is all fine and dandy except I tried to be clever and it won't do what I want. What I want is: auction stuff | parent_url_name | child_url_name if the parent_url_name is NULL (the item IS IN a parent category as currently determined by my query) then I want to set the parent_url_name to the value of the child_url_name, and I want to set the child_url_name to or ''. If that made no sense, hopefully you can work out what i'm trying to do in this query: Code MySQL: SELECT c.url_name AS child_url_name, CASE p.url_name WHEN IS NULL THEN p.url_name=c.url_name // and set c.url_name to '' or NULL somehow END AS parent_url_name FROM auctions a, categories c LEFT JOIN categories p ON c.parent_id = p.id WHERE a.category = c.id This particular one generated an error because of the use of IS NULL. If I change it to just WHEN NULL it doesn't seem to do anything. So in essence there are only two possibilities that I want: 1. item + parent_url_name + child_url_name (NULL) 2. item + parent_url_name + child_url_name
VIEWS ON THIS POST

84

Posted on:

Thursday 25th October 2012
View Replies!

how can do a single pass, string clean up?

situation: have full names that could have 1. franklin, joe r. 2. franklin, joe r 3. franklin jr, joe 4. franklin PhD, joe 5. frankin-ross, joe so far i came up with: Code: SELECT distinctrow CASE WHEN (substr(instr_name,-1,1)=".") THEN substr(instr_name,1,length(instr_name)-3) WHEN (substr(instr_name,-2,1)=" ") THEN substr(instr_name,1,length(instr_name)-2) WHEN ((locate("-",instr_name)>0)) THEN REPLACE(instr_name,"-"," ") WHEN ((locate("Jr",instr_name)>0)) THEN REPLACE(instr_name," Jr","") WHEN ((locate("Sr",instr_name)>0)) THEN REPLACE(instr_name," Sr","") WHEN ((locate("III",instr_name)>0)) THEN REPLACE(instr_name," III","") WHEN ((locate("PhD",instr_name)>0)) THEN REPLACE(instr_name," PhD","") else instr_name end as instr_name, instr_name from results where instr_name is not null i want to just end up with franklin, joe or franklin ross, joe meaning, i want to rip out any middle initials at the end of the name, including if there is a period. also, rip out any last name suffix (i.e. Jr, Sr, III, PhD, etc). and remove the hyphens from any person with a hyphenated last names. so far, it looks like it needs to be a 2 stage process. anyway to rewrite it but not include every single possibility and still end up with a single pass
VIEWS ON THIS POST

118

Posted on:

Thursday 25th October 2012
View Replies!

How can i get the navigation of category? plz

I have the following fields in "Categories" table in MySQL: cat_id: The category ID (0 is Main Category) cat_title: The categoty title subcat: It's for sub category. for example: I want to get The navigation for "IRAQ". The navigation is: News > Middle East > Arab Countries > IRAQ > Chlorine bombs kill 8 in Iraq SQL Query: HTML Code: CREATE TABLE `categories` ( `cat_id` int(11) NOT NULL auto_increment, `title` varchar(50) default NULL, `subcat` int(11) default NULL, PRIMARY KEY (`cat_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `categories` VALUES (1, 'News', 0); INSERT INTO `categories` VALUES (2, 'Middle East', 1); INSERT INTO `categories` VALUES (3, 'Arab Countries', 2); INSERT INTO `categories` VALUES (4, 'IRAQ', 3);
VIEWS ON THIS POST

91

Posted on:

Thursday 25th October 2012
View Replies!

Fed up with MySql, can someone help me run this ****ing query?

I come from MS SQL 2005 background and I cannot get this MySql query to run.. INSERT INTO [lookup] ([Type],[Key],[Value],Rank,Status,Created) VALUES (1,2,'a',1,1,CURRENT_DATE()); Why won't this run\t (I know type, key, value are MySql "keywords" BUT I use the column definitions []). In MS SQL 2005, this works fine perfectly. Also, how the ***** do I run multiple sql statements in MySql query browser Which ****** developer wrote it so you can only run one statement at a time It's these little annoying ****** that make me switch back to MS SQL. Also, how the ***** do you declare variables in MySQL WITHOUT using a ***** stored procedure Here's what I want to run IN MS SQL 2005: DECLARE @Id INT; SET @ID = 0; DECLARE @ResultDate Date; SET @ResultDate = GETDATE(); Begin IF @ID > 0 BEGIN INSERT INTO User (Name, Created) VALUES ('test', @ResultDate); SELECT @ID = @@IDENTITY; END ELSE BEGIN UPDATE User SET Name = @Name, Modified = @ResultDate WHERE ID = @ID; END SELECT @ID as ID, @ResultDate as ResultDate Try and Convert that into MS SQL (INLINE SQL...not a function, not a procedure, INLINE SQL). Is this possible in MySql If it isn't, I'm done with open source crapola. People keep talking how Microsoft sucks, but at the end of the day...Microsoft actually has products that get the job done.
VIEWS ON THIS POST

170

Posted on:

Thursday 25th October 2012
View Replies!