mysql-query

mysql multiple queries with php

I am trying to have a search result for someones name exploded then the first name and last name each search against each field 'first' and 'last'. But need to leave room what if someone search for first last midle name like = $who = James David Smith it will exploded it and search each of the names against the first column and the last...

complex sql order by

id | message | reply id | date 1 | my new app.. | 0 | 10 / 10 / 2009 (latest message on top, follow by replies) 5 | love ur app.. | 1 | 11 / 10 / 2009 (this should show under the main message) 6 | another comm | 1 | 12 / 10 / 2009 2 | application 2 | 0 ...

mysql query help?

I have a table with 'id' as the primary field and set to auto increment.. 'name' of type varchar .. 'category' of type varchar .. Table contents ::. * id=1,name=abc,category=tv * id=2,name=abc,category=radio * id=3,name=abc,category=tv * id=4,name=abc,category=radio * id=5,name=abc,category=tv * id=6,name=abc,category=tv Now, I want to...

INSERT INTO..SELECT..ON DUPLICATE KEYS ambiguous ids

Hey there, I have the following table: mysql> SELECT * FROM `bright_promotion_earnings`; +----+----------+------------+----------+-------+ | id | promoter | generation | turnover | payed | +----+----------+------------+----------+-------+ | 1 | 4 | 1 | 10 | 0 | | 3 | 4 | 5 | 100 | ...

Using MySQL MATCH for mix of column types in non-case-sensitive

I am using a MATCH condition to match against a int field and a varchar field. According to http://bugs.mysql.com/bug.php?id=22343 when mixing binary and non-binary column types the match becomes binary and thus case sensitive. My question is how would I go about making the search non-case-sensitive? I have tried using MATCH (lower(a),b...

Finding ID having all values (mySQL, SQL)

Think about a table like this ID Value 100 1 100 3 101 1 101 4 102 2 102 5 103 2 103 4 104 1 104 3 105 2 105 5 The problem is, if I give values 2 and 5 I should get 102 and 105 which both 102 and 105 are having values 2 and 5 at the same time or if...

MYSQL case sensitive search (using hibernate) for utf8

Hi every one, I have Login Table that have utf8 charset and utf8 collation when I want check user name and retrieve other information for this specific user name the hql query give me the same result with lowercase and uppercase. what should l do for my HQL query that work case sesitive I use Mysql 5 and java hibernarte this is my que...

How to update a MySql DB using Flex

Hello everybody. Just got developing with Flex, and i like it. I've done some simple apps to get the felling of it, nothing involving updating a file or a database. I wanted help, how to do a query to a MySql DB from a Flex application (thats gonna be running in a web server). I didn't saw any duplicated questions, sorry if any exist...

in a subselect query how do i refer to the id of the top level select statement?

i wanted to automate my table population for testing purposesi needed to edit some columns from a certain table but i must make sure that the values i put in that certain column does not simply come out of randomnessso the values actually comes from another table on a certain condition so how do i do that? like here: update table_one se...

sql pulling a row for next or previous row of a current row

id | photo title | created_date XEi43 | my family | 2009 08 04 dDls | friends group | 2009 08 05 32kJ | beautiful place | 2009 08 06 EOIk | working late | 2009 08 07 Say I have the id 32kJ. How would I get the new or previous row? ...

I need helping creating a query to salt and md5 a password then confirm the login on a MySQL Table.

I've created a view of a table on a MySQL database to enable another application to use our existing (centralized) clients table. Our passwords are stored as md5(password + salt) + ":" + salt Normally I decode this via a programming language of the given app we're connecting to it but... This time it's a third party app and I only h...

Using ORDER and GROUP with MySQL to get the highest number for each GROUP

I have made a simple auction section on my site, and I would like to display the user's current high bid on their My Bids page. I have a table that holds each unique bid that has the unique auction_id. My current query is as follows, but this only orders the groups instead of ordering what is inside the groups as well. I only want the...

generate a "create table" sql command based on an existing table in mysql

I have a mysql shell but for security reasons I cannot run the mysqldump command. I have a table that I made a while ago with a lot of columns, and I want to generate a new "create table" command to create that table on a different database. Is there some command I can run in the mysql shell to generate that? Thanks. ...

Group by and order with joining tables

Hi, I am having a problem sorting results from joining tables that have to be grouped in Mysql. This is my tables setup. Owners Table owner_id | owner_name 1 | Test owner 1 2 | Test owner 2 3 | Test owner 3 Images upload table image_id | image_name | ownerid | upload_date 1 | image1.jpg | 2 | 04-...

[MySQL] How to get the latest items distinctively in a row?

Hi, I want to get the remaining/latest balance of the cardnumber from the rows. Below is the sample of the table. trans_id | cardnumber | trans_date | balance --------------------------------------------------------------- 1 | 1000005240000008 | 2009-07-03 04:54:27 | 88 2 | 1000005120000008 | 2...

MySQL DATE_ADD - date is wrong?

So I've got a simple query in MySQL that sets a new member's expiration date once they pay their dues: UPDATE members SET joined=CURDATE(), expires=DATE_ADD(CURDATE(), INTERVAL 1 YEAR), active='1' WHERE id=1000 this query has run 200+ times, normally with the correct result - the current date is put in the joined field, and a year fro...

How can I optimize this MySQL query that involves two left joins?

I cannot figure out why my query slows down. What it boils down to are four tables: team, player, equipment, and metadata. Records in player and equipment have a FK to team, making team a parent of player and equipment. And all three of those tables' rows each have a record in metadata which stores things like creation date, creator ...

How to write SQL Query that matches data from a .csv file to a table in MySQL?

Is it possible for me to write an SQL query from within PhpMyAdmin that will search for matching records from a .csv file and match them to a table in MySQL? Basically I want to do a WHERE IN query, but I want the WHERE IN to check records in a .csv file on my local machine, not a column in the database. Can I do this? ...

MySql: Problem when using a temporary table

Hi, I'm trying to use a temporary tables to store some values I need for a query. The reason of using a temporary table is that I don't want to store the data permanently so different users can modify it at the same time. That data is just stored for a second, so I think a temporary table is the best approach for this. The thing is that...

how to optimize mysql query if i have too many OR operators in my query?

using PHP and MySQL i have grabbed an array of facebook user ids from facebook. Now i want to find the corresponding username in my application for this array. Clearly in my application the user table contains unique username and unique fb_uid values. my rudimentary understanding oof programming led me to 2 ways: 1) use a loop and r...