hi all, how can I count row based on its contents? assumed I have table like this [table a] ID_COMPANY | NAME ----------------------------- A1 | COMPANY A [table b] ID_COMPANY | USER | TYPE -------------------------------------- A1 | USER A | MANAGER A1 | USER B | DEP...
Hi, I have a test database on a separate remote server than my production DB. Every once in awhile, I want to try and test things by uploading a copy of my production DB to my testing DB. Unfortunately, the backup file is now half a gig and I'm having trouble transferring it via FTP or SSH. Is there an easy way that I can use the mys...
Hello, I am a PHP and ExtJS user. I am looking into developing an application using a good PHP framework (CakePHP; good as in "I consider this good for me") and ExtJS version 3. What I would like to achieve is a complete Ext viewport with many grids and functions that would call PHP urls for retrieving data, saving data, edit/remove data...
I don't remember ever seeing a way to use prepared statements from the console and somehow don't think running an explain query thought as a prepared statement from the API will get what I want. This is related to this old question of mine. I'm primarily interested in MySQL but would be interested in other DBs as well. ...
need help, how can we combine these query? ive a problem in join statement.. SELECT hc.id_company, hc.name, hc.email, dc.country, dc.date_join, sum(if(ha.type='ADMIN',1,0)) as ADMIN, sum(if(length(ha.type)=6,1,0)) as STAFF, sum(if(ha.type='CUST',1,0)) as CUST from h_company hc, d_company dc JOIN h_adminstaffcust ha USING (id_company...
Hi, I want to convert my sql data to csv files while clicking on a button. The code fragments I found for sql to CSV conversion were in PHP, and I'm trying to convert it to CakePHP since I'm working in CakePHP. Here is the PHP code I'm tring to convert: $result = mysql_query("SHOW COLUMNS FROM ".$table.""); $i = 0; if (mysql_num_rows($...
I work with symfony framework and Propel and i was wandering what is the easiest way to encrypt a database. I'm not talking about passwords but all the database . I built a small web application for a client that manages some information (user accounts, passwords etc) and i don;t want it to be visible if someone has access to phpmyadmin ...
Hello all, My Table Structure is: artist_id category 1 Song 1 Song 1 Song 1 Video 1 Video Now, my output must be id category total 1 Song 3 1 Video 2 How this can done by using MYSQL. ...
Hi guys, I have a simple social network project going and I would like to add a simple recommend this user profile function. A logged in user can 'recommend' a user once - its just like the 'This ansewr is helpful/not helpful' buttons here on posts in Stackoverflow. I want a simple table design to implement the exact idea - any tips? A ...
Hi all. I have two sets of results: SELECT name, count(appearance) as countA from table where results = '1' SELECT name, count(appearance) as countB from table where results = '2' And I wanted to combine them side by side, like this: +---------+---------+---------+ | col_1 | countA | countB | +---------+---------+---------+ | Jo...
I've had some bizarre results from queries I've been testing out with the DATE function, culminating in these little beauties: mysql> SELECT id FROM job WHERE DATE(due)=CURRENT_DATE; Empty set (0.00 sec) mysql> SELECT id FROM job WHERE DATE(due)=CURRENT_DATE AND id>2022; Empty set (0.00 sec) mysql> SELECT id FROM job WHERE DATE(due)=C...
I do datamining and my work involves loading and unloading +1GB database dump files into MySQL. I am wondering is there any other free database engine that works better than MySQL on huge databases? is PostgreSQL better in terms of performance? I only use basic SQL commands so speed is the only factor for me to choose a database ...
Hi, I'm having troubles with subcategories. I want to display this on my page with 1 sql query: Category 1: Sub cat 1 Sub cat 2 ... Sub cat n Category 2: Sub cat 1 Sub cat 2 ... Sub cat n Category 3: Sub cat 1 Sub cat 2 ... Sub cat n ... Table schema: categories id | catname | description subcategory id | pare...
I have a table with the following basic structure. unique_id | name | original | version ----------------- 1 | a1 | 1 | 1 2 | b1 | 2 | 1 3 | a2 | 1 | 2 4 | a3 | 1 | 3 5 | c1 | 5 | 1 6 | b2 | 2 | 2 Now it should be obvious from this that there is a form of version control, where we keep track of the original document, and also tra...
When creating the following procedure I receive this error message: ERROR 1064 (42000): 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 'd_query; set d_result = execute stmt; deallocate prepare stmt; ' at line 15 But after checking I can't see the ...
according to mysql certification guide, comparisons between binary collation vs binary strings are different because comparisons in binary collation is done per character vs per byte in binary strings. but what difference does that give? ...
Creation of objects like tables and indexes are fairly essential, even if the code has to be authorized or created by the dba. What other areas normally carried out by dbas should the accomplished developer be aware of? ...
i am looking at some practice questions Assume that you've just created this table: CREATE TABLE timestamptest ( ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, i INT ); When you look at its structure, you will notice that the TIMESTAMP column is declared NOT NULL. What happens if you insert these records: mys...
The count should be 3 and 1 in the following query. The count should be of the points earned consecutively. So once the user fails to earn any points, the count restarts. mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL) having name= 'john'; +------+----------+ | name | count(*) | +------+-------...
I have a PHP application using CakePHP. I need to present a table showing the count of particular events for each day of the month. Each event is record in the DB with a name and a Data. What is the best way to create a table for this, do I really need to do 31 SQL calls to count the events for each day, or take out the data for the whol...