bubblegum.anarchy

yes. no it can't.

Just think of indexing the same way as foreign keys are applied in oracle with the difference being that there are no actual cascading rules implemented, unless the innoDB storage engine is used in the table create statement. Using the index on what would normally be a foreign key in oracle just improves the linking speed of joined tables in mysql.

You have questions.. and each question is unique so start with a table of questions something like: CREATE TABLE question ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique identifier', content VARCHAR(255) NOT NULL DEFAULT 'This question is undefined' COMMENT 'unique question', PRIMARY KEY (id), UNIQUE KEY (content) ) TYPE=MyISAM; INSERT IGNORE INTO question (content) VALUES ('What is your favourite color?') , ('What is your favourite number?') , ('What is your favourite movie?') , ('What is todays day') , ('What is the current temperature'); now you also want categories that should also be unique, so create a categories table: CREATE TABLE category ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'category unique identifier', content VARCHAR(255) NOT NULL DEFAULT 'This category is undefined' COMMENT 'unique category', PRIMARY KEY (id), UNIQUE KEY (content) ) TYPE=MyISAM; INSERT IGNORE INTO category (content) VALUES ('Favourite Things') , ('About Today'); Now you need to create a table to link a question with a category: CREATE TABLE question_in_category ( category_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'category.id foreign key reference', question_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'question.id foreign key reference', primary key (category_id, question_id) ) TYPE=MyISAM; INSERT IGNORE INTO question_in_category (category_id, question_id) VALUE ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite color?')) , ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite number?')) , ((SELECT id FROM category WHERE content = 'Favourite Things'), (SELECT id FROM question WHERE content = 'What is your favourite movie?')) , ((SELECT id FROM category WHERE content = 'About Today'), (SELECT id FROM question WHERE content = 'What is todays day?')) , ((SELECT id FROM category WHERE content = 'About Today'), (SELECT id FROM question WHERE content = 'What is the current temperature?')); Now to select a random question from a particular category: SELECT question.* FROM question INNER JOIN question_in_category ON question.id = question_id INNER JOIN category ON category_id = category.id AND category.content = 'Favourite Things' ORDER BY RAND() LIMIT 1; This is a complex table structure.

Post the entire code block. You should have something like: $query = "SELECT column FROM table WHERE condition IS TRUE LIMIT 1"; $result = mysql_query($query); $record = mysql_fetch_array($result); Placing the query string where the result identifier variable is supposed to be would produce an error similar to what you have discribed.

no

I have always been under the impression that semi-colons were not allowed at the end of a query inside a string but another member here seemed adamant that the addition of a semi-colon was not erroneous.

Where have you incorporated the code I posted?

The three tables must be joined with something like this: WHERE users.user_id = user_books.user_id AND users.user_id = user_hats.user_id

The PHP mysql_affected_rows() returns the number of rows affected by a query that modifies a table.

This may work but the subquery does not know what t1.word is since the subquery is processed first. WHERE description LIKE concat('%', t1.word, '%')

As fenway suggested, the difference in the amount of weekends during a period will determine how many days without occur during a period... the following is a starting point that will work for a period starting and ending on a weeday that is greater than one week - Try to develop this to cater for weekend starting and ending points as well as periods shorter than one week. SET @date_start := '2007-04-03'; SET @date_end := '2007-04-17'; SELECT datediff(@date_end, @date_start) - round(datediff(@date_end, @date_start) / 7) * 2;

LEFT JOIN

I am not a mysql guru like the ones btherl refers to but looking for a bit of information that is at the end of a table of 1000 records is certainly going to take longer than looking through a table with 10 records, but this rational can't be applied to referencing tables... there is no point in storing uniquely define record in seperate location unless you can have an additional field to destinguish the records and what normalization is all about.

What is the difference of where a search location is as long is the location is known. The visual way you are looking at a folder storage system does not transpose well to how the information is actually stored as finite data... a folder location is stored as seperate bit of information so the actual location becomes a little irrelevant. Don't stop dreaming though, your imagination will serve you well.

The PHP function mysql_num_rows($result_id) will provide the row count after the query is processed.

hmm... possibly: SELECT * FROM ( SELECT ifnull(@n:=@n+points, @n:=1) as points_aggregate, tbl_name.* FROM tbl_name ) AS points_table WHERE points_aggregate > 5000 LIMIT 1

and ORDER BY count DESC

you can certainly lock this thread but no solution... just alternate path.

The associative array index for the $_POST data is formatted as a string and needs to be quoted... so $_POST[Age] should be $_POST['Age'], the same goes for Postcode... remove the ( ) brackets from the post data too... something like this may be more appropriate: $query = "SELECT * FROM PersonalTrainers WHERE Postcode = '".$_POST['Postcode']."' AND Age = '".$_POST['Age']."'"; Remember that AND means that both conditions have to be true for the record to be returned... use an OR instead if you require either one of the values to match.

what about the third, forth, fifth, etc record?

This is somewhat how I handle a dynanic where clause: mysql_query(" SELECT * FROM tbl_name WHERE TRUE ".( !empty($fld_name_1) ? " AND fld_name_1 = '".$fld_name_1."'" : null ).( !empty($fld_name_2) ? " AND fld_name_2 = '".$fld_name_2."'" : null ).( !empty($fld_name_3) ? " AND fld_name_3 = '".$fld_name_3."'" : null ); I use to format the lines like this: mysql_query(" SELECT * FROM tbl_name WHERE TRUE " .( !empty($fld_name_1) ? " AND fld_name_1 = '".$fld_name_1."'" : null ) .( !empty($fld_name_2) ? " AND fld_name_2 = '".$fld_name_2."'" : null ) .( !empty($fld_name_3) ? " AND fld_name_3 = '".$fld_name_3."'" : null );

This is the best I could come up with: SELECT ifnull(@n:=@n + 1, @n:=1), tablename.* FROM tablename LIMIT 20;

sorry, I am lost now - do you want all the information from player_stats grouped by the player_statsID prepended with a oolumn counter that resets every 20 records?

pretty much just replace the internal double quotes with single quotes.

Forget the REPLACE untill you have the SELECT returning the information required. At a glance, I do not see any joining of the tables player_stats and counttotwenty.