mysql

MySQL warning of Unknown column that exists

Having found out how to find PHP problems. I am now trying to solve them, but I have no clue on this one. I made use of mysql_error and I have found: 1054: Unknown column 'o.user_id' in 'on clause' Is there something wrong with this: $sql="SELECT o.*, u.user_name, u.email, od.artist_id,cm.nexchange_price FROM ".$tableprefix."...

How can I use MAX and LIMIT together in MySQL

I have a table with the following structure: ID | Color 1 | red 2 | blue 3 | yellow 8 | purple 10| green . . . 100|yellow I would like to be able to grab the MAX ID value for the first 5 rows. For example, I would like to do something like so: Select MAX(ID) From Table LIMIT 5 Hoping this would return the value 10 But, MySQL ke...

MySql Query browers results

If you execute a query you get line that looks like this: 658 rows fetched in 0.0110s (0.0003s) What does the numbers in parenthesis refer to? I assume the 0.0110s is how long it took to run the query ...

Mysql store procedure performance

Hi How is store procedure's performance? Is it worth using them or implement a complex query in PHP-MySQL call? Regards ...

I need to auto_increment a field in MySQL that is not primary key

Hey everyone, Right now, I have a table whose primary key is an auto_increment field. However, I need to set the primary key as username, date (to ensure that there cannot be a duplicate username with a date). I need the auto_increment field, however, in order to make changes to row information (adding and deleting). What is normally ...

What are the optimum varchar sizes for MySQL?

How does MySQL store a varchar field? Can I assume that the following pattern represents sensible storage sizes : 1,2,4,8,16,32,64,128,255 (max) A clarification via example. Lets say I have a varchar field of 20 characters. Does MySQL when creating this field, basically reserve space for 32 bytes(not sure if they are bytes or not) but ...

SQL join multiple values from column into one cell

There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy. Current output MemberID MemberName FruitName -------------- --------------------- --...

Merging MySQL Query results with PHP

I have a table of Calendar event information (Date, username). I want to print the following information for each "event" for a selected month in a table, using PHP and MySQL: SELECT DAYOFWEEK( eventDate ) AS DAY , YEAR( eventDate ) AS YEAR, `primary` , secondary, weekend FROM eventcal WHERE region = 4 AND MONTH( eventDate ) =7 O...

Escaping only what is necessary, is that possible?

I am working with a team of developers on a website. The website will be using classes. I am in charge of creating the data access layer for the classes. There is an understanding that all user input will be escaped upon retrieval (from the post or get). Having little control over the input level (unless I personally review everyone's co...

jdbc4 CommunicationsException

I have a machine running a java app talking to a mysql instance running on the same instance. the app uses jdbc4 drivers from mysql. I keep getting com.mysql.jdbc.exceptions.jdbc4.CommunicationsException at random times. Here is the whole message. Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.ex...

Select All Items In A Table That Do Not Appear In A Foreign Key of Another Table

Take for example an application which has users, each of which can be in exactly one group. If we want to SELECT the list of groups which have no members, what would be the correct SQL? I keep feeling like I'm just about to grasp the query, and then it disappears again. Bonus points - given the alternative senario, where it's a many t...

How to convert MyISAM to InnoDB

I'm using MySQL 5, and my database experienced some corruption last week. The technicians at my web host managed to save it, but the tables are all converted from the original InnoDB to MyISAM. Currently, some functions in my eCommerce application does not work. Is there a easy way for me to convert them back to InnoDB again? I can't f...

PHP- Getting yesterdays data from the database

I want to get yesterdays entries in the database. Any ideas on what query I should run? Its been blowing my mind for the last hour! mysql_query("SELECT id , fullname , address1 , address2 , citytown , postcode , telno , email , property_value , on_market , agent , asking_price , reason , total_secured_debt , price_concession , FROM_UNI...

Resolve MySQL error when changing engine from MyISAM to InnoDB

My database experienced some corruption last week, and the technicians from the web hosting company changed all my tables to MyISAM and performed repair on those tables. However, my application requires InnoDB tables. I've tried the method mentioned in this website However, I get this message #1025 - Error on rename of './user_db/#sql...

How come posting to PHP causes error in IE, but not in other browsers?

I have some PHP code that calls MySQL that works in Firefox and other browsers, but IE doesn't seem to handle it. <?php include "casti/mysql_connect.php"; $result = mysql_query("SELECT * FROM ".$_POST['table']." WHERE id='".$_POST['id']."'"); $row = mysql_fetch_array( $result ); // Line 60 ! echo $row['title']; ?> And here is what ...

PHP MySQL Alphabetical Linking

I have a huge table full of records and with PHP I'm making a seperate page for each one. On the page I want to interlink them alphatbetically, for example the page 'Dunn' would have links like this in the sidebar: Darren Dave Doom Dunn Dupp Duss Dutt Always with 3 links either side going up and down the alphabet. All the data is goi...

Create a matrix/table from a LEFT JOIN in SQL

I'd like to know a good solution for converting a LEFT JOIN into a table/matrix. For example, given this schema: objects id (integer) name (string) attributes id (integer) object_id (integer) name (string) value (string) And these values: 1,ball 2,box 1,1,colour,red 2,1,shape,sphere 3,1,material,rubber 4,2,colo...

mysql limit inside group?

I want to limit the size of records inside a group, and here is my trial, how to do it right? mysql> select * from accounts limit 5 group by type; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right...

Inverted beta in MySQL

I need to implement an inverted beta function in MySQL (similar to Excel's BETAINV). There is some related material is available on Wolfram MathWorld's Beta Distribution page. Any clues on where to start implementing this functionality in MySQL? ...

MySQL - Need help counting id's that correspond to certain in other tables

Hi, I have on table named players, then other tables named tries, conversions, penalties, dropgoals. I need to get the player_id from players, then count the following: number of tries for player_id in tries number of conversions for player_id in conversions number of penalties for player_id in penalties number of dropgoals for player...