So I need to track changes that happen on a Mysql table. I was thinking of using triggers to log all the changes made to it and then save these changes in another table. Then I will have a cron script get all these changes and propagate the changes into the Mssql database. I really dont expect a lot of information to be proporgated, but...
I'm planning to make a very simple program using php and mySQL. The main page will take information and make a new row in the database with that information. However, I need a number to put in for the primary key. Unfortunately, I have no idea about the normal way to determine what umber to use. Preferably, if I delete a row, that row's ...
I am writing a stored procedure where I have an input parameter called my_size that is an int, I want to be able to use in a limit clause in a select statement. Apparently this is not supported, is there a way to work around this? # I want something like: SELECT * FROM some_table LIMIT my_size; # Instead of hardcoding a permanent limit...
I'm building an application in Java to retrieve data from MySQL database. After executing a query I get a ResultSet and do the following to extract records: while (rs.next()) { for (int column = 1; column <= rm.getColumnCount(); column++) { row.add(rs.getObject(column)); } resu...
When running PHP in CLI mode, most of the time (not always), the script will hang at the end of execution for about 5 seconds and then output this: Error in my_thread_global_end(): 1 threads didn't exit It doesn't seem to actually have any effect on the script itself. Some web searches turned up blogs which suggest replacing the p...
What possible reasons could exist for MySQL giving the error “Access denied for user 'xxx'@'yyy'” when trying to access a database using PHP-mysqli and working fine when using the command-line mysql tool with exactly the same username, password, socket, database and host? Update: There were indeed three users in the mysql.user table, eac...
Is it possible to import csv data into mysql and automatically create the column names, as in can I create just the table, or must I create the table names as well? Is it possible to check for duplicate entries upon importing? I have an identifier field, but dont know how to make it so it will not be imported twice. How would you impor...
Does anyone have any idea what is wrong with this create statement for mysql? EDIT: now it states the error is near: revised VARCHAR(20), paypal_accept TINYINT, pre_terminat' at line 4 Thanks for the help everyone Still errors after using sql beautifier though CREATE TABLE AUCTIONS ( ARTICLE_NO VARCHAR(20), ARTICLE_NAME ...
I'm not sure how password hashing works (will be implementing it later), but need to create database schema now. I'm thinking of limiting passwords to 4-20 characters, but as I understand after encrypting hash string will be of different length. So, how to store these passwords in the database? ...
I'm importing data from another system to MySQL, its a CSV file. The "Date" field however contains cryptic of 3-digit time entries, here's a random sample set: > 540 > 780 > 620 > 965 What's this? obviously its not 5:40 and 6:20. But it's not UNIX either (I tried 1225295**XXX** before I realized the time range this represents is about...
I have this doubt, I've searched the web and the answers seem to be diversified. Is it better to use mysql_pconnect over mysql_connect when connecting to a database via PHP? I read that pconnect scales much better, but on the other hand, being a persistent connection... having 10 000 connections at the same time, all persistent, doesn't ...
i know how to import an sql file via the cli: mysql -u USER -p DBNAME < dump.sql but that's if the dump.sql file is local. how could i use a file on a remote server? ...
I want to store values in a bunch of currencies and I'm not too keen on the imprecise nature of floats. Being able to do math on them directly in queries is also a requirement. Is Decimal the way to go here? ...
Today I had an idea of writing a unit testing framework for stored procedures in MySQL. The full idea is written on a recent post on my blog. In short it goes like this: I want to automate my procedure testing, I want to use a standardized way to test my procedures. Unit testing is widely documented, and there are a zillion XUnit framew...
Hi all. I want to do a select in MySql that combines several columns... something like this pseudocode: select payment1_paid and payment2_paid as paid_in_full from denormalized_payments where payment1_type = 'check'; Edit: payment1_paid and payment2_paid are booleans. I can't use any other language for this particular problem than ...
This is the sequel to this question. I would like to combine three columns into one on a MySql select. The first two columns are boolean and the third is a string, which is sometimes null. This causes strange results: Select *, (payment1_paid && ((payment2_paid || payment2_type ="none"))) as paid_in_full from payments Note: payment1_...
I have a MySQL database table with a couple thousand rows. The table is setup like so: id | text The id column is an auto-incrementing integer, and the text column is a 200-character varchar. Say I have the following rows: 3 | I think I'll have duck tonight 4 | Maybe the chicken will be alright 5 | I have a pet duck now, awesome! ...
How do I take an efficient simple random sample in SQL? The database in question is running MySQL; my table is at least 200,000 rows, and I want a simple random sample of about 10,000. The "obvious" answer is to: SELECT * FROM table ORDER BY RAND() LIMIT 10000 For large tables, that's too slow: it calls RAND() for every row (which al...
Hello all, I am wondering what the best way is using php to obtain a list of all the rows in the database, and when clicking on a row show the information in more detail, such as a related image etc. Should I use frames to do this? Are there good examples of this somewhere? Edit: I need much simpler instructions, as I am not a program...
How to optimize queries which are already written? ...