Current Code: WHERE EXTRACT(YEAR_MONTH FROM timestamp_field) = EXTRACT(YEAR_MONTH FROM now())") Instead of EXTRACT(YEAR_MONTH FROM now()), I want EXTRACT(YEAR FROM now()), but I want to hard code the month in. How do I go about concatenating the extract results with the MM month, for example 09. I tried a few options below, with no l...
SELECT gameratingstblx245v.gameid,avg( gameratingstblx245v.rating ) as avgrating, count(gameratingstblx245v.rating) as count,gamedata.name ,gamedata.gameinfo FROM gameratingstblx245v LEFT JOIN gamedata ON gamedata.id = gameratingstblx245v.game_id WHERE gameratingstblx245v.game_id=gameratingstblx245v.game_id GROUP BY gameid ORDER BY a...
I am having a great deal of trouble porting some stored procedures to regular MySQL statements. We have stuff like this example http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END Where many statements are executed. Several If statements and...
I have a simple contest sign-up web form. Each contestant can check a box to be eligible to win one of 20 prizes. Upon submitting a form, a row is created in the "contestants" table. For each prize they have checked, a row in the "entries" table is created. I am trying to make a "results" page that lists all prize names, and below each ...
How do I SELECT rows and return all the columns, instead of specifying individual column names? Can I also return only columns that match a specific criteria? (eg. col names beginning with an "_") ...
I have the following bash script: #!/bin/sh MYSQLHOST="mysql.remote-host.com" MYSQLDB="mysqldb" MYSQLTABLE="table" MYSQLUSER="user" MYSQLPASS="pass" MYSQLDUMP="Report/report.csv" LOG="Report/report.log" MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}" echo "Report Begin: $(date)" echo "MySQL Du...
I have a tables rooms, apartments and hotel. Now its easy for me to get all free rooms, name of hotel address or same thing for apartments by using separate queries ex : Get all free rooms : SELECT hotel.name, count(*) AS num_of_free_rooms FROM hotel, rooms WHERE rooms.occupied !=0 AND hotel.hotel_id = rooms.room_hotel GROUP BY hotel...
I would like to set 5 new content items for monday through friday, but don't want to have to enable them each day. Would it be best to set a timestamp in a mysql field, then something like this. Set up a crontab with php that runs if timestamp is equal to today set "on" to 1. What date/time format would be best, also if I put dates 0...
When executing in *ONLY_FULL_GROUP_BY* mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in *ONLY_FU...
I want to know how to limit the MySQL result. I can use "select * from students" to show all the students, but it is too many. I can add where condition, but it still many students after I use where to filter the result. Is it possible to return first 100 students, and order by first name. And next time, I want the first 101-200 ... ... ...
For some reason, JavaScript/PHP wont delete my data from MySQL! Here is the rundown of the problem. I have an array that displays all my MySQL entries in a nice format, with a button to delete the entry for each one individually. It looks like this: <?php include("login.php"); //connection to the database $dbhandle ...
Hi guys, i dont remember how to join a table to itself.. my table is: | id | proc | value | kind | | 1 | 1 | foo | a | | 2 | 1 | bar | b | | 3 | 2 | some | a | And i need to retrieve the value col where proc is $proc and kind is both 'a' and 'b'.. well, i need to do have that (looking for proc = 1): | v_a | ...
I'd like to have the results of my full text search in MySQL sorted by how completely the pattern covers the match. For example searching for apple in a nutrition database should sort "apple, raw" higher than "apple fritter" since 5/9 > 5/12. I can do this rather trivially outside the database, but i'm looking for a query that'll do it...
Hello all. For each item in the first table, there is a 'numberOf' field. The value of this field must have an identical number of rows in a related table. These are like reservation rows so multiple users can book the item at the same time. This syncronisation sometimes goes out and there are more rows than the 'numberOf' field variabl...
I would like to insert some value into a table, which has an auto-incrementing field as a primary key. Then I want to retrieve the ID by using mysql_insert_id() and use this ID as a foreign key in another table. The problem is - although very unlikely - it may happen that between the first insertion and the later retrieving, another inse...
How do i find if my update is successful or not? i update using a where uniqueName=name so i should always only update 0 rows or 1. What is a good way to check if i updated a row or not? ...
Hi need some help, I looking to build a news archive in python/django, I have no idea where to start with it though. I need the view to pull out all the news articles with I have done I then need to divide them in months and years so e.g. Sept 09 Oct 09 I then need in the view to some every time a new news article is created for a new...
table: postid|userid|post|replyto post sql SELECT * FROM table WHERE postid=12 total replies sql SELECT COUNT(*) AS total FROM table WHERE replyto=12 the expected result is the "post table" + how many replies to the post. replyto field is the target postid. somehing like : postid|userid|post|replyto|totalreplies Is there a possibi...
I've got the following query to generate new order numbers in the format MMDDYY-# where the number is the next in the sequence for today's orders. select concat(date_format(now(),'%m%d%y'),'-',ifNULL(max(right(po_number, LENGTH(po_number)-7)),0)+1) newPO from orders where left(po_number, 6) = date_format(now(),'%m%d%y') This works fi...
I have the table below relID value charge 1 2 5 1 8 2 2 1 10 2 4 6 2 9 2 For the above table i need for a given value ex 10 to find what to charge for each relID In the above for value<10 i need to get charge=5 for relID=1 and charge=2 for relID=2 I am ...