mysql

MySQL INSERT/UPDATE without ON DUPLICATE KEY

I may have either misunderstood how to use the ON DUPLICATE KEY syntax, alternatively my database structure needs some work but here goes. I have a table (bookings-meta) which represents meta data associated with another table (bookings) , different rows in the bookings table may or may not have specific meta data associated with them i...

Is this a Efficient way to query relational tables on MySQL?

I'm dealing with a relational table and I've been wondering if there's a way to lower the number of queries I need to make when inserting data to the tables.. Here are the queries I currently use: I insert the "main" values. INSERT INTO products (title, description, status, url) VALUES ('some title', 'description o...

Is it possible to optimize a query that uses the '<>' operator?

This a follow-up to a previous question. How can I optimize this query so that it does not perform a full table scan? SELECT Employee.name FROM Employee WHERE Employee.id <> 1000; . explain SELECT Employee.name FROM Employee WHERE Employee.id <> 1000; +----+-------------+-------------+------+---------------+------+---------+------+...

geographic layers in mysql

I am looking for an implementation of MySql and Geographic layers (features\rasters), how does it work for you? which ORM \ dataaccess you are using? how do you write the geo-queries? as sql or in code ...

Can I save a very large array of userID's to a session variable for multiple users?

OK I have a social network, around 50,000 users so far and there is a friend table that show who is your friend on the site, this table is over a million rows Not the tricky part, I show user posted bulletin, status posts, stuff like that that is only visible to people on your friend list. Keep in mind the size of the tables, user table...

how to get numeric types from mysql using PDO

I'm using PDO and mysql, for some reason when getting values from the DB that are int type, the PDOStatement is returning a string representation of the number and not a value of numeric type, how do I prevent this from happening? I noticed there is a attribute of the PDO class: PDO::ATTR_STRINGIFY_FETCHES that is supposed to take care ...

PHP Export MySQL to CSV - Results showing HTML

Hey everyone, I saw a post on this already, but it didn't really provide a solution (that has worked for me at least)... I have a PHP page that does some basic MySQL queries, the results of which are displayed on the page. I'm using some $_GET and $_SESSION variables throughout this process. In the same page, I also allow the user to ...

PHP/MySQL Structuring a Table/Database for Profile Usage

Hello all, I am looking for some direction on how the most efficient way to structure my database for users on my website. Essentially, I already have a "tblusers" database that includes basic biographical information, such as: tblusers: UsrID, First, Last, DOB, Phone Number, etc. However, one aspect of my website include...

What is a query offset?

I saw this at the Kohana documentation: $content = new View('pages/items'); $items = new Items_Model; $content->items = $items->get_items($page_no, 10); // page to get starting at offset, number of items to get As you can see, we can assume that we have get_items method for Items model that receives 2 parameters, $page_no and 10 (as ...

Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE

In MySQL, if you specify ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: INSERT INTO table (a,b,c) VALUES ...

PHP/MySQL Output Data in TD's

Hi, How would I go about imposing a restriction on the number of HTML table columns when querying the database. For example, I have a MySQL table with the following values: myTable: id color 1 red 2 blue 3 green 4 pink 5 purple And when I run my query, instead of showing all rows in tra...

Integrating search on a website where the backend is MYSQL

I have a location search website for a city, we started out with collecting data for all possible categories in the city like Schools, Colleges, Departmental Stores etc and stored their information in a separate table, as each entry had different details apart from their name, address and phone number. We had to integrate search in the...

Whats the best way to implement typo correction into a search (in php/mysql)?

I have a site that lists movies. Naturally people make spelling mistakes when searching for movies, and of course there is the fact that some movies have apostrophes, use letters to spell out numbers in the title, etc. How do I get my search script to overlook these errors? Probably need something that's a little more intelligent than...

How to set the encoding for the tables' char columns in django?

I have a project written in Django. All fields that are supposed to store some strings are supposed to be in UTF-8, however, when I run manage.py syncdb all respective columns are created with cp1252 character set (where did it get that -- I have no idea) and I have to manually update every column... Is there a way to tell Django to ...

MySQL: How to search for spelling variants? ("murrays", "murray's" etc)

Hi Friends, I want to search like this: the user inputs e.g. "murrays", and the search result will show both records containing "murrays" and records containing "murray's". What should I do in my query.pl? ...

When is it considered too much for using strings in a MySQL IN clause?

Please tell me from experience with using the "IN clause" in a MySQL query is considered to be too large of a list to push through to be considered good practice? Example scenario is I have a string of user ID numbers that I will pull from a cache, it can range from anywhere from 1 number all the way up to being 5,000 numbers in this ...

Getting Random behaviour for a store procedure in mysql

Hi friend I am using MySQL as my database and java as frontend. I am using stored procedure if I want to retrieve the data. There is no synchronization concept. If I want to get the same result from database i am calling the same stored procedure passed the same value. So I got the result. But If I do it multiple times in fraction of s...

Storing and displaying unicode string (हिन्दी) using PHP and MySQL

I have to store hindi text in a MySQL database, fetch it using a PHP script and display it on a webpage. I did the following: I created a database and set its encoding to UTF-8 and also the collation to utf8_bin. I added a varchar field in the table and set it to accept UTF-8 text in the charset property. Then I set about adding data ...

What is the most effecient way to write this SQL query?

I have two lists of ids. List A and List B. Both of these lists are actually the results of SQL queries (QUERY A and QUERY B respectively). I want to 'filter' List A, by removing the ids in List A if they appear in list B. So for example if list A looks like this: 1, 2, 3, 4, 7 and List B looks like this: 2,7 then the 'filtered' Li...

Define default date value in MySQL, similar to timestamp

I'm using MySQL (nobody's perfect), version 4.1 and I'm used to define some timestamp columns like that: ALTER TABLE foo ADD creation TIMESTAMP DEFAULT NOW() ; I'd like to do exactly the same thing, but for a DATE field. The reason being I don't need a TIMESTAMP precision and since no functional index exists in MySQL, I cannot access ...