[UPDATE on changed fields]

mysql_affected_rows() just returns an integer count right?

 

yes.

 

It doesn't / cannot specify which records changed?

 

no it can't.

[setting foreign keys]

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.

[MySQL Help Please]

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.

[Inner Join of six tables query not running from php form?]

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.

[UPDATE on changed fields]

no

[Inner Join of six tables query not running from php form?]

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.

[How to select data from 3 tables]

Where have you incorporated the code I posted?

[How to select data from 3 tables]

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

[UPDATE on changed fields]

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

[Referencing Other Fields in Select using Like...]

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, '%')

[Using only Weekdays and Excluding Weekends when calculating dates]

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;

[distinct ? group by ?]

LEFT JOIN

[Nested tables]

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.

[Nested tables]

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.

[Somewhat Simple Count(*) question...]

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

[[SOLVED] I don't know]

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

[Modal Value]

and ORDER BY count DESC

[str_to_date issues]

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

[[SOLVED] PHP/MySQL Query Question]

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.

[Is there a way to group by text that can appear in multiple fields]

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

[[SOLVED] MYSQL Search]

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 );

[REPLACE .. INTO issue]

This is the best I could come up with:

 

SELECT ifnull(@n:=@n + 1, @n:=1), tablename.* FROM tablename LIMIT 20;

 

[REPLACE .. INTO issue]

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?

[[SOLVED] MYSQL CASE statement within PHP syntax or correct quoting ?]

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

[REPLACE .. INTO issue]

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.