mysql

Recursive stored functions in MySQL

I'm trying to make a function that recursively builds a path for a specific category CREATE FUNCTION getPath(inId INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_path TEXT; DECLARE return_parent_id INT; SELECT CONCAT('/', name) INTO return_path FROM article_categories WHERE id = inId; SELECT parent_id INTO return_pa...

How important is database normalization in a very simple database?

I am making a very simple database (mysql) with essentially two types of data, always with a 1 to 1 relationship: Events Sponsor Time (Optional) Location (City, State) Venue (Optional) Details URL Sponsors Name URL Cities will be duplicated often, but is there really much value in having a cities table for such a simple databa...

SQL replace vs. doing an insert or update statement

Hi, I was wondering what the advantages/disadvantages or doing a MySQL replace vs. doing either an update or insert statement are. Basically, doing dao.replaceEntry(entry); instead of: if(existing){ dao.insertEntry(entry); } else { dao.updateEntry(entry); } Also, would it be misleading to call the dao.replaceEntry ...

What to do with the older database?

I am assigned in a project where I need to work with a medium database. When I open that database I saw that the database is not correctly made and it MUST have more tables than it should be.No normalization is applied even ! But problem is the database has a medium scale of data of almost 500 users. when I will break the older databa...

PHP/MySQL Database image gallery deletion

Hi, I'm having trouble deleting images from a mysql database using php. The addition of images to the database works great, and it will display/retieve the images no problem, but it won't delete them properly. It doesn't delete the selected image, always the first one in the database. I've tested it using specific filenames and id's so ...

How do I compare 2 tables looking for missing items.

I have two tables one that contains a huge list of items and another that trading for those items. Here are examples tables: The main table | ID | TITLE | STATUS | TRADE | ------------------------------- | 1 | test1 | 1 | 1 | | 2 | test2 | 1 | 1 | | 3 | test3 | 1 | 0 | | 4 | test4 | 0 | 1 | Th...

Problem with Query Data in a Table

I have table test contain 4 fields +----+-----------+--------------+-----+ | id | int_value | string_value | qid | +----+-----------+--------------+-----+ | 1 | 111 | Red | 1 | | 2 | 111 | Green | 2 | | 3 | 111 | Blue | 3 | | 4 | 222 | Yellow | 1 | | 5 | 222 | Red ...

Weighted search in Django

I have three inputs coming in from a form. They are name, neighborhoods and tags. Neighborhoods and tags are multi-select box string lists. Here is my current query: q = Restaurant.objects.filter(name__icontains=name) q = q.filter(neighborhoods__name__in=neighborhoods) for tag in tags: q = q.filter(tags__name=tag) q = q.order_by('na...

MySQL Query - Using Aggregate and Group By to generate separate results

I have a table where one or more entries with the same 'id' value can be inserted into our log / fact table (contains over 100+ million records) At a set frequency a new record is inserted into this table with a new value for the columns 'created' and 'view_percent' (percentage of a video viewed). With two different queries, I would like...

LINQ Performance Problem

I have a Joomla site that uses JomSocial. I have a .NET web app that I'm working on that will eventually replace Joomla since I prefer .NET over PHP. Right now I have .NET mobile site that users are using. LINQ to Entity has made development very speedy, but I'm now in the process of trying to fix performance issues. Sending messages...

Can I bind multiple values as a single parameter using MYSQLI and PHP?

Imagine I have the following SQL query: SELECT id,name FROM user WHERE id IN ('id1','id2','id3') Now imagine I need the array of ids to be supplied by PHP. So I have something like this: $idList = array('id1','id2','id3'); $query = "SELECT id,name FROM user WHERE id IN (?)"; $stmt = $db->prepare($query); $stmt->bind_param(/*Somethin...

How to partition a MySQL table based on char column?

Is it possible to partition based on char column? After reviewing the MySQL 5.1 documentation it appears that only integer types can be used. Is this correct? Or can I use some function to convert the char into an integer? The char field in question contains a unique identifier. ...

search with a combination of structured criteria and freetext keyword/phrase - NOSQL vs Lucene/Sphinx

Hi all, we have a eMall application based mainly around a ~500k rows MySQL master table (with detail tables storing non searchable fields and other related tables with shop info etc). Users can today search based on specific structured product data (e.g. brand, category, price, specific shop etc). We would also like to support keyword...

Secure, community-driven mysql-forums?

Hey I'm having a hard time choosing between different php-forums. I'm looking for a lightweight, secure and easily customizable forum. Googling gives me dozens of "simple php-forums" made by inviduals, but I'm looking for a community-based one. Because of, you know, the help and plugins. I think that SMF and PhpBB are too solid packag...

Popularity, How to make new hits count more than old hits?

Each product a product_date_added which is a Date field contained the date it was added. They also have a product_views which is an int field containing how many times a product has been viewed. To display products by popularity, I us an algorithm to calculate how many hits per day a product has. SELECT AVG(product_views / DATEDI...

Mysqltuner suggestions and changes to my.cnf

Had this question on Serverfault for a few days with no luck. I've run mysqltuner.pl on a VPS and have a bunch of questions as to the suggestions on variables to change. I'm sure these are general questions with complex answers. I'm not knowledgable enough to write queries and test them against the server, but am just trying to get a b...

Using double quotes vs single quotes when passing string as a parameter to function

hi, i have a very strange problem, well not really a problem because i've fixed it but still, when i'm trying to connect to mysql db with: mysql_connect("server", "user", "pass") or die(mysql_error()); im getting: Access denied for user 'user'@'server' (using password: YES) but when i change the quotes around the password to sin...

dealing with dates in mysql?

i have this long mysql query, and im trying to find fetch the rows that are older then the given date $lastmsg. this is the code im using: $result="SELECT u.username, u.picture,m.id, m.user_note, m.reply_id, m.reply_name, m.recycle_id, m.recycle_name, m.dt FROM relationships r, ...

Query result in MySQL Console does not match the result of PHP's mysql_query()

My MySQL query returns different results depending on how the query is submitted. When the query is submitted through the MySQL Console results in. mysql> SELECT `modx`.coverage_nation.id, -> `modx`.coverage_nation.name, -> `modx`.coverage_national_region.id, -> `modx`.coverage_national_region.name -> FROM ...

How to manage measurement units in a PHP web application?

Hi, I am working on a LAMP web application which is managing a lot of data with different measurement units. This php application use a custom MVC framework. We now have customers in different countries and we would like to offer the choice to the customer between metric, imperial or combination. Currently, all the data are saved in in...