export query result as CSV through PHP
Say I have stored a query in a variable called $query. I want to create small hyper link called "export as CSV" on the results page. How do i do this? ...
mysql
How can I use a mySQL SELECT IF statement row to JOIN another table?
I am using an IF statement in my mySQL SELECT statement and depending on the returned result from the IF statement I would like to join another table. Eg. SELECT name, IF(apple = 'brown', color1, color2) AS ripeness FROM apples JOIN apple_type ON apple_type.color = ripeness My problem is that I am receiving the error msg: Unknown colum...
mysql muliple queries in one statement
I've looked around on stackoverflow for a similar question, but haven't found exactly what I was looking for, so here goes. In myPHPAdmin you can have mulitple queries in one statement and it executes it for you, eg:' UPDATE `test` WHERE `test2` = 4; UPDATE `test` WHERE `test4` = 8; UPDATE `test` WHERE `test8` = 1; Now if I try to do ...
Time & Date Stamp in a MySQL table row
Hello, I would like to put a time & date stamp on each row added to a MySQL table. If I understand correctly, I need to create a column for the time & date stamp. How do I do that for the CREATE TABLE query below? "CREATE TABLE `$table` (id INT(11) NOT NULL auto_increment, site VARCHAR(1000) NOT NULL, actions1 BIGINT(9) NOT NULL, act...
Error with MySQL syntax using REPLACE INTO
I am getting syntax error with the following statement REPLACE INTO users (screenname, token, secret) VALUES( '$screenname', '$token', '$secret' ) WHERE 'screenname' = $screenname The table has a primary key named id, which auto-increments. ...
MySQL extremely slow under Snow Leopard
Hi. I've installed MySQL 5.1.40 Source distribution (64-bit executable x86_64) following these instructions by Hivelogic and the mysql (2.8.1) gem using ARCHFLAGS="-arch x86_64" . I'm using it for Ruby on Rails development and my problem is that even if MySQL is working in every way, it is extremely slow. Take this comparison with sql...
What else should I be doing to sanitize user input?
Recently, I had an audit run on some of my sites by a client. One of the things they came back with was that I could be sanitizing the input data a little better as people could still cause potential harm to the database. The function below is what I am currently using (a leftover from the old developer) but I cannot see where the pot...
Order by in mysql using second table
Hi, I have two tables, one is a list os stores and attributes, the second is a list of allocationsa based on these attributes. The attribute table (stores_metadata) | key | store_key | field | value | 1 | 1 | size | Large | 2 | 1 | dist | Midlands | 3 | 2 | size | Medium | 4 | 3 | dist | Sout...
Return random rows from the best ones (i.e: 100 rows -> best 10 -> get 5 random)
Hello all again! Situation: A table with lots of photos, classified and with votes. I'm currently doing this: SELECT n.nid, n.title, ....... order by v.value desc limit 0,5 What I want is to get a random resultset after sorting my rows by its votes. Now, a table with hundreds of records is giving me the best 5 rows. Always the same 5...
wrong return value by mysql_num_fields in C++
For table creation and insertion i am using these string. Create table mystudents(sname varchar(50),sno varchar(25),mark1 numeric,mark2 numeric); insert into mystudents values('lala','tk001',100,100); int status = mysql_query(hnd,sql); if (status) { printf("Could not execute statement(s)"); mysql_close(hnd); /...
how to determine which PHP code opens MySQL connections that aren't getting closed
We have an application that is comprised of a couple of off the shelf PHP applications (ExpressionEngine and XCart) as well as our own custom code. I did not do the actual analysis so I don't know precisely how it was determined, but am not surprised to hear that too many MySQL connections are being left unclosed (I am not surprised bec...
PDO constructor very slow (mysql)
This bit of code is taking almost a half second to execute. Could somebody help me with some reasons this could be happening and some possible solutions? If it matters, the DB is hosted by amazon rds $this->_connection = new PDO( $dsn, $this->_config['username'], $this->_config['password'], ...
MySQL: select the closest match?
I want to show the closest related item for a product. So say I am showing a product and the style number is SG-sfs35s. Is there a way to select whatever product's style number is closest to that? Thanks. EDIT: to answer your questions. Well I definitely want to keep the first 2 letters as that is the manufacturer code but as for the p...
Two server setup for online store(Magento)
I'm developing an online store with Magento. The site will be high traffic and will need a two server setup, Testing and Production. My question is how do online stores, specifically Magento handle this? The idea I had is: Take Production site offline Empty Testing database Copy data from Production database. (Maybe ) Test Copy file...
rename tables columns in mysql
Hi, I would like to rename a table column in MySql and also have the name updated in any triggers, stored procs, etc. that reference the column. This does not appear to happen by default when using the alter table command. If a tool exists that can perform such a refactoring, it would be great if it could generate an SQL script that ap...
What's wrong with this query?
UPDATE `t` SET `col_x` = (SELECT `col_x` FROM `t` WHERE `col_y`='123456') WHERE `col_y`= '456789' MySQL version 4.0.27 I tried few changes - with LIKE '%123456%', without backticks mysql seems to not know subquery. it's error is "You have an error in your SQL syntax ..." thanks Error message: #1064 - You have an error in your SQL...
MySQL server has gone away - in exactly 60 seconds
I recently discovered that a sql query that was running fine earlier is now timing out after 60 seconds and throwing an error. The query is slow but runs as part of a nightly job so that's not a problem in itself (so please don't suggest I optimize it). I'm able to reproduce the error consistently by running "select SLEEP(120);" from P...
Painless converting from ODP rdfs to MySQL db?
I've downloaded ODP structure and sites archives. How can i convert them from rdf it to MySQL. The problem is that ODP rdfs are very buggy. And it's imposable to parse them with strict parser. I've found http://sourceforge.net/projects/dmoz2mysql/ but it's crashes after 30 minutes with longlonglong sql dump so i' unable to see even err...
Two If selection while a select query
Hello I have this query : SELECT o.id, o.id as oid, o.id as orderId, o.cid, o.date, o.state, o.price, o.currency, o.lastChange, o.url AS permalink, o.period, o.bloggerId, o.bloggerShare, o.offerValidity, o.rebate, o.cid, o.reason, o.bidReason, o.bidDate, o.bidPeriod, o.rate, o.lastChange2, o.permalinkDate, o...
Sphinx without using an auto_increment id
I am current in planning on creating a big database (2+ million rows) with a variety of data from separate sources. I would like to avoid structuring the database around auto_increment ids to help prevent against sync issues with replication, and also because each item inserted will have a alphanumeric product code that is guaranteed to ...