mysql

Using Array "Keys" In a MySQL WHERE Clause

I have a data set that is generated by a Zip Code range search: $zips: key -> value 11967 -> 0.5 11951 -> 1.3 The key is the Zip Code (Which I need to query the Database for), and the value is the miles from the user entered zip code. I need to take the key (Zip Code) and search the database, preferably using a MySQL query similar to...

How to set query parameters in MySQL Query Browser?

Can the MySQL Query Browser set parameters of a parameterized query? If so, how? I tried populating the Parameter Browser tab but it doesn't seem to actually set parameters when I execute the query. I searched for quite a while in Google (e.g. mySQL Query Browser parameterized) but had no luck finding the answer. I found this threa...

Problem with nhibernate, Mysql, and Guids

I have a view that flattens out a hierachy of 4 tables to display as a report. within the view it contains the primary keys (Guid) of each of the tables along with some display data. The problem is that the the guids are being returned as varbinary(16) instead of binary(16) and as a result nhibernate throws an error. This would appear...

Is there a difference between Binary(16) and VarBinary(16) in MySQL

Nhibernate seems to have a problem with varbinary(16) when tring to represent Guids. I would have thought that varbinary(16) and binary(16) equate to the same thing. ...

Calculating a row position in the table

Let's say I have a table like this (this is just a simplified example, the real table I'm talking about is much more complex): CREATE TABLE media ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT NOT NULL, voted INT NOT NULL DEFAULT 0, rating FLOAT NOT NULL DEFAULT 0 ) ENGINE = INNODB; The voted column re...

Time-Delayed MySQL "Update" for More Realistic Tally of Online Video Views

I'm creating a video embed page for a real estate site, where a user can go to watch a video tour of a given home. There is no other reason to visit that particular page, so I figured that I could use a simple MySQL Update to a "video view tally" column for that homes's row, which will update views=views+1 each time the page is loaded. ...

How to join results from two tables in one output file?

I have two tables in MySQL DB; table1, table2. Both of them have a single column (float) values. It's actually a dump from our research project which produces a single value result. And many of these values get repeated and sorting and filtering them in Python would be cumbersome, so I though perhaps dumping them in a table in DB woul...

Mysql Like Syntax

Hi all, Quick question: How do I mysqli_escape_string a variable enclosed in a like clause? "SELECT * FROM table WHERE name LIKE '%". %s . "%'" or "SELECT * FROM table WHERE name like '%"."%s"."%'" don't work. Thanks! ...

Directly fetching a list from a column's data with MySQL

Hello, Let us suppose I have to deal with lots of grandmothers that have lots of cats. I have a table granny_cat : granny_id | kitty_name -------------------- 1 Kizzy 1 Jimmy 1 Katty 2 Georges 2 Albert 3 Panther and I want to retrieve a list of granny 1 cat's, i.e. to get so...

How can I find every row in a table where the value of a field is contained in a specific string?

Usually, I use SELECT * FROM tableName WHERE fieldName LIKE '%string%' to find every row in tableName where the value of fieldName contains the string 'string'. How can I find every row in tableName where the value of fieldName is contained in the string 'string'? For example, in the following table names FirstName | LastName Bob ...

What DB for big databases?

I'm embarking on a project which will likely cross several million rows in the short future, so I am researching the database I use as that is sure to prove an issue. From what I have read, SQL in all its incarnations has issues once you get to the 2,000,000 rows issue for a table. Is there a good database recommended for these big size ...

Mysql table sync or trigger ?

What is the best way to do this with mysql : I have two tables in the same database (a table : Gene, and a table Gcur). In table Gene, I have a column last_updated. In table Gcur, I have a column last_modified. I'd like to synchronize column last_modified with column last_updated. For example, I made an update of column last_modified...

Excluding a variable when its value is blank

Hello, The code below works great. I have a MySQL database that contains book titles classified in different categories. In the code below, the variable "site" represents a book title. Each category is represented by a different table in the MySQL database. The code below ranks the top 25 book titles (site) by total votes across all...

Need sample java to query mysql and dispay result upon combo box update

I have a combo box that will be loaded with a list of choices. When the user selects a choice, I need a javascript to simply run a query of mysql (obviously based on the user choice in the combo box) which will return a simple, discrete value that then needs to be displayed on the page next to the combo box. The query is nothing more t...

Using a custom field in WHERE clause of SQL query.

My question is very similar to this one How to reference a custom field in SQL I have the following query: SELECT * , (SELECT COUNT( id ) FROM cms_store_items WHERE speaker = cms_store_items_speakers.id ) AS count FROM cms_store_items_speakers LIMIT 0 , 30 I need to add a WHERE clause that looks like WHERE count > 0 but when I do I g...

if a mysql databases table is going to hold two to four values should i use a varchar?

i am creating a database structure for a new project and there is a table which one of the fields will receive/contain one of three values upon selection from a selection box on the website. the values would be (opened,suspended,closed) would it be necessary to make the field of type varchar(some value) or is there a better way of doing ...

Mysql bad date (data) in string field how to find/validate?

I have a string field in Mysql (date fields imported from from MSSQL) and I'm using the following to convert the string value and place it in a new (MYSQL) date-time field, Update Table_name set STATUS_DATE= STR_TO_DATE(substring_index(SSTATUS_DATE," ",1),'%c/%e/%Y'), somewhere in the table I have bad data and the query stops and ...

JavaScript/VBScript SQL Database

I need information on JavaScript/VBScript with SQL database. I'm currently doing scripting. My problem is, in scripting, how to connect and insert some data to a MySQL database. I did find something mentioning on client/server side database. Do I need to consider that also? I'm not building the application for the Web services. ...

what is more costly, 2 updates, or a delete and an insert?

For a certain piece of business logic, a sequence could be either two separate updates, or a delete followed by an insert. Basically, the update would nullify a column so I can make it seem like a delete. I am not concerned with losing rows (so a soft delete is not necessary): what is the more efficient approach? ...

how to implement a login system under php?

Are there any good tutorials on how to create your own registration, authentications using php/mysql/or javascript. ...