LFS Forum - MySQL COUNT help ( i think)

#1 - franky500

MySQL COUNT help ( i think)

Sat, 1 Dec 2007 19:06

Hey guys.

I was wondering if somebody can help me out on what i "think" is simple for most of you guys.. but for me. a whole new ball game.

ok heres what im trying to do:

I am trying to be able to retrieve a total ammount of entries in a particular table.

EG: i want to sum up the total number of colums which have "test4" as an entry to it to then be able to output to a web page as a number... so if there are 30 entries in that column and 7 of them entries are "test4" then i simply want to have "7" displayed on the php webpage.

I hope some of what i have posted makes sense.

Attached images

#2 - Ian.H

Sat, 1 Dec 2007 19:17

You're almost there Franky...

SELECT COUNT(*) AS total FROM `TABLE_NAME` WHERE `COLUMN_NAME` = 'search_query'

Using your screenshot, something like:

SELECT COUNT(*) AS total FROM `your_table_name` WHERE `GAME_ID` = 'TC1704.......'

This will result in an array / object element (depending on your coding style) named 'total' containing the number of records found.. as you're expecting.

HTH

Regards,

Ian

#3 - franky500

Sat, 1 Dec 2007 19:24

Hey Ian, Thanks,

I'm assuming however that i am doing something completely and uterly wrong:

here is what i have in my php doc:

<?php


/* Database connection information */
$hostname = "localhost";
$username = "*****";
$password = "***";
$dbname = "*****";

SELECT COUNT(*) AS total FROM `tc_services` WHERE `GAME_ID` = 'TC1704400877836';
}

?>

which gives me the error:

Parse error: syntax error, unexpected T_STRING in test.php on line 10

I have a feeling im doing this completely wrong lol.

Any ideas?

Thanks

EDIT: i think im an idiot... Any chance you could whip something up for me ?. i think i'm trying to think to much.. dont you think? /me goes to brush up on basic PHP...

#4 - SamH

Sat, 1 Dec 2007 19:54

I don't do PHP, but I believe it'll be something like this..

<?php
 
 
/* Database connection information */
$hostname = "localhost";
$username = "*****";
$password = "***";
$dbname = "*****";
 
mysql_connect( $hostname, $username, $password );
mysql_select_db( $dbname );
$query = mysql_query( "SELECT COUNT(*) AS total FROM tc_services WHERE GAME_ID = 'TC1704400877836'" );

/* $query should contain your result */

echo $query;
mysql_close();
?>

#5 - franky500

Sat, 1 Dec 2007 20:10

Getting close. No longer get any errors.. but i dont get the result i was looking for.. all i get from that is:

Resource id #3

Thanks for the help guys. I'm one step closer

#6 - SamH

Sat, 1 Dec 2007 20:13

I'm assuming you changed the ***** for your user, pass and db name.. yeah?

#7 - AndroidXP

Sat, 1 Dec 2007 20:19

Quote from SamH :I don't do PHP, but I believe it'll be something like this..

It's a long time ago I did PHP, but I think it should look like this:

<?php
 
 
/* Database connection information */
$hostname = "localhost";
$username = "*****";
$password = "***";
$dbname = "*****";
 
mysql_connect( $hostname, $username, $password );
mysql_select_db( $dbname );
$query = mysql_query( "SELECT COUNT(*) AS total FROM tc_services WHERE GAME_ID = 'TC1704400877836'" );

/* $query should contain your result */

$count = mysql_result($query, 0, "total");
echo $count;

mysql_free_result($query);

mysql_close();
?>

As far as I know, the return value of mysql_query() is a result set, containing one or multiple rows with the columns you specified in the select. There are also other mysql_ methods to loop through multiple rows (mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_object, mysql_fetch_row).

-

(thisnameistaken) DELETED by thisnameistaken : Beaten!

Sat, 1 Dec 2007 20:20

#8 - SamH

Sat, 1 Dec 2007 20:22

Corr.. it's beginning to look easier and quicker in ASP

#9 - filur

Sat, 1 Dec 2007 20:32



<?php 
echo mysql_fetch_object(mysql_query($sql))->total;
?>

#10 - franky500

Sat, 1 Dec 2007 21:32

Your all stars, That one got it Android. and i have learnt something new. Cheers for the help guys.

Can anybody recommend anywhere that i can look up for tutorials... maybe a beginners guide to php / mysql. or perhaps i should skip that chapter and look at another coding language (any suggestions?)

Thanks for all the help again guys. massively appreciated.

#11 - mcgas001

Sat, 1 Dec 2007 21:55

Quote from franky500 :Your all stars, That one got it Android. and i have learnt something new. Cheers for the help guys.

Can anybody recommend anywhere that i can look up for tutorials... maybe a beginners guide to php / mysql. or perhaps i should skip that chapter and look at another coding language (any suggestions?)

Thanks for all the help again guys. massively appreciated.

Whats your goal ?, if its PHP and MySql i have a great PDF file that will help you

#12 - SamH

Sat, 1 Dec 2007 21:59

Quote from filur :



<?php 
echo mysql_fetch_object(mysql_query($sql))->total;
?>

Shut up, you!

#13 - franky500

Sat, 1 Dec 2007 22:01

Quote from mcgas001 :Whats your goal ?, if its PHP and MySql i have a great PDF file that will help you

Well i currently only tend to use HTML and chuck in a php script here and there (normally found from a script site of sorts...) so yeah. PHP / Mysql would suit me nicely.

#14 - mcgas001

Sat, 1 Dec 2007 22:12

Quote from franky500 :Well i currently only tend to use HTML and chuck in a php script here and there (normally found from a script site of sorts...) so yeah. PHP / Mysql would suit me nicely.

Check your P.M's

#15 - franky500

Sat, 1 Dec 2007 22:19

Quote from mcgas001 :Check your P.M's

Cheers, Much appreciated

#16 - mcgas001

Sun, 2 Dec 2007 00:08

Quote from franky500 :Cheers, Much appreciated

Np

Hope it helps

#17 - Ian.H

Sun, 2 Dec 2007 09:50

Hi franky..

Quote from franky500 :Hey Ian, Thanks,

I'm assuming however that i am doing something completely and uterly wrong:

here is what i have in my php doc:

<?php


/* Database connection information */
$hostname = "localhost";
$username = "*****";
$password = "***";
$dbname = "*****";

SELECT COUNT(*) AS total FROM `tc_services` WHERE `GAME_ID` = 'TC1704400877836';
}

?>

which gives me the error:

Parse error: syntax error, unexpected T_STRING in test.php on line 10

I have a feeling im doing this completely wrong lol.

Any ideas?

Thanks

EDIT: i think im an idiot... Any chance you could whip something up for me ?. i think i'm trying to think to much.. dont you think? /me goes to brush up on basic PHP...

Looks like you have everything sorted(?) from the follow-up posts. Sorry for the vagueness, I literally just posted the SQL statement, rather than adding the PHP code for it too.

Just for future reference if you're interested, the error you got basically means that the PHP script contains what it's interpreting as a string (ie: no function calls etc) but it's not enclosed with either single or double quotes (not that just adding quotes in this case would have got you the result you were after as you've probably worked out by now

). PHP's error handling / reporting isn't the most intuitive.. one thing I'm not keen about on the language.. even after 7 years of using it.. you just get used to working around it

If you do still have any problems.. feel free to drop me a PM and I'll try n help ya

Regards,

Ian