mysql

Quering and writing to a file : errorcode 28

hi, I am getting this error when I try to query the database and write results to a file. It never occured before. java.sql.SQLException: Error writing file '/tmp/MYwADPLw' (Errcode: 28) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) a...

Retrieving values from MySQL

I have a very simple table that contains a list of 'victims' and the corresponding number of that type destroyed. I'm trying to make an output page of this information, using this code: foreach( $victims as $vic ) { $hits = mysql_query("SELECT amount FROM victims WHERE victim = ".$vi...

How to fetch the id of the table to be stored

I had two table restaurant_orders with fields order_id, restaurant_id, tot_amt where order_id is (Autoinc, primary) order_menus with fields order_id, menu_id, quantity when the user clicks submit button the record will be stored at restaurant_orders with the field order_id is automatically generated. Also a record will be stored at...

What's the most elegant way to retrieve a MySQL table comment?

I'm trying to programmatically retrieve the comment for a MySQL table. The first method suggested to me was: $shown = $db->query('show create table ' . TABLE_NAME)->fetch_row(); preg_match("/COMMENT='(.*)'/", $shown[0], $m); $comment = $m[1]; But that kind of workaround makes me cringe. I stumbled upon another way: $result = $db->que...

mysql inner join statement and memory overuse !

hey guys im running bellow query to fetch from 3 mysql tables and it works fine but it increases memory usage and sometime it takes 10 seconds to start loading the page INNER JOIN table_tags AS ntg ON (ns.tags = '' OR CONCAT(' ',COALESCE(ns.tags,' '),' ') LIKE CONCAT('% ',ntg.tid,' %')) INNER JOIN table_topics AS nto ON (ns.asso...

Benefits of a 64 bit Machine for Hosting your Web Application

Hello all, I am about to buy a server, before I do so, I just wanted to ask the SO community on its thoughts of a 32 bit server or a 64 bit server and its advatnages on a LAMP stack. 1) Is it difficult to install Apache, MySQL and PHP on a 64 bit machine or is it the same as a 32 bit machine? 2) Are there any performance gains of havi...

Speeding up query with join, where, group by, and count.

I have an elementary query that is taking too long to execute even on small tables (<100,000 rows): select images.classification, count(boxes.id) from images join boxes on images.id = boxes.image_id where boxes.round = 0 group by images.classification; I have indices on boxes.round, boxes.image_id, and images.classification (only varc...

Ruby-mysql gem suddenly broken with uninitialized constant error message

I've been working on a ruby module that interacts with mysql on my server. I need to fetch and write data to the database, and it's been working fine for a while now. However, today I created a new file trying to access the database and it wouldn't run. When I tried to execute it (with command: ruby mysql.rb), I received the following er...

Removing bidirectional duplicates in MySQL

I'm modifying phpBB's table to have bidirectional relationships for friends. Unfortuntately, people that have already added friends have created duplicate rows: user1 user2 friend 2 3 true 3 2 true 2 4 true So I'd like to remove rows 1 and 2 from the example above. Currently, this is my query bu...

Bad results due to left joins

I am trying to get all my post where Vote IPAddress doesnt match mine own. Here is my SQL. The problem is, i get the post i voted for when someone else also voted for it. I thought this code says select post when left join is possible and there are no IPAddr which == mine. But that doesnt seem to be happening. How do i write this? selec...

Mysql Custom select query

Hello, I have these 2 mysql queryes SELECT `name` AS name1,`id` AS id1 FROM `table` WHERE id=1 SELECT `name` AS name2,`id` AS name2 FROM `table` WHERE id=2 The results will be outputed like this NAME1 ID1 john 1 NAME2 ID2 meg 2 Is there anyway that from 2 queries I would make 1 and it will show all results on 1 line from sam...

Using MYSQL DB, unit testing with HSQLDB = Problems

I recently modified a MYSQL query using the keyword RLIKE however when I ran the unit test which uses HSQLDB in order to mock a database it throws an unknown SQL token exception. In order to resolve this currently I have commented out these tests, but would like to find a work around so I can actually test my code. My google-fu faile...

Rewriting mysql select to reduce time and writing tmp to disk

I have a mysql query that's taking several minutes which isn't very good as it's used to create a web page. Three tables are used: poster_data contains information on individual posters. poster_categories lists all the categories (movies, art, etc) while poster_prodcat lists the posterid number and the categories it can be in e.g. one p...

Split php string into 2 variables

I have to split a field with an abundance of text into 2 variables, display one in one section of the page and then carry on with the remainder of the field in another spot. Any ideas? Edit: Thanks for the input. I have no delimiter available as the user inputs the text via FCKeditor..is it possible to get the length of the inputted te...

Stored Procedure Ignoring DELIMITER change?

Hi All - I'm running into some problems trying to import a Stored Procedure into a database running on my local MAMP server. This is the procedure in question: DELIMITER $$ CREATE PROCEDURE `ZipRangeFinder`( zipbase VARCHAR(15), rangex NUMERIC (15), inter NUMERIC(15), start NUMERIC(15), rows NUMERIC(15) ) B...

Need help optimizing a MySQL query - JOINs not using the correct indexes

I have this query below that I've rewritten a dozen different ways, yet I am unable to get it optimized and loaded in under 8 seconds. If I can get it to 2s, that would be good. 1s or less would be optimal. This query retrieves a list of books that are currently available for sale or trade, and performs a bit of filtering. This query...

Is the join query correct?

I have multiple tables that I want to join to get a specific result. Tables are: ad_categories, ad_image, ad_data, ad_location I need a row output for every one of the following rows in the ad_categories table, regardless if there exists data for any of these categories for a specific location when using the WHERE conditional. mysq...

Why can't I display the result of this mysql query in PHP?

$query = mysql_query("SELECT sum(rating) FROM stars WHERE post_id = 20"); echo $query; It outputs: Resource id #3 instead of 8, which is what the actual sum is. ...

iPhone - Accessing MySql Server??

I am basically trying to retrieve some data from a MySql server, and display them on my iphone Let's say i have a table named "car" and i need to send 10 rows of my table to my iphone. 1- Is there any possible way to send an actual array of objects "cars" to my iphone. Or does it have to be a string. 2- If it has to be a string, what i...

Force timeout on a MysqlQuery from PHP

Is there a way to force Mysql from PHP to kill a query if it didn't return within a certain time frame? I sometimes see expensive queries running for hours (obviously by this time HTTP connection timed out or the user has left). Once enough such queries accumulate it starts to affect the overall performance badly. ...