mysql

Perform a full text search for partial matches where shortest match has the highest relevance in MySQL?

I'd like to perform a fulltext search against a column, looking for a partial word match (IE: against('fra*' in boolean mode)). But I would like to receive results that assign a higher relevancy to shorter words that match. So, for example, if I perform a search for 'fra' and get the results 'frank', 'fran' and 'frankfurter', I would ...

How to store and index a typical TEXT field in MySQL?

How do I store and index a typical TEXT field in MySQL. In my case the text field will have a max length of 500 Characters, I understand that beyond 255 Chars varchar will behave like a TEXT field, but I need the data to be indexed as well. I will be having where clauses to count certain type of string appearances in the field. Do I stor...

php mysql_connect security

If a web server and a database server are on different hosts, is it possible for a hacker to do packet sniffing or use some other method to get the database username/password when you use mysql_connect in the PHP code? ...

SQL query not returning expected results

Here's a SQL query that I slightly scrubbed (just the column/table/database names). I kept the structure the same as production. This is a query taken from my MySQL log. SELECT master.pk AS pk, master.vendor_id AS vendorId, master.vendor_text AS vendorName, master.device_id AS deviceId, ...

mysql performance for php calls

I have multiple php functions for my code and each function tries to setup mysql connection, send queries and closes mysql connection. Is this a good design or should I set up one connection from a master function and then pass the variables to each of these functions to execute queries? Also, is it possible that this code will give err...

Store Open/Close Times and DST Changes

I've been stuck on this for two days and have gotten no where. I tend to think future and the future problems that will come around. My server's time is set to UTC and linux box is fully updated with the timezones as well as the data is in my database. I'll explain my system for the best answer. This site sells "items" but can only s...

SQL: get the place in results query

I have a table called 'points' that has a field 'total' which contains the total points for a record. Now I would like to calculate the rank of a specific record. So like: SELECT (...) as rank FROM points WHERE id=63 Is this possible in SQL? ...

MySQL: Set user variable from result of query

Is it possible to set an user variable based on the result of a query in MySQL? What I want to acheive is something like this (we can assume that both USER and GROUP are unique): set @user = 123456; set @group = select GROUP from USER where User = @user; select * from USER where GROUP = @group; Please note that I know its possible ...

Zend Framwork Database Insert Issue

Hi, I'm starting out using the Zend Framework and have created a suitable model which saves data back to a database table. The issue I am having is that the sql statement is trying to insert '?' as the value for each column in the database. I have created the following save function which passes an array of data to the DBtable adapter...

Basicdatasource connection time out problem (using mysql)

I am using BasicDatasource in my application. This application is processing huge amount of raw data. Sometimes 1 query can take more than 15 minutes. (using mysql as db) Here is my question, I acquire a connection from pool, then execute several queries on it. But when I use the same connection more than 15 minutes, I get the error bel...

Amending a join query to add a new value to a query

Hello, The query below works well. It pulls information from 3 MySQL tables: login, submission, and comment. It creates a value called totalScore2 based on calculation of values pulled from these three tables. The MySQL tables "comment" and "submission" both have the following fields: loginid submissionid In the table "submission,"...

how to make a join statement between multi table to one table?

First, i have 4 table and columns such as feeds (id, type, type_id) feeds_normals (id, type, content) feeds_links (id, type, title, link) feeds_youtubes (id, type, title, link, description, image) feeds_photos (id, type, link) The table of "feeds type_id" is match/linkup "id" of normals, links, youtubes, photos And The table of "...

Getting a error defining MySql 5.1 stored procedure

I am defining a stored procedure and it throws an error at DECLARE variable. CREATE PROCEDURE test_proc() BEGIN DECLARE venueid VARCHAR(50); DECLARE i INT; Heres the Error - 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 'DECLARE venuei...

C# unitesting method with MySql query

Hi all, I've made an attempt to unit test a methods in the database layer of my app. In my test I was planning to write mocks to avoid having a live connection to MySQL. I'll write down a simplified version of my code to illustrate. Let's start with the unit test: [TestMethod()] public void TestMethod1() { DataBaseInterface database ...

SQL generalization/specialization, data redundancy

I have three tables: actions, messages, likes. It defines inheritance, messages and likes are actions' childs (specialization). Message and Like both have column userId and createdAt. Those should be of course moved to the parrent table Action and removed from Message and Likes. But there's only one case when I need to select both messa...

is it possible to transfer table data from one table to another?

is it possible to copy data from a table to another table?. here is my example. i have two tables, warehouse and showroom my warehouse table has (product_id and stock_quantity) and my showroom table has (product_id(fk from warehouse), stock_transferred ).. for example in warehouse table product_id stock_quantity 1 10...

Populate a PHP Dropdown List from MySQL Database

Hi guys, I'm trying to populate a dropdown list in my web page from a mysql database table which has only one column (pathology_id). I know there is test data in there but the best I can do is populate the box with the field name, not the row values. The code I have thus far is below, can anyone suggest how to get more than just the colu...

AND, OR output wrong result

Suppose I use 2 AND and one OR to retrieve a result, first test with input text value on name, I could get correct result but when I change $getc to any value other than empty string, the result does not change, it only query name value. What is wrong? $query1 = "SELECT * FROM $tableName WHERE name LIKE '%$asd%' OR descriptions LIKE ...

In MySQL, how can I split one existing table into many new related tables?

I have an existing table that resembles this. The number of bars to foo is exactly two in reality table foo_old ID int name string bar_name_1 string bar_code_1 int bar_name_2 string bar_code_2 int And since this makes me cry, I want to split it into two tables like this table foo_new ID int...

Display MYSQL query result in horizontal view

Dear all, We have employee attendance system in mysql database but it dont have the reporting capability, i am trying to generate reports using PHP. Let me explain clearly : employee punch in and punch out daily. These punch ins and outs are stored in mysql database table ( attendance). This table contain 5 fields like punch in, punc...