I'm guessing this a fairly easy question, but I've run into this problem a number of times and I can't find a solution either through searching (maybe I don't know what to search for) or trial and error. I have the following table structure and data: CREATE TABLE `table_a` ( `id` int(11) NOT NULL auto_increment, `table_b_id` int(11...
Hello again, I have a C# application over MySql, using MySQL Connector; I'm trying to make a DataReader request, the query executes fine, however, when trying to access a DateTime field, i'm getting MySqlConversionException {"Unable to convert MySQL date/time value to System.DateTime"} this is the prototype if (dr != null && !dr.Read...
Hello, I've been tasked with a SQL problem that is outside of the limited scope of sql knowledge that I have. I have the following problem. I have a table that currently looks like this: widgets --------- a a a b b c d d d I would like to have another table that has each unique value incrementall...
Hi - I haven't written C in quite some time and am writing an app using the MySQL C API, compiling in g++ on redhat. So i start outputting some fields with printfs... using the oracle api, with PRO*C, which i used to use (on suse, years ago), i could select an int and output it as: int some_int; printf("%i",some_int); I tried to d...
I have a PHP script that calls an API method that can easily return 6k+ results. I use PEAR DB_DataObject to write each row in a foreach loop to the DB. The above script is batch processing 20 users at a time - and although some will only have a few results from the API others will have more. Worst case is that all have 1000's of resul...
Say you have a table layout like the following: couses: id (INT), courseName (VARCHAR) instructors: id (INT), courseId(INT), instructor(VARCHAR) Create a query which will print the all the courses and if one instructor is present display their name, if two instructors are present print both of their names on the row in sorted ord...
I am currently looking for a quick way to sync my production db and my dev-db. I was thinking of doing it with something like this : mysqladmin -u <user> -p<password> <dev-db_name> | mysqldump -u <user> -p<password> --databases <production-db-name> --add-drop-table but it seems that it just prints all of the drump on the screen inste...
The AVG() function calculates column-means but how can I retrieve the mean of several values in the same row like so: SELECT MEAN(A.a, A.b, ... , A.n) FROM A; EDIT: sure, as cherouvim suggests I can do: SELECT MEAN(A.a + A.b + ... + A.n) / n FROM A; but I'm trying to find out if there's a leaner way. ...
I just moved my site to a new web host, and after changing the database-login file, it still can't connect. I get the following error: Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /usr/www/users/simpleof/index.php on line 91 I changed the password and tried a...
How can I iterate through a mysql result before AMFPHP passes the data to Flash? When Flash calls the service the following code gets the data $sql = sprintf("SELECT file_type, file_name .. $result = mysql_query($sql); return $result; Then I assume AMFPHP processes this result resource. For each file_name retrieved I need to run a ...
I'm trying to create some functionality that keeps an audit trail of how data in a given user form has been changed over time, with a dated audit at the foot of that page. For example: 02/04/09 21:49 Name changed from "Tom" to "Chris". I'm doing this by storing the data in it's present format in the session and then on save checking ...
Assuming I have a table foo where I have something like this: id, user_id, timestamp, some_value What I want to do is remove all rows that aren't the newest N per user. The deletion itself could be handled by a: DELETE FROM foo WHERE id NOT IN (...) so you could rephrase the problem into: How do I get the newest N(there might be le...
I am wondering what a .sql file looks like. How do I create one from mysqldump? ...
I'm setting up a large database that will generate statistical reports from incoming data. The system will for the most part operate as follows: Approximately 400k-500k rows - about 30 columns, mostly varchar(5-30) and datetime - will be uploaded each morning. Its approximately 60MB while in flat file form, but grows steeply in the ...
I'm building an app that will store some of our clients details, things like usernames / passwords, information that we need to remember and keep secure. What's the best method for storing this information securely? ...
Hi, I am using MySQL 5.0.51b on microsoft windows xp. I am trying to load data from zoneinfo files(generated by library downloaded from here) to database tables as described here. Now i am not able to find where would i get this "mysql_tzinfo_to_sql" program for windows. I tried executing it on mysql command line client but no success...
hello, I'm developing applications which can be build partly from modules. For example I would be able to develop some online community which contains the modules 'forum', 'blog', 'gallery', etc. Currently I have one large database ERM containing all the tables of all modules with foreign-key connections and I'm using dbwrench to build...
Can I write a custom function that I can call when making mySQL queries eg. My database table has fields 'filetype' and 'filename'. I want to perform a function on filename before returning the result. $query = "SELECT filetype, MY_FUNCTION(filename) FROM table.."; $result = mysql_query($query); return $result SO $result now has the ...
I am using - MySQL v 5.0.22, PHP v 5.2.0, Windows 2003, Apache. My skills - Basic PHP knowledge, Basic MySQL. What is the best way for me to write and use a mysql user defined function in my PHP scripts? ...
What is the best function to run my strings through to ensure that MySQL injection is impossible? Also, will it require running it through another function on the way out to make it display correctly? See also Are Parameters really enough to prevent Sql injections? C# Parameterized Query MySQL with in clause Can I protect aga...