MySQL table view limitations
Are there any limitations in the functionality of SQL Views for MySQL? ex: Can you create a table view using 'JOIN' commands? ...
mysql
Basics rules on UPDATE statement for relational MySql table: for vs while
Hello, I'm doing my first steps with mysql and php, so I have doubts on foundamental rules for a right code optimization. I have a case where my UPDATE statement need to be executed on a certain number of rows, because it should be executed on a relational table, so is a for cicle correct? <? // connection already created $data[] = arr...
MySQL Falcon: add/remove column performance
I'm maintaining an app which adds/removes columns from increasingly large tables -- not often, but regularly (during deployment of new versions, and hence also frequently during development). Right now, we're using InnoDB. Performance of adding a column isn't great: it has to copy the entire table. I've read that the Falcon engine doe...
retrieve results from mysql using jquery/php/ajax
Hi all, I'm using codeigniter for this project. I have a search form and search form results. Now when I click search in search form I'm being redirected to search form result and I get results. Is it possible to create div or table in search form which is hidden at first then call the search procedure and which results it returns to...
Elegant way to handle upsert with Hibernate and MySQL
I'm currently working on a batch import feature that sits on top of Hibernate and MySQL. My goal is to have Upsert functionality for several tables. I'm finding myself writing a lot of code to deal with seeing if the row exists by key and branching to right method. I was wondering if there might be a better way, i.e. something analogous ...
SQLBuddy: How to select database?
I have the following sql which I want to import with SQLBuddy. However I get an error telling that no database selected. Could anyone tell me what line I need to add please? CREATE TABLE IF NOT EXISTS `jos_banner` ( `bid` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL DEFAULT '0', `type` varchar(30) NOT NULL DEFAULT 'ba...
PDO Cannot execute queries while other unbuffered queries are active
I know this has to be a simple fix and I partially understand why I am getting this error but don't know how to fix it. I've looked over the docs but can't find a solution other than using buffered queries option. I have tried that as well but it doesn't work. The error is: PDO Cannot execute queries while other unbuffered queries are a...
Top techniques to avoid 'data scraping' from a website database
I am setting up a site using PHP and MySQL that is essentially just a web front-end to an existing database. Understandably my client is very keen to prevent anyone from being able to make a copy of the data in the database yet at the same time wants everything publicly available and even a "view all" link to display every record in the...
mysql - return results grouped in a column
I am working on a search/tag system. My original query I wrote was for when I was storing 'title', 'description' and a comma seperated 'tags' column in my article/video table. I have since realised the advantage of normalising my tags. I now have three table to deal with... tbl_Articles article_id title description content tbl_tag_i...
PDO Cannot execute queries while other unbuffered queries are active
I know this has to be a simple fix and I partially understand why I am getting this error but don't know how to fix it. I've looked over the docs but can't find a solution other than using buffered queries option. I have tried that as well but it doesn't work. The error is: PDO Cannot execute queries while other unbuffered queries are a...
Convert mysql query results to CSV (with copy/paste)
I often work in command line mysql. A common need is to take a query's results and import them into a Numbers document (similar to an Excel document). What is the fastest method for doing this? Method 1: Select into outfile You can select into an outfile directly from MySQL, but this takes several steps. export your query with all ...
import data from excel in php
i want to import data from excel file using php. and then if possible to save it mysql database. Thanks ...
What's the best way to store a MySQL database in source control?
I am working on an application with a few other people and we'd like to store our MySQL database in source control. My thoughts are two have two files: one would be the create script for the tables, etc, and the other would be the inserts for our sample data. Is this a good approach? Also, what's the best way to export this information? ...
django select query--how do I make this?
class Content(models.Model): .....stuff here class Score(models.Model): content = models.OneToOneField(Content, primary_key=True) real_score = models.IntegerField(default=0) This is my database schema. As you can see, each Content has a score. How do I do this: Select all from Content where Content's Score is 1? ...
Strange PDO MySQL Problem
I am trying to perform a query in a PHP script. The query works fine in my MySQL client however it does not seem to working within my code. I am using PDO. I am thinking it might have limitations, because it seems to work fine with a less complicated query. Here is the query: SELECT D.status, D.createdBy, D.createDate, D.modified...
Convert MYSQL Timestamp to time_t
I'm writing a multi-threaded program that needs to be able to check if a row requires updating and act accordingly. I had problems using the built in date/time functions of MySql and so decided to just store the "lastupdate" timestamp as an integer in the table. However, I'm having problems converting this timestamp to time_t so that I...
mysqldb pulls whole query result in one chunk always even if I just do a fetchone?
So if I do import MySQLdb conn = MySQLdb.connect(...) cur = conn.cursor() cur.execute("SELECT * FROM HUGE_TABLE") print "hello?" print cur.fetchone() It looks to me that MySQLdb gets the entire huge table before it gets to the "print". I previously assumed it did some sort of "cursor/state" lazy retrieval in the background, but...
How do I subract using one single statement in MySQL
How can I subtract the result of this query: SELECT COUNT(Laptops) FROM (SELECT aaaUser.FIRST_NAME AS User,COUNT(workstation.WORKSTATIONNAME) AS Laptops FROM SystemInfo workstation LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT...
SQL for delete query
Hi, I'm trying to write an SQL query for my program, but I just can't figure out how. I don't know enough SQL. I'm trying to implement an online team system (for some website). I have two tables: teams | teamId, eventId teammembers | teamId, userId, status Now, I need to: "delete all records in teammembers where the eventId for the ...
Automate MySQL fields (like Excel)
Say I have a table like ID, NAME, SCORE. Now normally, to get the rankings of the teams, I'd select all and order by. Sometimes though, I don't want to know all the rankings, just the ranking of one team. If I added a column RANK, is there any way for MySQL to automatically fill in those values for me based off of SCORE? (I believe MS Ex...