Ok, I have two tables that I am joining and doing a select on to get three fields back (timestamp, message, and articleNum). Now, this data is basically a log of when an article was created and modified, I have to write the logic to process those three fields I got back. In order to figure out when an article was created and/or modified ...
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys. I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support fo...
I'm wondering if it's worth the server time when updating a record to retrieve the existing record, loop through the fields checking for changes and only putting the changed fields in the update query? (I am using MySQL & PHP.) The main reason for doing this is to reduce the size of the update query for change log purposes. Normally the...
Hi, I have a problem binding booleans using mysqli_stmt::bind_param in PHP5. The SQL query is the following: insert into nvp_notes (subject,messageid,receivedate,read) values (?,?,?,?) Where 'read' is a tinyint, either 0 or 1, as I've had issues with bit using mysqli. So the types that I list in bind_param are: $stmt->bind_param('sds...
I know how you do it from the console, and I know you can execute console commands with php, but would there be a way to recursively dump a database into a file, and then restore it from that file later, just using php? I want it to be able to work on windows and nix servers. I am guessing it would need to loop through the tables and ro...
Hi, I have a table with 2 fields (name, confirm). confirm is of type tinyint because I want to put in 0 or 1 Now I want to put 1 in confirm; is this statement correct? if($row['confirm']==0) { $query = "INSERT INTO opt (confirmed) values('0')"; echo 'The user selected options has confirmed'; } ...
I'm trying to find a simple MySQL statement for the following two problems: I have 4 tables: Employees, Customers, Orders, Products (Each entry in Orders contains a date, a reference one product, a quantity and a reference to a customer, and a reference to an Employee). Now I'm trying to get all customers where the volume of sale (quan...
Hi, I'm looking for a Unix shell script that will truncate all tables in a schema. A similar question was already asked, but I have some additional requirements which makes none of the provided answers satisfactory: Must be a Unix shell script (i.e. no python, perl, PHP) The script must truncate the tables in an order which respects f...
How can I evaluate whether a column contains any non-ascii characters in mysql? In this case the charset is actually latin1, so I'm just looking for high-byte chars. I tried this: select * from company where ticker regexp concat('[', x'7f', '-', x'ff', ']') but this returns this error: ERROR 1139 (42000): Got error 'invalid charact...
I am having a problem with ActiveRecord inserting the DATETIME as per the documentation; "Active Record automatically timestamps create and update operations if the table has fields named created_at/created_on or updated_at/updated_on." I have a column named created_at but ActiveRecord is not inserting the DATETIME. My insert query looks...
Hi, I have a nested SQL query that is exhibiting results which I can't understand. The query joins the PARTNER and USER tables via the PARTNER_USER table. A partner is basically a collection of users, and the objective of this query is to figure out when the 20th user registered with the partner that has ID 34: select p.partner_id id, ...
If I have a USER table like class | age -------------- 1 20 3 56 2 11 1 12 2 20 Then I can easily get the youngest user in each class via select class, min(age) from user group by class; Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in...
When doing an ALTER TABLE statement in MySQL, the whole table is read-locked for the duration of the statement. If it's a big table, that means insert or update statements could be locked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process? ...
Are indices (indexes) defined as UNIQUE case sensitive in MySQL? ...
I know: Firebird FIRST and SKIP; MySql LIMIT; Sql Server ROWNUMBER(); Does someone knows a SQL ANSI way to perform result paging? ...
Hi , i read one post regarding this topic, i also want to do the same, i have my database on remote linux machine, and i want to connect using ssh and php funcitons,(i am currently using ssh2 library for that) i tried using mysql_connect, but it gives me cant access (although i have granted permission) when i tried using this function:...
I originally had my field set as latin1_swedish_ci, which I changed to utf8_general_ci (both field and table) and then found my query went from ~1.8 seconds to ~3.3. I have an index on the field and have even recreated the index (delete then add). The field is used in an order by clause. Any ideas if there might be a problem or is this ...
I have the following code: public Object RunQuery(String query) throws Exception{ System.out.println("Trying to run query"); Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); System.out.println("Got Statement"); rs = stmt.executeQuery(query); System.out.prin...
Hi, I've created a MySql function and would like to raise an error if the values passed for the parameters are invalid. What are my options for raising an error within a MySql function? Thanks, Don ...
This is the MySQL table structure that I have: itemID (BIGINT) userID (BIGINT) wasAdded (DATETIME) I need to get the number of items introduced in the current day, by the X userID What would be the most efficient way to do this? Thanks ...