Working on an in-house ticketing system in php/mysql. This short term ticketing system has been in place for a year or so now. We are reaching 10,000 tickets and I would like to make the queries more efficient, since 9,500 or so of the tickets do not need to be looked at each day. Right now the queries are getting rows by tech id, the...
I can certainly do this by iterating through results with PHP, but just wanted to know if someone had a second to post a better solution. The scenario is that I have a list of transactions. I select two dates and run a report to get the transactions between those two dates...easy. For one of the reporting sections though, I need to on...
In my way.I just want to export the data of a week and used it to update somewhere else. I am new to MySql. So . Can I do it by the PHP code written by myself or using other software? Thank you in advanced!! ...
Hey All, I'm trying to DECODE a string (USING MYSQL) and isert it to mysql database. When i'm in the webpage for phpmyadmin utility and inside the table page, using the SQL Tab. and using the following INSERT: INSERT INTO `usercake_users` (`User_ID`,`Password`) VALUES (111,ENCODE("b8e3-3adc846d70a8","key101")) The results are OK !!!...
If I have a group members table with two columns in it: group_id and user_id, where users can be part of multiple groups and groups can contain many users, what would be the best way to setup the indexes? I want to be able to quickly determine which users are in a single group, so I think I would need to index on group_id, but I also wan...
I am wondering how Google does it. I have a lot of slow queries when it comes to page count and total number of results. Google returns a count value of 250,000,00 in a fraction of a second. I am dealing with grid views. I have built a custom pager for a gridview that requires an SQL query to return a page count based on the filters s...
Hi, I use MySQL 5.1. I have two tables T1(hash, value) and T2(hash, value) and I connect them using hash. They contain: ---- T1 ---- 1 A 2 B 3 C 4 D ---- T2 ---- 1 E 1 F 3 G 4 H My purpose is to get all rows from T1 which has connection with any row from T2. I tried to do so with: SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.hash = T2.h...
Is it possible to run some SQL to change the contents of a field? My field looks like this ..//uploaded_images/1284058574.jpg and I want it to simply be 1284058574.jpg all the records that I wish to change will start with ..//uploaded_images/ ...
Hello, I was just wondering wether i can do this: SELECT * FROM calendar ORDER BY ((month * 31) + day) ...
Hey I am sorry if this has been already posted or its on internet. I came here after long search Suppose this is the table: +----+-------+----------+---------------------+ | id | name | group_id | created_time | +----+-------+----------+---------------------+ | 1 | foo | 1 | 2010-09-22 00:00:00 | | 2 | rafi | 2 ...
My query: SELECT * FROM forum_topics WHERE cat_id IN(1,2,3,4,5,6,7) ORDER BY last_message DESC LIMIT 7 I want to get the biggest and only one value of each cat_id (7 values total). How to correct this query to make it work if it's even possible? There is forum topics and each has value last_message and I want to get the latest to...
I'm working on a webapp with a set of data that users can browse and edit. Users want to customize one of the fields that appear for each item, say "product_name". Each product has a set of default values in a table, which is the same for all users: The product table would be like (skipping syntax cruft, you get the idea): CREATE TA...
Hi there, We have a database with 150 GB in size running MySQL 5.0.45 using MyIsam tables, there are big tables with over 5 GB in sizes, and 2GB in indices. The server config is: 8GB Dual Core 3.2GHz – hyper threading enabled Single raid 5 - SCSI 1gb nic 64 bit OS Here is our my.cnf file: [client] port = 3306 socket ...
Can we use aliases with insert into syntax? None of the following work: INSERT INTO tableblabla AS bla INSERT INTO bla tableblabla INSERT INTO tableblabla bla I can't seem to find any information about this; is there a valid way to use aliases in an INSERT statement? About the possible reasons: http://bugs.mysql.com/bug.php?id=3275 ...
For the following (simplified) mysql DB setup, I'd like to copy the applicable guids into the message table. Can this be done with a single SQL update? CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `guid` varchar(13) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `user` (`id`, `guid`) VAL...
I want a list of all timezones in the mysql timezone tables, and need to select: 1) Their current offset from GMT 2) Whether DST is used by that timezone (not whether it's currently in use, just whether DST is considered at some point in the year for that timezone) Reason: I need to build a web form and match the users time zone inform...
I'm trying to use PHP to do this: <if !empty($list) {echo . . . ?> And get the result: Additional options: <p><input type="checkbox" name="1">1</input></label></p> <p><input type="checkbox" name="2">2</input></label></p> . . . <p><input type="checkbox" name="n">n</input></label></p> </legend> </fieldse...
In javascript I can get the offset from UTC & whether DST is used by the users timezone. In MySQL I have a set of timezone tables. I'd like to auto select a timezone for the user from the MySQL tables. The mysql tables are not easy to understand (nor can I find schema documentation). I can't figure out how to go from a UTC offset + DS...
Imagine we have a query: SELECT * FROM somewhere WHERE `id` IN(1,5,18,25) ORDER BY `name`; and an array of IDs to fetch: $ids = array(1,5,18,25) With prepared statements it's adviced to prepare one statement and call it multiple times: $stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id`=?;'); foreach ($ids as $id){ $stm...
Is it safe to use php_include file for mysql login information or is is better to copy/paste the login info for mysql on every page? If you go with php_include, how will you block that file from being seen? ...