mysql

MySQL - generate numbers for groups of a result

I need a query to return this result: +---------+-----+-------+ | ref_nid | nid | delta | +---------+-----+-------+ | AA | 97 | 1 | | BB | 97 | 2 | | CC | 97 | 3 | | DD | 98 | 1 | | EE | 98 | 2 | | FF | 98 | 3 | +---------+-----+-------+ However, I do not have the delta colum...

Select in a many-to-many relationship in MySQL

I have two tables in a MySQL database, Locations and Tags, and a third table LocationsTagsAssoc which associates the two tables and treats them as a many-to-many relationship. Table structure is as follows: Locations --------- ID int (Primary Key) Name varchar(128) LocationsTagsAssoc ------------------ ID int (Primary Key) LocationID ...

Getting Popular Topics on a Custom Made Forum

For this website we're working on, we're trying to get the most popular topics (based on how many posts have been made in them within the last 24 hours). We have a medium to large based forum, and the current MySQL query looks like this: SELECT `forums_topics`.`id`,`forums_topics`.`name`, ( SELECT COUNT(`id`) FROM `...

Select number of rows in all tables in the database

I wonder if this is possible with straight SQL on MySQL. I need to do SELECT COUNT(*) FROM on each table in the database and output results in one result set. Is it possible to do with just SQL? ...

Using LIKE in MySQL SELECT fields

I've been trying to select the status of doing a LIKE comparison: SELECT (`my_column` LIKE '%thepattern'%) AS `did_match` Thanks! Edit: Just to clarify this was used so I could get this: SELECT (CASE WHEN `title` LIKE '%keyword%' THEN 20 ELSE 0 END) + (CASE WHEN `desc` LIKE '%keyword%' THEN 10 ELSE 0 END) AS `match` F...

Best practices to store CreditCard information into DataBase

In my country the online payments are not an old thing, the first time i saw a web application taking payments directly to a local bank account was last year. So, Im a newbie coding web payment system. My question is, what are the best practices to store creditcard information into the database... I have many ideas: encrypting the cre...

How to add dynamic profile fields in Invision Power Board?

I run a game server and want to link the persons in game character name and stats to Invision Power Board. I've setup IPB so players currently login with their game login. That means their user name on the forum is the same as their user name for the game. They can have multiple characters on 1 account so ideally I'd like to allow them...

Search SQL Question Between Related Two Tables

Hi, I am writing some kind of search engine for my web application and i have a problem. I have 2 tables first of is projects table: PROJECTS TABLE id int(11) NO PRI NULL auto_increment employer_id int(11) NO MUL NULL project_title varchar(100) NO MUL ...

PHP auto refresh page without losing user input

I'm working on a PHP collaboration software project. I have a page that shows the latest updates from other users who are adding content to the database, but also has a form input to allow the user to enter text. I am currently using this code to refresh the page automatically every 30 seconds: header('Refresh: 30'); The problem is ...

How to add indexes to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, recently for a development in the project, I have to search the database by another field. For example SELECT * FROM table WHERE produc...

PHP database class pattern questions/suggestions

I am developing my own class model for my database and I would like to know what you guys think of it. For each table in my database I have 2 classes. The first one, is a fetching class (I don't know how to call it..), this class has some static methods interacting with the database (fetchAll, fetchById etc..). The second class is the m...

Recreation of MySQL DB using "mysql mydb < mydb.sql" is really slow when the table has tens of millions of records. How to improve it?

It seems that a MySQL database that has a table with tens of millions of records will get a big INSERT INTO statement when the following mysqldump some_db > some_db.sql is done to back up the database. (is it 1 insert statement that handles all the records?) So when reconstructing the DB using mysql some_db < some_db.sql then the...

posting multiple radio button values to mysql using "foreach"

i have adjusted my code slightly but i am still having difficulty posting it to the table. could someone please provide me with an example of the foreach array? form page <div style="padding: 15px;"> <span class="loginfail" style="font-size:24px; font-weight: bold">Notifications</span><p> <?php include("progress_insertcomment.php")...

Troubleshoot MySQL query

I need help with this code, it doesent insert the values to my database. Probably a simple problem but it's late here in Sweden so I would appriciate if someone could have a look at this and tell me what's wrong: include "../../inc/mysql_config.php"; $to = mysql_real_escape_string($_POST['to']); $message = mysql_real_escape_string($...

How to use a proprety/value table in MySQL

I inherited a mysql database that has a table with columns like this: object_id, property, value It holds data like this: 1,first_name,Jane 1,last_name,Doe 1,age,10 1,color,red 2,first_name,Mike 2,last_name,Smith 2,age,20 2,color,blue 3,first_name,John 3,last_name,Doe 3,age,20 3,color,red ... Basically what I want to do is treat...

Need to map classes to different databases at runtime in Hibernate

I have MainDB database and unknown number (at compile time) of UserDB_1, ..., UserDB_N databases. MainDB contains names of those UserDB databases in some table (new UserDB can be created at runtime). All UserDB have exactly the same table names and fields. How to handle such situation in Hibernate? (database structure cannot be change...

Reduce durability in MySQL for performance

My site occasionally has fairly predictable bursts of traffic that increase the throughput by 100 times more than normal. For example, we are going to be featured on a television show, and I expect in the hour after the show, I'll get more than 100 times more traffic than normal. My understanding is that MySQL (InnoDB) generally keeps m...

more radio problems * sighs * been at this for one week now

hi guys really i don't know what im doing wrong... i am trying to process multiple radios with mysql. the first step has involved me echoing the results on the process page. each time i select any radio option it simply displays the first row result. this is that i see after trying to submit the form: Notifications Thank you. T...

how to get Client IP address

Possible Duplicate: How can I get the clients IP address in a PHP webservice? How can i get Client IP address using php. I want to keep record of the user who logged into my website through his/her ip address. Please help me. ...

select query related problem

i have interest rate and amount in a table where interest rate are ranged in different values like 4.5,4.6,5.2,5.6 etc. i want to get sum of amounts classified by interest rate where interest rate will be separated by .25. for example all amount having interest rate 1.25,1.3,1.4 will be in one group and 1.5,1.67,1.9 will be in another ...