Glitch in php or count() in mysql?



  • 1. Select more than just *
    Is it possible to run a query like this: SELECT *, DATEDIFF(CURDATE(), `updated`) AS `daysSinceUpdate` FROM `Admin` Or should I let PHP calculate the days since update?
  • 2. deleting all records over 6 months old
    I want to delete all records that are 6 months older than when the script is run. Here is my attempt, it gets a syntax error. DELETE from tasks where completed < date_sub('now()', interval 6 months) I have tried lots of variations on the theme, still can't get it right. completed is a datetime column What is the correct syntax ? bill
  • 3. Count lines in select
    I have a table t1: id int unique added timestamp and second table t2: eid int id int unique are IDs from t1. t2.eid with the same value can be found in t2 more than for one entry but they will all have different IDs. I want to: find all EID that have IDs added after some DATE. Result should count how many of these IDs are there for the same EID. Result should be of the form (this is example): EID | counts -------+----------- 39 | 3 45 | 1 which means that EID 39 has 3 IDs in t1 that were added after DATE and 45 has only 1. How to do that?

Re: Glitch in php or count() in mysql?

Postby Christian Kirsch » Sun, 23 Oct 2005 22:18:24 GMT

Race condition? If user 1 increments the $msgid and user 2 selects
count(*) before user 1 has added her message -> duplicate entries for
this $msgid. You can avoid this by putting a unique constraint on your
message ID. Which you should have done anyway ... Or using transactions.
Or autoincrement. Or you could, of course, re-invent other wheels.


Similar Threads:

1.Glitch in php or count() in mysql?

X-Followup: comp.lang.php

I have a PHP script that adds messages to a simple MySQL Database.
(PHP 5.0.3, MySQL 4.1.1)

One of the fields it stores is msgid.
The new msgid is a count of all current msgs in the db plus one

$query = 'select count(*) from messagesdb;';
$result = mysql_query ($query, $conn);
$msgid = mysql_result ($result, 'count(*)') + 1;

The next message is added using the above msgid.
For some reason (there are NO other scripts/systems accessing this 
table, it is all on a local testmachine) I now have about 200 messages 
in the system, but some id's occur more than once, up to 4 times.

I cannot explain this behaviour. I know I could avoid the entire issue 
by autonumbering the messages, but still there is something funny going on.

I am wondering, is php messing up, or is this a mysql glitch, or am I 
missing something here ?

Thanks for your time

2.What is more efficient - using mysql_num_rows or COUNT(key) - php+mysql

As in the subject - what is more efficient way to get number of
affected rows - using mysql_num_rows or just ind the query SELECT
COUNT(key) FROM tabale WHERE='conditions'? Which one is quicker?

Best regards,

3.MySQL Count() question.. (mysql newb)

Hi there,

I have a small question:
I have a table with lots of rows in it. Of course all have a different id,
but each can be assigned to a certain category. Categories correspond
with 1 - 10. 

So a row looks like this:  'unique id  |   4'

What i want is to do a count how much rows belong to a certain  category.
Is it possible to do the following (from 1 query)

    echo $counted_rows['4']; 

Which sould return the number of rows with category '4';

Or is there anything similar if this is wrong?

Any help is great!!  :)

4.:mysql::st execute failed: Column count doesn't match value count at row 1 a

5.Count(*) different than record count

Can someone explain why the following query will return 80 records:

SELECT DISTINCT c.caseid FROM cases c LEFT JOIN casespecialty cs ON c.caseid 
= cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid WHERE (cs.uid 
= 86 OR es.uid = 86)

but when c.caseid is wrapped with Count() the query will return 225:

SELECT DISTINCT Count(c.caseid) FROM cases c LEFT JOIN casespecialty cs ON 
c.caseid = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid 
WHERE (cs.uid = 86 OR es.uid = 86)

The correct answer in this case is 80. I'm looking for Count() to return the 
correct (desired) total.

6. "Column count doesn't match value count at row 1"

7. COUNT(t.column_name) vs. COUNT(t.*)

8. Glossary - First Glitch

Return to mysql


Who is online

Users browsing this forum: No registered users and 48 guest