MYSQL :: Song Request System

Implement 'Rate A Song' Functions
Im developing a multimedia-contents website... All audio/video files are stored in mySQL (file location/path. Im not using BLOB due to performance reason). I want to be able to rate a particular song/file. Based on my reading, this could be implemented using database approach. Every time user rate a song, the rate value in the database/table will be updated and some computation will take place and finally display the average rating.. Can anyone explain this approach as im totally stuck?? Any other way to perform similar actions??

How To Pull Album Infor And Song Info Out Of Mysql Db?
i have a mysql database for music information and it has the following fields :

id ,filename, artist, album, title, remix ,track alternate_name,
parody ,comments, genre, seconds, filesize, bitrate ,visual, url

Request Help For Subquery
I have a table that records targets and the time it appears on a
display. What I would like to do is to report the time difference for
each individual target from the initial appearance to the subsequent
one, and the time difference from the subsequent one to the next, and
so on.

So how do I put these these all together to produce one query:

for each "select distinct target from display"
for number of rows -1 with target
"select timeX - timeY from (subquery for distinct target) where
(X,Y = subsequent, initial times)"

Request Error
this is my mysql query:

$requete1= " SELECT DISTINCT mariages.* FROM mariages, ";
$requete1.= " articles_a_offrir AS A1 INNER JOIN articles_a_offrir AS A2 ";
$requete1.= " ON (A1.id_mariage=A2.id_mariage) WHERE ";
$requete1.= " ((mariages.id_mariage=A1.id_mariage) ";
$requete1.= " AND (mariages.pren_epouse='$prenom_epouse') AND ";
$requete1.= "(mariages.nom_epouse='$nom_epouse')) ";
$requete1.= " OR ((mariages.nom_mari='$nom_mari') ";
$requete1.= " AND (mariages.pren_mari='$prenom_mari')) ";
$requete1.= " OR ((DAYOFMONTH(mariages.date_du_mariage)='$jour') ";
$requete1.= " AND (MONTH(mariages.date_du_mariage)='$mois') ";
$requete1.= " AND (YEAR(mariages.date_du_mariage)='$annee')); ";

but i would like it to display only one wedding even if the family name of somebody exists for two differnet people with a different first name

Exclusiv Request
I'd like to select ID's from a table wich are not in another table:

| TABLE1.ID | TABLE2.ID |
|--------------------------------|
| 1 | 1 |
| 5 | 5 |
| 7 | |
| 8 | |

So, the result of my SELECT should be: 7, 8.

Request Modelling
I would like to list all courses with have less than lets say 10 participants.
Does anybody know how I would have to modify the code below to get this to
work ?

schedule_id is the course itself

SELECT schedule_id,count(schedule_id) FROM attendance WHERE
count(schedule_id)<10 GROUP BY schedule_id ;

My problem is that I would need to use the count function actually within the
WHERE (clause which is not allowed).

Enhancment Request
How does one go about submitting an official MySQL enhancement request?

I am in the process of trying to certify our product (WebLogic Portal) for MySQL and have run into the following in several places ...
database error code [1,093] SQL state [HY000] 2263.; nested exception is: java.sql.SQLException: You can't specify target table 'xxx' for update in FROM clause

The fact that this SQL works just fine for FIVE other currently supported DATABASES but that we will have to change it to make it to work with MySQL is quite an annoyance. As best as I can tell, we won't even be able to use the same SQL for MySQL as we do for other databases so the code will have to look at what DB the app is configured for and choose the appropriate SQL.

Request | Username
I didn't found a good example to set the three % to single variable characters...

My sql string =
SELECT * FROM table
WHERE username LIKE '".$_SESSION['user'] &&& $_SESSION['domain']."' ORDER BY username ASC LIMIT $current_page, $results_per_site");

Password Request
I just recently downloaded the community version of MySQL 5.0. I'm trying to set it up using the Config wizard. A while ago I asked how I go about getting or finding out what my root password is so I can proceed with the set up. I was told to set up my password using the command line.I was also told to log in as root and issue the command

