database *count

[bimmer528]

Hey all.

Is there a manual, script, or could someone tell me how to do these things?

I would like to develop a 'member points' system for my web site.

1. Where can I find out about counting each field of a giving database table then multiplying that in the last $myrow of a table?

2. My site requires usernames and such which I would like the php to fetch each username *instead of there id # and return database info about them in each row where as the its counts posts, stories, downloads, ect and adds the up in the last row.

the script would aslo have to connect to differant tables of the database..

[hand]

Since I'm here this is the first thread I really can't understand. Is this because I'm not a native speaker? 😕

[cgraz]

Here's a link to count()

To select a username rather than id, you'd still need to select more than just a username field if you're going to be displaying other info about htem.

$query = mysql_query("SELECT * FROM table WHERE identifier='$them'");

while($row=mysql_fetch_array($query)) {
   print "your username is " . $row["username"] . "<br>\n";
   print "Field 1: " . $row["field1"] . "<br>\n";
   print "Field 2: " . $row["field2"] . "<br>\n";
   print "Field 3: " . $row["field3"] . "<br>\n";

}

This will print out all the results. replace table with your table name, and identfier and them with some kind of uniqu way to identify each record in your database (usually an id set to auto increment).

Replace $row["field_name"] with the field name in your database that you want to display.

For more on this: look at the SELECT syntax

Cgraz

[bimmer528]

Thanks cgraz, lots of usefull info here.

Ok, let me try and get this strait!

Say I would like to pull this info out of my database

Example here..

1. I would like to pull posts (field name=posts) from the forum table.
2. I would like to pull stories(field name=stories) out of the news table
3. I would like to pull Money Donated (Field name=mdonated) out of the money_table.
   Counting rows from different tables in one query:

//Using 3 queries in the same string here.

$query = mysql_query("SELECT * FROM forum WHERE identifier='$id'")("SELECT * FROM news WHERE identifier='$id'")("SELECT * FROM money_table WHERE identifier='$id'");

//Print posts from the forum table
//Print stories from the news table
//Print money donated from the money_table

while($row=mysql_fetch_array($query)) {
   print "your username is " . $row["username"] . "<br>\n";
   print "Field 1: " . $row["posts"] . "<br>\n";
   print "Field 2: " . $row["stories"] . "<br>\n";
   print "Field 3: " . $row["mdonated"] . "<br>\n";

}

Also when using count, I cannot pull information from a field that is null? Why is that?

[cgraz]

$query = mysql_query("SELECT FROM forum WHERE identifier='$id'")("SELECT FROM news WHERE identifier='$id'")("SELECT * FROM money_table WHERE identifier='$id'");

is invalid.

1. I would like to pull posts (field name=posts) from the forum table.
2. I would like to pull stories(field name=stories) out of the news table
3. I would like to pull Money Donated (Field name=mdonated) out of the money_table.
Counting rows from different tables in one query:

$query = mysql_query("SELECT posts.forum, stories.news, mdonated.money_table");

while($row=mysql_fetch_array($query)) {
   print "Field 1: " . $row["posts"] . "<br>\n";
   print "Field 2: " . $row["stories"] . "<br>\n";
   print "Field 3: " . $row["mdonated"] . "<br>\n";

}

Tht should work, but I can't remember if you need to do

$query = mysql_query("SELECT posts.forum FROM forum, stories.news FROM news, mdonated.money_table FROM money_table");

I don't think you do since you're not abbreviating the table names, but if the first one doesn't work, use this query instead.

Cgraz

[bimmer528]

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /usr/local/plesk/apache/vhosts/soulcowboy.clansite.org/httpdocs/user_points/points_total.php on line 9

Which is this line

while($row=mysql_fetch_array($query)) {

also tried this

while ($myrow = mysql_fetch_row($result)) {

[cgraz]

No, $query is right. while($row=mysql_fetch_array($query)) { uses mysql_fetch_array, your error uses mysql_fetch_row, did using fetch array print not a valid mysql result resource also, or just when you used while ($myrow = mysql_fetch_row($result)) {

Try adding mysql_error() after the query to see what it prints out.

$query = mysql_query("SELECT posts.forum FROM forum, stories.news FROM news, mdonated.money_table FROM money_table") or die(mysql_error());

Surprisingly enough, I have never done a joined query like this but it should work. Maybe someone else can offer a bit more experience here.

[bimmer528]

Well I just put together a simple script here to fetch 1 id from 3 differant tables and I get this error

You have an error in your SQL syntax near 'FROM xoops_bb_forums, total.points FROM points' at line 1

It really seems like mysql is touchy.. I put together a script to fetch all rows from a table but I had to have this

$result = mysql_query("SELECT * FROM points",$db);

I count not just have this

$result = mysql_query("SELECT * FROM points");

anyway here is the code in full

require_once('include/mysql.php'); 

$query = mysql_query("SELECT xoops_users.uname FROM xoops_users, forum_posts.xoops_bb_forums FROM xoops_bb_forums, total.points FROM points") 
or die(mysql_error());

while($row=mysql_fetch_array($query)) {
   print "Field 1: " . $row["uname"] . "<br>\n";
   print "Field 2: " . $row["forum_posts"] . "<br>\n";
   print "Field 3: " . $row["total"] . "<br>\n";

}

[bimmer528]

I am doing a query in phymyadmin.. here we go..

error with

SELECT uname.xoops_users FROM xoops_users

error with

SELECT uname.xoops_users

query ok with

SELECT uname FROM_xoops_users

[cgraz]

Maybe

$query = mysql_query("SELECT posts FROM forum, stories FROM news, mdonated FROM money_table") or die(mysql_error());

[diego25]

Why do you have so many FROMs? (Sorry, I didn't read the entire thread...)

Diego

[cgraz]

this is what he wants to do

1. I would like to pull posts (field name=posts) from the forum table.
2. I would like to pull stories(field name=stories) out of the news table
3. I would like to pull Money Donated (Field name=mdonated) out of the money_table.

And i thought you could use

$query = mysql_query("SELECT posts.forum, stories.news, mdonated.money_table");

or if that didn't work

$query = mysql_query("SELECT posts.forum FROM forum, stories.news FROM news, mdonated.money_table FROM money_table");

but that wasn't working for him. It's about time someone else jumps in. I've never done selections from more than one table, just read about it a little a long time ago, we could really use some assistance here. . . 😉

[bimmer528]

I have been reading on the links that cgraz gave me and and this should work np

$query = mysql_query("SELECT posts.forum, stories.news, mdonated.money_table");

but why when I query it, there is an error?

The site is hosted on a unix server if that has anything to do with it?

Also another thing

I can SELECT a table and query 6 fields that prints $myrow [1] but I get an error when $myrow ["field_name"]