mysql

Can [My]SQL combine multiple overlapping composite indexes?

Basically, if I have a table with composite indices (colA, colB, colC) and (colB, colD, colE), is MySQL able to take advantage of their overlap on colB to combine them and speed up a query involving colA, colB and colD, even if there is no single index covering these three particular columns? I tried using EXPLAIN on a test case like th...

help with count with left join

hello I have 2 tables 1st table called services has id_service, name, date. 2nd table called services_images has id_img, img_name, id_service. What I wanna do is get list of services with a row called num_images that counts how many images there are per service. I tried this SELECT COUNT(services_images.id_img) as num_images, serv...

Zend PDO error - how can I debug it?

I have a weird problem using Zend Framework and Mysql. I generated a query: SELECT events.idUser, szForename, szLastname, readers.szName, idZoneFrom, events.dtTime FROM events, users, readers WHERE events.idUser = users.idUser AND events.idReader = readers.idReader AND dtTime >= '2010:02:15 0:00:00' AND dtTime < '2010:02:16 0:00:00' ORD...

Conditional of a loop within a loop is not matching

I have two databases, each containing email addresses of users. I've written a script that has the sole purpose of seeing which email addresses exist in both databases. With that being said, please look at the following loops. Each loop contains hundreds of records (I've printed out the results from each as a verification). However,...

Trying to get the path to a node with PHP & MySQL

Hello, I'm trying to build a breadcrumb here and I'm having some trouble doing it. The problem arises from the fact that I have to save category name (in two languages), slug and id. My categories table looks like this: CREATE TABLE `categories` ( `category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(2...

Setting a Value by assuming column in a Table MySQL/PHP

Hey i am having a table with 8 column and following values 1 2 3 4 5 6 7 8 0 1 0 0 0 0 0 0 i need a query which will analyze the no of 1's and 0s in the column and if no of 0 is greater than 4 then it should print failed else pass,how can i do that? either by MYSQL or PHP? ...

MySQL add time to column

Bit of a puzzle for you guys. I can come up with blunt workaround (below) but I thought it would present it to you all to see if there was a more elegant solution out there. Keep in mind I'm looking for a PHP or MySql only solution. Tables is 'candles2'. I have a MySQL TEXT column 'time' that has a datestamp in it ( e.g. 2010.08.13 19:3...

SERIAL-like INT column

I have an app where depending on the type of transaction being added or updated, the ticket number may or may not increment. I can't use a SERIAL datatype for ticket number because it would increment regardless of the transaction type, so I defined ticket number as an INT. So in a multi-user environment if user A is adding or updating a ...

Installing problem for MySQL-Python

I'm trying to install MySQL-python (MySQLdb) to start on the Django Project. Can anyone help me below. Many thanks. I'm running on Mac OSX (snow leapord, 10.6.4) Python 2.7 and MySQL 5.1 and MySQLdb 1.2.3. I got below error because I tried to compile it in 32-bit. I got the error at such: Traceback (most recent call last): File "<...

PHP, making MySQL statements and the use of quotes

After setting up a mysqli object in php, i want to make to insert some POST variables into a table, but I have a question about how the quotes will work out: $sql = "INSERT INTO whatever (a, b, c) VALUES ('$_POST[a]','$_POST[b]','$_POST[c]')"; I'm aware, however, that most times I've used global variables like POST or GET, there are ...

sql query returns error when using limit

hi, why does this query work? how am i supposed to write it? select * from tbl_content order by year desc where visible = '1' limit 0,30; ...

MySQL values limit to 1 and 0

Hello guys I'm just a newbie so sorry for my question :) I already tried using char, tinyint, bool and CHECK constraint. Here's my CHECK constraint: CHECK (user_type>=0 AND user_type<=1) But still I can insert values more than 1, all I want is just 1s and 0s. I'll use it as type of my users. Although I can validate this in front-en...

return multiple arrays from a query

i have 2 tables 1st table called services has id_service, name, date. description 2nd table called services_images has id_img, img_name,id_service Let's say now that I have to, with one query (if possible), return 2 arrays 1st array with fields from one specific id from table "services" 2nd array with fields from all the images relat...

How to convert the following MySQL schema into CouchDB?

I am not sure how I can design the following problem in CouchDB. I have a logger web app that keeps track of how many items are in a warehouse. To simplify the problem we just need to know the total number items currently in warehouse and how long is each item stays in warehouse before it ships. Lets say the warehouse only have shoes bu...

read an xml file into database

Hey I want to store the contents of an xml file in the database. Is there an easy way to do it? Can i write some script that can do the task for me? The schema of the XML file looks like this: <schedule start="20100727120000 +0530" stop="20100727160000 +0530" ch_id="0210.CHNAME.in"> <title>Title_info</title> <date>20100727</d...

Extracting Browser Element from User Agent

I'm still working on the whole gathering user data from a visit thing, so I'm hung up on grabbing the actual name of the browser being used by the visitor. I have this code, but it seems that after execution, $browser is empty. $userAgent = mysql_real_escape_string($_SERVER["HTTP_USER_AGENT"]); $browser = get_browser($userAgent, tru...

Page including with mysql in php

I have a nice page including system here is the code for it if(isset($HTTP_GET_VARS['mod'])) { $page = $HTTP_GET_VARS['mod']; } else { $page = 'home'; } switch($page) { case 'home': require('home.php'); break; default: echo('Error: There is no file on this server with that name'...

Adding a new URL to the database using PHP & MySQL.

So far when I create a new sub category the url only grabs the new sub category but not the parent category for example if I create a new artists sub category of leonardo-da-vinci the new sub category url will be stored as, leonardo-da-vinci Output: http://www.example.com/leonardo-da-vinci when it should be stored as. arts-enterta...

Any easy to use php mysql datagrid?

Hi Anyone know of a FREE and easy to use PHP MySQL Datagrid class? I have used rGrid and Eyesis but need something like phpdatagrid. No requirement of AJAX features though. Just need basic features inbuilt like search by columns, pagination with page numbers(with jump to page dropdown), how many rows to show per page, multiple delete etc...

How to display categories and end-less sub categories using PHP & MySQL?

I was wondering what is the best way to display 100's of categories using PHP & MySQL? For example, it would not be wise to create a folder for each category or sub category. And how would I create my php page to display categories and end-less sub categories? And how would my url look like exactly for example with multiple sub catego...