Hi I have a project where I need to store a large number of values. The data is a dataset holding 1024 2Byte Unsigned integer values. Now I store one value at one row together with a timestamp and a unik ID. This data is continously stored based on a time trigger. What I would like to do, is store all 1024 values in one field. So would ...
Hi, I have a JOBS and a COMPANIES table, and I want to extract 20 jobs that meet the following criteria a) jobs only from 2 named companies b) there can at most be 10 jobs per company I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set, whereas I want it to apply...
Hi, I have a c# application that uses a MySql database, the problem that after a period of inactivity (8 hours) or when connection to the server where database is host is lost, the connection to the database is closed and database queries cannot be executed. how can i enable the auto reconnect to the database. Best regards. ...
Hello, I'm currently working on improving my database to make room for growth. As it stands, different users have different 'permissions' to areas of the website. Some users have permissions to multiple areas of the website. I'd like some feedback if I'm doing this in the most efficient way: tblUsers: usrID usrFirst ...
I am looping through a number of values (1 to 100 for example) and executing a prepared statement inside the loop. Is there and advantage to using a transaction - committing after the loop ends - compared to a direct execution inside the loop? The values are not dependant on each other so a transaction is not needed from that point of ...
Hi there I have a projects table which has two foreign keys for users (user_id and winner_user_id), one for the owner of the project and one for the winner of the project. Something like +----------------+-------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Defaul...
I am able to do this on my linux box in my.cnf sql-mode = "PIPES_AS_CONCAT" but on my OS X laptop, in my.cnf sql-mode = "PIPES_AS_CONCAT" immediately halts the server. not sure how to fix or further investigate. ...
Hello, I am facing a problem on developing my web app, here is the description: This webapp (still in alpha) is based on user generated content (usually short articles although their length can become quite large, about one quarter of screen), every user submits at least 10 of these articles, so the number should grow pretty fast. By na...
I have a mysql db query that I'd like to take and condense into a single row. The output is currently two rows and will only ever be two rows. The reason I am getting two rows in the first place is because there will always be a primary and a secondary phone number associated with my result. Since all of the data is exactly the same for...
How to clean a database from identical by a specific field rows so that only one copy of each set of identical rows is left there? I have a table: CREATE TABLE table1 (field1 varchar(255), field2 varchar(255)); I'd like to purge table1 of any unnecessary copies of rows, leaving a random row from each set of rows with the same fie...
Hello, I'm currently using $.getJSON to pass an array of ids. It basically constructs a URL like this: http://domain.com/json.php?id=1&id=2&id=4 My question is: How can I take these ids that are passed (1,2,4) and place them in my where clause? Something like: $id = $_GET['id']; $sql = SELECT * FROM table WHERE usrID ...
How to use illegal names for MySQL with SQLObject? In pure SQL it is possible to use backquotes, say: SELECT `select from` FROM table1 WHERE 1; ...can be used to select the field called select from. Is it possible to tell SQLObject to utilize backquotes? ...
Ok, you have login form and you want to ban users for 5 minutes if they get password/user wrong. User can try few times in a row until banned. LOGIN_FAILS table: id PK, serial/auto_increment ipaddress int/text/whatever you prefer added DATETIME/TIMESTAMP SQL: SELECT IF(COUNT(id) < 3, 'false', 'true') AS is_banned FROM LOGIN_FAILS W...
the question is Which of the following methods for providing explicit names for the columns in a view work? a. Include a column list b. Provide column aliases in the view SELECT statement c. Rename the columns when you select from the view answer a. Works: Include a column list b. Works: Provide column aliases in the view SELECT stat...
Hi there, I'm wanting to find the ranking / number of a row. I'm not sure if I'm explaining this well so I'll try. I have the query $sql = 'SELECT SUM(amount) AS total FROM sales ORDER BY total DESC'; $res = mysql_query($sql); while($row = mysql_fetch_array($res)) { // go through and print each row biggest 'total' first echo $row['...
select * from A left join B on A.columnc=B.columnd results returned by above SQL will include both columns of A and B. And what if A and B have some columns with the same name? How to retrieve the value from PHP? ...
I am entering records in the MySQL DB. Now I want to have a "Serial_Number" field that increements automatically whenever a record is entered into the DB. I don't want this "Serial_Number" field to be the primary key of the DB. How can I create this field (with the attributes needed to be set). I am using "SQL YOG" to access MySQL. If...
When I execute the following query on a database containing a table "comm_list" and a field "sr_no" of type "Int" in the table "comm_list", I get the correct resultset SELECT MAX(sr_no) FROM comm_list; The above query is to get the max. serial no. so that when I enter a new record, I enter the (serial no. + 1) in the "sr_no" coloum so ...
Is there any way to get just a part from MySql cell and ignore everything after predefined character? For example, I have row with cell's "ID, LINK, PHONE". Inside LINK cell DB wrights links like mysite.com/mypicture.jpeg. Is there any way, to get just "mysite.com" from this cell and ignore everything after slash, so I can echo it inside...
i noticed after i ran DELETE FROM tablename my ID (auto increment) valus became weird 7, 8, 9, 0, 1, 12, 3, 4, 15 in this order when i do a SELECT * FROM tablename i know that the certification guide says that IDs may or may not be reset when DELETE without WHERE is used to empty a table, but what caused the ID sequence to be s...