Using MySQL, I've got a set of tables with 1->N relationships from member -> items -> photos. I'm trying to select a list of a member's items + some columns from the first associated photo (by first I mean the photo with the lowest sort order). Currently I have this query which works, but seems overly complex. Is there an easier way a...
I have a MYSQL table that holds events information. I need a mechanism that updates the status of each event according to its date ("over" if its a past date). What´s the best way to do such a thing? Update the status of the event when the any user logs in (useless routine for most users), create some sort of internal task (like a cron j...
Is there any C++ Library api available which converts Unix timestamp to XML datatype datetime eg : http://books.xmlschemata.org/relaxng/ch19-77049.html I am looking to convert into pattern : 2001-10-26T19:32:52+00:00 I also have access to mysql, so I can get hold of : mysql> select now(); +---------------------+ | now() ...
I have table structure like that: users smf, user_id, group_id, status With every query the value of smf should increase with a current max value + random number. Here is the query INSERT INTO users SELECT(SELECT MAX(smf) + FLOOR(RAND() * 15 from invoices ), 0, 0, '1'' My first question is about that how to keep concurrent integr...
I have incorporated ActiveRecord into a script I am writing to process some data in a MySQL database. In my development environment, using Mac OS X, everything works fine, however, when I attempt to deploy the script in a Linux environment, I keep getting the following error: /activerecord-3.0.0/lib/active_record/connection_adapters/mys...
I want to write query like this: SET @id := 0; UPDATE table1 SET field1 = @id + 1, @id := @id + 1 And get error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@id := @id + 1' at line 3 How write this query correctly? 1...
Trying, but failing, to do this: DELIMITER // CREATE PROCEDURE render_denorms() BEGIN SOURCE /path/to/some/sql.sql; END // DELIMITER ; Seemed like a rather elegant solution to allow a third party to call SQL which we can maintain easily. Not happening? Cheers. ...
SHOW INNODB ENGINE STATUS shows me this and calls it deadlock: LATEST DETECTED DEADLOCK 100923 22:29:21 * (1) TRANSACTION: TRANSACTION 0 5335752, ACTIVE 0 sec, OS thread id 7992 inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 3 MySQL thread id 26, query id 14422 lo...
I'm using MySQL. I have 3 tables I'm trying to connect in a query and I can't see what I'm doing wrong with the following query: Table 1: books (list of book information) Table 2: bookshelf (list of books a member owns) Table 3: book_reviews (list of book reviews) I want to generate a list of all books a user has in their bookshelf, a...
Hello all, I have a simple html input textbox in a very simple form. the information form this form is transmitted to a mysql database with an sql string. Everything works slick, except when someone types " or '. I don't want to limit the users as to what they can type. Should I do a find and replace to the string before I run the que...
Hi guys, I was wondering if anyone could take a look at this code and help me come up with a better way to do this if there is one. I am using jQuery append/remove to add and remove list items. My code first selects any existing rows from the db and displays those in the list, and then the user can add or remove any elements from the l...
I have two tables user and location user has id(primary) and location fields/columns location has id(primary) and city column Now i wish to relate the two tables by user.location with location.city How can i do it considring city is not a primary key but is unique. I am using cakephp 1.2. Also in mysql can i relate/join tables wit...
Hi Guys, I have a problem with MySql primary keys, it's for my project i'm working on now! i have to have a unit name(not a number)as a key in my database, it is a combination of letters and numbers, i can't use any primary key in INT type, where it increments as usual! what is better? should i stick to old INT, auto increment way? ...
Hi, I have the following sql query which, for some strange reason, it doesn't display the same results. The back end is mysql database (innodb), the front end an Excel GUI. When run on Excel 2003, the following query shows all the correct records. However when run on Excel 2010, the results are different ! Same mysql database, just ...
In an event scheduler i need to transfer data from server1.DB.TableName to server2.DB.TableName I guess in sql server it can be done via linked server, did anyone implement using federated storage engine in mysql? if so pls. give your suggestions. Thanks in advance. ...
I have a table containing shipping rates and maximum weights, eg: max_weight shipping_cost 100 1.50 250 3.00 500 5.00 1000 8.50 30000 12.50 I want to be able to get the shipping rate based on the weight of the order, where the weight is less than max_weight in the table. So if th...
// Database Settings define('DB_HOST', '**'); define('DB_PORT', '**'); define('DB_USER', '**'); define('DB_PASS', '**'); define('DB_NAME', '**'); // Connection to Database $database = new MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT); $sql = 'SELECT AManufactureBrand.brand, AManufactureModel.model, AManufactureEdition.edition' ...
Hi all, Am running a sql query with php to retrieve some data from mysql database. Everything works well except that, if the data contains symbols like < and >, then whatever data present between these symbols doesnt show in the output. for example, if the data is something like "<hello there> how are you?" then only "how are you?" i...
Can someone help me understand why the following query is not offsetting correctly? It's meant to select all records in the games table, and add a column with a value of 0 or 1 based on whether a record in another table (wishlist) exists with the same gameId @memberId (in plain English, get me all records from games, and mark any game t...
Can I make my .htaccess be generated with php? I would like to use php to dynamicly create my htaccess file from information from the database. It would save me the trouble of making a new .htaccess file whenever I change a bit of my code. ...