I have an array full of random content item ids. I need to run a mysql query (id in the array goes in the WHERE clause), using each ID that's in the array, in the order that they appear in the said array. How would I do this? This will be an UPDATE query, for each individual ID in the array. ...
Hi, I need to get an auditrail in mysql; is there a way to configure the binary log to get not only the changes, also the user, (connection) who made this change? Or do I have to use mySQL Proxy? TIA Peter ...
I've got a multi-user application having some critical data of the users. I am working on the backup and disaster recovery plan for the application.. Would like to know some best practices regarding this.. Thanks. ...
I'm using the following query, but I currently have to enter a value in every parameter for the query to work. Is there a way of making the parameters optional, so that 1 or more values will return a result? SELECT * FROM film WHERE day LIKE '%day%' AND month LIKE '%month%' AND year LIKE '%year%' thanks ...
How do you specify the Fill Factor when creating an index in MySql? ...
Is it possible to do a simple count(*) query in a php script while another php script is doing insert...select... query? The situation is that I need to create a table with ~1M or more rows from other table, and while inserting, I do not want the user feel the page is freezing, so I am trying to keep update the counting, but by using a...
SELECT avg(con_hits) as avg_hits FROM content WHERE con_type = 1 AND con_posttime < $twelve_hrs_ago AND con_refresh = 0 ORDER BY con_posttime DESC LIMIT 100 I would like it to go to the first record that was posted at least 12 hours ago (denoted by the $twelve_hrs_ago variable which has the suitable timestamp), an...
I may be barking up the wrong tree... However, what I have is a MySQL server that accepts connections only from a client with a valid SSL cert (see this link). This works great for example with Rails. I have my database on one server, and a Rails app that connects using the client certificate. Maybe not the fastest, but it works. The co...
Hello, I am building a "document storage" system for storing text files on my server. I was wondering if anybody has any recommendations or best practices on the best way to store text files remotely. The system will be user-based, each user can upload multiple text files. I want the text files to be stored in some fashion which is e...
I was just reading this post on ars technica "Is MySQL really this bad?" and was really surprised most people seem to agree that MySQL is indeed bad. I was just curious, does it really matter for most applications? Should everyone stop using MySQL? I'd really like to hear from people that have worked with various databases. ...
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables. My argument, as detailed here, is that the performanc...
I've developed an application at working using MySQL 5, that uses Views to access the major pieces of data. It turns out that our production server uses MySQL 4, which does not have Views included. Does anybody have a quick and dirty way to deal with this that doesn't involve rewriting all my code? ...
This is making me kind of crazy: I did a mysqldump of a partitioned table on one server, moved the resulting SQL dump to another server, and attempted to run the insert. It fails, but I'm having difficulty figuring out why. Google and the MySQL forums and docs have not been much help. The failing query looks like this (truncated for ...
okee, i followed all instructions i could find here and i could display all kinds of multilingual characters on my pages... the problem is in phpmyadmin the japanese characters are replaced by question marks, as in a bunch of ???? ??? pieced together i think there's a problem with my database's collation but i just wanted to verify that ...
What is the equivalent of varchar(max) in MySQL? ...
Hello I'm working on a site now that have to fetch users feeds. But how can I best optimize fetching if I have a database with, lets say, 300 feeds. I'm going to set up a cron-job to which fetches the feeds, but should I do it like 5 every second minute or something? Any ideas on how to do this the best way in PHP? ...
I am using CODBCRecordset (a class found on CodeProject) to find a single record in a table with 39 columns. If no record is found then the call to CRecordset::Open is fine. If a record matches the conditions then I get an Out of Memory exception when CRecordset::Open is called. I am selecting all the columns in the query (if I change th...
Hi All, Pls, could you help me on the following issues: I would like to migrate from oracle to mysql and one of important step is to replace the actual job built on oracle environment; Basically every day I receive from other oracle environment some 'oracle' dump files (mainly CTL or oracle table exports). Today my oracle jobs load the...
I need to add user roles and permission system into my web application built using PHP/MySQL. I want to have this functionality: 1- One root user can create sub-roots, groups, rules and normal users( all privilegis) . 2- Sub-roots can create only rules, permissions and users for his/her own group ( no groups). 3- A user can a...
Hi, I'm having problems with an SQL query used to display custom profile fields and any (optional) corresponding values. Here is the SQL query I'm using: SELECT pf.`id`, pf.`name`, pv.`value` FROM `profile_fields` AS pf LEFT JOIN `profile_values` AS pv ON (pf.`id` = pv.`field_id`) WHERE (pf.`site_id` = '0' OR pf.`site_id` = '%d') AND ...