[Warning: long post ahead!] I've banging my head at this for quite some time now but can't get on a common denominator what is going on. I've found a solution to workaround, see at the end, but my inner Zen is not satisfied yet. I've a main table with forum messages (it's from Phorum), simplified looks like this (ignore the anon_user_i...
How can i get the nth row position from table without using limit ? I have a table with four fields id,name,country,description. The query is returning by condition country = 'asia'. The total number of records is more than hundred. Now what i need is if i hav a name 'test' in 23rd position in table then how can i get the position 23r...
I need to store posts from users. Their usernames are unique and I'm sure they can't make posts at the same millisecond. Then the URL of their post would contain /Username/12345678890/title-of-the-post I can't use the Auto_Inc number as I don't know it in the DB. want them to know the number or be able to guess. Is this sufficient e...
I like to prefix my tables in case I need to install the application to a host with only one database. I was wondering if there is a simple way of working with table prefixes using the PDO class? At the moment, I am having to overwrite each method in my own database wrapped, replace %p with the prefix, and call the super method. It's wo...
I'm trying to calculate some columns in a mysql database with this code: "SELECT SUM(klick) FROM svar GROUP BY pollid HAVING pollID="& rstPoll("PollId") But it doesn't work. So what I want to do is to get the sum of all "klick" where a pollId has a certain value. I got this code to work with access but not with mysql: "SELECT SUM(kli...
I'm using Subsonic with SimpleRepository: using System; using System.Collections.Generic; using System.Linq; using System.Text; using SubSonic.DataProviders; using SubSonic.Repository; namespace SubSonicTest { class Program { public class Product { public int ProductID { get; set; } publi...
Recently I was told to add auto_increment primary key to all our tables because our database is going to be replicated (master-master solution). All our tables had some kind of primary key, but not all of them were autoincremented. I looked through mysql documentation about replication and I didn't find any indication that this (auto_in...
I've seen this question around the internet (here and here, for example), but I've never seen a good answer. Is it possible to find the length of time a given MySQL query (executed via mysql_query) took via PHP? Some places recommend using php's microtime function, but this seems like it may be inaccurate. The mysql_query may be bogged ...
I have a table in MySQL dB that records when a person clicks on certain navigation tabs. Each time it will soft-delete the last entry and insert a new one. The reason for soft-delete is for analytics purposes, so I can track over time where/when/what users are clicking. The ratio of soft-deletes to new entries are 9:1, and the table size...
I'm having trouble trying to use $stmt->store_result() after executing a prepared statement. The manual says "Transfers a result set from a prepared statement", but to where? I would ideally like to use it, all nice an buffered in memory, like mysqli->query() returns. The php documentation examples make it seem like the only good reaso...
Hi, I know I can select multiple rows like this: select * FROM table WHERE id in (1, 2, 3, 10, 100); And I get the results returned in order: 1, 2, 3, 10, 100 But, what if I need to have the results returned in a specific order? When I try this: select * FROM table WHERE id in (2, 100, 3, 1, 10); I still get the results returned...
What is an efficient way to put the tree data into an array? I followed the sitepoint tutorial to retrieve the tree data. However, the tutorial only shows how to output the tree, not how to make a multidementional array. I used SELECT title, lft, rgt FROM tree_structure WHERE lft BETWEEN $parentLft AND $parentRgt ORDER BY lft ASC ...
Currently I have the following fulltext index setup: fulltext on: Number - Name - Suffix - Direction - City - State - ZIPCode Select id, MATCH(Number, Name, Suffix, Direction, City, State, ZIPCode) AGAINST ("Test") as Relevance from test where 1, and MATCH(Number, Name, Suffix, Direction, City, State, ZIPCode) AGAINST ("+Test...
I'm having a problem with JSTL formatDate when displaying dates from MySQL database. I am using DAO layer for communication with database and in beans, dates are stored in java.util.Date objects. In JSP the code looks like this: <fmt:parseDate value="${season.startDate}" pattern="dd.MM.yyyy."/> When I try to run this page I get java.te...
I have a list of about 2,300K rows of bad data that I'd like to delete from my database. Is there a way that I can delete all of these rows using a single sql statement? I can 'WHERE IN' but the issue is that these values are not quoted and fail. Thanks ...
I'm setting up a table that might have upwards of 70 columns. I'm now thinking about splitting it up as some of the data in the columns won't be needed every time the table is accessed. Then again, if I do this I'm left with having to use joins. At what point, if any, is it considered too many columns? ...
This may not be possible, but I'd like to ask anyway and see if there's a creative solution out there. I'd like to have a "file" form field on my site that uploads a file into a BLOB field in a MYSQL database. I must use PHP to accomplish this task. Normally, I know you would open the temp file found in the $_FILES array, using fope...
Alright here's the situation, I have an application written in the Zend_Framework, that is compatible with both MySQL and MSSQL as the backend. Now, ZF is pretty good at solving a lot of the SQL discrepancies/differences between the two languages, but I still have yet to figure this one out. The objective is to select 1 random record f...
I have a file upload site, and I want to run a maintenance script that will run every day and delete items that haven't been accessed in a week. I log views for each day, and each item into a table: hit_itemid hit_date hit_views The main table actually has the files that were uploaded, for the purposes of this example, its just vid_...
I have a form which sends lots of GET['var'] in a form. Ive worked out how to set it to send var[1]=data&var[2]=some&var[3]=thing etc etc but how do I catch each variable and combine it into one string, seperated with a ", "? so $var = data, some, thing UPDATE: Sorry I should of mentioned I already have the function that implodes t...