mysql

DB Schema of a Role Based Access Control

Hi, I'm currently developing a member administration for a local association here and I'm developing the database schema at the moment. I'd like to share it with you to improve it and give other an example of a Role Based Access Model (RBAC). I'd appreciate any constructive criticism especially about the relationships I used between the...

MySQL transaction in stored procedure

I created a transaction in a stored procedure with some update statements. What will happen if the transaction is running, and one select request the value from the updating table? ...

PHP and MySQL: Store and print hex char

I'm trying to store an hexadecimal value (\xC1) in MySql Database. When I print that with PHP, i get: xC1lcool instead of the right word. INSERT INTO veiculos VALUES (1, 'Ford', 'Ka RoCam Flex', 25850.00, 1998, 1999, 'Vermelho', 15000, '\xC1lcool;Gasolina;GNV', 'Ar-Condicionado;4 portas;Câmbio automático', 'imagem1.jpg;imagem2.jpg;im...

Executing MySQL Query from PHP error

This part of my code creates a multiple query by this: $sql = ""; $sql .= "INSERT INTO projects (project_id, project_name, project_description, project_deadline, project_status, project_priority) VALUES ('" . $project_id . "', '" . $name . "', '" . $description . "', '" . $final_deadline . "', '" . $status . "', '" . $p...

Alphabetical pagination gets progressively slower as you page (MySQL)

I've got a dataset with over 100k rows, so it's not tiny, but not huge either. When paging through the results, it gets progressively slower as you go to higher pages. In other words, this query: SELECT * FROM items WHERE public = 1 ORDER BY name LIMIT 0,10 executes much faster than SELECT * FROM items WHERE public = 1 ORDER BY name ...

Sorting results in sphinx?

Is there an easy way to sort sphinx results? Recently posted (by dates) Highest paid (highest value) ...

Is it possible to set mySQL to have a saturday as the start of the week?

Is it possible to set mySQL to have a saturday as the start of the week? I'm trying to run a query like: SELECT DISTINCT(week(`date`)) FROM `table` WHERE `date` BETWEEN '2010-08-14' AND '2010-08-27' But the week starts on a Saturday, not Sunday or Monday. (It's for a pay period week, not a regular week) It seems that the modes ...

MySQL: Selecting the rows having min value of a computed column

The naive way of doing this that comes to mind would be: SELECT name, lev FROM (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) WHERE lev = (SELECT MIN(lev) FROM (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table )); However the "(SELECT name, levenshtein(name, parameter) as lev FROM my_table)" subqu...

Convert DD/MM/YYYY HH:MM:SS into MySQL TIMESTAMP

I would like a simple way to find and reformat text of the format 'DD/MM/YYYY' into 'YYYY/MM/DD' to be compatible with MySQL TIMESTAMPs, in a list of text items that may or may not contain a date atall, under python. (I'm thinking RegEx?) Basically i am looking for a way to inspect a list of items and correct any timestamp formats found...

MySQL query limit part of query

I have a mysql query: $result = mysql_query("SELECT * FROM $table WHERE cat = 'category'"); while($row = mysql_fetch_array($result)) { echo ' <hgroup><h3>'.$row['mag'].'</h3><h4>'.$row['date'].'</h4></hgroup> <a href="'.$row['href'].'" onclick="this.target=\'blank;\'">'.$row['title'].'</a> '; } This query...

Composite primary key comprising two foreign keys referencing same table: SQL Server vs. MySQL

I've read over a number of posts regarding DB table design for a common one-to-many / users-to-friends scenario. One post included the following: USERS * user_id (primary key) * username FRIENDS * user_id (primary key, foreign key to USERS(user_id)) * friend_id (primary key, foreign key to USERS(user_id)) > This will s...

Deallocate Memory in PHP

I have a script that is transferring about 1.5 million rows (~400mb worth of data) from a table to another table (during this process, some data is converted, modified, and placed in the correct field). It's a simple script, it just recursively loads data, then places it in the new tables under the correct fields and formats. The scripts...

MySQL Sum one field based on a second 'common' field

Not a SQL guy, so i'm in a bind, so I'll keep this simple +--------------+------------------------+ | RepaymentDay | MonthlyRepaymentAmount | +--------------+------------------------+ | 3 | 0.214847 | | 26 | 0.219357 | | 24 | 0.224337 | | 5 | ...

In a single mysql query, select multiple rows from one table, each containing multiple rows from a linking table

Have 2 tables with a linking table between then. USERS +-------+---------+ | userID| Username| +-------+---------+ | 1 | Nate | | 2 | Nic | | 3 | John | +-------+---------+ SITES +--------+---------+ | siteID | Site | +--------+---------+ | 1 | art | | 2 | com | | 3 | web | +-...

Drupal Mask Email Addresses and Replace With Image

Hi All, I've created my own business directory using CCK but I now need to mask the email addresses so they are unreadable by email spam bots. I also need to hide displaying the address by replacing it with an image. Are there any modules out there that can do this? Or can anybody provide some code or examples or suggest any ideas. An...

Best hardware to buy for a small startup web php/mysql web development team?

Hi all, we are given some funds to renew our IT infraestructure (about 20000$). We are a small team of 5 coders, working primarily with Ubuntu / PHP-Drupal / MySQL. One of the first things we want to buy is a brand new server with lots of gigs or ram (64 at least) to allocate all the local databases, svn repos, shared directories, etc....

unserialize() function does not work on a variable from the MySQL table on new server

We just switched our website over to a new server. There is a part of my PHP software that pulls out a serialized data value from the MySQL table, and puts it into a variable, and then it's supposed to unserialize(). I never had this issue on any other servers (and this exact code is used on many different servers..), but I'm getting an...

rails mysql error issue - possibly tables not linked

I've been working through some issues getting rails to behave nicely with sqllite, and decided to move over to mysql. I ran my rake db:create and rake db:schema, and assumed everything was ok, but then mysql show tables displayed a complete table was missing. I created a migration with the create table details and ran rake db:migrate,...

How to set key_cache_size in Mysql

Hi there, I saw our mysql server Key Efficiency hitrate reaching 100%, it's suggested by Mysql to increase key_cache_size setting. But how do I set this, and how do I check what the current setting is? Thanks, David ...

php variable in regex?

I have a variable $word and I'd like to match it in my select statement somethig like this: "select * from table where column regex '[0-9]$word'" but this doesnt work, so how can i put a variable in a regular exprssion? Thank you ...