SET PASSWORD ('[new password]');

When I open Command line it automatically asks for my password. Since I don't have one yet I tried to ignore the request by typing the info. above and it won't let me in. Is there another way for me to set up my password using command line?
I also don't understand how to log in as root.

Baffled By A Query Request
I got a task in my database design class which has baffled me. No matter what I'm trying to to, it's a no go.

CREATE TABLE IF NOT EXISTS `country` (
`Code` char(3) NOT NULL default '',
`Name` char(52) NOT NULL default '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
`Region` char(26) NOT NULL default '',
`SurfaceArea` float(10,2) NOT NULL default Ɔ.00',
`IndepYear` smallint(6) default NULL,
`Population` int(11) NOT NULL default Ɔ',
`LifeExpectancy` float(3,1) default NULL,
`GNP` float(10,2) default NULL,
`GNPOld` float(10,2) default NULL,
`LocalName` char(45) NOT NULL default '',
`GovernmentForm` char(45) NOT NULL default '',
`HeadOfState` char(60) default NULL,
`Capital` int(11) default NULL,
`Code2` char(2) NOT NULL default '',
PRIMARY KEY (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I am asked to display the highest populated country on each populated continent. And it is supposed to be done in one query.

How To Get The Time Spent On A Request From PHP?
I would like to write the spent time for a mysql select, in the style
of Google's searches: "x results found for xxxxxx in xx secs".

How could i get the spent time from my PHP code? When you do a mysql
statement on a terminal this is shown at the end of the results, but i
can't find how to get this from PHP.

How Database Handle Request?
I want to write a program that can access to multiple database based on the user ID, but I dont know how the database handle user request. Please help me...

if user A execute program and redirect to database abc, and user B also accessing it. user A retrieve value from database but not yet update it, at the same time user B go get the value. How can I control it? can I code a program that control if A not yet finish, we cannot allowed user B to retrieve the value?

I cannot use Java synchronization. if I use it, it will synchronize for all the request although the request is redirect to different database(I am using many databases but sharing 1 program). There is no point to do synchronization if another user accessing other database that is no user at that moment but still need to wait user at different database finish their tasks first.

MySQL Crashes During Request
I'm working on MySQL 5.0 on Windows XP. I've tested a request on MSAccess and it's working. But if I launch it under MySQL, the PC crashes and I have to restart server.
this is the request :

SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 147 and
Ve_Val ='NO_OUTIL' and Ve_Groupe
IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
148 and Ve_Val ='FIB' and Ve_Groupe
IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
149 and Ve_Val ='1.4732'));

There are 3 requests. If I put 2 requests, it's running. But If I put
more than 2 requests, it crashes.

Have you an an idea about this problem ?

Multiple Tables Request
I would like to perform a query on two tables. I want only records that match the conditions in each of the base.

First bas would be : where date>mktime()
Second would be : where test is not empty

When I performed with the following SELECT, I have too many records with strange joining.

$now=mktime()
SELECT *
FROM table1, table2
WHERE table1.date>$now
AND table2.test<>""

Enter Parameter Value Request
I keep receiving a request to enter paramerters value, even when I remove the where or order by expressions. What should I look for that would prompt this?

Is There A Way To Make 2 ORDER BY In The Same Request?
I'm using Mysql for my databsae but I don't know a lot about it.

I made a request like this:
$query = 'SELECT * FROM serie ORDER BY title ASC'

But I also have a 'season' colunm in my database an I want the title to be order by their name and either the season.

For example my db is filled like this:

id:1, title:spongebob, season:2
id:1, title:alien, season:1
id:1, title:spongebob, season:1

My $request will echo: alien, season 1 - spongebob, season 2 - spongebob, season 1
It's ok for the title but not for the season...

