mysql-query

Use select query inside the Insert query for the same table name

Hi Friends, Is it possible to use a "select" query inside the "Insert" query, But the condition is i want to use the same table name for both "select" and "Insert" query.For Examplemysql> insert into sample_elements (name,position,ownerel) values ('Namespace1', select id from sample_elements where name='Namespace1',0); Please guide me o...

MySQL Query help

I'm not very good at database queries. Need help with what is probably a simple query. Database: MYSQL zipcodes [table] zip | city | state post [table] post_id | title | post | zip I need to display the number of posts for each unique city. ...

MySQL MAX_JOIN_SIZE Error! Need to optimize query.

I run this query: SELECT u.user_id, u.fname, u.lname, n.title, n.news_id, n.post, n.zip, z.city,z.state_abbr FROM yc_users u, yc_news n, yc_zipcodes z WHERE u.user_id = n.user_id AND n.zip = z.zip ORDER BY n.stamp LIMIT 10 And get this error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BI...

Return ALL table rows in an <OL> instead of only returning 2 out of 3.

Trying to pull rows from a table and list them in an ol, however, my code only wants to return the last 2 out of the 3 total rows. <?php // Connects to your Database mysql_connect("mysql.***.com", "***", "***") or die(mysql_error()); mysql_select_db("shpdb") or die(mysql_error()); $data = mysql_query("SELECT * FROM savannah") or die(mys...

DELETE FROM table WHERE ID='$id' — Variable refuses to stick.

Trying to perform a very simple task here. I have an <ol> that contains 4 rows of data in some handy <li>s. I want to add a delete button to remove the row from the table. The script in delete.php appears to have finished, but the row is never removed when I go back and check dashboard.php and PHPMyAdmin for the listing. Here's the cod...

Making a form submit a variable safely.

while($info = mysql_fetch_array( $data )) { // Print out the contents of the entry Print "<li><b>Name:</b> ".$info['name'] . " <br />"; Print "<b>ID:</b> ".$info['ID']." <br />"; Print "<b>Age:</b> ".$info['age'] ." <br />"; Print "<b>Location:</b> ".$info['location'] ."<br /> "; Print "<form action=delete.php method=POST><input name=ID ...

GROUP_CONCAT and DISTINCT are great, but how do i get rid of these duplicates i still have?

hi, i have a mysql table set up like so: id uid keywords -- --- --- 1 20 corporate 2 20 corporate,business,strategy 3 20 corporate,bowser 4 20 flowers 5 20 battleship,corporate,dungeon what i WANT my output to look like is: 20 corporate,business,strategy,bowser,flowers,battleship,dungeon b...

Query for match in MySQL ListOfValues

Whats the best way to query MySql when I want the query return all rows where at least one value in a list of values matches at least one value in a list of values? example If my table contains the following rows: name groups item1 gr1, gr2 item2 gr1,gr2,gr3 item3 gr1,gr3 And I have a list of values: "gr...

Why doesn't this index work (Mysql)

I have this table: CREATE TABLE `maindb`.`daily_info` ( `di_date` date NOT NULL, `di_sid` int(10) unsigned NOT NULL default '0', `di_type` int(10) unsigned NOT NULL default '0', `di_name` varchar(20) NOT NULL default '', `di_num` int(10) unsigned NOT NULL default '0', `di_abt` varchar(1) NOT NULL default 'a', PRIMARY KEY ...

MySQL query that computes partial sums

What query should I execute in MySQL database to get a result containing partial sums of source table? For example when I have table: Id|Val 1 | 1 2 | 2 3 | 3 4 | 4 I'd like to get result like this: Id|Val 1 | 1 2 | 3 # 1+2 3 | 6 # 1+2+3 4 | 10 # 1+2+3+4 Right now I get this result with a stored procedure containing a cursor and ...

Select different field in mysql if a field is set to null?

I want to select displayname as username if username =null and if username is NOT NULL then select username as username eg. id username displayname 1 xyz NULL 2 NULL abc How can I do that in one single select statement...Thanks ...

Concatenating records in a single column without looping?

I have a table with 1 column of varchar values. I am looking for a way to concatenate those values into a single value without a loop, if possible. If a loop is the most efficient way of going about this, then I'll go that way but figured I'd ask for other options before defaulting to that method. I'd also like to keep this inside of ...

Select * from table1 where JID is not in table2 with table2.uid also not being 11111

I want to select everything from table one, which contains a column JID. These are available things the player can learn. But there is a table2 which has a list of things the player has already learned. So if JID is in table2, it has been learned, and I do not want that selected from table 1. For exampel. table 1 JID Title Descripti...

GROUP BY behavior when no aggregate functions are present in the SELECT clause

I have a table emp with following structure and data: name dept salary ----- ----- ----- jack a 2 jill a 1 tom b 2 fred b 1 When I execute the following SQL: SELECT * FROM emp GROUP BY dept I get the following result: name dept salary ----- ----- ----- jill a 1 fred b ...

how to limit result based on the fields

How to use ' LIMIT' in mysql database query based on fields . just consider a user has 10 phone number and i would like to get only 5 phone number for that user . I would like to get only 5 phone numbers per user . not only 5 results from database ? ...

mysql query to dynamically convert row data to columns

I am working on a pivot table query. The schema is as follows Sno, Name, District The same name may appear in many districts eg take the sample data for example 1 Mike CA 2 Mike CA 3 Proctor JB 4 Luke MN 5 Luke MN 6 Mike CA 7 Mike LP 8 Proctor MN 9 Proctor JB 10 Proctor MN 11 Luke MN As you see i have a set of 4 distinct districts (...

rails - apply complex visibility settings to thinking sphinx results

in the product i'm developing, i have a Message model. Message can be restricted to groups, or not restricted (available to everyone). If user belongs to one of Message's groups OR message is not restricted, user can see the message. here is the query selecting visible messages (in hope that it can clarify what i mean) (2,3,4,5,6,1) ...

SQL rounding and truncation, needs a thorough explanation

Hello. I'm a novice when it comes to SQL and PHP, and I'm trying to round an average price result and take off the extra zeroes that currently appear. Currently my result turns up as: $3.005000 My code currently reads as follows: $result = mysql_query("SELECT AVG(price) FROM milk WHERE price > 0 "); $row = mysql_fetch_row ($result); ...

Can column aliases be evaluated in the WHERE clause?

First off, I'm not an expert with MySQL queries, and I can't seem to find a solution to my problem. Example: SELECT SUM(IF(table1.col1<4,1,0)) AS score WHERE score>0 GROUP BY table1.col2 The above would give an error as the column score does not exist in table1. This is my workaround: SELECT SUM(IF(table1.col1<4,1,0)) AS score GROU...

What's the best way to query for position changes?

I have a table of results I'd like to display: | change | position | name | score | |----------------------------------| | - | 1 | Bob | 10 | | +1 | 2 | Tom | 8 | | -1 | 3 | Sam | 7 | |----------------------------------| The change column reflects the position movement of the person, so ...