mysql-query

Need help with MySQL query

Hi, I need help with MySQL query. Here is a part of my database: +---------------+ | users | +---------------+ | id |--- | username | | +-----------------+ +---------------------+ | first_name | | |users_in_projects| | regular_posts | | last_name | | +-----------------+ +----...

Convert query into cakePHP find statement

hello friends, Any one please help me to convert this query into cakePHP find statement? $sql = "select categories.id FROM categories WHERE categories.competition_id='".$id."' AND ". $logger['Competitor']['weight']. " betwee...

MySQL query help with grouping and adding

I have a table called user_logins which tracks user logins into the system. It has three columns, login_id, user_id, and login_time login_id(INT) | user_id(INT) | login_time(TIMESTAMP) ------------------------------------------------------ 1 | 4 | 2010-8-14 08:54:36 1 | 9 | 2010-8-16 08:...

Mysql -change DB,tables to utf8

In /etc/my.cnf the following has been added character-set-server=utf8 collation-server=utf8_general_ci But for the database and tables created before adding the above how to convert the database and tables to utf8 with collation settings ...

MySQL grouping months based on TIMESTAMP

I have a table called user_logins which tracks user logins into the system. It has three columns, login_id, user_id, and login_time login_id(INT) | user_id(INT) | login_time(TIMESTAMP) ------------------------------------------------------ 1 | 4 | 2010-6-14 08:54:36 1 | 9 | 2010-7-16 08:56:36 ...

Using LIKE to search for a name

I have a table which contains contact information. This table has 1 column relevant to names. It looks like this: Sort Name: Doe, John Clinton, Bill Dooby Doo, Scooby Sadly, there are no first name / last name columns, and they can't be added. If a user enters the search term "john doe", is there any way I can get mysql to return the ...

mysql optimization with IN or OR

Dear all, I have a set of large values that need to be compared in mysql. May I know which is faster? For example: Opt 1: SELECT * FROM table WHERE v = 1 or v = 2 or v = 3 or v = 4 or... v = 100 Opt 2: SELECT * FROM table WHERE v IN (1,2,3,4,5,6,7,8,...,100) May I know which option is faster for large value? Is there any better s...

MySQL, stored-function, using STRING variable as QUERY

I was wondering if it is possible in a mysql stored-function or stored-procedure compose a mysql query as a string variable you can execute later? I have a stored-function get_district_part ((district_id INT,county_id INT,city_id INT,zip_id INT)) RETURNS INT that references the table: CREATE TABLE IF NOT EXISTS `modx`.covereage_distric...

Inserting an email address into a table field

// write student record $query = "INSERT INTO Student (SLastName, SFirstName, SeMail, SGrade, SPhone, SCell, SLunch)". " VALUES ($LastName, $FirstName, $email, $grade, $phone, $cell, $lunch)"; echo $query . '<br />'; $result = mysql_query($query); if (!$result) die("Error inserting Student record: ". mysql_error()); INSERT INTO ...

Mysql - How to select users who have similar info (like a shared phone number)

Just for an example. How can I query my db to find two users who might be using the same phone number? I'm not inputting a specific number but I want to find all instances phone numbers that are being used more than once in the user table. ...

Table data increased - query is slow.

Hi, I have mysql DB in which a single table has 7 million records, the table needs to be optimized, i am little bit hesitant to add index as that is going to take much time to add across 7 million records. I keep getting this table queries in slow query log, along the query is written in optimistic manner. What is the best approach to ...

how to set up daily php functions or mysql queries

Hi. I have wondered for a while now how to this. If I want to take weekly backups of all or many of my tables that stores values that changes every day in mysql. I also want to execute daily functions with php that update update values in my db. I was thinking of making a stock investing function. Where I have fictional data as the val...

PHP script to list total posts by users

I have a database with two tables, Users and Posts. I'd like to write a php script to list, in order, the top 20 usernames with the number of posts they've made. The username field is cUsername in the Users table. The Users table intUserID field and the Posts table's intPosterID field correspond to eachother. Any help would be much appr...

mysql - orderby and left outer join issue

When I use left outer join with order by in the below query the query executes infinitely without giving a resultset. However - individually they work fine that is if I change it from left outer join to inner join. But I need all the values from interregen table below. Not understanding what could possibly be the error :( My mysql qu...

mysql select error for uppercase and lowercase table names

I have a table let say is it called "test". I use Ubuntu and MySQL 5.1.41-3ubuntu12.6 version. If I try to do "select * from test" it works but if try "select * from Test" or "select * from TEST" it does not work. Does any had this problem, or knows how to solve it? Thank you. ...

Replacing a string in a text field

Hello, I have a column in my database table which has lows of text and lows of rows. In the text, a url needs to be changed for every row. In each row the url can exist more than once. Can I use the replace function to change all the urls in the text field in my database table without affecting the rest of the text in that same col...

Quering and writing to a file : errorcode 28

hi, I am getting this error when I try to query the database and write results to a file. It never occured before. java.sql.SQLException: Error writing file '/tmp/MYwADPLw' (Errcode: 28) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) a...

Mysql Custom select query

Hello, I have these 2 mysql queryes SELECT `name` AS name1,`id` AS id1 FROM `table` WHERE id=1 SELECT `name` AS name2,`id` AS name2 FROM `table` WHERE id=2 The results will be outputed like this NAME1 ID1 john 1 NAME2 ID2 meg 2 Is there anyway that from 2 queries I would make 1 and it will show all results on 1 line from sam...

Need help optimizing a MySQL query - JOINs not using the correct indexes

I have this query below that I've rewritten a dozen different ways, yet I am unable to get it optimized and loaded in under 8 seconds. If I can get it to 2s, that would be good. 1s or less would be optimal. This query retrieves a list of books that are currently available for sale or trade, and performs a bit of filtering. This query...

How to sort a PHP array?

Is there any way to "custom" sort an array? For instance, if I have the following numbers: $array = array('0' => 1, '1' => 2, '2' => 3); I would like it to order them in this fashion: $array = array('0' => 2, '1' => 1, '2' => 3); How would I do this or is it not possible? I am basically wanting to list this array in one database ...