I know I can copy all my MySQL code manually to files and then put those files into source control. But is there any way to do this automatically? I would like to do this to stored procedures, but also to table/event/trigger creation scripts. ...
I have a table that stores a value that will be added to over time. When I want to add to the value I would like to do so in a single query rather than - Get oldValue from database newValue = oldValue + X update row with newValue $query1 = "SELECT value FROM table WHERE id = thisID"; $result1 = mysql_query($query1); while($row=mysql_f...
I'm implementing a table per subclass design I discussed in a previous question. It's a product database where products can have very different attributes depending on their type, but attributes are fixed for each type and types are not manageable at all. I have a master table that holds common attributes: product_type ============ pro...
Hi ! I have a table with a varchar column. Its collation is set to utf8_bin. My software using this table and column works perfectly. But when I look at the content in phpmyadmin, I only see some hex values or [Blob xB]. Can I make phpmyadmin show the content correctly? Besides, when I set the collation to utf8_general_ci or utf8_unico...
The mysqldump option --tab=path writes the creation script of each table in a separate file. But I can't find the stored procedures, except in the screen dump. I need to have the stored procedures also in separate files. The current solution I am working on is to split the screen dump programatically. Is there a easier way? The code I...
This query fails when I add the line shown... Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <------------------------- FAIL group by CompanyID Any way to do this? Here's a long winded background if it'll help.... I have a single table query. Here's a sample of the table: CompanyID, State 1,OH ...
Can we join a table with the result of a subquery, such as: select name from gifts LEFT OUTER JOIN (select giftID from gifts) ... If not, can it be done by some methods, such as creating a temporary table? P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS? ...
Using php / mysql Hi Guys. I am building an application that allows people to download rather large sums of data as rows from a mysql database. The data exceeded 2 million rows so i sharded the data (this all went fine). The data is collated using geocoded latitude/longitude paring and uses a very complex SQL query to gather the query. ...
Let`s have a example hotels table: CREATE TABLE `hotels` ( `HotelNo` varchar(4) character set latin1 NOT NULL default '0000', `Hotel` varchar(80) character set latin1 NOT NULL default '', `City` varchar(100) character set latin1 default NULL, `CityFR` varchar(100) character set latin1 default NULL, `Region` varchar(50) charact...
In this query select wrd from tablename WHERE wrd LIKE '$partial%' I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end. Would it be select wrd from tablename WHERE wrd LIKE ':partial%' where :partial is bound to $partial="somet" or would it be select wrd from tablename WHERE w...
Every few hours, My MySQL database (running 5.1) seems to hang and viewing the processlist shows dozens of processes running "SHOW VARIABLES". They finish instantly only to be replaced my more of these processes. After a few minutes, everything disappears and goes back to normal. I'm using the .Net Connector and the web application is o...
Hi, I currently have a table which consists of user information and lesson id; the table layout looks like: ---------------------------------------------------- |employeeID|numVisits|lessonID1|lessonID2|lessonID3| ---------------------------------------------------- |33388 |2 |1 |0 |3 | and a lessons ta...
I have a table called Sessions with two datetime columns: start and end. For each day (YYYY-MM-DD) there can be many different start and end times (HH:ii:ss). I need to find a daily average of all the differences between these start and end times. An example of a few rows would be: start: 2010-04-10 12:30:00 end: 2010-04-10 12:30:5...
I would like to try using Outer Join to find out duplicates in a table: If a table has Primary Index ID, then the following outer join can find out the duplicate names: mysql> select * from gifts; +--------+------------+-----------------+---------------------+ | giftID | name | filename | effectiveTime | +--------+--...
I need to get the most recent record for each device from an upgrade request log table. A device is unique based on a combination of its hardware ID and its MAC address. I have been attempting to do this with GROUP BY but I am not convinced this is safe since it looks like it may be simply returning the "top record" (whatever SQLite or M...
Hi, I need to provide 4 MySQL stored procedures for each table in a database. They are for get, update, insert and delete. "Get", "delete" and "insert" are straightforward. The problem is "update", because I don't know which parameters will be set and which ones not. Some parameters could be set to NULL, and other simply won't change s...
Hello guys, I have datetime-row in mysql database. I have to check time between now and that date using php. If the range is bigger then 1 month - do somtething. I tried something like this: $dateFromMysql = strtotime($rowData); $currentDate = date("m/d/y g:i A"); And then comparsion by hands. It's ugly. ...
hi I only want to dump the tables which begin with 'JI' et 'QRTZ' I searched in strackoverflow, the way to do this is using --ignore-table, but i have hundreds tables to ignore... any tools to get this job done? thanks ...
I'm have a rails project. I'm make following: script/generate scaffold product title:string description:text image_url:string as in book(Agile Web development with Rails) and rake db:migrate. When I'm create a new product, post show as '??????'. In file database.yml section encoding is utf8, but in phpmyadmin encoding: latin1_swedish_ci....
The following statements give the same result (one is using "on", and the other using "where"): mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID; mysql> select * from gifts INNER JOIN sentGifts WHERE gifts.giftID = sentGifts.giftID; I can only see in a case of a Left Outer Join finding the "unmatched"...