mysql

MySql: does it have namespace-like SCHEMA?

Hi, I have to move a database from sql server to mySql, and our current db uses schemas as namespace. So all of the FOO tables can be reference like FOO.TableFoo1, FOO.TAbleFoo2, FOO.TableF003, etc. Can we do this with MySql? Will it work. ...

Where to use mysql_real_escape_string to prevent SQL Injection?

Hi friends, I'm in trouble with a group of hackers. they hacked my client's site few times, and my client gets more angry :( my client lost his database (which has hundreds records), and had to enter all :( now I'm following some more introductions; fixed file permissions changed ftp and host login info cleared all remote mysql acce...

How to do this in MySQL: if field value > 0 then minus one, else let it be.

UPDATE tbl SET counts=counts-1 ... ...

Excel to MySQL export - every week updates and constraint problems

Hi guys, Every monday end user opens Excel 'prices.csv' and does insert/update/delete on products and their new fresh prices. After that he performs export of 'prices.csv' to MySQL DB into table called PRODUCTS. I've seen here many solution using CSV or using commercial solutions for this. I understand that this can be easily done f...

Mysql Join Query gives the same results multiple times

Hi, I have two tables. One is items, and one istags. Query's here. The idea is to create a search box to search through the items and then narrow down the search by selecting tags. (Some kind of faceted search). After fiddling around with mysql tutorials i came up with this: SELECT `items`.`id`, `items`.`name`, `items`.`pay`, `items`...

Query optimisation

In my application, my module uses the following tables PUBLIC_APPLICATION CATEGORY_MASTER NOTIFICATION_SITE_DETAIL DIMENSION_MASTER DM PUBLIC_REGISTRATION ALLOTMENT_NOTIFICATION. From the following tables I am retrieving the data SELECT PA.REGISTRATION_NO,PA.APP_ID,PA.NO_OF_ATTEMPTS,CM.CATEGORY_NAME, DM.SITE_DIMENSION,PR.B...

JDBC MySql Connection Pooling practices

I have a Java-JSF Web Application on GlassFish, in which I want to use connection pooling. Therefore I created an application scoped bean that serves with Connection instances for other beans: import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import javax.naming.Context; import javax.naming.InitialCo...

Python: Access a MySQL db without MySQLdb module.

Is there another way to connect to a MySQL database with what came included in the version of Python (2.5.1) that is bundled with Mac OS 10.5.x? I unfortunately cannot add the the MySQLdb module to the client machines I am working with...I need to work with the stock version of Python that shipped with Leopard. ...

Changing character sets on a live MySQL database

I currently have a bunch of tables using the latin1 charset in a MySQL 5.1.x DB. Problem is, we recently had a bunch of users trying to input text using UTF-8 encoding, and that seemed to break things. Is it safe to blindly update the table's character set? What are some best practices (besides obviously backing everything up) for a sit...

Field contains foreign IDs for different tables

I am developing a php/mysql driven facebook game. I am stuck on an element the table design. When a user completes a task I want to trigger any number of events. I was thinking of something like so: tbl_events *event_id - serogate primary ID *task_id - foreign ID of the task just completed *event_type - what type of event e.g is it a f...

Best way to implement alphanumeric id

I'm noticing short URL sites are using alphnumeric id instead of integers for unqiue id. Have anybody have experience with implementation? What is the best way to implement this feature in MySQL? I believe the tricky part would probably be the incrementation and indexing of the id. example: bit.ly/AbC123 ...

MySQL query to get two most recent records before a given DateTime

I have an application that keeps track of prices for various products. Prices might change for any given product at any time during any day, and will not change regularly. There is a PriceHistory table that has dated records for all of the prices for the products over time. Users of the application can select a time and date and see what...

Looking for a way to limit SQL results 3 results per a specific column

Hello, I have a list of 9 user id's and want to select 3 entries per user id. My current query looks like this. SELECT * FROM entries WHERE user_id IN (1,2,3,4,5,6,7,8,9) LIMIT 27 Some users have many more than 3 entries, so I want to limit the results of this query to 3 entries per user. I tried making a UNION of 9 separate querie...

PHP var_dump array

array(14) { [0]=> string(1) "1" ["id"]=> string(1) "1" [1]=> string(7) "myUserName" ["UserID"]=> string(7) "myUserName" [2]=> string(10) "myPassword" ["passwordID"]=> string(10) "myPassword" [3]=> string(24) "[email protected]" ["emailAddress"]=> string(24) "[email protected]" [4]=> string(7) "myFirs...

How to recover a MySQL database: Incorrect information in file: './xxx.frm'

A very important database has gone corrupt that was sitting on server at a shared web host, and I didn't back up. The table contains a large list of very important email addresses. I can get a table listing, but if I open any of the tables with Navicat or phpMyAdmin, I get the following error: Incorrect information in file: './the-table...

PHP mysqli updating a blob value in table does nothing.

I have a variable called $description that has a paragraph of information in it. Some of these descriptions are a sentence or 2, some are long, so im using blobs to save this instead of var char. This statement executes without a problem, but nothing actually gets saved. No errors reported. $query = "UPDATE event SET description=? WHERE...

Sorting by ratings in a database - Where to put this SQL? (PHP/MySQL)

Hi folks OK - I'll get straight to the point - here's the PHP code in question: <h2>Highest Rated:</h2> <?php // Our query base $query = $this->db->query("SELECT * FROM code ORDER BY rating DESC"); foreach($query->result() as $row) { ?> <h3><?php echo $row->title." ID: ";echo...

ON DUPLICATE KEY UPDATE for Specific Key in Multi-Key Table

Let's say I have a table Foo. This table has the columns ID and UniqueCode defined like so: CREATE TABLE Foo ( ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, UniqueCode VARCHAR(255) NOT NULL, PRIMARY KEY (ID), CONSTRAINT UNIQUE(UniqueCode) ); Now let's say I have the following query: INSERT INTO Foo (ID, UniqueCode) VAL...

Stored procedure causes "Commands out of sync" on the next query.

I am running a query with a mysql stored procedure : $AddProf_qr = mysql_query("call AddStudent('$d_Pass', '$d_Titl', '$d_Firs', '$d_Midd', '$d_Last', '$d_Addr', '$d_City', '$d_Stat', '$d_County', '$d_Zipc', $d_Gend, '$d_Birh', '$d_Phom', '$d_Phoh', '$d_Phoo', '$d_Email', '$d_Webs', '$d_Natn', '$d_Profsn', '$d_Compny', '$d_Desig', $d...

What is the better way to use a my_sql database with a C# .Net software ?

Hi, I would like to create a C# .Net software linked to a database. I had a look at some solutions found on google but none of them convinced me ... So, what do you think is the better way to integrate a my_sql database into a C# .Net Software. Of course a nice integration into visual studio would be better :p Thanks. PS : I use Rub...