mysql

Best way to show friends of a user in alphabetical order

On a social network I am working on in PHP/MySQL, I have a friends page, it will show all friends a user has, like most networks do. I have a friend table in mysql, it only has a few fields. auto_ID, from_user_ID, to_friend_ID, date I would like to make the friends page have a few different options for sorting the results, 1) By auto...

Counting how many times a rating was entered in a MySQL Database using PHP

I'm trying to count how many times an article has been rated by my members buy using PHP to count a certain articles total entered ratings that have been stored in my MySQL database. I really want to use PHP and not MySQL to do this and was wondering how I can do this? I hope I explained this right? An example would be very helpful m...

Which Search / Tag system is better?

I am working on a website that has users and user-generated articles, galleries and video's. I am trying to make a tagging system and a search for them all. At first I was thinking in tbl_articles, tbl_galleries and tbl_videos I would have a title, description and a tags field. Then run a query like the following for each: select * fro...

How often should I close database connections?

Currently, I'm opening a database connection in my app's initialization. It's a fairly small app, PHP if that's relevant. Should I be connecting to the database, making calls, then closing and repeating this process for each database function I write? For example, I have the following function which grabs the $db variable from my app's...

Searching in mysql db with narrow results

Hello, My search query is something like this SELECT files.id,files.file,files.name,files.uid,files.size,files.downloads,files.deleted,files.descr, files.upload_id,files.thumb,files.category,files.expirydate, MATCH(name,descr) AGAINST ('$all $none $any' In BOOLEAN MODE) AS score, users.`user` FROM files Inner Join users O...

Doctrine-CLI database creation issue.

I have Doctrine setup in my Zend Framework application and I built my schema YAML file. But when I tell Doctrine to build the tables it says it does but it doesn't actually make them. It creates the models, and will create the DB but it will not populate the DB with the tables and throws no errors. Does anyone have a guess or know why th...

MySQL - Selecting "Everyone who booked last January, but hasn't booked this January".

I am having a few issues with what is probably a reasonably simple SQL statement - unfortunately, my skills don't go as far as this and I can't get my head round it! I have, for example, the following tables: booking_record client Booking record may have many bookings for one client, they are linked via 'labelno'. I would like to s...

Magento Store - SQL Error

I get this error in my Magento store when I turn on flat catalogs: SELECT COUNT(_table_views.event_id) AS `views`, `e`.*, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`category_ids`, `e`.`created_at`, `e`.`enable_googlecheckout`, `e`.`has_options`, `e`.`image_label`, `e`.`links_purchased_separately`, `e`.`links_title`, `e...

How to structure database for relational? data

I need to keep track of points scored and who they are scored against. Previously, I did this using a flat file database (that was a mess) that looked like this: 03611100025 00001000000 21011000000 00003000000 00021000000 10001050000 00001402000 00001000100 00001013050 00001000100 00001000000 Each team got a row and a column, and I gu...

In mysql How can I multiply two tables?

Basically what I want is cross product of two tables. t1 is : +------------+ | student | +------------+ | 1234567890 | | 1234567890 | | 1234567890 | | 000000001 | +------------+ t2 is: +--------+ | number | +--------+ | 1 | | 3 | +--------+ How can I get a tab...

How to nest select statements in SQL?

I'm trying to select only the User_IDs that are in the first select statement and the last select statement, but I am doing something wrong. SELECT COUNT(DISTINCT User_ID) AS total FROM UserClicks WHERE (Date > :startDate AND Date < :endDate) AND User_ID IN ( SELECT User_ID FROM UserClicks WHERE (Date > :mo...

Does an enum in MySQL need to be NOT NULL?

Lets say I have this: ALTER TABLE asdf ADD field ENUM('Y', 'N') DEFAULT 'N'; Is putting a NOT NULL on the end necessary as it can only be Y and N? EDT: based on comments, if I know the software always sets it to 'N' or 'Y' and is hardcoded in then is it OK to leave it off or could it still potentially become null some how. ...

How to optimize this SQL select query?

I am able to complete this query but it takes 25 seconds. That's too long! How can I optimize this query? SELECT COUNT(DISTINCT u1.User_ID ) AS total FROM UserClicks u1 INNER JOIN (SELECT DISTINCT User_ID FROM UserClicks WHERE (Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)) u2 ...

JDBC always tests the last row of MySQL table?

Hi, I have a Manager class that saves data in the SQL table and also get result from SQL table and test these data.when I run my program,one frame will be shown that gets ID and password and if they are correct ,the other frame will be shown.BUT I don't know that why it just test the last row of SQL table?? i mean if I set those text fie...

mySQL updating but also not updating

I am writing in mySQL/PHP, but have this problem I cannot get my head around. This one PHP script contains two SQL statements. What I am trying to do is update a sports league table (a SQL table called tblrank - which contains many league tables, separated by TableID), and to indicate if the team has gone up or down since the last time i...

MySQL 4.0.18 syntax error while using GROUP BY

I have a project management website running on PHP and MySQL. But I am not the person who developed it, which makes it harder for me to figure out what the problem is. So I turned to stack overflow. When I try to sort a list of objects, MySQL server throws an exception at me, saying: "You have an error in your SQL syntax. Check the ...

Complicated/Simple SQL Insert: adding multiple rows

I have a table connecting principals to their roles. I have come upon a situation where I need to add a role for each user. I have a statement SELECT id FROM principals which grabs a list of all the principals. What I want to create is something like the following: INSERT INTO role_principal(principal_id,role_id) VALUES(SELECT id FROM ...

sql date order by problem

i have image table, which has 2 or more rows with same date.. now im tring to do order by created_date DESC, which works fine and shows rows same position, but when i change the query and try again, it shows different positions.. and no i dont have any other order by field, so im bit confused on why its doing it and how can i fix it. c...

unique index slows down?

does it slow down the query time to use a lot of unique indexes? i dont have that many im just curious, i think i have heard this some where id (primary auto_increment) username (unique) password salt (unique) email (unique) ...

Erlang mysql example

Just wondering if anyone could give a working example of using the erlang-mysql module (http://code.google.com/p/erlang-mysql-driver/). I am new to erlang and I am trying to replace some old scripts with a few erlang batch processes. I am able to connect to the DB and even complete a query, but I am not sure how I use the results. Her...