Barand

Which is it?

According to your data, none of the teams in the matches table have a corresponding team_catid in the catID table. Is this a possible situation?

You peeped! Your connection code is using the obsolete mysql_ library. Use mysqli_ or PDO connection

Make the primary key (pet_type, pet_id) where the pet_id is auto_increment. (This increments it within the pet type) EG mysql> CREATE TABLE `pet` ( -> `user_id` int(11) DEFAULT NULL, -> `pet_type` varchar(45) NOT NULL, -> `pet_id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`pet_type`,`pet_id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.34 sec) mysql> INSERT INTO pet (user_id, pet_type) VALUES -> (1, 'cat'), -> (2, 'cat'), -> (2, 'dog'), -> (3, 'cat'), -> (4, 'dog'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM pet; +---------+----------+--------+ | user_id | pet_type | pet_id | +---------+----------+--------+ | 1 | cat | 1 | | 2 | cat | 2 | | 2 | dog | 1 | | 3 | cat | 3 | | 4 | dog | 2 | +---------+----------+--------+

Two methods. Loop through the query results 1 ) When the occupation changes, start a new table (hence the order by occupation) or 2 ) store the results in an array indexed by occupation This is the second method (you may peep if you get stuck) And this is the first method (again, no peeping)

1 ) GROUP BY foccup will give you one row for each value of foccup. Use ORDER BY. 2 ) Functions beginning with mysql_ are obsolete and have been removed from PHP. Use PDO (or mysqli_ functions)

Welcome to a fellow fortyniner.

A prerequisite to writing a working query is knowledge of the table structures and relationships. So how about providing that information?

Best way is to sidestep the problem and use a datepicker component. Then you are not at the mercy of whatever format a user wants to enter.

If your table headings show the occupation, why the occupation column in the table?

It's the short term memory that goes fir ...... what was I saying?

It's guaranteed with my code - the hour is the array key

Even assembler - treated as an address (array name equivalent) and an offset (index equivalent) from that address

Provided that you can spell "February", only two of those formats do not work $dates = [ 'feb 21, 1999', 'February 21, 1999', '02/21/99', '2/21/99', '99/2/21', '2-21-1999', '19990221', 'sun, Feb 21, 1999', 'Sunday February 21, 1999' ]; echo '<pre>'; foreach ($dates as $dstr) { printf("%-30s%-15s%s\n", $dstr, date('Y-m-d', strtotime($dstr)), strtotime($dstr)==0 ? 'X' : '' ); } Outputs feb 21, 1999 1999-02-21 February 21, 1999 1999-02-21 02/21/99 1999-02-21 2/21/99 1999-02-21 99/2/21 1970-01-01 X 2-21-1999 1970-01-01 X 19990221 1999-02-21 sun, Feb 21, 1999 1999-02-21 Sunday February 21, 1999 1999-02-21 For rogue formats you can always use DateTime::createFromFormat()

Each time the status changes you need to store the status and timestamp in a new record. You may even want to store old_status|new_status|timestamp. In addition, each record will need the id of whatever the status refers to.

But the only table reference by that query is "match_summ" (the one you are inserting into). As mentioned, what are you trying to do?

What error message? Give us a clue.

Have you tried it without the WHERE line? That will give all scores with ranks. Is $udid numeric? If not, it needs to be inside single quotes. (Really it shouldn't be in the query at all, it should be a prepared query with a placeholder for the udid parameter. But that's another lesson.

Sorry - I have no a crystal ball.

You can do something like this my data mysql> select * from score; +-------+-------+ | name | score | +-------+-------+ | Bob | 75 | | David | 106 | | Jane | 75 | | Joe | 61 | | Mary | 59 | | Mike | 61 | | Sam | 76 | +-------+-------+ 7 rows in set (0.00 sec) query SELECT a.name , a.score , COUNT(b.name)+1 as rank , tot FROM score a JOIN (SELECT COUNT(name) as tot FROM score) as total LEFT JOIN score b ON b.score > a.score WHERE a.name = 'Jane' -- OPTIONAL GROUP BY a.name ORDER BY a.score DESC; +------+-------+------+-----+ | name | score | rank | tot | +------+-------+------+-----+ | Jane | 75 | 3 | 7 | +------+-------+------+-----+

The only query you have posted will only return a single result with a count in it, so sorting one record does not make sense. What are you trying to achieve?

You would have to assign log records to a specific goal. So you would store the id of a goal record in each log record. +-----------+ | user | +-----------+ | userid |--+ | name | | | etc | | +-----------+ | | | +----------+ | | goal | | +----------+ | | goalid |---+ +-------<| userid | | | weighloss| | | units | | | whenby | | | etc | | +----------+ | | | +-------------+ | | tracklog | | +-------------+ | | userid | +-------<| goalid | | calories | | etc | +-------------+

Regarding CASE statements, yes you can use them in an insert. BUT whether the one you propose is feasible is impossible to say without knowing what you are trying to accomplish. Where do "home_id", "away_id" and "l_team_id" come from in that statement?

So there could be old tracklog records for a user that were targetted against a previous goal. At present, the calories burned in these would also be included against the latest goal.

You shouldn't be putting $userid in the query string. It looks as though the actual query being executed is SELECT COUNT(udid) AS `rank` FROM myTable WHERE score > ( SELECT score from myTable WHERE udid = myLongUdidStringHere Because "myLongUdidStringHere" isn't in quotes, sql treats it as a column name. Try function getUserRanks($udid, $conn) { $sql = "SELECT COUNT(udid) AS `rank` FROM myTable WHERE score > ( SELECT score from myTable WHERE udid = :udid )"; $stmt = $conn->prepare($sql); $stmt->execute( [ ':udid' => $udid ] ); $ranks = $stmt->fetchObject(); return $ranks->rank; }