mysql

Sort flat list, group by parents

I have a flat list of pages in a mySQL Table. ID | Tab index | Page Name | parent Pages can be children of another page, specified by the "parent" property. The whole list is sorted using the "Tab index" int column. Can anybody think of a way to query this list so that Items are ordered by Tabindex Items that are children of a...

MySQL joining four tables and using WEEK and DATEDIFF to get current data?

Hi! I'm trying to write a query getting information from four tables and I'm not able to figure it out. Can someone please help me with some guidance? The model is that a user can book a room for a specified time period. I would like to retrieve the data that is current for the next seven days so I'm using WEEK and DATEDIFF. The DATEDI...

MySQL Blob not showing up correctly.

Hi I asked a similar question earlier about retrieving an image from a MySQL database Blob. I am using code as: header("Content-type: image/jpeg"); // change it to the right extension print $image; //where image is the blob file This code is nested inside of a webpage, so should I still modify the header? When I include the header...

Java Exception Error - Sqlite preparedStatement.setBlob

I'm placing and image into a databse, it could be eitehr an MYSQL database (the Server) or an SQLITE database (a Tablet PC for on the road). The Java application synchs with the server on a dialy basis, uploading new data and downloading any new data. Thats working terrirfic, the requirement is for it to be able to handle images as well....

PHP Ban Visitor & Insert into mySQL DB.

I have the below code that i am wanting to into certain files so that when someone visits this "certain" file they get banned if they are not allready. but for some reason it is not adding new visitors into the database, if i add a user manually it works fine and echo's Banned! but otherwise it just echo's the $sql query but does not ac...

Why does the balance become 1.00 whenever the sql command below is executed no matter what value of $cost is?

Why does the balance become 1.00 whenever the sql command below is executed no matter what value of $cost is? update account set balance=balance-'$cost' and username='steven'" The value of balance is bigger than $cost initially, after the execution, the balance of 'steven' becomes 1.00. What's wrong? Say, when the initial balance is...

sort the "rollup" in group by

I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause. Is there any way to order by the way I want as well as calculate the sub-totals? CREATE TABLE `mygroup` ( `id` int(11) default NULL, `country` varchar(100) default NULL ) ENGINE=MyISAM ; INSERT INTO `mygroup` VALU...

Can i execute a single query on 2 database?

I am using php and mysql. i have a question, lets say, I have a userid column in users table under database A, and userid column in purchases table under database B. Can I execute a single query, using innerjoin, to get value from 2 databases? Or I must use multiple queries? Oh ya, if let say, i have this variable: $conn // connect t...

Why is the table filled with Array[0], Array[1],Array[2],Array[3] after the following SQL statement is executed?

Why is the table filled with Array[0], Array[1], Array[2], Array[3] after the following SQL statement is executed? mysql_query("INSERT INTO choicetable (announcementid, question, option1, option2, option3, option4) VALUES ('$announcementid', '$choicequestion[$j]', '$option[$j][0]', '$option[$j][1]', '$option[$j][2]', '$option[$j][3...

How to connect 2 databases in php and mysql?

I have 2 databases. Users database and purchases database. Each database has different user and password. If i want to execute a query to call both databases. How do I connect the databases? $db = mysql_select_db(??????); ...

How to make python_select work for '$>python' command?

I installed a couple of pythons in different versions with macports, and the apple python 2.6 is also working. Now I need to run a program which requires MySQLdb package support in python, and this package was installed to the python I installed by macports. The program tells me that there is no MySQLdb installed, so I guess it is the ap...

Upload 95Gb csv file into MySQL MyISAM table via Load data infile: CSV engine an alternative?

Hi guys, I'm trying to upload a 95Gb csv file into a MySQL database (MySQL 5.1.36) via the following command: CREATE TABLE MOD13Q1 ( rid INT UNSIGNED NOT NULL AUTO_INCREMENT, gid MEDIUMINT(6) UNSIGNED NOT NULL , yr SMALLINT(4) UNSIGNED NOT NULL , dyyr SMALLINT(4) UNSIGNED NOT NULL , ndvi DECIMAL(7,4) NOT NULL comment 'NA value is 9', r...

PHP Populating an Array from a MySql Query

I am looking to create an array of data to be pass to another function that is populated from a database query and am not sure how to do this. $dataArray[0][1]; $qry = mysql_query("SELECT Id, name FROM users"); while($res = mysql_fetch_array($qry)) { $dataArray[$res['Id']][$res['name']] } Thanks in advance. ...

Order by IF EXISTS

Is there a way to order by column if it actually exists? ie: SELECT * ORDER BY IF(EXISTS(order_column), order_column ASC, name_column DESC) Thank you! ...

How to return all result rows resulting from a SQL statement execution to the page where the AJAX request came from

How to fetch all result rows in MySQL+PHP? You know, if I use mysql_fetch_assoc() , it only returns one result row as an associative array. I am using AJAX to fetch the data in a MySQL table. $result=mysql_query("select * from questions where announcementid='$taskid'")or die(mysql_error()); How to return the value of $result which i...

select and echo a single field from mysql db using PHP

Im trying to select the title column from a particular row $eventid = $_GET['id']; $field = $_GET['field']; $result = mysql_query("SELECT $field FROM `events` WHERE `id` = '$eventid' "); echo $result; all i get is Resource id #19 How should i do this? What is best method? ...

postgreSQL group by different from mysql?

I've been migrating some of my mySQL queries to postgreSQL to use Heroku... most of my queries work fine, but I keep having a similar recurring error when I use group by: ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function Could someone could tell me what I'm doing wrong? MySQL which works 100%...

Transferring all rows of a MySQL table to another.

I want to bulk insert all rows from one table to another. I am confused on how to use Select with Insert. Is there a way that a new table is automatically created if it does not exist? ...

mysql problem, there is data in ib_logfile0 and ib_logfile1 but not in the mysql database.

My innodb_flush_log_at_trx_commit is 2, but whenever I examine the ib_logfile the data is present but when i query the database the data is not there e.g. if i do a insert 'xyz' into the db a couple of 1000 times all of the entries are there in the ib_logfile but some of them are not there in the db, when retrieved by a query. So all 100...

Optimize simple update on table

Hi All, I am running following query in a stored proceudre and it is taking 30 milliseconds to execute. Can anyone help me out to optimize this query: Table Definition is: Create Table Customer ( CustID int not null auto_increment, CustProdID int, TimeStamp DateTime, primary key(CustID) ); Update Customer Set TimeStamp =...