mysql

Formating an SQL timestamp with PHP

I have a mySQL database with a timestamp field. It currently only has one entry while I'm testing, it is 2010-02-20 13:14:09 I am pulling from the database and using echo date("m-d-Y",$r['newsDate']) My end result is showing as 12-31-69 Anyone know why? Edit: editedit: disregard that edit... the FTP addon for notepad++ timed ou...

SQLAlchemy: checking unicode string's validity for a given type of db column

Hi folks! Am developing an extract-transform-load script with sqlalchemy. Scenario is as follows: take 30+ mln text file (csv, tab-delimited or any other...). parse it and generate file, suitable for 'Load data infile' mySQL import command (as described http://dev.mysql.com/doc/refman/5.0/en/load-data.html ) From within script, disabl...

Copy the contents of a file to one field in mysql

Is it possible to copy the contents of a file into a field in a mysql table from the command line? Either at the command line or the mysql prompt. I don't want to have to write a script if there is an easier way. Ideally, I'd like something like: UPDATE MYTABLE SET MYFIELD=READ_CONTENTS_OF_FILE('myfile.txt') WHERE ID=1234; Obviously ...

How can I view the contents of a prepared statement?

Hi, I'm working on learning to use prepared statements with mysqli in PHP and usually, if I'm having a problem with a query I just echo it to the screen to see what it looks like as a first step. How can I do that with a prepared statement? I'd like to see the SQL statement after the variables are substituted. ...

MySQL full-text search in Rails?

When I added search functionality to my first Rails app, I used Sphinx, after reading that using MySQL's built-in fulltext search was a bad idea. While Sphinx works well, it's a bit complicated to set up, and I feel there's too much overload for the simple searching functionality I require in my app. Searches aren't performed very often...

Not inserting correct values into MySQL database

I'm having some trouble using MySQLi to insert values into a database. I don't get any errors, but the values being inserted are not correct at all. One of the TEXT fields is always empty, and the other one always has the value "ý". The INT field always contains the value 50396416. I am using utf8_general_ci. CREATE TABLE events ( i...

MySQLdb within python2.5 virtualenv

I have a Fedora 11 box with MySQL server. Fedora 11 uses python 2.6 internally and python 2.6 is automatically installed on the box. I have created a python virtual-env for version 2.5.5, so that I can run turbogears 1.x application. I have MySQLdb rpm installed on the box (and it works fine with python 2.6). When I import MySQLdb fr...

Keeping SQL Dry

Here is a MySQL query I'm running: -- get the sid of every supplier who does not supply both a red and green part SELECT Suppliers.sid, Parts.color FROM Suppliers JOIN Catalog ON Catalog.sid = Suppliers.sid JOIN Parts ON Parts.pid = Catalog.pid WHERE Suppliers.sid NOT IN ( SELECT Suppliers.sid FROM Suppliers JOIN Catalog ON ...

SQL: Help with a nested queries

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) bold indicates primary key. I want to write a query to find all suppliers who supply every part. Here are two queries I have already: -- get all parts for a giv...

Having a problem getting mysqli_query to execute.

Here's the problem: I started a swap today to use mysqli. No biggie, just had to change a few statements. Everything went fine, no errors... Except that I can't get it to execute any queries at all. I have double and triple checked my syntax. I even started creating situations where it SHOULD return an error (Trying to get it to INSERT t...

MySQL: SELECT(x) WHERE vs COUNT WHERE?

This is going to be one of those questions but I need to ask it. I have a large table which may or may not have one unique row. I therefore need a MySQL query that will just tell me TRUE or FALSE. With my current knowledge, I see two options (pseudo code): [id = primary key] OPTION 1: SELECT id FROM table WHERE x=1 LIMIT 1 ... and t...

java cms(opencms) and my SQL configuring problem

i have installed open cms on my local machine and it perfectly works fine. But in order to work it corre,ty it is mentioned that i have to modify the my.ini file and set max_alllowed_packed site to 32 I have done it and it works perfectly fine but can i modify this file if i use a third party hosting provider for tomcat and mysql?? ...

Automate converting excel xls file to excel xml

I have a web page where a user can upload xls files. When he uploads a file, i want to automate converting the excel file to excel xml. I want to convert it to an xml file as i think it will be easy to handle the data. Is it possible to write my own php function to do that? ...

Complex SELECT statement; one-to-many all details in one row

There are two tables: Products ID (Primary Key), ProductName PlannedByMonths ProductID (Primary Key) (Link to the Products table many-to-one), MonthNumber (Primary Key), QtytoProduce, How to write SELECT statement to retrieve results in the following format? ProductName, QtytoProduceMonth1, QtytoProduceMonth2, QtytoProduceMonth3,...

How to put images on a 3 by 3 table?

Hi, I am using php/mysql, and have a database table with image url. I would like to know how can I put them on a php page with a 3 x 3 table, such that each td will show a different image based on the image url from the database? I want to create something like this, where the alphabets are the images: |a|b|c| |d|e|f| |g|h|i| So far...

Optimizing WordPress SQL query for older posts list

On my WordPress site, when a user pages far back in the list of posts, the queries end up taking a couple seconds. I'd like to bring this down. Here's the query that's being executed: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_s...

How to prevent empty values of enum column in MySQL

mysql> CREATE TABLE foo ( f ENUM('a', '123') ); mysql> insert into foo(f) value(3); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from foo; +------+ | f | +------+ | | +------+ How to make it produce a failure when inserting a value out of range? ...

What's the difference between the two DDLs in MySQL?

create table categories(a integer unsigned NOT NULL,b integer unsigned NOT NULL,primary key(a,b)); And create table categories(a integer unsigned NOT NULL,b integer unsigned NOT NULL,unique (a,b)); Are there any functional difference? ...

Select order products in MySQL from muliple tables

I have 3 tables: orderProducts (orderId, productId, productValue), products (productId, productName) values (productId, productValue, productValuePrice). I need to select order products (product id, name, value, price) with defined orderId. How to do that with one MySQL query? ...

Django: sqlite for dev, mysql for prod?

Quick question: is it a good idea to use sqlite while developing a Django project, and use MySQL on the production server? ...