Having problems with A count of the number of customers who have not hired cars create table customer (customer_id char(4) primary key not null, customer_sname varchar (30) not null, customer_fname varchar (30) not null, customer_title varchar (6) not null, customer_address1 varchar (35) not null, customer_address2 varchar (35) null, c...
I am interested in people's opinions on MySQL vs SQL Server. Our database is fairly simple; no stored procedures, and we use NHibernate as our ORM layer. I would like to make a case for switching from SQL Server to MySQL cluster on Linux. We need an enterprise level, clustered DB with failover and load balancing. Any opinions pro or con ...
Hi, i'm using php and mysql for a webpage, and i want to reuse information which i have in three database tables, similar to a 'save as' operation. The tables are related as a 1-many and then 1-many, a tree structure. I have so far implemented it by flatten out the tree structure with php (similar to one giant table) and storing it in...
Hello, Given table | id | user | | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 5 | | 2 | 6 | | 2 | 7 | | 2 | 8 | I want to write a query that will return 3 rows for id=1 and 3 rows for id=2 (order does not matter, yet I would assume that it can be enforced). So the end result should be something lik...
Hi, I need to count the number of persons with the same status from a table for a specific user.. I want to use prepared statements so I did something like this (not working): $sql = 'SELECT status, COUNT(personID) FROM user_persons GROUP BY status WHERE userID = ?'; if ($stmt->prepare($sql)) { $stmt->bind_param('i', $userID); ...
Here's my code <?php $host = 'localhost'; $username = 'sam_sam'; $password = '[censored]'; $name = 'sam_ballpointradio'; mysql_connect($host, $db_username, $db_password) or die(mysql_error()); mysql_select_db($db_name) or die(mysql_error()); ?> and the error message is Warning: mysql_connect() [function.mysql-connect]: Access deni...
Dear all, i´d like to update all NULL fields in one table to 0. Of course UPDATE mytable SET firstcol=0 WHERE firstcol IS NULL would do the job. But I wonder if there´s a smarter solution than just c&p this line for every column. Thx in advance for any suggestions ...
Hi, I want to select the last three rows of a table in ascending order. How the query should be for that? Thanks ...
Hello, Been trying to put together an SQL query that sorts data into financial years (which I've sorted) and then SUMs it according to each distinct value in the claim_id column table structure is: claim(id, claim_id, date, amount) SQL so far is: SELECT CASE WHEN MONTH(date)>=4 THEN concat(YEAR(date), '-',YEA...
Firstly I should mention that I am aware of (and use some) statistics packages, however in this instance i want to write my own ulta-lightweight solution. what i want to achieve: user visits a page once all content loading, parsing etc is complete, a request is made to .php file which increments a mysql db what I'd like your thought...
I need to select some records from a table where a column value is equal to any of the values in a list. This can be done using either IN operator (like WHERE column IN (v1, v2, ...)) or multiple OR operators (like WHERE column = v1 OR column = v2, ...). When should I use either of these ways? ...
Hi folks, i would like to get from a single table, all rows, but order them in different ways. For example i write (SELECT * FROM table1 ORDER BY fieldA ASC LIMIT 3 ) UNION ( SELECT * FROM table1 ORDER BY FieldB DESC ) It works, excpet that the second order by (FIELDB DESC) is ignored... Somebody know Why ? Thank you ...
hey guys one of my main problems in coding is including config.php and db class where i need to connect to databse consider i have a mysql.php and config.php file in root of my files now if im in this path : Root/Sub/portal/modules/Gallery.php and i need to fetch config.php vars such as $dbhost = "localhost"; $dbuname ...
I have a PHP web app with MySQL tables taking utf8 text. I recently converted the data from latin1 to utf8 along with the tables and columns accordingly. I did, however, forget to use mysql_set_charset and the latest incoming data I would assume came through the MySQL connection as latin1. I don't know what happens when latin1 comes in t...
Say for example I have a table like this, with a primary key on the first 3 fields: itemid | type | value | other_field [etc] If on my web pages I am nearly always selecting by the type field only, it is worth having a separate index on type (i.e. will performance improve), or does the primary key index cover the field anyway? ...
I am deleting approximately 1/3 of the records in a table using the query: DELETE FROM `abc` LIMIT 10680000; The query appears in the processlist with the state "updating". There are 30m records in total. The table has 5 columns and two indexes, and when dumped to SQL the file about 9GB. This is the only database and table in MySQL. ...
I'm trying to block login for x minutes after y failed attempts. I'm already planning to log user logins, so I guess I could use the same database to calculate if blocking needs to happen. My questions: does it make sense to use the same logs table to run the logic of the y failed attempts blocking? Some people have a table just for t...
Hey guys, I am trying to create a query that allows me to get the sum of a total stored in one table based on values in another table. Specifically, I have one table called 'winning_bids', that I want to join with another table, called 'objects'. 'winning_bids' contains a User ID, and an Object ID (primary key of 'objects' table). The ...
Dear Santa, I hope you're reading this! I have to insert some data into a MySQL table. The data will be retrieved and then (at present) unserialized at which point the correct display language will be selected... I've managed to munge the data (text encoded with markdown) into a set of PHP statements, roughly along the following line...
I have a table id_user, hour, medition 1 0 100 1 1 101 1 14 102 2 5 108 2 17 103 How I can complete the inexistent hours with a 0 value for any user? example 1 0 100 1 1 101 1 2 0 1 3 0 ... ...