Date Range Request
Dates are stored in unix format GMT. I want to find all records either by "day" or by A Date Range "week" i have to deal with users seeing data in their time zone so people in GMT +9 would see records posted accordingly to someone in say GMT -8? Code:

Request Thing With MySQL .HELP ME!
i own a online radio and i was told i could make a request thing with mySQL .AND I NEED TO NO how to get thru the steps... if u can help that would be great

Request Returned With Sql_error
iam useing windows 2000 pro as server,i install mysql 5.0,
can use window 2000 pro as mainserver for mysql 5.0?
when i create dsn
[MySQL][ODBC 3.51 Driver]Unknown MySQL server host 'ss' (11001)
i cant connect mysql

2nd Request, Alpha & Numeric Queries
I am trying to pass through a string that I am exploding into individual values to use in a query. For example, I want to pull the following three values through a query:

99201
A0040
99211

When I run it through the following query (it's missing some html tages, just trying to do a dry run) i twill run the first value through (regardless if it's a alpha based or numeric based), but after that it hits the else phase of the if (mysql_num_rows($mcalookup) >= '1') { code.

Any clues what's going on? BTW< if I strip off the ' ' around the variable, it dies when it hits the alpha based variable. Code:

MySQL Execute An HTTP Request
Can the MySQL engine execute an HTTP request?

Here is he scenario. I have a table that rarely changes but when it does I need to notify a web service. Is it possible to create a trigger that will make an HTTP call when fired and pass a couple of the field values as URL parameters.

Send Request From Shell To Database
Is there a meaning to send data from the Unix's Shell to a mysql database?

How Do I Get System Date
I have a Timestamp which shows the Date and Time. I would like to change it to just show the Date. How do I do this using phpMyAdmin?

Getting System Date
I was wondering how to set up table to column to get the system date for a date stamp. Could someone help me with this.

I tried
date date SYSDATE

with no luck

Best Way To Set Up Membership System
My site will have users that sign up and can then become part of multiple groups (think similar to Yahoo Groups or something like that). How would be the best way to set up the database/table structure so queries could be run to find both all the groups that a user is a member of and also get membership lists for each individual group?

System Tables?
Is there documentation on the system tables? I couldn't seem to find anything when I did a search on system tables.

I know about the information schema, but I want to know about the tables the information schema uses to build the views it contains.

Registration System
I have my database and HTML forms made. When I click submit to submit the registration it doesnt go to to the database it comes to a page with all of my database stuff on it. Can someone help? I have the page register.html made with the forms on it. When you click on submit its supposed to go to register.php which will send the info to the database. But is doesnt. Here is the register.php code:

System Tables
When I create a new DB it's empty. Are there a base set of system tables / functions and so forth created?

Privilege System
Is there a way to limit update access for a user to a single record? I have a database table that contains various information about people. That information is contained in a record for each person. I want people to have read access to the whole table but update access only to their own record. Is this possible and if so how would I set it up?

System Log For Triggers
Is there a system log file which can be checked to see the histroy of triggers. I am trying to debug a trigger that is not executing when it should.

Rating System
i want to do a rating system like youtube's where it averages out the total ratings. i'd store each of the ratings per each row and have them seperated by comma's then in my code put them in to an array.

i also wanted to store the usernames of the voters in the database in the same fashion (without displaying them though) as above say instead of using cookies (as they can be deleted). would method this be feasible? or would this say overload the database and cause slow down?

Booking System
Hi, does anyone know of a site (tutorialised / open source) that offers a PHP-MySQL Online Booking System (similar to a Hotel Booking System)

Privilege System
Can I change structure on "mysql" database to implement new Privilege System?
Views named "user", "host" etc will return old structure style.

System Variable
i am connected to the server and
i build some tables etc etc. everything is fine.

i run the command "show variables". it executes fine and shows me the values
of all the variables on the server. now if i try and change the value of the
variable tmpdir using the set command, it tells me unknown "error 1193:
system variable tmpdir". help needed here........

another point, the variable "tmpdir", points to the dir c:/windows/temp. i
put a text file in that dir and try to load some data from that file into a
table, and i get an error "file not found".

Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts?
And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.

System Variable.
i am running mysql version 4.0.16-nt. now, i am connected to the server and
i build some tables etc etc. everything is fine.

i run the command "show variables". it executes fine and shows me the values
of all the variables on the server. now if i try and change the value of the
variable tmpdir using the set command, it tells me unknown "error 1193:
system variable tmpdir". help needed here........

another point, the variable "tmpdir", points to the dir c:/windows/temp. i
put a text file in that dir and try to load some data from that file into a
table, and i get an error "file not found".

System.OutOfMemoryException
sql 2 error: Exception of type 'System.OutOfMemoryException' was thrown.

this is the error I am getting using Mysql connect and Visual Studio.

My program is set on a loop and after the program has opened and closed around a million connections in my error logs i see this error about 80 or 90 times then it gives me a timeout error until I stop the loop. If i try to restart it it will continue to fail unless I restart the computer containing the database. Does anyone know what is causing this or how I can fix it?

Edited 1 time(s). Last edit at 07/18/2007 12:39PM by Joe Gates.

Voting System
***********************
| game_id | discussion_id |
***********************

SELECT game_id, discussion_id, count( * ) FROM `games_voting` AS votes
GROUP BY votes.game_id, votes.discussion_id

using the above query to generate a count of votes grouped by game and then user who got the votes
right now it returns all the users that received votes for each game, i'd like to only receive the user with the top vote count

Scoring System
Im sure some of you have come across 'neave' games?

The scoring system is setup as below:
CREATE TABLE games_pacman (
name VARCHAR(20) PRIMARY KEY NOT NULL,
score INT(11) DEFAULT '0' NOT NULL,
ip VARCHAR(15)
);

CREATE TABLE games_banned_ip (
ip VARCHAR(15) PRIMARY KEY NOT NULL
);

Clearly, it is possible to ban ip addresses from using the score system.

But, can anyone tell me please if it is possible to set it up to ban certain names from being used?

System Configuration
I'm currently rebuilding my home system - a couple of linux boxes with Debian.
Although I don't have a high load on any of my systems, I am going to set up a separate box with my mail server and another for my web services. I'm wondering if there is an arguement for or against putting MySQL over on the mail box or even putting it on its own box?Assuming the arguement is for putting MySQL on a different box, are there any pitfalls, tips, tricks I should consider before starting this project?

Heavy Traffic System
I am developing a very heavy traffic site. It may happen that at same time (exactly same) two user will try to register with the same username. What will be the approach to prevent this situation. Which user will be able to register and which one will be denied? Do I need to use any locking facility? If so is it a wise decision to lock entire table?

How To Get Today System Date?
Can anyone tell me how to fire a query in MySQL to get todays system Date.
I want to Subtract yesterday's date from Today's date.I want to check that if todays date is greater than yesterdays date i have to delete yesterdays date.

What Is The Best Content Managment System?
I'd like to speed up queries of the form...

SELECT ... WHERE lower(mytable.name)=?

...by using an index.

Online Booking System
Im working on a dog walking site for my girlfriend and she wants to have an online booking system for walks.

Can i use MySQL & PHP to create this or is the a language more suited to this form of application.

Standardized Naming System ?
is there a common naming system for db objects ?

Like:

1) Tables: mytable, tblmytable, tbl_mytable

2) Indices: idx_anindex

3) Columns: int_somenumber, date_lastupdate

4) id for the numerical primary key e.g. table customers.id
and then for referencing foreign keys
table addresses : addresses.customer_id or addresses.customer_fk

OK, I know I could name them the way I want but perhaps there is some
kind of common sense in this regard ?

System Variable Tx_isolation
The System Variable tx_isolation is used for what? What this actually means,

Development Server

System Variable Value
tx_isolation READ-COMMITTED

Production Server
trx_isolation REPEATABLE-READ

What will be the difference between these two thing?