Say I am not sure if table t in database d has a column x or not. Perhaps this is because some of the databases the application uses have been upgraded and some have not, so some have t.x and some don't. Now say I just need a simple query to get a row from d.t and I want the value of d.t.x if the column exists and some default if not. ...
I have a social network site I have been working on for a couple years in PHP/MySQL, I am now re-buidling the whole site from scratch again though. This time around I would really like to add in the ability for users to set a timezone for times on my site. So with that said, I am asking on a guide from start to finish of the steps I ne...
Hope this format is better... thanks to OMG ponies, Peter Lang, and astander for their patience in answering my first attempt. I'm setting up a game in which each USER selects one CONTESTANT every WEEK, kind of like picking which football team you think will win. I need to create a view that has, for each combination of USER, CONTESTAN...
We have a solution that uses JBoss and Oracle, in the datasource configuration file it makes reference to a OracleXAExceptionFormatter class. We are migrating from Oracle to MySQL, my question is does JBoss have an equivalent exception formatter class for MySQL or at least a generic equivalent? <datasources> <xa-datasource> <jndi-...
At first I was thinking I was running into an issue with cfqueryparam and mysql. However when I change substitute them with static values I get the same error. This is stumping me, I'm too used to Microsoft SQL Server I guess. Any help would be greatly appreciated. Here's the query, this works perfectly in mySql query browser, but f...
I have a database full of phrases (80-100 characters), and some longish documents (50-100Kb), and I would like a ranked list of phrases for a given document; rather than the usual output of a search engine, list of documents for a given phrase. I've used MYSQL fulltext indexing before, and looked into lucene, but never used it. They bot...
Question 1) Assuming a user has a timezone setting saved as 'America/New_York' in my application, would I just use this on a page where I need to show the correct time with timezone offset and daylight savings applied <?php $time_zone = 'America/New_York'; date_default_timezone_set($time_zone); echo date('D,F j, Y, h:i:s A'); ?> OR s...
I have no idea what formulas I should use to create stock-market for my players that they could play it. I use php+mysql. Maybe you can throw any good ideas? I'll be grateful. ...
I am moving to a new server and thinking about how to keep my 2 MySQL server data consistent is causing me to lose both sleep and hair. I was thinking about using a Master-Master setup to ensure that I lose nothing in the process. How viable is that. Any potential gotchas? ...
I use date_default_timezone_set($_SESSION['time_zone']); to set a users timezone on a page, I am wanting to know, how do I save current time to mysql in php with the current UTC date and time? Also should I be storing it as a timestamp? My existing site used datetime fields and when I saved a new item to mysql I could just use now(...
/** * */ package ORM; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * @author Gwilym * @version 0.0 */ public class DatabaseConnection { private String userName=""; private String password=""; private String host=""; Connection conn; /** * @param userName * @param passwo...
i have some delete queries to run against some pretty huge table (~100 GB), and i want to optimize them as much as possible: delete from table1 where column1 < date_sub(now(), interval 100 hour); column1 is a datetime column, i assume making an index for this column will speed up the deletions. besides that, anything i can do here? wi...
I am trying to get a document from Excel or OpenOffice that contains UTF 16 characters into MySQL, but I can't find the best way to export the document so that phpmyadmin will read it. I can export out of NeoOffice as "unicode" format, but the closest option in MySQL is ucs2. When I try that format, it just spins and thinks. UTF8 doesn't...
Hi, I have a mysql table containing news data and categories and i'm trying to slect all data within a certain category. I didn't think far enough ahead so i'm currently not using a normalised table at some point i may go back and re-write that portion to use a normalised table. Here is my table structure +---------+------------+----...
What's the difference between the two types of column/table collations? Which is preferred and why? Any thoughts? ...
I have a situation where I need to group data by a portfolio name, then sort through the data and display check boxes. Here's the table structure. Table A -------- portfolio_id portfolio_name Table B ------- file_id portfolio_id (Foreign Key fk_port_id references portfolio_id in table A) file_name Basically Table A links to table B ...
Hello! I'm hitting the DB for a 100 records from a MySQL DB that have a DateAdded (timestamp) column. I'd like to find a SMART way to display the records as follows in Coldfusion, w/o hitting the database multiple times. Today: - records..... Yesterday: - records..... Earlier This Week: - records..... Earlier This Month: - records.......
How do I execute a query like this using PHP: SELECT [name] FROM [items] WHERE [ID]=1, [ID]=2, [ID]=3 and have MySQL return me all 3 rows? ID name -- ---- 1 John 2 Jane 3 Jack ...
I'm writing a small database adapter in Python, mostly for fun. I'm trying to get the code to gracefully recover from a situation where the MySQL connection "goes away," aka wait_timeout is exceeded. I've set wait_timeout at 10 so I can try this. Here's my code: def select(self, query, params=[]): try: self.cursor...
MY previous site used DATETIME fileds in MySQL to store all dates/times. On my new site I am adding in timezone capability so I am wanting to store all submitted times at UTC timestamp, I am a little confused now though after reading another post on SO, I read that the timestamp is updated everytime you update the record, if that is the...