I'd like to store an additional column in a table as a 'sort value', which is a numeric representation of the title column, such that the order of such values represents the string's natural alphabetical sort order. Ie, so that I can retrieve rows ordered by the sort value, and they'll be in natural sort order - and when I insert a new ...
I have an order by clause that looks like: ( user_id <> ? ), rating DESC, title Where ? is replaced with the current user's id. On postgresql this gives me the ordering I'm looking for i.e. by current user, then highest rating, then title (alphabetically). However on MySQL I get an unclear order current user is neither first nor las...
Does anyone have experience they can share using MySQL savepoints (directly or via an ORM), especially in a non-trivial web service? Where have you actually used them? Are they reliable enough (assuming you're willing to run a fairly recent version of MySQL) or too bleeding-edge or expensive? Lastly, does anyone have experience with s...
According to the documentation at mysqli_use_result One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched. Does this only pertain to myISAM tables or also for InnoDB? ...
I'm setting up a database and I'm interested in having a facebook-like friends system. My original plan was to have a table like so: uid friends 4 30,23,12,33 30 54,92,108 with all these numbers being FK's to tables with user information. I was told that this is inadvisable and practically impossible since MySQL will only deal w...
A user will input text in a textarea. It is then inserted directly into a mySQL database. I use trim, htmlentities, mysql_real_escape_string on it and I have magic quotes enabled. How should I sanitize it when outputting that data back into a textarea? Thanks for your help. I've never been too sure on the correct way of doing this... ...
Working on a particular application, I keep writing very similar queries, again and again. They're not exactly the same, but are of very similar form, and embedded in almost identical chunks of code, e.g., $Mysqli = new mysqli; if ($Stmt = $Mysqli->prepare("SELECT foo FROM tblFoo ...
I have the following table and data: CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `test` (`id`, `name`) VALUES (1, 'test'); INSERT INTO `test` (`id`, `name`) VALUES (2, 'test '); When I do eithe...
I'm hosting a website on Zymic (free host) that utilizes MySQL. I opened an account, and wrote the SIMPLEST function to connect to the DB. It looks like this: <?php $conn = mysql_connect("uuuq.com","paulasplace_sudo","mypassword"); if(!$con) { die("Could not connect: " . mysql_error()); } else { e...
Hi to all, I am developing one social-networking. For DB Load Balancing, i want to use Master-slave Replication in Mysql. Before start working on Replication, i want to know something like 1) How can we setup that replication 2) what is the advantage & Dis-advantages of Master-slave Replication 3) when we are using "select" queri...
How can I recognize when a user has missed a space when entering a search term? For example, if the user enters "usbcable", I want to search for "usb cable". I'm doing a REGEX search in MySQL to match full words. I have a table with every term used in a search, so I know that "usb" and "cable" are valid terms. Is there a way to constru...
Trying to duplicate some rows in a table but just change the ssreportid column from 4 to 6: INSERT INTO ssreportparticipant (ssreportid, sssurveyparticipantid) VALUES SELECT 6, sssurveyparticipantid FROM ssreportparticipant WHERE ssreportid = 4 The error says #1064 near 'select 6, ...' but if I just run the select clause, it selects...
Possible Duplicate: Regarding Mysql Master-slave configuration Hi to all, I have some doubs regarding Mysql master-slaves For example , if i setup 2 slaves to Master.In between production ,i want to add another slave to that master.at that time,what i have to do? thanks in advance ...
Firstly - my description ;) I've got a XmlHttpRequests JSON response from the server. MySQL driver outputs all data as string and PHP returns it as it is, so any integer is returned as string, therefore: Is there any fast alternative (hack) for parseInt() function in JS which can parse pure numeric string, e.g. var foo = {"bar": "12...
Which one would you choose for a small ASP.NET 2.0 web site with little traffic? I have no experience with either of them, but my provider wants me to choose one. In fact, I have no experience in ASP.NET too, I am just starting to learn, using VS2008 Professional. Thank you, Petr ...
How can I do to get the next row in a table? `image_id` int(11) NOT NULL auto_increment `image_title` varchar(255) NOT NULL `image_text` mediumtext NOT NULL `image_date` datetime NOT NULL `image_filename` varchar(255) NOT NULL If the current image is 3 for example and the next one is 7 etc. this won’t work: $query = mysql_query("SELE...
Befor I ask this question, I have a favor to ask, PLEASE don't down vote this question till it has some real answers.... PLEASE! I have submitted this previously as the following question http://stackoverflow.com/questions/579892/php-calendar-recurrence-logic but because someone down voted it and said it was already answered, noone w...
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? The LIMIT keyword limits the number of rows returned by a SELECT e.g: SELECT * FROM People WHERE Age > 18 LIMIT 2; returns 2 rows. SELECT * FROM People WHERE Age > 18 LIMIT 10, 2; returns 2 rows after the first 10. ...
I am trying to pass an array of values from php to mysql stored procedures as parameter list and how to use the array inside the stored procedure. The query in the procedure has three IN statements in in there so I would like to do IN(@listOfids) where @listOfids is 1,2,3,4 (an imploded array from php). ...
Something like SELECT COUNT(*) AS c FROM BANS WHERE typeid=6 AND (SELECT ipaddr,cidr FROM BANS) MATCH AGAINST 'this_ip'; So you don't first fetch all records from DB and then match them one-by one. If c > 0 then were matched. BANS table: id int auto incr PK typeid TINYINT (1=hostname, 4=ipv4, 6=ipv6) ipaddr BINARY(128) cidr INT ...