mysql

mySQL: Multiple COUNT()s based on differing criteria

Alright, so what I'm trying to do is perform different COUNT()s on the same table based on different criteria without nested queries (efficiency) or subqueries (so it can be made into a view). Is this possible? Example: a table has a date column - the query should be able to produce a count of the number of rows prior to & after a const...

Sort tinytext time from mysql db query with sql or sort the array it produces using PHP

Hey guys - The problem stems from a poorly designed database used to store real estate information. I set up a template for my client to select a weekend and to display the open houses for that weekend. Open house times (ohtime1, ohtime2, ohtime3) are stored as tinytext, with no way of knowing AM or PM. "12:00 - 2:00" and "01:00 - 03:00"...

Query works ok in phpmyadmin but not with mysql_query php function

I have this sql query: SELECT DISTINCT r.uri uri FROM resource r INNER JOIN object o ON o.idResource = r.idResource WHERE r.type = 2 AND r.idResource IN ( SELECT DISTINCT r1.idResource FROM object o1 INNER JOIN resource r1 ON r1.idResource = o1.idResource INNER JOIN class c1 ON c1.idClass = o1.idClass INNER JOIN property p2 ON...

how to select a row where one of several columns equals a certain value?

Say I have a table that includes column A, column B and column C. How do I write I query that selects all rows where either column A OR column B OR column C equals a certain value? Thanks. Update: I think forgot to mention my confusion. Say there is another column (column 1) and I need to select based on the following logic: ...where...

Compare 2 database schemas on Mysql

I have 2 schemas in a database about the same application (Different versions) I want to generate a Delta script with the differences It exists some tool that helps me (Open source solution should be perfect) Thank you ...

how safe are PDO prepared statements

Started using PDO prepared statements not too long ago, and, as i understand, it does all the escaping/security for you. for example, assuming $_POST['title'] is a form field. $title = $_POST['title']; $query = "insert into blog(userID, title) values (?, ?)" $st = $sql->prepare($query); $st->bindParam(1, $_SESSION['user']['userID'], PD...

Do mysql indexes translate UTF-8 to ASCII?

I've been working with UTF-8 characters in my db, and have been using php inconv function to translate characters from utf-8 to ascii before putting them into the database. This way, I thought, I would just translate a query into ASCII before querying the database. However, now I am seeing results which lead me to believe that mysql d...

.db file and MySQL

Hi , I am having real issues with a .db file its around 20gb in size with three tables and the rest data. I am on a mac so i am having to use some crappy apps but it wont open in Access. Does any one know what software will produce a .db file and what software will allow me to open it and export it as a CSV or MySQL file ? Also if...

How to select dates greater than current using date_format

Hi, I have my records in mysql stored with a datetime column. I would like to get records that are greater than the current date. example: select * from MY_TABLE WHERE mytime is greater than today. I know I need to use DATE_FORMAT but not sure how to construct the query. Thanks. ...

What is the difference between latin1_general_ci and utf8_bin in MYSQL

I am currently working with multi-lingual site. In some cases i need to save the other language data in the database. While i did it does not save the actual data what i entered. Instead of it contains "?????????????????????". After that i changed the collation latin1_general_ci into utf8_bin. Then its working well now. could anyone...

What is the problem in this code?

I have the following code: function query_tip($title,$desc) { $q1=("SELECT id, company, name FROM abc where ('$title' LIKE CONCAT('% ',company,' %') or '$desc' LIKE CONCAT('% ',company,' %') or '$title' LIKE CONCAT('% ',name,' %') or '$desc' LIKE CONCAT('% ',name,'...

How to use sphinx plugin for MySQL to implement fulltext search with PHP?

Can someone provide a demo here? And how to retrieve the matching snippet at the same time? ...

how to wrap (prefix & suffix) all values in a column with a string?

I have a mysql table with about 1000 rows in it. id column 1 apple 2 banana ... etc I need a mysql query (UPDATE) to wrap (prefix & suffix) all values in a column with a specific string, let's say "_", so I am expecting a result: id column 1 _apple_ 2 _banana_ ... etc How to do that? Please, advice. ...

MySQL Get rows between months

Hi all, I'm trying to SELECT the visitors of my site per month for the current year. For every different IP/user_agent combination there will be added a row per minute. To track the hits and the unique visitors. My scheme looks like this: CREATE TABLE `stats` ( `id` int(11) unsigned NOT NULL auto_increment, `domain` varchar(4...

VB2008 with MySQLConnector .net 6.1

I have the following code: Imports MySql.Data.MySqlClient Imports MySql.Data.Types Public Class FormMain Private Sub FormMain_Load ... ' Open Database Dim objMySQL As New MySqlConnection End Sub End Class I get the compiler error message: error BC30560: "MySqlConnection" ist im Namespace "MySql.Data.MySqlClient" nicht eindeu...

Slow response to database write from php

Hello All, I have my PHP scripts running on the WAMP server. Here's what i am doing PHP script A that queries the database and gets a set of rows (I have set the set_time_limit (0) // unlimited time for the script to execute ) Based on the result set I execute a tcl script for each row of the result set The TCL script takes about a...

hierarchical category SELECT query in mysql

This is my table: CREATE TABLE IF NOT EXISTS `Category` ( `Name` varchar(25) NOT NULL, `lft` INT UNSIGNED NOT NULL, `rgt` INT UNSIGNED NOT NULL, `CategoryId` int UNSIGNED auto_increment NOT NULL, PRIMARY KEY (`CategoryId`) ) Engine = InnoDb; I have a url that looks like this: products.php?category=5 From the category id I need to re...

Should I move columns on a wide mysql table to the left if they are used often?

I have a mysql user table that is around 35 columns wide, I notice that the things I query for most often, picture_url, username, onlinestatus are located on the far right side of the table. Would it be better performance to have the most used items be at the very beginning of the table? ...

Normalised beyond my SQL knowledge?

I think I've normalised my database beyond my SQL knowledge. :) Here's a query I'm struggling with from my Snooker league web app. It calculates overall stats for high breaks for a team from all seasons. In the example team id 3. select max(break_score) maxBreak,avg(break_score) avgBreak, count(break_score) breaks from breaks join m...

Using 'OR' between HAVING and WHERE clause in MySQL?

I am trying to fetch records in MySQL using a simple used submitted field. More precisely, the user inputs a name (firstname or lastname or fullname) and the server should return matched rows. What I am doing so far is something like: SELECT * FROM people WHERE firstname LIKE '%user_submitted_data%' OR lastname LIKE '%user_sub...