php/sql query

[Wildthing]

I have a simple test table in mysql

fields
 

name, booked,
3 entries

 

nick, 1

john, 1

nick, 1


I want to echo out the name with the least amounts of booked sum
so output should be john

can anyone help ?

 

[PravinS]

Use MIN function in mysql to fetch least amount

 

Refer: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min

[Jessica]

You'd need to group them by name, get the sum of each group, then using min.

[Wildthing]

mysql> select rep_surname, AVG(booked) from appointments group by rep_surname;
+-------------+-------------+
| rep_surname | AVG(booked) |
+-------------+-------------+
| nick             |      1.0000 |
| john             |      1.0000 |
+-------------+-------------+
2 rows in set (0.00 sec)
 

But there are 3 rows all all, John has 1 more entry

 

Did I miss something ? I am not real good at this

[Barand]

SELECT name, COUNT(*) as tot

FROM table

GROUP BY name

ORDER BY tot

LIMIT 1

[Wildthing]

So far I have

 

$result = mysql_query("SELECT rep_surname, COUNT(*) as tot  FROM appointments  GROUP BY rep_surname  ORDER BY tot");
 

 

Need it to echo the rep_name with the least amounts of "1" in booked field

kind of stuck

[Jessica]

order by DESC and LIMIT 1

[Barand]

order by DESC and LIMIT 1

that gives highest first

[Jessica]

I misread, I thought he wanted the highest. Lowest first obviously would be ASC then

[Barand]

I gave him the query, can't  understand what there is left to be stuck on

[Jessica]

I feel ya

[Wildthing]

Doing my head in

lol

 

 

$result = mysql_query("SELECT rep_surname, COUNT(*) as tot  FROM appointments  GROUP BY rep_surname  order by ASC LIMIT 1");

echo $row['rep_surname'];
---------------------------------------------

 

error in apache log

PHP Notice:  Undefined variable: row in /home/wildthin/public_html/grabbs/test.html on line 10
 

[mac_gyver]

the error means that you didn't fetch the data from the result set.

 

before you can use php/mysql for your data, you must first learn how to use php/myqsl for any data. you need to read a basic php/mysql tutorial/book so that you understand the steps needed to form an sql query statement, run that query, check if the query executed without any errors, and then fetch any resultant data from that query.