mysql

create function though mysqldb

How can I define a multi-statement function or procedure in using the MySQLdb lib in python? Example: import MySQLdb db = MySQLdb.connect(db='service') c = db.cursor() c.execute("""DELIMITER // CREATE FUNCTION trivial_func (radius float) RETURNS FLOAT BEGIN IF radius > 1 THEN RETURN 0.0; ELSE RETURN...

performance implications of php output buffering with Apache and MySQL

I am a bit confused about how php buffering works. Let's say you have a php page foo.php and output_buffering = 4096 in php.ini, and Apache recieves a get request for foo.php. foo.php starts executing, and as soon as 4096 bytes are ready, they are sent to Apache. Apache starts sending those 4096 bytes to the client. Here's what I d...

Migrating php4/mysql4 to php5/mysql5: switch to InnoDB?

I have a legacy web application php4/mysql4 (MyISAM, db contains some cms, some user data, some calendar application). Now I am going to migrate to a new server with php5/mysql5. Should I change to InnoDB while migrating a mysql database? - expected advantages / disadvantages / risks? - Is MyISAM deprecated or will it some day be? Or ...

mysql prepared statement parameters and ordering queries

I have a form, the purpose of which is to place the currently displayed record into a category. I am using the following html code via php to do so: <form name="categoryForm"> <input name="radiobutton" type="radio" value="fakeapproved" />Fake (Approved)<p> <input name="radiobutton" type="radio" value="fakesuspected" />Fake (Suspecte...

PHP form checkbox and undefined index

I am getting an "Undefined index" error when submitting a form with an un-checked checkbox. Is there any other way besides running an "isset" or "empty" check on each individual posted value? I have looked at this Question and am having trouble believing that this is the only solution. Below is some example code: EDIT: please not that...

Help me for creating huge database in Mysql

We are building a website for business on global wise, for every country major cities are covered in this concept. I need some suggestions from PHP Mysql People. Can i create single databse for all cities or multiple databases. Because in this system contains some relations between cities ,every chamber need nearly 50 tables for networ...

How do I select a unique list of first characters [MySQL]

I have a column containing the list of names. I need to select only the first (unique) letters from the name. For non-utf-8 characters the following query works pretty well: SELECT DISTINCT LEFT(T1.Name, 1) AS firstLetter However, when the Name starts with UTF-8 encoded character this returns: �-sign. I suppose it's only the first cha...

MySQL encoding issue

For some reason, my mysql table is converting single and double quotes into strange characters. E.g "aha" is changed into: “aha” How can I fix this, or detect this in PHP and decode everything?? ...

Database for a limited VPS

Hello! Which database is more suitable for a limited VPS: MySQL or PostgreSQL ? I am developing a small hobby project which I plan to host in a limited XEN based VPS. At work we use PostgreSQL but we also use dedicated servers for DB and our hardware are very good. I've never had an extensive experience with MySQL but wouldn't it be mo...

Is a GROUP BY on UNIQUE key calculates all the groups before applying LIMIT clause?

If I GROUP BY on a unique key, and apply a LIMIT clause to the query, will all the groups be calculated before the limit is applied? If I have hundred records in the table (each has a unique key), Will I have 100 records in the temporary table created (for the GROUP BY) before a LIMIT is applied? A case study why I need this: Take ...

Getting non-existant month/week/year when using MySql group by clause with month/week/year date functions.

I am trying to implement a query where I am using aggregates to sum certain values and then group by mysql date functions (month | year | week). Those group by clauses don't return non-existant months OR year OR week respectively for obvious reasons. I was wondering if there is a way to get them? ...

How to offset dates in a MySQL database by 1 second?

I've imported a bunch of posts into a Wordpress site that all have the exact same date. I'd like to edit the post dates so they are offset by one second. My concern is that there may be a performance issue when all of the dates are the same. Anyone know quick way to get this done using phpMyAdmin to edit the MySQL database or some other ...

301 redirect with PHP and MySQL on 404 ... how?

I am transferring a large static website of 500+ pages to a Joomla installation but what I am wanting to do, rather than code a 500+ line .htaccess file, is to use PHP's built in 301 header redirect to dynamically redirect requests that come in to the new URL. So for example, after I move the site, the following URL would 404 without a ...

Using SQL to determine word count stats of a text field

I've recently been working on some database search functionality and wanted to get some information like the average words per document (e.g. text field in the database). The only thing I have found so far (without processing in language of choice outside the DB) is: SELECT AVG(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1) FR...

Web Service using Rails + MySql Best Practices

Alright, I need some advice and best practices from some Rails people. I'm fairly new to the platform but I've done database-backed web development work before in Java. At this point I've worked through about twenty tutorials, and I keep getting obstructed at the same place. Here's what I've done and what I'm experiencing. I've created...

Cannot find mysql.sock

I just had to re-install mysql and I am having a problem starting it up. It cannot find the socket (mysql.sock). The problem is that neither can I. In my Mac OS X 10.4 terminal, I type: "locate mysql.sock", and I get back "/private/tmp/mysql.sock". It makes sense that the socket file exist in that location, but it actually does not. ...

Updating display order of multiple MySQL rows in one or very few queries

I have a table with say 20 rows each with a number for display order (1-20). SELECT * FROM `mytable` ORDER BY `display_order` DESC; From an admin area you can drag the rows around or type a new number manually for each row. Surely it's not good to loop over an UPDATE query for every row, what's an alternative in one or very few queri...

Best way to gather, then import data into drupal?

I am building my first database driven website with Drupal and I have a few questions. I am currently populating a google docs excel spreadsheet with all of the data I want to eventually be able to query from the website (after it's imported). Is this the best way to start? If this is not the best way to start what would you recommend?...

How to structure survey data?

Note: this is NOT like this question I've been tasked to construct a survey that our customer service people can use to get info from our customers about our service etc. My question is how to store the questions/answers in the db where the questions are stored like so: boolean question1 if (false): string question2 (typically the ...

Is it possible using PHP to backup SQL without dependecies? If not, which is the best?

After a huge MySQL blunder on a production server (my fault, and yes I have learned), I am now looking at creating a dump of the MySQL database before I do a critical operation (about 15 queries to the db, insert/update/selects). I know phpMyAdmin can 'dump' the database to SQL queries, but I think this may be a feature of it, not a feat...