If I have a column short_title in MySQL table and it is defined as UNIQUE, do I also have to add FULLTEXT for it to be searchable really fast? Or does UNIQUE already guarantee it will be searchable quickly (without full table scan)? Thanks, Boda Cydo ...
My head is hurting trying to figure out a SQLquery. I have a table of vote data with team_id, ip_address and date_voted fields and I need to return a count of votes for each team_id but only count the first 10 rows per IP address in any 24 hour period. Any help will be greatly appreciated and may stop my head from pounding! ...
In the same way SHOW CREATE TABLE tblname; brings back what was previously executed, is there anyway to view the SQL of an ALTER TABLE query? please help? ...
I have a query where I have a complex date expression as one of the columns. SELECT date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation FROM table I want to refer to that column in the where clause WHERE complex_date_calculation < NOW() But mysql chokes on it. 1054: Unknown column 'complex_date_calcul...
Hi all, I need to do a query like this: SELECT wposts.ID FROM posts wposts WHERE ( SELECT COUNT(ID) FROM surveys WHERE POST_ID = wposts.ID) > 0 but need it to be working ? ...
I have a table with a column of type varchar where the majority of rows have numerical looking values, that is, each string contains nothing but the digits 0 through 9. +------+ | n | +------+ | 123 | | 234 | | BLAH | -- This row is an exception to the rule. | 456 | | 789 | +------+ In MySQL, this works: SELECT * FROM t WHERE n...
I am fairly new to PHP and I have found that a lot of my functions require access to the database. The problem is I currently connect to the database in a file that I require at the top of every page like so: $db = new mysqli("localhost", "my_user", "my_password", "world"); The problem I find is then when I want to access it within a ...
Old server db = MySQL v4.0.21 New server db = MySQL v5.0.45 I am moving an app to a new server, and I copied over the database. The app sends an agenda for the day and whenever there are double & single quotes " ', they show up as question marks ? Comes up like this on the server it was moved to ?The Horror of Race: HBO?s True Blood? ...
How do I get the size of data in a BLOB field in the Result Set? (Using C++ and MySQL Connector C++) In order to read the data from the result set, I have allocate memory for it first. In order to allocate memory, I need to know the size of the blob data in the result set. Searching the web and StackOverflow, I have found two method...
DELETE FROM mytable WHERE column1.find("has_this_word_somewhere") ? ...
I found this nifty little order by condition that sorts strings of the type "First Last" nicely, even handling "First Van Damn" properly. "SUBSTRING(p.name, LOCATE(' ', p.name) +1) Now, I have some names in there like "Alfred E. Newman" and want the sorting to work properly for that name (ie it does not end up under E). Any help woul...
Can someone provide with me some code to connect to a MySQL database and check the whole of one column to see if it is less than 2? I've looked online and have found nothing which can help. Thanks so much, Lawrence ...
When I run the following command, the output only consists of the create syntax for 'mytable', but none of the data: mysqldump --single-transaction -umylogin -p mydatabase mytable > dump.sql If I drop --single-transaction, I get an error as I can't lock the tables. If I drop 'mytable' (and do the DB), it looks like it's creating the ...
Hello, I am developing a Python web app using sqlalchemy to communicate with mysql database. So far I have mostly been using sqlalchemy's ORM layer to speak with the database. The greatest benefit to me of ORM has been the speed of development, not having to write all these sql queries and then map them to models. Recently, however, I'...
Hello. I have this: $scCost = $row["gpsc"]; mysql_query(" UPDATE member_profile SET points = points-$scCost WHERE user_id = '".mysql_real_escape_string($userid)."' ") or die(mysql_error()); That takes do the user´s points - scCost. How do i check if the user can afford it or not? So, if the user...
I am working on an application that runs on multiple clients that talk to one main MySQL database. Additionally, we have a web application that uses the same MySQL database. Due to latency issues, all clients will have to run their own local MySQL database which should be a copy of the main MySQL database. The web application remains to...
Scenario : I have few duplicate contacts in a table. The duplicates are identified, I can just delete them but the problem is I don't want to lose the data the duplicate might have and the original don't. Any tips? Sample data : ID Name Email School Dupe_Flag Key 1 AAA a@a X 1 2 AAB JKL 1 3 BB...
I am using MySQL. Here is my schema: Suppliers(sid: integer, sname: string, address string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (primary keys are bolded) I am trying to write a query to select all parts that are made by at least two suppliers: -- Find the pids of parts s...
I know its possible to autoincrement values, but i was wondering if its possible to fill a field based on the value of two other fields. I have a table with the fields: CREATE TABLE pligg_links ( ... link_votes INT, link_reports INT, link_votes_total INT, ... ); Field link_votes_total should hold the value of field link_vote...
For some reason, this MySQL fails: CREATE SCHEMA IF NOT EXISTS `partB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `partB`; CREATE TABLE Employees ( ssn CHAR(11), Name CHAR(30), mlot INTEGER, PRIMARY KEY(ssn)) ENGINE = InnoDB; CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR(20), ssn CHAR(11) NOT NULL, PRIMARY KEY (did...