Part of the system I'm working on at the moment involves a log in mysql, with counts being frequently updated. The data being inserted is of the format: date | name | count | -----------+------+-------+ 2009-01-12 | alan | 5 | 2009-01-12 | dave | 2 | 2009-01-12 | mary | 1 | This data is parsed regularly from a flat ...
I have the following query: SELECT DISTINCT c.id FROM clients AS c LEFT JOIN client_project AS cp ON (cp.client_id = c.id) WHERE cp.project_id = 1 AND c.active_flag = 1 ORDER BY c.client_name If I remove the order by, the query takes 0.005 seconds. With the order by, the query takes 1.8-1.9 seconds. I have an index on client_name....
I currently work for a social networking website. My boss recently had the idea to show search results by random instead of normal results (registration date). The problem with that is simple and obvious: if you go from one page to another, it's going to show you different results each time as the list is randomized each time. I had t...
I'd like to avoid mysqldump since that outputs in a form that is only convenient for mysql to read. CSV seems more universal (one file per table is fine). But if there are advantages to mysqldump, I'm all ears. Also, I'd like something I can run from the command line (linux). If that's a mysql script, pointers to how to make such a t...
I have a MySql database with three tables I need to combine in a query: schedule, enrolled and waitlist. They are implementing a basic class enrollment system. Schedule contains the scheduled classes. When a user enrolls in a class, their user account id and the id of the scheduled class are stored in enrolled. If a class is at capacity...
What query do I need to run in PHP to get the structure of a given table in the database and what query do I need to run to get a list of all the tables in the database. ...
I am trying to connect to a remote mysql database from an iPhone. I have searched many web sites but I did not find any help. If anyone has worked with this please send a solution. ...
I'm trying to inspect my mysql database information_schema to find out the attributes of the columns. I can't find where the details as to which columns are auto_increment. Does anyone know where I can find this info in the information_schema DB? ...
Hi! I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field. What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'....
Let's say I have two tables, news and comments. news ( id, subject, body, posted ) comments ( id, parent, // points to news.id message, name, posted ) I would like to create one query that grabs the latest x # of news item along with the name and posted date for the latest comment for each news post. Speed matters...
Here is the current state of my table: mysql> select * from page; +----+----------+----------------+------+---------+ | id | title | body | page | visible | +----+----------+----------------+------+---------+ | 1 | my title | my body | NULL | 1 | | 2 | my title | my body edited | 1 | 0 | +----+----...
I'm building a wepage in php using MySQL as my database. Which way is faster? 2 requests to MySQL with the folling query. SELECT points FROM data; SELECT sum(points) FROM data; 1 request to MySQL. Hold the result in a temporary array and calcuale the sum in php. $data = SELECT points FROM data; EDIT -- the data is about 200-500 ...
I have trouble defining a trigger for a MySQL database. I want to change a textfield before inserting a new row (under a given condition). This is what I have tried: CREATE TRIGGER add_bcc BEFORE INSERT ON MailQueue FOR EACH ROW BEGIN IF (NEW.sHeaders LIKE "%[email protected]%") THEN SET NEW.sHeaders = NEW.sHeaders + "BCC:inter...
Date and time in MySQL can be stored as DATETIME, TIMESTAMP, and INTEGER (number of seconds since 01/01/1970). What are the benefits and drawbacks of each, particularly when developing under a LAMP stack? ...
We have a Microsoft Access database that has been used for many years. Our staff is very used to using the Access tables and forms to query, view, update and add to the data. But the database is at the end of its lifetime. We want to convert it to a PHP/mySQL solution. I have found tools that can convert the Access database to mySQL. ...
Hello. I have developed an application in PHP + Javascript. I'm using MySQL as database support. Every account for this app will come with a subdomain... so the client Stardust would have stardust.mysite.com. I want to put the application files in a folder outside public_html and link every account from every subdomain created to this fi...
I'm storing a varchar in a utf8 MySQL table and using utf8_general_ci collation. I have a unique index on the varchar. I'd like to do a string comparison in PHP that is equivalent to what MySQL will do on the index. A specific example is that I'd like to be able to detect that 'a' is considered equivalent to 'À' in PHP before this ha...
I am trying to take several RSS feeds, and put the content of them into a MySQL Database using PHP. After I store this content, I will display on my own page, and also combine the content into one single RSS Feed. (Probably after filtering) I haven't dealt with RSS Feeds before, so I am wondering the best Framework/Method of doing this ...
I have a mySQL database behind a Joomla install. I changed the database password because I forgot it but now Joomla crashes looking for the database. I guess joomla has the password written somewhere - if anyone knows I might be able to manual edit it and enter the new database password. Otherwise I'm hoping a manual install of a back...
Basically I have a mysql query something like the following: mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id"); And what I need to do is to grab the title from the posts table and the current vote value from the votes table. At the moment, votes aren't stored into the votes table unless a vote happ...