mysql

While exists in mysql

How should i use while loop in mysql? while exists (select * from table1) Do //sql statements end while Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near while.... This what I want to do: Inserting 20K records into a table...

Duplicate entry error message format

I have a problem with mysql duplicate error messages. On one of my machines the error looks like: Duplicate entry 'foobar' for key 2 on the other machine it looks like: Duplicate entry 'foobar' for name One machine prints the key index, the other the key name. One machine is an Ubuntu 10.04 Desktop; the other a Debian Lenny...

strategy for SQL headers and detail report

I want to get all schools from the mysql school table and get all students of each school in the students table. for a web based report with heading and totals. I could do something like select * from schools join students on schoolNo = schoolNo order by schoolNo Howwever this way I think I will have to keep track of current schoolNo ...

Checking if some one has already voted

Im using a simple ajax script to allow users to vote against multiple posts on a page. However I dont want the same user to vote against the same post more than once, to try and avoid this I log the users, ID, Username and the ID of the post to which they have voted in a database. Like so: $updatevoters = "INSERT INTO voters VALUES('...

MySQL User Defined Function for Latitude Longitude Syntax

I have created a MySQL function to determine if a set of latitude and longitude coordinates are within a certain range of another set of latitude and longitude coordinates. However, the function is giving me a syntax error so I cannot test to see if it is working properly. Any help figuring out what is causing the error would be greatly ...

How to enable auto mysql real escape to all queries?

Hi, I'm running php 5.2.13 and i have an app that contains tons of files but they all calling one file at the beginning, i want to put some line in that file to automatically mysql real escape any query, because i don't want to go across every file and change code. Thanks! ...

performance issue on displaying records

I have a table with just 3,000 records. I render these 3000 records in the home page without pagination, my client is not interested in pagination... So to show page completely it takes around 1 min, 15 sec. What can be done to make the page load more quickly? My table structure: customer table customer id customer name guider id ...

SQL::Self join a table to satisfy a particular condition?

I have the following table: mysql> SELECT * FROM temp; +----+------+ | id | a | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ I am trying to get the following output: +----+------+------+ | id | a | a | +----+------+------+ | 1 | 1 | 2 | | 2 | 2 | 3 | | 3 | 3 | ...

Can someone explain the concept of MySQL to me?

First and foremost, I do understand that MySQL is used for databases, and can be used to update content on a website. What I don't understand is how you actually are able to get MySQl to change the content on a site automatically, or if it is even possible. I really am a beginner so sorry if this question comes comes as noobish. I rea...

In MySQL, does putting SELECT foo increase in performance if foo is indexed?

In MySQL, does putting SELECT foo increase in performance if foo is indexed? At RedditMirror.cc, I have a database with 1.2 million records in the GrabbedSites table, a number that increases by approx 500-2000 per day. Early in my career, I was mentored that the only columns that should be indexed are those which you will do WHERE o...

simple sql for mass changing passwords

Changing password for user 1 looks like this: UPDATE `mydb`.`wp_users` SET `user_pass` = MD5( 'password' ) WHERE `wp_users`.`ID` = 1; Now, I have text file with such format: user30 pass30 ... user2 pass2 user1 pass1 How can I change passwords for all these users without doing it manually? Maybe some sql command that could imp...

Why nosql with cassandra instead of mysql ?

I work on large database (hundreds of GB) and Mysql now gives me more or less satisfaction. I hesitate to cassandra on launch. What I want to know everything before, so this kind of DBMS NoSQL is supposed to be faster than MySQL? Several points: The change in the number of column on a row In Mysql, they must all be defined in advance...

MySQL uft8_unicode_ci

Hi, I want to make sure something is write. My database tables are utf8_unicode_ci and my site encoding and header is utf-8 etc and so on. I done a test and in my guestbook i entered this: á ʵßăāÇϢϞﻨ☺ ▓ ▓ẻ ▓ẻṎ ۞ ݤ Now great it displays on the webpage like it should and i tested other languages to etc, but on checking this in p...

Reorder the database record rows through front-end using php

I am using PHP/MYSQL. I want in my frontend, for the users to sort and rearrange database records entered by the user himself in an earlier stage. The records uploaded by the user may sometimes inserted into the database randomly, so in the user profile he/she might have a facility to rearrange the the rows in the database according to t...

PHP: What's the fastest way to query MySQL? Because PDO is painfully slow

I need to perform a simply query. Literally, all I need to perform is: SELECT price, sqft, zipcode FROM homes WHERE home_id = X When I use PHP PDO, which I've read is the recommended way to connect to a MySQL database, simply creating the connection takes a measured 610ms. My code is below: try { $conn_str = DB . ':host=' . DB...

Rails select random record

I don't know if I'm just looking in the wrong places here or what, but does active record have a method for retrieving a random object? Something like? @user = User.random Or... well since that method doesn't exist is there some amazing "Rails Way" of doing this, I always seem to be to verbose. I'm using mysql as well. ...

Mysql UPDATE incrementing

Is MySQL UPDATE incrementing operation transaction-safe? I mean could it possible to get into the race condition while many concurrent clients execute queries like "UPDATE table SET field=field+1"? If 1000 clients will execute such query simultaneously, what value that field will be set, 1000 greater than before? ...

execute command denied to user 'tisegoco_goitse'@'localhost' for routine 'tisegoco_tisego.offices

Hi I tried to upload my project on my machine and the error that I get from mysql is: execute command denied to user 'tisegoco_goitse'@'localhost' for routine 'tisegoco_tisego.offices I granted this user all privileges on the db. Now I am trying to access this procedure: delimiter // create procedure sample() begin select * ...

Return a result set in numeric pattern / sequence

I have a problem in hand and that is to fetch a result set in a numeric pattern / sequence based on a single column's value. Say I have the following table: +-------+ | val | +-------+ | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | +-------+ How can I make...

PHP & MYSQL : building a query based on multiple input values from users

Hypothetically I three tables: * recipes - contains recipes * ingredients - list of items * meals - list of meals I have a form that generates a selection like: Choose what ingredients you have: - apples - bananas - cherries Choose the meal this is for: - breakfast - lunch - dinner I want the user to be able to choose from either o...