mysql

Quickly add a copy of a column to a MySQL table.

I need a fast way duplicate a DATETIME column in a table and give it a new name. I have a column named myDate in my table called myResults, I need a query to make a new column in the table called newDate which has the exact same data as the myDate column. Is there a faster way to do this than by doing the obvious 2 step approach o...

WHERE clause causing table to omit LEFT JOIN rule

Hi, I am essentially attempting to modify this stored procedure. Modified stored procedure: CREATE PROCEDURE sp1(d1 date, d2 date, client INT(10)) declare d datetime; create TEMPORARY TABLE foo (d date NOT NULL, Amount INT(10) DEFAULT 0); set d = d1; while d <= d2 do insert into foo (d) values (d); ...

accessing database values in javascript

Well The situation is we are using mysql.We have written the queries and extracted the result from the assoc array.Now how do we access the result from javascript. $query = "SELECT firstname FROM employees; $result = mysql_query( $query , $resource ); $value= mysql_fetch_assoc( $result); extract( $value ); Now does the javasc...

MySQL - Counting rows and left join problem

I have 2 tables, campaigns and campaign_codes: campaigns: id, partner_id, status campaign_codes: id, code, status I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign. I have the following SQL, but of course the WHERE statement e...

mySQL Table Structure for a User-based website.

I am developing a website that has user profiles, accounts, account settings, inboxes, etc. just like Facebook or LinkedIn and I'm wondering how to set up the mySQL tables for it. Should I create a table for every user for each function (profile, inbox, etc.)? ...

How do you coerce float values in MySQL for classic ASP scripts?

I have been charged with maintaining a legacy classic ASP application. The application uses an ODBC system DSN to connect to a MySQL database. We had to recently update the servers to satisfy some licencing requirements. We were on Windows, with MySQL 4.x and the 3.51 ODBC driver. We moved to a Linux machine running MySQL 5.1.43 and ...

mysql cli: how to list all databases I have permisions to create/read/update/delete?

What command do I use on a mysql command line to see all the databases on some database server that I have permissions to? Specifically I am looking for the DBs that I have full CRUD permissions to. ...

Should I also index columns included in a PRIMARY KEY?

This question suddenly popped into my head... I have a table that ties two other tables together based on their ID. The CREATE TABLE looks like this: CREATE TABLE `ticket_contact` ( `ticket_id` INT NOT NULL, `entity_id` INT NOT NULL, `notify` INT NOT NULL DEFAULT 0, PRIMARY KEY (`ticket_id`, `entity_id`...

mySQL UPDATE query returns "0 rows affected"

I have this query: UPDATE phonecalls SET Called = "Yes" WHERE PhoneNumber = "999 29-4655" My table is phonecalls, I have a column named PhoneNumber. All I want to update is a column named Called to "yes". Any idea what I am doing wrong? when I return my query it says 0 rows affected. ...

Multiple row INSERT into SQL, from text file

Hi, I have a table with 3 columns, 2 of which are auto-incrementing. I have a text file with various content that I would like to insert into the 3rd column. In that text, values are separated as such: "value1", "value2", "value3", "etc", How would I go about inserting all those values into a specific column, while being able to use th...

Selective Export of MySQL database

I have a webapp that spans many different users, each with selective permissions about what they are able to see. The app is built on top of a MySQL database. One feature I am interested in providing to my "power users" is a sql dump of all their data so that they can run off and do their own things with it. Now I can't just use mysql...

Help with database design

I'd like some advice designing my database tables for a small project I'm working on. Assuming in this systems I have articles, subarticles, and comments. Each article can have subarticles. Both articles and subarticles can have comments. I considered having an autoincrementing int primary key for each table (i.e. articleId, subarticl...

Reusing MySQL results

Hi there, I'm having somewhat theoretical question: I'm designing my own CMS/app-framework (as many PHP programmers on various levels did before... and always will) to either make production-ready solution or develop various modules/plugins that I'll use later. Anyway, I'm thinking on gathering SQL connections from whole app and then ...

Can MySQL convert a stored UTC time to local timezone?

Hi Can MySQL convert a stored UTC time to local timezon:ed time directly in a normal select statement? Let's say you have some data with a timestamp (UTC). CREATE TABLE `SomeDateTable` ( `id` int(11) NOT NULL auto_increment, `value` float NOT NULL default '0', `date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMAR...

Displaying number of results from a MySQL db...

I have a classifieds website, and it is using MySQL as a database. My goal is to show the number of private, company and total ads in the db with the search-term entered in a text-input inside the form! The Problem: There are two types of ads: Private, and Company. The user may specify which to display, or both, so thats three options....

mysql: possible loss of UTF data from char to text field type conversion?

Hi, I have a database that I use for a foreign language / vocabulary web application and was interfacing with it through phpMyAdmin (and of course php). I had about a thousand rows for Arabic text, which had been put into a char field. I wanted to expand the size of my entries, so I read that a text field could get me past the 255 cha...

Check for equality on a MySQL Float field

I have a Joomla system and I'm trying to change the search so that it correctly finds floating point values in the database. So, I have a query that is built at runtime that looks something like this: select 'column1' from 'some_table' where 'some_float_field' <=> '2.18' This doesn't work, it never matches anything, even though I see...

proper way to delete album and its media and sub album and its media

what would be proper way to delete albums hierarchical data with its media. should i just start looping and find all the sub album id and delete thous and make query to delete all the media belonging to that album id. that will be loads of query execution if data is alot or albums are alot for a user. what your device is on this. my...

MySQL - SELECTing based on an IF-statement

If I have a MySQL-table consisting of the fields one, two, three and four can I then somehow use the IF statement to receive the field four, only when a certain condition is true? Do I have to have two sets of SELECT statement? Can I do (in pseudocode): SELECT one, two, three IF (1 == 1) THEN four ENDIF FROM table Or do I somehow hav...

Importing text to MySQL: strange format

I'm importing some data from a .txt file into a MySQL database table, using mysqlimport. It seems to import OK (no error messages) but looks very odd when displayed, and can't be searched as expected. Here are the details. The original text file is saved in UTF-8, with records that look (in a text editor) like this. The second field inc...