PostgreSQL create table if not exists
In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL? ...
mysql
How can I join in data from a key : value meta table in mysql?
Say I have three tables in my database: CREATE TABLE `users` ( `user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR(16) NOT NULL ); CREATE TABLE `users_meta` ( `meta_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `user_id` INT NOT NULL , `key` VARCHAR(200) NOT NULL , `value` TEXT NOT NULL ); CREATE TABLE `posts` ( `po...
suggest a link with such concepts implemented - java and mysql
i need to write a program in java that will connect to a mysql database and execute some sql queries and display the result. suggest me a link with similar implementation / discussion of such concepts for guidance. ...
MySQL Connectors and Licensing
It occured to me that the MySQL Connectors(Java and .NET) are GPL licensed. Does that mean vendors(not developing for inhouse apps) will have to purcase a commercial license for proprietary software talking to a MySQL database through these connectors ...
Complicated SQL query - how do i do it ?
Hey Everyone I'm currently working on a small feature for a project and wondered how best to achieve the result, i have a table full of reviews with the score being out of 5 and being stored as score in the database, on one page i want to show how many of each score reviews there are, ie number of 5 star reviews, 4 star etc But i don't...
How do you set a date in a mysql update from a milliseconds since epoch value?
I want to fill out some null dates with default dates, which should be the epoch date. eg set updateDate = somethingtoconvertEpochDateToDateTime(numberofMillisSinceEpoch) ...
MAX on columns generated by SUM and GROUP BY
I'm trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the 'GROUP by' syntax. This is the original query, however it needs to be modified to work with grouping, sums and of course MAX. SELECT SUM(video_plays) AS total_video_plays FROM `video_statistics`...
Use mysql user in trigger
I'm creating a trigger in MySQL to let me know WHO (which mysql user) is doing an update on a specific table. I know MySQL has a function CURRENT_USER(), but is that going to insert the username at the time the trigger is CREATED, or at the time the trigger is CALLED? This is my trigger so far. I want to insert the username in the 'con...
Check if final record?
I am using mysql_fetch_array and I want to check if it's the final record. How can I do this? ...
MySQL floating point rounding error
Internally, what is going on with the floating point representation on the right to get false? mysql> SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0; +-----------------------+-----------------------------+ | 3.1415 + 0.9585 = 4.1 | 3.1415E0 + 0.9585E0 = 4.1E0 | +-----------------------+-----------------------------+ | ...
How can I make my mysql database records visible to search engines ? (part 2)
Part 1: http://stackoverflow.com/questions/1749520/how-can-i-make-my-mysql-database-records-visible-to-search-engines I have tried to figure out how to create a site-map for my classifieds website, however, I dont understand the way it works with php and database websites. I have understood that I need all links on a html page, right? ...
MySQL SELECT with LEFT JOIN and GROUP problem
I have a main table foo with dates and values, and a table bar with exception dates and values. If the date exists in the bar table, that value applies, otherwise the default foo value. So, for example: foo id date value 1 2009-11-19 25 2 2009-11-20 50 bar id date value 1 2009-11-19 50 To select the right va...
Can i force mysql to perform subquery first?
I have query like this: SELECT `table_1`.* from `table_1` INNER JOIN `table_2` [...] INNER JOIN `table_3` [...] WHERE `table_1`.`id` IN( SELECT `id` FROM [...] ) AND [more conditions] When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions. Is is poss...
Is this the right way to make my website google-friendly ?
I have created several Q about correctly mapping a website with a database so that google can index it properly. However, need more info. My website is a classifieds website (PHP). Users can search ads on my site. Searching for 'BMW' will bring up only the titles of all 'bmw' ads and display them as a search result. (like google kindof)...
How to manage a pageview DB
I am interested in tracking my users' pageviews on my site. Being that traffic is expanding very quickly, I am worried about robots, etc, and I also want to be able to use tracked data live to alter the user experience (so, while I do use Google analytics, it does not serve this purpose). What is the most efficient way to store my infor...
sql join query question (mysql)
I posted a question earlier- got it fixed but not i have another problem- i have a table with 4 records in it and my sql is only returning three records I tried two types of join this here one SELECT items.id, items.link, items.title, items.image, lists.user, lists.dated FROM lists, items WHERE lists.user = '506161637' AND lis...
How to concatenate multiple rows, where fieldnames are duplicated?
I am using SQL. Here is an example of my table: (There are actually thousands of rows like these, with varying course numbers.) Course No | Meeting Day | Course Name | Instructor 123 | M | English | Smith 123 | W | English | Smith 123 | F | English | Smith I need to...
collating stats into time chunks
I'm trying to work out an SQL statement for MySQL. I have a series of stats for a range of servers that are reported on a half hourly basis. I have a stats table with columns similar to this: server varchar(64), time datetime, bytesIn int, bytesOut int Now, I want to generate a series of reports that collate all of the bytesIn and by...
Plotting Histogram: How can I do it from scratch using data stored in a database?
I have some data stored in a database like this: TableName: faults Table: +------------+--------------+ | fault_type | total | +------------+--------------+ | 1 | 1 | | 2 | 3 | | 3 | 8 | | 4 | 2 | ............................. How am I supposed...
MySQL temporary vs memory table in stored procedures
What's is better to use in a stored procedure: a temporary table or a memory table? The table is used to stored summary data for reports. Are there any trade offs that developers should be aware off? CREATE TEMPORARY TABLE t (avg (double)); or CREATE TABLE t (avg (double)) ENGINE=MEMORY; ...